PostgreSQL 数据库安装部署

前言

从PG9.1版本开始接触PostgreSQL数据库，到如今的PG14.1，每个版本的PG数据库在源码安装基本都是保持一致的，但是在通过流复制搭建主备集群，在PG12版本有了一些变化。

环境准备

操作系统：CentOS7.9 on x86_64
PG版本：PostgreSQL 14.1

服务器设置

--关闭防火墙
# systemctl disable firewalld
# systemctl stop firewalld

--关闭SElinux
# sed -i 's/^SELINUX=.*$/SELINUX=disable/g' /etc/sysconfig/selinux
要永久关闭selinux 需要重启服务器，可以使用setenforce 0 临时关闭selinux

--时钟同步
# timedatectl set-timezone Asia/Shanghai （修改时区,可自定义）
# timedatectl set-local-rtc 1 （同步系统时间到硬件）

# echo "server xxx.xxx.xxx prefer" /etc/ntp.conf
# systemctl start ntpd
# systemctl enable ntpd

--字符集设置, locale查看当前字符集，如果不是UTF-8,则修改
# locale
# echo 'LANG="en_US.UTF-8"' > /etc/locale.conf

系统参数

--/etc/sysctl.conf
cat >> /etc/sysctl.conf << EOF
net.ipv4.tcp_retries1=5
net.ipv4.tcp_syn_retries=5
net.ipv4.tcp_synack_retries=5
net.ipv4.tcp_fin_timeout=60
net.ipv4.tcp_sack=1
net.ipv4.tcp_timestamps=1
vm.extfrag_threshold=500
vm.overcommit_ratio=90
net.ipv4.tcp_max_tw_buckets = 10000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_retries2 = 12
net.sctp.addip_enable = 0
net.core.wmem_max = 21299200
net.core.rmem_max = 21299200
net.core.wmem_default = 21299200
net.core.rmem_default = 21299200
net.sctp.sctp_mem = 94500000 915000000 927000000
net.sctp.sctp_rmem = 8192 250000 16777216
net.sctp.sctp_wmem = 8192 250000 16777216
kernel.sem = 250 6400000 1000 25600
net.ipv4.tcp_rmem = 8192 250000 16777216
net.ipv4.tcp_wmem = 8192 250000 16777216
vm.min_free_kbytes  = total_system_memory*5%
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.somaxconn = 65535
net.ipv4.tcp_syncookies = 1
vm.overcommit_memory = 0
kernel.shmall = 1152921504606846720
kernel.shmmax = 18446744073709551615
EOF

--/etc/security/limits.conf
echo "* soft nofile 1000000" >> /etc/security/limits.conf
echo "* hard nofile 1000000" >> /etc/security/limits.conf
echo "* soft nproc 131072" >> /etc/security/limits.conf
echo "* hard nproc 131072" >> /etc/security/limits.conf

依赖安装

yum -y install gcc.x86_64 gcc-c++.x86_64 readline.x86_64 readline-devel.x86_64 zlib.x86_64 zlib-devel.x86_64

创建系统用户及目录

# groupadd postgres
# useradd -g postgres postgres
# mkdir /data/pgdata14 && chown -R postgres: /data/pgdata14

单机安装

PostgreSQL数据库基本支持市面上的所有操作系统，除非你所维护数据库操作系统版本完全一致，否则建议使用源码编译的方式进行安装，更加灵活，可以直接去PG源码进行源码包下载。

--编译安装
# ./configure --prefix=/opt/postgresql_14.1
# make world -j 8
# make install-world -j 8
# rm -f /opt/pgsql && ln -s /opt/postgresql_14.1/ /opt/pgsql
# chown -R postgres: pgsql postgresql_14.1

--配置环境变量
# su - postgres
$ echo 'export PATH=$PATH:/opt/pgsql/bin' >> ~/.bashrc
$ source ~/.bashrc

--初始化数据库
$ initdb -D /data/pgdata14
$ pg_ctl -D /data/pgdata14 start

--创建密码文件（可省略）
$ echo "hostname:port:database:username:password" >> ~/.pgpass
$ chmod 0600 ~/.pgpass

初始化参数

listen_addresses = '*'
port = 5432
max_connections = 1000
max_prepared_transactions=1000
shared_buffers = 32GB
work_mem = 64MB
maintenance_work_mem = 2GB
autovacuum_work_mem = -1
wal_level = replica
synchronous_commit = on
full_page_writes = on
wal_log_hints = on
archive_mode = on
archive_command = 'cp %p /data/pgarchive/%f'
autovacuum = on
logging_collector = on
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on
log_statement = 'mod'
log_line_prefix = '%m %d %u %r %a %p v%'
idle_in_transaction_session_timeout = 10000
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

开机自启动

# cp /opt/software/postgresql-14.1/contrib/start-scripts/linux /etc/init.d/postgresql
# chmod +x /etc/init.d/postgresql

--修改启动文件
vim /etc/init.d/postgresql

prefix=/opt/pgsql
PGDATA=/data/pgdata14

--启动
# systemctl start postgresql
# systemctl enable postgresql

主备集群

像安装单机数据库实例一样，可以在不同的服务器上配置多个数据库实例，也可以在一台服务器上启动多个数据库，但是要关注服务器资源消耗情况

PG12之前的版本搭建流复制需要手工单独配置recovery.conf文件，从PG12开始，recovery.conf相关内容添加到postgresql.conf文件中，但是需要单touch一个standby.signal文件

主库操作

--创建具有replication权限的用户
create user rep replication encrypted password 'rep@1234';

--修改pg_hba.conf
host     replication     rep     xxx.xxx.xxx.xxx/32    md5

--重新加载数据库
$ psql -c "select pg_reload_conf();"

--开始备份主库并物理压缩传输到备库服务器(如果有全量备份或者使用备份工具，此步可以被替换)
$ psql -d postgres -c "select pg_start_backup('first_backup');"
$ tar -zcvf ./pgbackup.tar.gz pgdata
$ psql -d postgres -c "select pg_stop_backup();"
$ scp ./pgbackup.tar.gz postgres@xxx.xxx.xxx.xxx:/tmp

PG12以前的版本配置备库

--配置recovery.conf文件,此文件不存在，直接生成即可
$ vim pgdata/recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=source_ip port=5432 user=rep password=rep@1234 application_name=standby_1'
recovery_target_timeline = 'latest'
trigger_file = '/home/postgres/trigger_activestandby'

--启动备库
$ pg_ctl -D pgdata start

PG12及以后版本配置备库

--修改postgresql.conf文件
standby_mode = 'on'
primary_conninfo = 'host=source_ip port=5432 user=rep password=rep@1234 application_name=standby_1'

--生成standby.signal文件
$ touch pgdata/standby.signal

--启动备库
$ pg_ctl -D pgdata start

状态检查

$ psql -c "select * from pg_stat_replication;"

关注点

同步关系

1、当synchronous_standby_names为空，synchronous_commit设置为on/remote_apply/remote_write/local/off时，是异步
2、当synchronous_standby_names不为空，synchronous_commit设置为local/off 时，即使pg_stat_replication的sync_state字段值是sync，主备之间也是异步
3、synchronous_standby_names='*' 等同于 synchronous_standby_names='first 1(s1,s2,sn)' 等同于 synchronous_standby_names='s1,s2,sn'