• 【postgresql 数据库运维文档】

    目录

    pg单机和主从部署

    安装前准备 （master和standby）

    节点信息

    节点名称操作系统ip内存cpu核数postgresql版本
    pg-masterCentos7.6192.168.233.308G4核postgresql-11.13.tar.gz
    pg-standbyCentos7.6192.168.233.318G4核postgresql-11.13.tar.gz

    关闭firewall

    systemctl stop firewalld.service #停止firewall

systemctl disable firewalld.service #禁止firewall开机启动

systemctl mask firewalld #屏蔽FirewallD服务

systemctl stop firewalld #停用FirewallD服务

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    安装iptables防火墙

    yum -y install iptables-services #安装

vi /etc/sysconfig/iptables #编辑防火墙配置文件
#增加这个放通5432端口
-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

systemctl restart iptables.service #最后重启防火墙使配置生效

systemctl enable iptables.service #设置防火墙开机启动

/usr/libexec/iptables/iptables.init restart #重启防火墙

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    关闭SELINUX

    vi /etc/selinux/config
SELINUX=disabled  # 这里置为disabled
# SELINUXTYPE=targeted # 这里备注掉

setenforce 0 #使配置立即生效

    • 1
    • 2
    • 3
    • 4
    • 5

    修改主机名

    hostname pg-master

hostnamectl set-hostname pg-master

vi /etc/hostname #编辑配置文件
pg-master


vi /etc/hosts #编辑配置文件
127.0.0.1   localhost pg-master
192.168.233.30 pg-master
192.168.233.31 pg-standby

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    hostname pg-standby

hostnamectl set-hostname pg-standby

vi /etc/hostname #编辑配置文件
pg-standby


vi /etc/hosts #编辑配置文件
127.0.0.1   localhost pg-standby
192.168.233.30 pg-master
192.168.233.31 pg-standby

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    下载软件包

    PostgreSQL版本：postgresql-11.13.tar.gz

    安装(master和standby)

    安装编译工具包

    yum -y install tcl tcl-devel uuid-devel perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake gcc* readline-devel

    • 1

    编译安装postgresql

    mkdir -p /usr/local/pgsql #创建安装目录

mkdir -p /usr/local/pgsql/data #创建数据库存放目录

cd /usr/local/src

rz #上传源码包

tar zxvf postgresql-11.13.tar.gz #解压

cd postgresql-11.13

./configure --prefix=/usr/local/pgsql --with-openssl --with-pgport=5432 --with-tcl --with-perl --with-python --with-libxml --with-libxslt --with-ossp-uuid --with-pam --with-ldap

gmake world #gmake包括第三方插件全部编译

gmake install-world #包括第三方插件全部安装

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    配置postgresql主数据库(master)

    创建运行用户

    #创建PostgreSQL运行用户，PostgreSQL不允许使用root用户运行服务
#创建用户群组postgres
groupadd postgres

useradd -g postgres postgres #创建用户postgres，并加入postgres组

    • 1
    • 2
    • 3
    • 4
    • 5

    设置目录权限

    chown postgres.postgres -R /usr/local/pgsql

chown postgres.postgres -R /usr/local/pgsql/data

    • 1
    • 2
    • 3

    把postgresql加入系统环境变量

    vi /etc/profile
#添加如下4行内容
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
export PATH=$PATH:$PGHOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib

source /etc/profile #使生效

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    初始化数据库

    su - postgres #切换到postgres用户

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 #初始化数据库

    • 1
    • 2
    • 3

    修改数据库配置文件

    vi /usr/local/pgsql/data/postgresql.conf
port = 5432 #端口号
max_connections = 1000 #最大连接数
listen_addresses = '*' #监听本机所有ip，也可以按需设置
wal_log_hints = on
full_page_writes = on

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    启动数据库

    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start #启动

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop #停止

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile restart #重启

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile status #查看状态


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    设置PostgreSQL开机启动

    exit #切换到root

cp /usr/local/src/postgresql-11.13/contrib/start-scripts/linux  /etc/init.d/postgresql #拷贝启动文件

chmod +x /etc/init.d/postgresql #设置运行权限

vi /etc/init.d/postgresql #编辑修改
prefix=/usr/local/pgsql #安装目录
PGDATA="/usr/local/pgsql/data" #数据库存放目录
PGUSER=postgres #运行用户

service postgresql start

service postgresql restart

service postgresql stop

chkconfig postgresql on

vi /usr/lib/systemd/system/postgresql.service #使用Systemd启动
[Unit]
Description=The PostgreSQL Database Server
After=syslog.target
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
ExecStop=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop
ExecRestart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data restart
ExecReload=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data reload
ExecStatus=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status
TimeoutSec=300
[Install]
WantedBy=multi-user.target

systemctl daemon-reload

systemctl start postgresql

systemctl enable postgresql

systemctl restart postgresql

systemctl status postgresql

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    设置postgres的密码

    su - postgres

psql

alter user postgres with password 'postgres';

\q

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    设置数据库访问权限

    vi /usr/local/pgsql/data/pg_hba.conf #添加修改
#添加
host all all 127.0.0.1/32 trust  #允许所有用户从本机免密访问所有数据库
host all postgres 0.0.0.0/0 md5   #允许postgres从任何ip以密码访问所有数据库

systemctl restart postgresql #重启服务

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    创建主从同步账号

    #进入控制台，创建数据库同步用户repl，密码为Aa123456
psql

create role repl with login replication encrypted password 'Aa123456';

\q

#设置数据库主从同步用户权限信息
vi /usr/local/pgsql/data/pg_hba.conf
#添加
host all repl 192.168.233.0/24 trust
host replication repl 192.168.233.0/24 md5

vi /usr/local/pgsql/data/postgresql.conf #修改数据库配置文件信息
wal_level = replica
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql/data/pg_archive/%f'
wal_keep_segments = 10240
wal_sender_timeout = 60s
archive_command = 'gzip < %p > /usr/local/pgsql/data/pg_archive/%f.gz'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    创建归档日志目录

    mkdir -p /usr/local/pgsql/data/pg_archive

