Linux运维部落
postgresql介绍
编辑
2022-10-20
pgsql
00

目录

postgresql简介
一、pgsql架构和原理
1. 存储结构
2. 物理存储结构
3. pgsql和各数据库性能对比
4. pgsql的优势
二、 pgsql安装和初始化等操作
1. 安装脚本
2. 初始化数据库等操作

postgresql简介

PostgreSQL 是当前功能最强大的开源的关系型数据库系统,支持跨平台的多种操作系统, 基于C语言开 发。通常简称为PG或PGSQL。

PostgreSQL 宣称是世界上最先进的开源数据库。PostgreSQL的狂热者认为它的性能缺Oracle不分上 下，而且没有高成本的负担。

一、pgsql架构和原理

pgsql.jpg

1. 存储结构

PG数据存储结构分为：逻辑存储结构和物理存储存储。其中：逻辑存储结构是内部的组织和管理数据的方式；物理存储结构是操作系统中组织和管理数据的方式。

  1. 逻辑存储结构

  • 数据库集簇是数据库对象的集合。

  • 在关系数据库理论中，数据库对象是用于存储或引用数据的数据结构。

  • 表就是一个例子，还有索引、序列、视图、函数等这些对象。

  • 在PostgreSQL中，数据库本身也是数据库对象，并且在逻辑上彼此分离。

  • 除数据库之外的其他数据库对象（例如表、索引等）都属于它们各自的数据库，虽然它们隶属于同一个数据库集簇，但也无法直接从集簇中的一个数据库访问集簇中的另一个数据库对象。

  • 数据库本身也是数据库对象，一个数据库集簇可以包含多个Database、多个User、每个Database以及Database中的所有对象都有它们的所有者：User。

    1）创建一个Database时，会为这个Database创建一个名为public的默认Schema，每个Database可以有多个Schema。

    2）在这个数据库中创建其他数据库对象时，如果没有指定Schema，都会在public这个Schema中。

    3）Schema可以理解为一个数据库中的命名空间，在数据库中创建的所有对象都在Schema中创建。

    4）一个用户可以从同一个客户端连接中访问不同的Schema。

    5）不同的Schema中可以有多个相同的名称的Table、Index、View、Sequence、Function等数据库对象。

2. 物理存储结构

  1. 数据库的文件默认保存在initdb时创建的数据目录中。
  2. 在数据目录中有很多类型、功能不同的目录和文件。
  3. 除了数据文件之外，还有参数文件、控制文件、数据库运行日志及预写日志等。
  4. 数据目录
sh
#数据库初始化创建的目录下还会生成如下一些子目录
base #默认表空间的目录,每个数据库都对应一个base目录下的子目录,每个表和索引对应一个独立文件
global #这个目录对应pg_global表空间,存放实例中的共享对象
pg_clog #存储事务提交状态数据
pg_bba.conf #数据库访问控制文件
pg_log #数据库系统日志目录，在查询一些系统错误时就可查看此目录下日志文件。(根据配置定义,可能没
有这个目录)
pg_xact #提交日志commit log的目录,pg 9之前叫pg_clog
pg_multixact #共享行锁的事务状态数据
pg_notify #异步消息相关的状态数据pg_serial #串行隔离级别的事务状态数据
pg_snapshots #存储执行了事务snapshot导出的状态数据pg_stat_tmp #统计信息的临时文件
pg_subtrans #子事务状态数据
pg_stat #统计信息的存储目录。关闭服务时,将pg_stat_tmp目录中的内容移动至此目录实现保存
pg_stat_tmp #统计信息的临时存储目录。开启数据库时存放统计信息
pg_tblsp #存储了指向各个用户自建表空间实际目录的链接文件
pg_twophase#使用两阶段提交功能时分布式事务的存储目录
pg_wal #WAL日志的目录,早期版一本目录为pg_xlog
PG_VERSION #数据库版本
postmaster.opts #记录数据库启动时的命令行选项
postmaster.pid #数据库启动的主进程信息文件，包括PID，SPGDATA目录,数据库启动时间，监听端口，
socket文件路径，临听地址,共享内存的地址信息(ipsc可查看),主进程状态

3. pgsql和各数据库性能对比