systemctl restart postgresql   #最后重启数据库

    • 1
    • 2
    • 3

    配置postgresql从数据库(standby)

    创建运行用户

    groupadd postgres #创建用户群组postgres

useradd -g postgres postgres #创建用户postgres，并加入postgres组

    • 1
    • 2
    • 3

    设置目录权限

    chown postgres.postgres -R /usr/local/pgsql

chown postgres.postgres -R /usr/local/pgsql/data

chmod 0700 /usr/local/pgsql/data

    • 1
    • 2
    • 3
    • 4
    • 5

    把postgresql加入系统环境变量

    vi /etc/profile
#添加
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
export PATH=$PATH:$PGHOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib

source /etc/profile #使生效

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    同步主库的数据文件

    su - postgres #切换到postgres用户

/usr/local/pgsql/bin/pg_basebackup -Fp --progress -D /usr/local/pgsql/data -R -h 192.168.233.30 -p 5432 -U repl --password

    • 1
    • 2
    • 3

    创建recovery.conf文件

    #从模板文件拷贝到data目录
cp /usr/local/pgsql/share/recovery.conf.sample  /usr/local/pgsql/data/recovery.conf

vi /usr/local/pgsql/data/recovery.conf
#去掉备注并修改
standby_mode = on #on为从库
primary_conninfo = 'host=192.168.233.30 port=5432 user=repl password=Aa123456' #对应主库信息
recovery_target_timeline = 'latest' #流复制同步最新数据

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    修改从库postgresql.conf文件

    vi /usr/local/pgsql/data/postgresql.conf #修改从库postgresql.conf文件
max_connections = 1000 #最大连接数
hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
listen_addresses = '*' #监听本机所有ip，也可以按需设置
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #间隔时间
hot_standby_feedback = on #如果有错误的数据复制，是否向主进行反馈

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    启动从数据库

    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start #启动

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop #停止

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile restart #重启

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile status #查看状态

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    设置从库PostgreSQL开机启动

    exit #切回root

cp /usr/local/src/postgresql-11.13/contrib/start-scripts/linux /etc/init.d/postgresql #拷贝启动文件

chmod +x /etc/init.d/postgresql #设置运行权限

vi /etc/init.d/postgresql #编辑修改
prefix=/usr/local/pgsql #安装目录
PGDATA="/usr/local/pgsql/data" #数据库存放目录
PGUSER=postgres #运行用户

service postgresql start

service postgresql restart

service postgresql stop

chkconfig postgresql on

vi /usr/lib/systemd/system/postgresql.service #使用Systemd启动
[Unit]
Description=The PostgreSQL Database Server
After=syslog.target
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
ExecStop=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop
ExecRestart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data restart
ExecReload=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data reload
ExecStatus=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status
TimeoutSec=300
[Install]
WantedBy=multi-user.target

systemctl daemon-reload

systemctl start postgresql

systemctl enable postgresql

systemctl restart postgresql

systemctl status postgresql

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    验证主从配置

    进入主库，查看状态

    su - postgres

psql

select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state 
----------------+------------
 192.168.233.31 | async
(1 row)

\x on
Expanded display is on.

select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 45833
usesysid         | 16384
usename          | repl
application_name | walreceiver
client_addr      | 192.168.233.31
client_hostname  | 
client_port      | 49816
backend_start    | 2022-07-13 08:07:25.080263+00
backend_xmin     | 570
state            | streaming
sent_lsn         | 0/3000140
write_lsn        | 0/3000140
flush_lsn        | 0/3000140
replay_lsn       | 0/3000140
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async

\q

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    测试数据是否同步

    • 在主库建立一个pgtest的数据库
    psql
create database pgtest;
\connect pgtest
\q

    • 1
    • 2
    • 3
    • 4
    • 从库也同步建了一个pgtest的数据库
    su - postgres 

psql
\connect pgtest
\q

    • 1
    • 2
    • 3
    • 4
    • 5

    主从库进程查看

    /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/| grep 'Database cluster state'
Database cluster state:               in production

    • 1
    • 2
    /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/| grep 'Database cluster state'
Database cluster state:               in archive recovery

    • 1
    • 2

    数据库主从切换

    • 配置数据库主从是为了实现高可用，当主库发生故障，我们可以切换从库为主库，继续提供服务

    停止主库(模拟主库故障)

    su - postgres 

systemctl stop postgresql #停止主库

/usr/local/pgsql/bin/pg_ctl stop -m fast #停止主库

ps -ef|grep postgres #查看数据库进程，已经看不到了

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    角色变换(从变主)

    su - postgres

ps -ef|grep postgres #查看进程，数据库正常

/usr/local/pgsql/bin/pg_ctl status #查看数据库状态正常

/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"

/usr/local/pgsql/bin/pg_ctl promote #提升从库为新主库

ps -ef|grep postgres #查看进程，数据库正常

cd /usr/local/pgsql/data/

ls recovery.done

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    • 提升从库为主库之后，可以看到后台进程中没有startup recovering进程了，多了postgres: walwriter 写进程

    • 之前的/usr/local/pgsql/data/recovery.conf文件自动更改为/usr/local/pgsql/data/recovery.done 这是告诉postgresql，我现在不再是从库了，我的身份是主库

    验证新主库是否可以写入数据

    psql

create table test as select 1 id;

\q

    • 1
    • 2
    • 3
    • 4
    • 5

    在新主库上设置主从同步用户权限信息

    #允许新从库(原主库192.168.1.100)可以通过replica用户访问数据库(按理说已经有了，不用做，没有就添加)
vi /usr/local/pgsql/data/pg_hba.conf
## 添加
host replication repl 192.168.233.0/24 md5
host all repl 192.168.233.0/24 trust

    • 1
    • 2
    • 3
    • 4
    • 5

    在新从库创建recovery.conf文件

    • 如果不配置该文件的话，那么原来的主库一旦重新启动话，就将成为了1个新的独立主库，脱离了主从数据库环境
    cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf

vi /usr/local/pgsql/data/recovery.conf
#去掉备注并修改
recovery_target_timeline = 'latest' #流复制同步最新数据
standby_mode = on #on为从库
primary_conninfo = 'host=192.168.233.31 port=5432 user=repl password=Aa123456' #对应主库信息

chmod 0700 /usr/local/pgsql/data #设置权限，重要，否则无法启动

systemctl start postgresql #启动新从库（原主库）数据库

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    版本升级

    小版本升级pg_upgrade

    su - postgres

#将旧的数据库目录重命名
mkdir /usr/local/pgsql.old
chown -R postgres.postgres /usr/local/pgsql.old
mv /usr/local/pgsql/* /usr/local/pgsql.old/

exit #切回root

cd /opt

rz #上传源码包

tar -zxvf postgresql-11.16.tar.gz #解压

cd postgresql-11.16/ #进入到源码目录

./configure --prefix=/usr/local/pgsql --with-openssl --with-pgport=5432 --with-tcl --with-perl --with-python --with-libxml --with-libxslt --with-ossp-uuid --with-pam --with-ldap

gmake world #gmake包括第三方插件全部编译

gmake install-world #包括第三方插件全部安装

mkdir /usr/local/pgsql/data #创建数据目录

chown -R postgres.postgres /usr/local/pgsql

chown -R postgres.postgres /usr/local/pgsql/data #授权数据目录

su - postgres

#初始化数据库
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 #初始化数据库

#如果有外部extension插件则在这安装插件

#关闭旧数据库
/usr/local/pgsql.old/bin/pg_ctl -D /usr/local/pgsql.old/data/ stop -m fast

#环境变量临时赋值本地ip
export PGHOST=127.0.0.1

#升级前检查
/usr/local/pgsql/bin/pg_upgrade -d /usr/local/pgsql.old/data/ -D /usr/local/pgsql/data/ -b /usr/local/pgsql.old/bin/ -B /usr/local/pgsql/bin/ -c

#升级
/usr/local/pgsql/bin/pg_upgrade -d /usr/local/pgsql.old/data/ -D /usr/local/pgsql/data/ -b /usr/local/pgsql.old/bin/ -B /usr/local/pgsql/bin/ 

#收集表的统计信息
./analyze_new_cluster.sh

cd $PGDATA

rm -f pg_hba.conf #删除新的pg_hba.conf
rm -f postgresql.conf #删除新的postgresql.conf

#拷贝旧的pg_hba.conf和postgresql.conf到升级的库
cp /usr/local/pgsql.old/data/pg_hba.conf $PGDATA/
cp /usr/local/pgsql.old/data/postgresql.conf $PGDATA/

#启动数据库
pg_ctl -D $PGDATA start

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 这是个bug，版本升级后，pg_config改变了，会导致后面装外部extension时没有装到指定目录
    #旧版本
/database/postgres11.old/psql/bin/pg_config
BINDIR = /database/postgres11.old/psql/bin
DOCDIR = /database/postgres11.old/psql/share/doc
HTMLDIR = /database/postgres11.old/psql/share/doc
INCLUDEDIR = /database/postgres11.old/psql/include
PKGINCLUDEDIR = /database/postgres11.old/psql/include
INCLUDEDIR-SERVER = /database/postgres11.old/psql/include/server
LIBDIR = /database/postgres11.old/psql/lib
PKGLIBDIR = /database/postgres11.old/psql/lib
LOCALEDIR = /database/postgres11.old/psql/share/locale
MANDIR = /database/postgres11.old/psql/share/man
SHAREDIR = /database/postgres11.old/psql/share
SYSCONFDIR = /database/postgres11.old/psql/etc
PGXS = /database/postgres11.old/psql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/database/postgres11/psql/' '--with-perl' '--with-python'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/database/postgres11/psql/lib',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm 
VERSION = PostgreSQL 11.6

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    #新版本
/database/postgres11/psql/bin/pg_config
BINDIR = /database/postgres11/psql/bin
DOCDIR = /database/postgres11/psql/share/doc/postgresql
HTMLDIR = /database/postgres11/psql/share/doc/postgresql
INCLUDEDIR = /database/postgres11/psql/include
PKGINCLUDEDIR = /database/postgres11/psql/include/postgresql
INCLUDEDIR-SERVER = /database/postgres11/psql/include/postgresql/server
LIBDIR = /database/postgres11/psql/lib
PKGLIBDIR = /database/postgres11/psql/lib/postgresql
LOCALEDIR = /database/postgres11/psql/share/locale
MANDIR = /database/postgres11/psql/share/man
SHAREDIR = /database/postgres11/psql/share/postgresql
SYSCONFDIR = /database/postgres11/psql/etc/postgresql
PGXS = /database/postgres11/psql/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix='
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/lib',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm 
VERSION = PostgreSQL 11.16

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    大版本升级

    逻辑备份旧数据库

    详见：逻辑备份

    安装新版本数据库

    详见：pg单机和主从部署

    创建对应的role和schema和database并且grant

    详见：数据库管理

    在新版本库中进行逻辑恢复

    详见：逻辑恢复

    数据库管理

    简单的psql命令

    [postgres@pg_master ~]$ psql -h 192.168.233.30 -p 5432 -U postgres -d pgtest -E
# -h 主机名 默认读取环境变量 PGHOST  PGHOST默认为当前主机
# -p 端口号 默认读取环境变量 PGPORT  PGPORT默认为5432
# -U 用户名 默认读取环境变量 PGUSER  PGUSER默认为postgres
# -d 数据库名 默认读取环境变量 PGDATABASE PGDATABASE默认为postgres
# -W 强制输入密码 当有配值环境变量 PGPASSWORD 时，无需输入密码，加入该参数后，强制用户登录时输入密码
# -E 回显命令对应的sql语句

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    命令扩展说明
    \l\l+查看数据库（详细信息）
    \d\d+查看所有对象 （详细信息）
    \dn\dn+查看所有模式（详细信息）
    \db\dt+查看所有表（详细信息）
    \df\df+查看所有函数（详细信息）
    \dv\dv+查看所有视图（详细信息）
    \du\du+查看所有用户及权限信息（详细信息）
    \set设置会话级别的参数；设置绑定变量，通过:调用
    \！执行外部命令
    \i执行sql脚本
    \connect以指定用户登入到指定数据库
    \q退出psql

    创建数据库

    CREATE DATABASE name [ [WITH] [OWNER [=] user_name]
               [TEMPLATE [=] template]
               [ENCODING [=] encoding]
               [LC_COLLATE [=] lc_collate]
               [LC_CTYPE [=] lc_ctype]
               [TABLESPACE [=] tablespace]
               [CONNECTION LIMIT [=] connlimit ] ]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • name：要创建的数据库的名称。
    • user_name：拥有新数据库的⽤户的名称，或者使⽤默认所有者（执⾏命令的⽤户）DEFAULT。
    • template：创建新数据库的模板的名称，或者DEFAULT使⽤默认模板的模板（template1）。
    • encoding：在新数据库中使⽤的字符集编码。 指定字符串常量（例如’SQL_ASCII’），整数编码数字或
    • DEFAULT以使⽤默认编码。 有关更多信息，请参⻅字符集⽀持。
    • lc_collate：在新数据库中使⽤的排序规则（LC_COLLATE）。 这会影响应⽤于字符串的排序顺序，例如在使⽤ORDER BY的查询中，以及在⽂本列的索引中使⽤的顺序。 默认设置是使⽤模板数据库的排序规
      则。 有关其他限制，请参⻅“注释”部分。
    • lc_ctype：在新数据库中使⽤的字符分类（LC_CTYPE）。 这会影响字符的分类，例如下，上和数字。 默认值是使⽤模板数据库的字符分类。 有关其他限制，请参⻅下⽂。
    • tablespace：将与新数据库关联的表空间的名称，或者为DEFAULT以使⽤模板数据库的表空间。 该表空间将是⽤于在此数据库中创建的对象的默认表空间。
    • connlimit：可能的最⼤并发连接数。 默认值-1表示没有限制。
    create database etl;  --创建一个etl数据库,其他参数不用配置，直接用模板数据库的即可
drop database etl;  --删除数据库

    • 1
    • 2

    用户管理

    官方文档

    PostgreSQL 11.2 手册：CREATE ROLE

    创建用户组

    --创建管理员组 admin
create role admin;
--创建开发人员用户组 developer
create role developer;
--创建数据装载用户组 dataload
create role dataload;
--创建外部接口用户组 interface
create role interface;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    创建用户

    --创建管理员 pgadmin
create role pgadmin with superuser login password 'pgadminAa123456';
--创建开发用户
create role yuzhenchao with login password 'yzc+Aa123456' connection limit 10 valid until '2023-01-16 00:00:00';
--创建数据装载用户
create role copyload with login password 'copy+Aa123456' connection limit 60 valid until '2023-01-16 00:00:00';
--创建外部接口用户
create role finebi with login password 'finebi+Aa123456' connection limit 20 valid until '2023-01-16 00:00:00';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    用户加入到指定的用户组

    --将pgadmin加入到admin组
alter group admin add user pgadmin;
--将yuzhenchao加入到developer组
alter group developer add user yuzhenchao;
--将copyload加入到dataload组
alter group dataload add user copyload;
--将finebi加入到interface用户组
alter group interface add user finebi;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    创建用户名对应的模式名

    --创建pgadmin对应的模式名pgadmin
create schema pgadmin;
--创建yuzhenchao对应的模式名yuzhenchao
create schema yuzhenchao;
--创建copyload对应的模式名copyload
create schema copyload;
--一般外部接口都只有只读权限，所以不需要给他建单独的模式

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    授权管理

    官方文档

    PostgreSQL 11.2 手册：GRANT

    用户模式映射

    --将pgadmin模式的所有权限授权给pgadmin
grant create,usage on schema pgadmin to pgadmin;
--将yuzhenchao模式的所有权限授权给yuzhenchao
grant create,usage on schema yuzhenchao to yuzhenchao;
--将copyload模式的所有权限授权给copyload
grant create,usage on schema copyload to copyload;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    所有模式公开usage权限

    --将pgadmin模式的usage权限授权给public
grant usage on schema pgadmin to public;
--将yuzhenchao模式的usage权限授权给public
grant usage on schema yuzhenchao to public;
--将copyload模式的usage权限授权给public
grant usage on schema copyload to public;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    回收public模式的create权限

    --任何用户都拥有public模式的所有权限
--出于安全，回收任何用户在public的create权限
revoke create on schema public from public;

    • 1
    • 2
    • 3

    收回函数的执行权限

    /*
 * pg中函数默认公开execute权限
 * 通过pg的基于schema和基于role的默认权限实现
 */