数据库硬件TPS
OracleDELL R910 CPU : Intel(R) Xeon(R) CPU E7530 @ 1.87GHz 四路48线程 MEM : 32* 4G 128G 存储 : FusionIO 640G MLC稳定值2000,峰值2600
MySQL Percona 5.1.60-13.1 修改版DELL R910 CPU : Intel(R) Xeon(R) CPU E7530 @ 1.87GHz 四路48线程 MEM : 32* 4G 128G 存储 : FusionIO 640G MLC峰值12004，谷值0， 均值9504
PostgreSQL 9.3.1IBM x3850 X5 CPU : Intel(R) Xeon(R) CPU X7560 @ 2.27GHz 四路32线程 内存 : 8 * 8GB 64G 存储：OCZ RevoDrive3X2 480GB稳定值 24487

4. pgsql的优势

  • PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，并且正在实现新的功能以兼容最新的SQL标准 SQL2003。
  • 稳定可靠: PostgreSQL是唯一能做到数据零丢失的开源数据库。有报道称国外的部分银行也在使用PostgreSQL数据库。
  • 开源免费: PostgreSQL数据库是开源的、免费的，而且是 BSD协议，在使用和二次开发上基本没有限制。
  • 支持广泛: PostgreSQL数据库支持大量的主流开发语言，包括C、C++、Perl、Python 、Java、Tcl，PHP等。
  • PostgreSQL社区活跃: PostgreSQL基本上每三个月推出一个补丁版本，这意味着已知的 BUG很快会被修复，有应用场景的需求也会及时得到响应。

二、 pgsql安装和初始化等操作

使用源安装不推荐，因为版本都比较旧，一般都是二进制安装和编译安装

1. 安装脚本

sh
#!/bin/bash
#
#********************************************************************
#Author:            zhangwei
#QQ:                200957079
#Date:              2022-10-23
#FileName:          install_postgresql.sh
#URL:               http://vanblog.ztunan.top
#Description:       The test script
#Copyright (C):     2022 All rights reserved
#********************************************************************

POSTGRESQL_VERSION=14.2
INSTALL_DIR=/apps/pgsql
DATA_DIR=/pgsql/data
DB_USER=postgres
CPUS=`lscpu |awk '/^CPU\(s\)/{print $2}'`
. /etc/os-release

color () {
    RES_COL=60
    MOVE_TO_COL="echo -en \\033[${RES_COL}G"
    SETCOLOR_SUCCESS="echo -en \\033[1;32m"
    SETCOLOR_FAILURE="echo -en \\033[1;31m"
    SETCOLOR_WARNING="echo -en \\033[1;33m"
    SETCOLOR_NORMAL="echo -en \E[0m"
    echo -n "$1" && $MOVE_TO_COL
    echo -n "["
    if [ $2 = "success" -o $2 = "0" ] ;then
        ${SETCOLOR_SUCCESS}
        echo -n $"  OK  "    
    elif [ $2 = "failure" -o $2 = "1"  ] ;then 
        ${SETCOLOR_FAILURE}
        echo -n $"FAILED"
    else
        ${SETCOLOR_WARNING}
        echo -n $"WARNING"
    fi
    ${SETCOLOR_NORMAL}
    echo -n "]"
    echo 
}