--在schema为pgadmin上创建的任何函数，除定义者外，其他人调用需要显式授权
alter default privileges for role pgadmin revoke execute on functions from public;
--由pgadmin用户创建的任何函数，除定义者外，其他人调用需要显式授权
alter default privileges in schema pgadmin revoke execute on functions from public;

--在schema为yuzhenchao上创建的任何函数，除定义者外，其他人调用需要显式授权
alter default privileges for role yuzhenchao revoke execute on functions from public;
--由yuzhenchao用户创建的任何函数，除定义者外，其他人调用需要显式授权
alter default privileges in schema yuzhenchao revoke execute on functions from public;

--在schema为copyload上创建的任何函数，除定义者外，其他人调用需要显式授权
alter default privileges for role copyload revoke execute on functions from public;
--由copyload用户创建的任何函数，除定义者外，其他人调用需要显式授权
alter default privileges in schema copyload revoke execute on functions from public;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    公开表的select权限（视情况而定）

    /*
 * pg与oracle不同，没有select any table的权限
 * 但是pg有默认权限
 * 通过pg的基于schema和基于role的默认权限实现
 */

--在schema为pgadmin上创建的任何表默认公开select权限
alter default privileges in schema pgadmin grant select on tables to public;
--由pgadmin用户创建的任何表默认公开select权限
alter default privileges for role pgadmin grant select on tables to public;

--在schema为yuzhenchao上创建的任何表默认公开select权限
alter default privileges in schema yuzhenchao grant select on tables to public;
--由yuzhenchao用户创建的任何表默认公开select权限
alter default privileges for role yuzhenchao grant select on tables to public;

--在schema为copyload上创建的任何表默认公开select权限
alter default privileges in schema copyload grant select on tables to public;
--由copyload用户创建的任何表默认公开select权限
alter default privileges for role copyload grant select on tables to public;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    动态sql函数

    /*
 * 为了方便各用户的管理
 * 需要用定义者权限创建动态sql函数
 * 最终由pgadmin用户集中管理
 */

--为pgadmin用户创建sp_exec函数
create or replace function pgadmin.sp_exec(vsql varchar)
 returns void  --返回空
 language plpgsql
 security definer  --定义者权限
as $function$ 
begin
    execute vsql;
end;
$function$
;
--将对应模式的对应模式的函数给对应的模式的拥有者
alter function pgadmin.sp_exec(varchar) owner to pgadmin;
--将对应模式的sp_exec函数授权给定义者和集中用户execute权限
grant execute on function pgadmin.sp_exec(varchar) to pgadmin;

--为yuzhenchao用户创建sp_exec函数
create or replace function yuzhenchao.sp_exec(vsql varchar)
 returns void  --返回空
 language plpgsql
 security definer  --定义者权限
as $function$ 
begin
    execute vsql;
end;
$function$
;
--将对应模式的对应模式的函数给对应的模式的拥有者
alter function yuzhenchao.sp_exec(varchar) owner to yuzhenchao;
--将对应模式的sp_exec函数授权给定义者和集中用户execute权限
grant execute on function yuzhenchao.sp_exec(varchar) to yuzhenchao,pgadmin;

--为copyload用户创建sp_exec函数
create or replace function copyload.sp_exec(vsql varchar)
 returns void  --返回空
 language plpgsql
 security definer  --定义者权限
as $function$ 
begin
    execute vsql;
end;
$function$
;
--将对应模式的对应模式的函数给对应的模式的拥有者
alter function copyload.sp_exec(varchar) owner to copyload;
--将对应模式的sp_exec函数授权给定义者和集中用户execute权限
grant execute on function copyload.sp_exec(varchar) to copyload,pgadmin;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53

    集中管理函数

    create or replace function pgadmin.sp_execsql(exec_sql character varying,exec_user character varying)
 returns void
 language plpgsql
 security definer
as $function$ 
/* 作者 : v-yuzhenc
 * 功能 : 集中处理程序，以某用户的权限执行某条sql语句
 * exec_sql : 需要执行的sql语句
 * exec_user : 需要以哪个用户的权限执行该sql语句
 * */
declare 
    p_user varchar := exec_user;
    o_search_path varchar;
begin
    --记录原来的模式搜索路径
    execute 'show search_path;' into o_search_path;
    --临时切换模式搜索路径
    execute 'SET search_path TO '||p_user||',public,oracle';
    case p_user 
        when 'pgadmin' then perform pgadmin.sp_exec(exec_sql);
        when 'yuzhenchao' then perform yuzhenchao.sp_exec(exec_sql);
        when 'copyload' then perform copyload.sp_exec(exec_sql);
        else raise exception '未配置该用户:%',p_user;
    end case;
    --恢复模式搜索路径
    execute 'SET search_path TO '||o_search_path;

    exception when others then
        --恢复模式搜索路径
        execute 'SET search_path TO '||o_search_path;
        raise exception '%',sqlerrm;
end;
$function$
;

--将对应模式的对应模式的函数给对应的模式的拥有者
alter function pgadmin.sp_execsql(varchar,varchar) owner to pgadmin;
--将对应模式的sp_exec函数授权给定义者和集中用户execute权限
grant execute on function pgadmin.sp_execsql(varchar,varchar) to pgadmin;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    备份与恢复

    逻辑备份

    su - postgres
#先备份全局对象
pg_dumpall -f backup.sql --globals-only
#再备份数据库
pg_dump hy_observe -Fc > hy_observe.dump

    • 1
    • 2
    • 3
    • 4
    • 5

    逻辑恢复

    su - postgres
#先恢复全局对象
psql
\i backup.sql
--创建对应的数据库
create database hy_observe;
\q

#pg_restore进行恢复
pg_restore -d hy_observe hy_observe.dump -v

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    物理备份

    # 开启归档日志
vi $PGDATA/postgresql.conf
wal_level = replica  # 或者更高级别
archive_mode = on
# backup_in_progress文件用来辅助wal日志备份，通过删除配合test指令控制wal日志备份
archive_command = 'test ! -f /usr/local/pgsql/backup_in_progress || (test ! -f /usr/local/pgsql/data/pg_archive/%f && cp %p /usr/local/pgsql/data/pg_archive/%f)'

# 重启数据库
pg_ctl restart -mf

touch /usr/local/pgsql/backup_in_progress
# 开始基础备份,可以在代码里连接数据库执行
psql -c "select pg_start_backup('hot_backup');"
# 将数据库文件进行备份
BACKUPDATE=`date '+%Y%m%d%H%m%S'`
tar -cf /data/pg_backup/pgbackup_${BACKUPDATE}.tar $PGDATA
# 结束备份,可以在代码里连接数据库执行
psql -c "select pg_stop_backup();"
# 停止wal日志备份
rm /usr/local/pgsql/backup_in_progress
# 将wal日志和基础备份打包在一起
tar -rf /data/pg_backup/pgbackup_${BACKUPDATE}.tar /usr/local/pgsql/data/pg_archive

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    物理恢复

    pg_ctl stop -mf
mv $PGDATA ${PGDATA}.old
tar -xf /data/pg_backup/pgbackup_${BACKUPDATE}.tar -C $PGDATA
vi $PGDATA/recovery.conf
restore_command = 'cp /usr/local/pgsql/data/pg_archive/%f %p'
# 指定要恢复的时间点，也可以不指定，直接恢复所有数据
recovery_target_time = '2022-09-01 10:00:00'
pg_ctl start

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    开启ssl

    su - postgres