install_postgresql () {
    if [ $ID = 'centos' -o $ID = 'rocky' ];then
	    yum install -y  gcc make readline-devel zlib-devel
	elif [ $ID = 'ubuntu' ];then
	    apt update
	    apt install -y  gcc make libreadline-dev zlib1g-dev
	else
	    color "不支持此操作系统，退出!" 1
	    exit
	fi
    if [ ! -f  postgresql-${POSTGRESQL_VERSION}.tar.gz ] ;then
	    wget https://ftp.postgresql.org/pub/source/v12.9/postgresql-${POSTGRESQL_VERSION}.tar.gz
	fi
    tar xf postgresql-${POSTGRESQL_VERSION}.tar.gz
    cd postgresql-${POSTGRESQL_VERSION}
     ./configure --prefix=${INSTALL_DIR}
    make -j $CPUS world
    make install-world
    
    useradd -s /bin/bash -m -d /home/$DB_USER  $DB_USER
    
    mkdir ${DATA_DIR} -pv
    chown -R $DB_USER.$DB_USER ${DATA_DIR}/
    
    cat > /etc/profile.d/pgsql.sh <<EOF
export PGHOME=${INSTALL_DIR}
export PATH=${INSTALL_DIR}/bin/:\$PATH
export PGDATA=/pgsql/data
export PGUSER=postgres
export MANPATH=${INSTALL_DIR}/share/man:$MANPATH

alias pgstart="pg_ctl -D ${DATA_DIR} start"
alias pgstop="pg_ctl -D ${DATA_DIR} stop"
alias pgrestart="pg_ctl -D ${DATA_DIR} restart"
alias pgrestatus="pg_ctl -D ${DATA_DIR} status"
EOF
    
    su - $DB_USER -c 'initdb'
    
}
start_service () {
    cat > /lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
User=postgres
Group=postgres

ExecStart=${INSTALL_DIR}/bin/postmaster -D ${DATA_DIR}
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

EOF
    systemctl daemon-reload
	systemctl enable --now postgresql.service
	systemctl is-active postgresql.service
	if [ $? -eq 0 ] ;then 
        color "PostgreSQL 安装成功!" 0  
    else 
        color "PostgreSQL 安装失败!" 1
        exit 1
    fi   
}


install_postgresql
start_service

注意

PostgreSQL默认不支持 以root 身份启动服务,虽然也可修改源码实现root启动,但基于安全考虑不建议，因此必须创建一个用于启动PostgrepSQL的普通用户。脚本里有一条useradd命令就是创建pgsql用户的。

2. 初始化数据库等操作

  • 初始化数据库
sh
su - postgres
#初始化
initdb
initdb -D $PGDATA
#如果没有指定选项 -D <datadir> ,按环境变量$PGDATA指定的路径进行初始化
#生产建议初始化方式
initdb -A md5 -D $PGDATA -E utf8 --locale=C -U postgres -W
-A #指定local connections默认的身份验证方法
-D #指定数据目录
-E #指定字符集
--locale=C #指定语言环境
-U  #指定数据库superuser用户名
-W  #指定数据库superuser的用户密码
  • 启动和停止
sh
#启动
pg_ctl -D /pgsql/data -l logfile start
postgres -D /pgsql/data &
#停止数据库的命令如下:
pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
#其中-m是指定数据库的停止方法，有以下三种:
smart:等所有的连接中止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库。

fast :快速关闭数据库，断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。相当于Oracle数据库关闭时的immediate模式。此为默认值,建议使用

immediate:立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要进行恢复。相当于Oracle数据库关闭时的 abort模式

#smart关闭
pg_ctl stop -D /pgsql/data/  -ms
#fast关闭,推荐使用,也是默认模式
pg_ctl stop -D /pgsql/data/  -mf
#immediate 相当于kill -9
pg_ctl stop -D /pgsql/data/  -mi
#或者发送信号,直接向数据库主进程发送的signal 信号有以下三种。
SIGTERM:发送此信号为Smart Shutdown关机模式。
SIGINT:发送此信号为Fast Shutdown关机模式。
SIGQUIT:发送此信号为Immediate Shutdown关机模式。
#重启
pg_ctl restart -mf
#源码目录中内置PostgreSQL的启动脚本
postgresql-12.9/contrib/start-scripts/linux

#也可将脚本写入开机启动
Ubuntu：
[root@ubuntu2004 ~]#cat /etc/rc.local
#!/bin/bash
su - postgres  -c "/apps/pgsql/bin/pg_ctl -l logfile start"
#/etc/init.d/postgresql start

CentOS：
[root@rocky8 ~]#cp postgresql-12.9/contrib/start-scripts/linux
/etc/init.d/postgresql
[root@rocky8 ~]#chmod +x /etc/init.d/postgresql
[root@rocky8 ~]#chkconfig --add postgresql
[root@rocky8 ~]#vim /etc/init.d/postgresql
#修改下面两行
prefix=/apps/pgsql
PGDATA="/pgsql/data"
[root@rocky8 ~]#service postgresql start
如果对你有用的话，可以打赏哦
打赏
ali pay
wechat pay

本文作者:笑一个吧~

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 本文为博主「笑一个吧~」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 许可协议。转载请注明出处！