#进入到数据目录
cd $PGDATA

#创建证书
openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=pg_master"

#只读权限
chmod 400 server.{crt,key}

#修改pg_hba.conf
vi $PGDATA/pg_hba.conf
#所有远程连接都通过ssl连接
hostssl    all             postgres        0.0.0.0/0               md5
hostssl    all             repl            192.168.233.0/24        trust
hostssl    replication     repl            192.168.233.0/24        md5
hostssl    all             all             0.0.0.0/0               md5 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    #开启ssl
alter system set ssl=on;

#重新加载数据库配置
select pg_reload_conf();

#重新登录
\q
psql

#查看当前连接信息
\conninfo

#查看所有连接信息
select 
     pg_ssl.pid
    ,pg_ssl.ssl
    ,pg_ssl.version
    ,pg_sa.backend_type
    ,pg_sa.usename
    ,pg_sa.client_addr
from pg_stat_ssl pg_ssl
inner join pg_stat_activity pg_sa
on (pg_ssl.pid = pg_sa.pid);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    密码安全策略

    密码加密存储

    show password_encryption;--md5
select * from pg_shadow where usename='yuzhenchao';

    • 1
    • 2

    密码有效期

    alter role yuzhenchao valid until '2022-12-31 23:59:59';
select * from pg_user where usename='yuzhenchao';

    • 1
    • 2

    注意：

    • pg密码有效期仅针对客户端有效，服务器端不受限制。
    • 网络访问控制文件中不能配置为trust认证方式

    密码复杂度策略

    ls -atl $LD_LIBRARY_PATH/passwordcheck*

    • 1
    alter system set shared_preload_libraries=pg_stat_statements,passwordcheck;

    • 1
    pg_ctl restart -mf

    • 1

    密码验证失败延迟

    ls -atl $LD_LIBRARY_PATH/auth_delay*

    • 1
    --重启生效
alter system set shared_preload_libraries=pg_stat_statements,passwordcheck,auth_delay;

    • 1
    • 2
    pg_ctl restart -mf

    • 1
    --重新加载生效
alter system set auth_delay.milliseconds=5000;
--重新加载
select pg_reload_conf();

    • 1
    • 2
    • 3
    • 4
    pg_ctl reload

    • 1

    防止密码记录到数据库日志

    • 使用createuser命令加上-W选项创建用户

    开启服务器日志

    postgresql 服务器日志

    postgresql扩展组件

    oracle兼容性函数

    su - postgres

cd /opt

wget https://api.pgxn.org/dist/orafce/3.21.0/orafce-3.21.0.zip --no-check-certificate

unzip orafce-3.21.0.zip #解压

cd orafce-3.21.0/ #进入orafce-3.21.0目录

make clean 

make #编译

make install #安装

psql -d pgtest -U pgadmin -W

create extension orafce;  --创建orafce扩展

\q

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    postgis模块

    官方文档：doc-html-3.2.1.tar.gz

    安装cmake3.x版本

    cd /opt

wget https://github.com/Kitware/CMake/releases/download/v3.16.2/cmake-3.16.2.tar.gz

tar -zxvf cmake-3.16.2.tar.gz

cd cmake-3.16.2

./configure --prefix=/usr/local/cmake-3.16.2

make -j 4

make install

vi /etc/profile
export CMAKE_HOME=/usr/local/cmake-3.16.2
export PATH=$CMAKE_HOME/bin:$PATH

source /etc/profile

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    安装geos

    cd /opt

wget https://download.osgeo.org/geos/geos-3.11.0.tar.bz2 --no-check-certificate

tar -jxvf geos-3.11.0.tar.bz2

cd geos-3.11.0/

./configure --prefix=/usr/local/geos-3.11.0

make -j 4

make install

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    安装sqlite3.11以上版本

    cd /opt

wget https://www.sqlite.org/2022/sqlite-autoconf-3390100.tar.gz --no-check-certificate

tar -zxvf sqlite-autoconf-3390100.tar.gz

cd sqlite-autoconf-3390100

vi ./sqlite3.c
#define SQLITE_CORE 1
#define SQLITE_AMALGAMATION 1
#ifndef SQLITE_PRIVATE
# define SQLITE_PRIVATE static
#endif
#define SQLITE_ENABLE_COLUMN_METADATA 1 //增加这句

./configure --prefix=/usr/local/sqlite

make -j 4

make install

mv /usr/bin/sqlite3  /usr/bin/sqlite3_old

ln -s /usr/local/sqlite/bin/sqlite3   /usr/bin/sqlite3

sqlite3 --version

export PKG_CONFIG_PATH=/usr/local/sqlite/lib/pkgconfig:$PKG_CONFIG_PATH

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    安装proj

    cd /opt 

wget http://download.osgeo.org/proj/proj-6.3.2.tar.gz

tar -zxvf proj-6.3.2.tar.gz

cd proj-6.3.2/

./configure  --prefix=/usr/local/proj-6.3.2

make -j 4

make install

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    安装gdal

    cd /opt

wget https://download.osgeo.org/gdal/3.2.1/gdal-3.2.1.tar.gz --no-check-certificate

tar -zxvf gdal-3.2.1.tar.gz 

cd gdal-3.2.1 

./configure  --prefix=/usr/local/gdal-3.2.1 --with-proj=/usr/local/proj-6.3.2

make -j 4

make install 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    安装json-c

    cd /opt

wget https://github.com/json-c/json-c/archive/json-c-0.13.1-20180305.tar.gz

tar -zxvf json-c-0.13.1-20180305.tar.gz

cd json-c-json-c-0.13.1-20180305

./configure  --prefix=/usr/local/json-c-0.13.1

make -j 4

make install

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    安装libxml

    cd /opt

wget https://mirror.ossplanet.net/gnome/sources/libxml2/2.9/libxml2-2.9.14.tar.xz --no-check-certificate

tar -xvf libxml2-2.9.14.tar.xz

cd libxml2-2.9.14

chmod +x configure

./configure --prefix=/usr/local/libxml2-2.9.14

make -j 4

make install

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    安装protobuf

    cd /opt

wget https://github.com/protocolbuffers/protobuf/archive/v3.10.1.tar.gz

tar -zxvf v3.10.1.tar.gz

cd protobuf-3.10.1/

./autogen.sh #自动生成configure配置文件

./configure  --prefix=/usr/local/protobuf-3.10.1

make -j 4

make install

vi /etc/profile
export PROTOBUF_HOME=/usr/local/protobuf-3.10.1
export PATH=$PROTOBUF_HOME/bin:$PATH

source /etc/profile

protoc --version
libprotoc 3.10.1

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    安装protobuf-c

    cd /opt

wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.3.2/protobuf-c-1.3.2.tar.gz

tar -zxvf protobuf-c-1.3.2.tar.gz 

cd protobuf-c-1.3.2/
#导入protobuf的pkgconfig，否则"--No package 'protobuf' found"

export PKG_CONFIG_PATH=/usr/local/protobuf-3.10.1/lib/pkgconfig

./configure  --prefix=/usr/local/protobuf-c-1.3.2

make -j 4

make install

vi /etc/profile
export PROTOBUFC_HOME=/usr/local/protobuf-c-1.3.2
export PATH=$PROTOBUFC_HOME/bin:$PATH

source /etc/profile

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    安装boost-devel

    yum -y install boost-devel

    • 1

    安装cgal

    cd /opt

wget https://github.com/CGAL/cgal/archive/releases/CGAL-4.13.tar.gz

tar -zxvf CGAL-4.13.tar.gz 

cd cgal-releases-CGAL-4.13/

mkdir build && cd build

cmake ..

make

make install

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    安装sfcgal

    cd /opt

wget https://github.com/Oslandia/SFCGAL/archive/v1.3.7.tar.gz

tar -zxvf v1.3.7.tar.gz

cd SFCGAL-1.3.7

mkdir build && cd build

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/sfcgal-1.3.7 ..

make -j 4

make install

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    安装postgis

    vi /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/local/pgsql/lib
/usr/local/proj-6.3.2/lib
/usr/local/gdal-3.2.1/lib
/usr/local/geos-3.11.0/lib64
/usr/local/sfcgal-1.3.7/lib64
/usr/local/json-c-0.13.1/lib
/usr/local/libxml2-2.9.14/lib
/usr/local/protobuf-3.10.1/lib
/usr/local/protobuf-c-1.3.2/lib

ldconfig -v #重启生效

su - postgres

cd /usr/local/pgsql/contrib

wget http://download.osgeo.org/postgis/source/postgis-3.2.1.tar.gz

tar -zxvf postgis-3.2.1.tar.gz

cd postgis-3.2.1/

./configure --prefix=/usr/local/pgsql --with-gdalconfig=/usr/local/gdal-3.2.1/bin/gdal-config --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-geosconfig=/usr/local/geos-3.11.0/bin/geos-config --with-projdir=/usr/local/proj-6.3.2 --with-xml2config=/usr/local/libxml2-2.9.14/bin/xml2-config --with-jsondir=/usr/local/json-c-0.13.1 --with-protobufdir=/usr/local/protobuf-c-1.3.2 --with-sfcgal=/usr/local/sfcgal-1.3.7/bin/sfcgal-config

make -j 4

make install

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    创建extension

    psql -d pgtest -U pgadmin -W

--postgis扩展
create extension postgis;

--验证栅格类数据需要的raster扩展
create extension postgis_raster;

--如果安装带有sfcgal，验证下三维sfcgal扩展
create extension postgis_sfcgal;

create extension fuzzystrmatch;

create extension postgis_tiger_geocoder;

create extension postgis_topology;

\q

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 创建extension时遇到问题
    could not load library "/usr/local/pgsql/lib/postgis-3.so": /usr/local/pgsql/lib/postgis-3.so: undefined symbol: GEOSLargestEmpt

    • 1
    • 查找原因，是geos存在多个版本
    ldconfig -p | grep libgeos_c.so.1
libgeos_c.so.1 (libc6,x86-64) => /usr/geos39/lib64/libgeos_c.so.1
libgeos_c.so.1 (libc6,x86-64) => /usr/local/geos-3.11.0/lib64/libgeos_c.so.1
#查找geos39
rpm -qa geos39
geos39-3.9.2-1.rhel7.x86_64

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    • 解决方案：卸载geos39-3.9.2-1.rhel7.x86_64

    • 再次校验，发现只剩一个了

    ldconfig -p | grep libgeos_c.so.1
libgeos_c.so.1 (libc6,x86-64) => /usr/local/geos-3.11.0/lib64/libgeos_c.so.1

    • 1
    • 2

    数据库开发

    数据库开发规范

    命名规范

    • 标识符总长度不超过63，由于oracle标识符长度不超过30，原则上，为了兼容oracle，标识符长度最好不要超过30；
    • 对象名（表名、列名、函数名、视图名、序列名、等对象名称）规范，对象名务必只使用小写字母，下划线，数字。不要以pg开头，不要以数字开头，不要使用保留字;
    • 查询中的别名不要使用 “小写字母，下划线，数字” 以外的字符，例如中文;
    • 主键索引应以 pk_ 开头， 唯一索引要以 uk_ 开头，普通索引要以 idx_ 打头
    • 临时表以 tmp_ 开头，子表以规则结尾，例如按年分区的主表如果为tbl, 则子表为tbl_2016，tbl_2017等；
    • 库名最好以部门名字开头 + 功能，如 xxx_yyy，xxx_zzz，便于辨识；
    • 禁用public schema，应该为每个应用分配对应的schema，schema_name最好与user name一致。

    设计规范

    • 多表中的相同列，必须保证列名一致，数据类型一致；
    • btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引；
    • 对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用；（create table test123(id int, info text) with(fillfactor=85); CREATE TABLE）
    • 表结构中字段定义的数据类型与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或无法使用索引的情况发生；
    • 建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操作，而是DROP或者TRUNCATE对应的表；
    • 为了全球化的需求，所有的字符存储与表示，均以UTF-8编码；
    • 对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使用BRIN索引。例如流式数据，时间字段或自增字段，可以使用BRIN索引，减少索引的大小，加快数据插入速度。（create index idx on tbl using brin(id); ）
    • 设计时应尽可能选择合适的数据类型，能用数字的坚决不用字符串，使用好的数据类型，可以使用数据库的索引，操作符，函数，提高数据的查询效率；
    • 应该尽量避免全表扫描(除了大数据量扫描的数据分析)，PostgreSQL支持几乎所有数据类型的索引；
    • 应该尽量避免使用数据库触发器，这会使得数据处理逻辑复杂，不便于调试；
    • 未使用的大对象，一定要同时删除数据部分，否则大对象数据会一直存在数据库中，与内存泄露类似；
    • 对于固定条件的查询，可以使用部分索引，减少索引的大小，同时提升查询效率；（create index idx on tbl (col) where id=1;）
    • 对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询；（create index idx on tbl ( exp ); ）
    • 如果需要调试较为复杂的逻辑时，不建议写成函数进行调试，可以使用plpgsql的匿名代码块；
    • 当用户有prefix或者 suffix的模糊查询需求时，可以使用索引，或反转索引达到提速的需求；（select * from tbl where reverse(col) ~ ‘^def’; – 后缀查询使用反转函数索引）
    • 用户应该对频繁访问的大表（通常指超过8GB的表，或者超过1000万记录的表）进行分区，从而提升查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等；
    • 设计表结构时必须加上字段数据的入库时间inputed_time和数据的更新时间updated_time；

    查询规范

    • 统计行数用count(*)或者count(1),count(列名)不会统计列为空的行；
    • count(distinct col) 计算该列的非NULL不重复数量，NULL不被计数；
    • count(distinct (col1,col2,…) ) 计算多列的唯一值时，NULL会被计数，同时NULL与NULL会被认为是想同的；
    • NULL是UNKNOWN的意思，也就是不知道是什么。 因此NULL与任意值的逻辑判断都返回NULL；
    • 除非是ETL程序，否则应该尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理；
    • 尽量不要使用 select * from t ，用具体的字段列表代替*，不要返回用不到的任何字段，另外表结构发生变化也容易出现问题。

    管理规范

    • 数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误才能提交执行；
    • 用户可以使用explain analyze查看实际的执行计划，但是如果需要查看的执行计划设计数据的变更，必须在事务中执行explain analyze，然后回滚；
    • 如何并行创建索引，不堵塞表的DML，创建索引时加CONCURRENTLY关键字，就可以并行创建，不会堵塞DML操作，否则会堵塞DML操作；（create index CONCURRENTLY idx on tbl(id); ）
    • 为数据库访问账号设置复杂密码；
    • 业务系统，开发测试账号，不要使用数据库超级用户，非常危险；
    • 应该为每个业务分配不同的数据库账号，禁止多个业务共用一个数据库账号；
    • 大批量数据入库的优化，如果有大批量的数据入库，建议使用copy语法，或者 insert into table values (),(),…(); 的方式，提高写入速度。

    稳定性与性能规范

    • 游标使用后要及时关闭；
    • 两阶段提交的事务，要及时提交或回滚，否则可能导致数据库膨胀；
    • 不要使用delete 全表，性能很差，请使用truncate代替；
    • 应用程序一定要开启autocommit，同时避免应用程序自动begin事务，并且不进行任何操作的情况发生，某些框架可能会有这样的问题；
    • 在函数中，或程序中，不要使用count(*)判断是否有数据，很慢。 建议的方法是limit 1;
    • 必须选择合适的事务隔离级别，不要使用越级的隔离级别，例如READ COMMITTED可以满足时，就不要使用repeatable read和serializable隔离级别；
    • 高峰期对大表添加包含默认值的字段，会导致表的rewrite，建议只添加不包含默认值的字段，业务逻辑层面后期处理默认值；
    • 可以预估SQL执行时间的操作，建议设置语句级别的超时，可以防止雪崩，也可以防止长时间持锁；
    • PostgreSQL支持DDL事务，支持回滚DDL，建议将DDL封装在事务中执行，必要时可以回滚，但是需要注意事务的长度，避免长时间堵塞DDL对象的读操作；
    • 如果用户需要在插入数据和，删除数据前，或者修改数据后马上拿到插入或被删除或修改后的数据，建议使用insert into … returning …; delete … returning …或update … returning …; 语法。减少数据库交互次数；
    • 自增字段建议使用序列，序列分为2字节，4字节，8字节几种(serial2,serial4,serial8)。按实际情况选择。 禁止使用触发器产生序列值；
    • 使用窗口查询减少数据库和应用的交互次数；
    • 如何判断两个值是不是不一样（并且将NULL视为一样的值），使用col1 IS DISTINCT FROM col2；
    • 对于经常变更，或者新增，删除记录的表，应该尽量加快这种表的统计信息采样频率，获得较实时的采样，输出较好的执行计划。

    pl/pgsql和pl/sql

    Greenplum开发

    数据库巡检

    postgresql 数据库巡检

    参考

    首页 ＞ PostgreSQL

    PostgreSQL 兼容Oracle - orafce

    PostGIS 扩展创建失败原因调查

    PostgreSQL 数据库开发规范

    PostgreSQL密码安全策略

  • 相关阅读:
    【机器学习】鸢尾花数据的基本信息 || sklearn
    【计算机专业毕设之基于ssm的大学生勤工俭学管理系统-哔哩哔哩】 https://b23.tv/IADQcmb
    设计模式简介
    【项目经验】elementui抽屉（从下到上方向）实现向上拉伸
    剑指 Offer 56 数组中数字出现的次数（异或）
    [MAUI] 开篇-初识MAUI
    获取并导入Mybtis源码到Idea &&HSQLDB数据库简介
    每天一个数据分析题（一百八十七）
    如何修复-谷歌浏览器-打开任何一个网页都显示崩溃
    C语言之动态内存分配二
  • 原文地址：https://blog.csdn.net/qq_33445829/article/details/126636295