PostgreSQL主从架构方案

作者：汪pallotta | 来源：互联网 | 2023-07-31 16:12

PostgreSQL主从架构基于其原生的流复制方案实现，通常在主从复制架构下，主节点数据（异步复制）通过流复制方式到从节点之间的延迟控制在秒级别内。在主从复制架构下，Postgre

PostgreSQL 主从架构基于其原生的流复制方案实现，通常在主从复制架构下，主节点数据（异步复制）通过流复制方式到从节点之间的延迟控制在秒级别内。在主从复制架构下，PostgreSQL 推荐主从配置一致，以达到性能的平衡和传输无区别等待。同时，为了避免从节点未及时应用主节点日志而导致主节点日志日志切换后发生覆盖，主节点提供了日志归档功能和最大备节点日志保留策略，以保证从节点能够正常运行，并能够实现读写分离。

此文档方案适用于 PostgreSQL 业务开发人员及 DBA 工程师

一、操作系统及硬件选型
1、操作系统版本
操作系统版本推荐使用CentOS 8.5版本，由于Redhat 对于 CentOS 支持的终止，也可建议使用 RockyLinux 来替代 CentOS 8.5 版本。RockyLinux 通过 RHEL 重建，完全兼容 Redhat Enterprise 操作系统，因此稳定可靠，目前最新的 CentOS 版本为 CentOS Stream 8,不存在 CentOS 8.5 以后的系统，该系统可以通过 www.centos.org 网站获取，国内及多数镜像源已不提供下载。RockyLinux 最新版本为 8.6 版本，因其发行于5月份，相对较新，因此推荐使用 8.5 版本，该系统可以通过 www.rockylinux.org 网站获取。
2、硬件选型
由于CPU、交换机、内存，硬盘型号较多，此处不直接推荐涉及的具体的机架及CPU、交换机、内存和硬盘型号，仅推荐使用的 CPU 核数，交换机带宽、内存大小和硬盘大小信息，具体参考以下表一。
CPU核数（个）
网络带宽（GE）
内存容量（GB）
硬盘容量（TB）
16
10
64
6
16
10
128
6
32
10
128
6
32
10
256
6
48
10
192
6
48
10
384
6
60
10
480
6
表一
以上CPU 核数和内存信息不做说明。网卡推荐使用双网卡，做bond0，并且日志传输网络和业务应用访问网络区分开。硬盘推荐使用RAID 5，以保证存储层面的可用性，如果有条件，推荐使用 LVM 管理，方便后期扩容操作。

二、数据库版本选型及参数配置
针对数据库版本，首选应与云上数据库对应的版本一致，其次推荐高于云数据库版本的数据库，以达到向下兼容和相关新特性应用及相关 bug 修复的目的。目前社区开源的数据库最新稳定版本为 PostgreSQL 14.5 版本，建议使用源码编译的方式安装数据库软件，以明确数据库安装目录结构和开发运维管理。官方软件下载地址: https://www.postgresql.org/ftp/source/v14.5/

2.1 云数据库信息查看

云数据库版本查看：

或者

查看数据库中已经存在的插件

2.2 参数配置参考

以下配置基于32 核心CPU，128G内存配置参考：

#连接配置

max_connections = 960

superuser_reserved_connections = 3

#内存配置

shared_buffers = '32768 MB'

work_mem = '64 MB'

maintenance_work_mem = '820 MB'

effective_cache_size = '90 GB'

effective_io_concurrency = 400

random_page_cost = 1.25

#监控配置

shared_preload_libraries = 'pg_stat_statements' # per statement resource usage stats

track_io_timing=on # measure exact block IO times

#检查点配置

checkpoint_timeout = '15 min'

checkpoint_completion_target = 0.9

max_wal_size = '10240 MB'

min_wal_size = '5120 MB'

wal_keep_size = '1GB'

# WAL 日志写配置

wal_compression = on

wal_buffers = -1 # 由 PostgreSQL 自动优化

wal_writer_delay = 200ms

wal_writer_flush_after = 1MB

# 后台刷新数据配置

bgwriter_delay = 200ms

bgwriter_lru_maxpages = 100

bgwriter_lru_multiplier = 2.0

bgwriter_flush_after = 0

#并行查询配置

max_worker_processes = 32

max_parallel_workers_per_gather = 16

max_parallel_maintenance_workers = 16

max_parallel_workers = 32

parallel_leader_participation = on

三、 主从节点创建

注意:以下所有配置过程基于 RockyLinux 操作，数据库版本选择 PostgreSQL 14.5 ，由于从 PostgreSQL 9.3 版本以后，内核参数内存模块配置不做硬性要求，因此可以省略，对于资源限制，建议可以配置打开文件数和进程数据的参数，最大值配置为65535.

3.1主节点配置

1. 查看操作系统版本及配置主机名与解析

[root@master ~]# cat etc/redhat-release
Rocky Linux release8.5 (Green Obsidian)
[root@master ~]# hostnamectl set-hostname master
[root@master ~]# head -4 etc/hosts | tail -2
10.10.20.91 master.com master
10.10.20.92 standby.com standby

2. 关闭防火墙和SELinux

[root@master ~]# systemctl stop firewalld
[root@master ~]# systemctl disable firewalld
[root@master ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' etc/selinux/config
[root@master ~]# setenforce 0
setenforce: SELinux is disabled
[root@master ~]# getenforce
Disabled

3. 配置资源限制

[root@master ~]# cat etc/security/limits.d/99-postgresql.conf
* soft nofile 8192
* hard nofile 65536
* soft nproc 2047
* hard nproc 16384
* soft stack 10240
* hard stack 32768
* soft memlock 5000000
* hard memlock 8000000

4. 配置YUM 源

[root@master ~]# cat /etc/yum.repos.d/local.repo
[BaseOS]
name=BaseOS
baseurl=file:///mnt/BaseOS
enabled=1
gpgcheck=0
[AppStream]
name=AppStream
baseurl=file:///mnt/AppStream
enabled=1
gpgcheck=0

5. 安装依赖软件

yum install -y make \
readline-devel\
bison-devel\
llvm-devel\
libicu-devel\
clang\
perl-ExtUtils-Embed\
perl-ExtUtils-MakeMaker\
platform-python-devel\
python39\
python39-devel\
openssl-devel\
pam-devel\
libxml2-devel\
libxslt-devel\
openldap-devel\
libuuid-devel\
systemd-devel\
tcl-devel\
gcc-c++

6. 上传PostgreSQL 软件包并解压

在 /opt/目录下创建存放软件包 PostgreSQL 14.5 的目录

mkdir -p /opt/software

上传包创建的该软件存放目录

[root@master software]# pwd
/opt/software
[root@master software]# ls
postgresql-14.5.tar.bz2

7. 规划存储路径

关于PostgreSQL 存储路径，这里划分为五个部分，分别为自定义表空间如果有需求，表空间存储为单个存储，pg_wal 日志目录存储为单个存储，log 为单个存储，归档日志为单个存储，默认数据库目录为单个存储，这里规划如下:

目录名称	挂载点
PGDATA 默认数据库目录	/pg/pgdata
自定义表空间目录	/pg/custbs
pg_wal 目录	/pg/pg_wal
log 目录	/pg/log
归档日志目录	/pg/archive/

关于存储的管理请根据实际情况选择默认管理还是LVM管理，RAID 推荐 RAID5

LVM和 RAID 过程略，建议使用硬RAID（RAID 卡制作），而非软 RAID（RAID 软件制作）。

具体操作如下:

创建必要的目录

[root@master software]# mkdir -p /pg/{pgdata,pg_wal,log,custbs,archive}

格式化已经挂载的磁盘

使用mkfs.xfs 来格式化

将磁盘挂载信息配置到/etc/fstab 文件中

[root@master software]# tail -5 /etc/fstab
/dev/nvme0n2p3 /pg/pgdata xfs defaults 0 0
/dev/nvme0n2p1 /pg/custbs xfs defaults 0 0
/dev/nvme0n2p2 /pg/log xfs defaults 0 0
/dev/nvme0n2p5 /pg/archive xfs defaults 0 0
/dev/nvme0n2p6 /pg/pg_wal xfs defaults 0 0

这里仅仅显示已挂载的目录

[root@master software]# df -hT | tail -5
/dev/nvme0n2p3 xfs 100G 746M 100G 1% /pg/pgdata
/dev/nvme0n2p1 xfs 50G 390M 50G 1% /pg/custbs
/dev/nvme0n2p2 xfs 50G 390M 50G 1% /pg/log
/dev/nvme0n2p5 xfs 50G 390M 50G 1% /pg/archive
/dev/nvme0n2p6 xfs 50G 390M 50G 1% /pg/pg_wal

关于磁盘分配，默认数据库目录，即 /pg/pgdata 空间应该要比其它目录的空间要大，如上显示，在假设的绝对空间已满的情况下，确保数据目录空间不被占用，以防止数据目录空间不足导致的数据库集簇意外宕机或者无法启动。

8. 创建安装用户

[root@master ~]# useradd -u 2000 postgres
[root@master ~]# echo postgres | passwd --stdin postgres
Changing password for user postgres.
passwd: all authentication tokens updated successfully.

9. 给相关目录授权

[root@master ~]# chown postgres.postgres -R /pg/*
[root@master ~]# chmod 0700 /pg/pgdata

这里的/pg/pgdata 目录作为数据库默认的集簇目录，其权限应该为 0700 ,否则将导致无法启动数据库集簇。

10. 编译安装

数据库软件默认安装在 /usr/local/pgsql 目录下，该软件目录对于数据库集簇来说，假设在操作系统损坏的情况下，只要数据库集簇存储正常，那么可以将数据库存储移动到正常的环境，重新构建该目录即可启动数据库。端口默认为5432，在生产环境中，不建议使用默认的端口，这里将其修改为5532端口。

解压软件包并切换至解压目录

[root@master ~]# cd /opt/software/
[root@master software]# ls
postgresql-14.5.tar.bz2
[root@master software]# tar -jxf postgresql-14.5.tar.bz2
[root@master software]# cd postgresql-14.5/

执行编译安装

export PREFIX=/usr/local/pgsql
export PGPORT=5532
./configure \
--prefix=${PREFIX}/ \
--exec-prefix=/data1/pgsql \
--bindir=${PREFIX}/bin \
--syscOnfdir=${PREFIX}/etc \
--libdir=${PREFIX}/lib \
--includedir=${PREFIX}/include \
--datarootdir=${PREFIX}/share \
--datadir=${PREFIX}/share \
--localedir=${PREFIX}/locale \
--mandir=${PREFIX}/share/man \
--docdir=${PREFIX}/share/doc/postgresql \
--htmldir=${PREFIX}/share/html \
--enable-nls='zh_CN en_US' \
--with-perl \
--with-python \
--with-tcl \
--with-icu \
--with-openssl \
--with-ldap \
--with-pam \
--with-systemd \
--with-libxml \
--with-llvm \
--with-libxslt \
--with-readline \
--with-libedit-preferred \
--with-uuid=e2fs \
--with-zlib \
--enable-spinlocks \
--enable-depend \
--with-pgport=${PGPORT} \
--with-segsize=1 \
--with-blocksize=8 \
--with-wal-blocksize=8
[root@master postgresql-14.5]# make world -j16 && make install-world -j16

11. 配置环境变量

切换至 postgres 用户配置环境变量

[postgres@master ~]$ tail -5 ~/.bashrc
export PGHOME=/usr/local/pgsql
export PGDATA=/pg/pgdata
export LD_LIBRARY_PATH=${PGHOME}/lib:${LD_LIBRARY_PATH}
export MANPATH=${PGHOME}/man:${MANPATH}
export PATH=${PGHOME}/bin:${PATH}
[postgres@master ~]$ source ~/.bashrc

12. 初始化数据库集簇

数据库集簇的初始化由于使用的是源码编译安装，因此需要使用普通用户 postgres 操作系统用户进行初始化，因此，此处需要切换至 postgres 用户。

[root@master ~]# su - postgres
[postgres@master ~]$ initdb -D $PGDATA -E UTF8 -k -U postgres -X /pg/pg_wal --wal-segsize=16

参数解释:

-D: 指定的默认数据库集簇的目录

-E:数据库默认编码

-k: 启用页面校验（用于从节点流复制中的切换操作,pg_rewind）

-U:指定数据库的超级用户，如果不指定，默认以初始化执行的操作系统用户作为默认的超级用户

-X: 指定数据库 pg_wal 的日志目录

--wal-segsize: 指定单个 wal 段文件的大小，默认为16MB

13. 启动数据库

[postgres@master ~]$ pg_ctl start -D $PGDATA -l /tmp/logfile
waiting for server to start.... done
server started

14. 验证数据库启动状态和版本

[postgres@master ~]$ psql
psql(14.5)
Type "help" for help.
postgres=# select * from version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
(1 row)
postgres=# show server_version;
server_version
----------------
14.5
(1 row)

15. 创建流复制用户

流复制用户需要具有 replication 的权限，同时超级用户也可以用做流复制用户，但为了安全，推荐自定义具有 replication 权限的用户，而非超级用户。

[postgres@master ~]$ psql
psql(14.5)
Type "help" for help.
postgres=# create user repluser with replication password 'repluser';
CREATE ROLE

16. 启动数据库监听

主从环境由于在不同的节点，因此需要启动监听，以监听从节点或者外部源到主节点上的连接信息，启动监听操作需要重新启动数据集簇。

postgres=# alter system set listen_addresses = '*';
ALTER SYSTEM

此处先不需要启动数据库，完成 17 小节的操作后，一同启动即可，参考 20小节。

17. 配置基于主机的认证

由于从主节点需要认证连接来源，以允许来自哪里（指客户端）的哪个用户，以什么认证方式，用什么方式，连接到哪个数据库，因此需要配置基于主机的认证，基于主机的认证配置文件存放于文件 $PGDATA/pg_hba.conf 中，如下：

[postgres@master ~]$ egrep "10.10.20" $PGDATA/pg_hba.conf
host all all 10.10.20.0/24 scram-sha-256
host replication repluser 10.10.20.0/24 scram-sha-256

18. 配置归档

流复制从节点会需要归档信息，因此归档必须配置，启用归档模式需要重新启动数据库，如下:

[postgres@master ~]$ psql
psql(14.5)
Type "help" for help.
postgres=# alter system set archive_mode = on;
ALTER SYSTEM
postgres=# alter system set wal_level = logical;
ALTER SYSTEM
postgres=# alter system set archive_command = 'cp %p /pg/archive/%f';
ALTER SYSTEM

19. 启动日志收集信息

源码编译环境，默认未启用日志，因此手动启动日志收集功能，日志目录为规划的目录 /pg/log，并启用语句记录信息。

postgres=# alter system set logging_collector = on;
ALTER SYSTEM
postgres=# alter system set log_directory = '/pg/log';
ALTER SYSTEM
postgres=# alter system set log_statement = mod;
ALTER SYSTEM

20. 重新启动数据库

[postgres@master ~]$ pg_ctl restart -D $PGDATA -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

到目前为止，主节点信息配置完毕。

21. 扩展安装

扩展模块，此处以 pg_logical 插件作为扩展模块安装示例：

包名称：pglogical-REL2_4_1.tar.gz

1、上传包解压

上传包至主从节点的源码目录 contrib 中，并解压，如下：

此处使用 root 用户上传即可。

主节点：

[root@master ~]# cd /opt/software/postgresql-14.5/contrib/
[root@master contrib]# ls pglogical-REL2_4_1.tar.gz
pglogical-REL2_4_1.tar.gz
[root@master contrib]# tar -zxf pglogical-REL2_4_1.tar.gz

从节点：

2、切换至解压目录编译安装

主节点配置临时环境变量并编译

[root@master ~]# export PGHOME=/usr/local/pgsql
[root@master ~]# export PGDATA=/pg/pgdata
[root@master ~]# export LD_LIBRARY_PATH=${PGHOME}/lib:${LD_LIBRARY_PATH}
[root@master ~]# export MANPATH=${PGHOME}/man:${MANPATH}
[root@master ~]# export PATH=${PGHOME}/bin:${PATH}
[root@master ~]# cd /opt/software/postgresql-14.5/contrib/pglogical-REL2_4_1/
[root@master pglogical-REL2_4_1]# make -j16 && make install -j16

3、配置到共享预载库

[postgres@master ~]$ tail -1 $PGDATA/postgresql.auto.conf
shared_preload_libraries= 'pglogical'

4、重启数据库并创建扩展

[postgres@master ~]$ pg_ctl restart -l /tmp/logfile
waitingfor server to shut down.... done
server stopped
waitingfor server to start.... done
server started
[postgres@master ~]$ psql
psql(14.5)
Type "help" for help.
postgres=# create extension pglogical;
CREATE EXTENSION

查看扩展信息

postgres=# select extname from pg_extension ;
extname
-----------
plpgsql
pglogical
(2 rows)

‍

3.2 配置从节点

以下操作没有明确说明，都在从节点 standby 执行。

1.操作系统配置参考配置主节点配置

这里仅仅标识需要变更的信息

主机名变更:

[root@standby ~]# hostnamectl set-hostname standby

配置主机名解析：

[root@standby ~]# head -4 /etc/hosts | tail -2
10.10.20.91 master.com master
10.10.20.92 standby.com standby

创建软件存放目录:

[root@standby ~]# mkdir -p /opt/software

2.软件拷贝和环境变量文件拷贝

主节点已经完成软件安装，现在从节点不需要重新编译，只需要将源码编译目录和安装目录拷贝至相应的目录即可，如下：

在主节点执行：

[root@master ~]# scp -r /opt/software/postgresql-14.5 standby:/opt/software/
[root@master ~]# scp -r /usr/local/pgsql standby:/usr/local/

切换至postgres 用户，拷贝环境变量文件

[postgres@master ~]$ scp ~/.bashrc standby:/home/postgres/
The authenticity of host'standby (10.10.20.92)' can't be established.
ECDSA key fingerprint is SHA256:eRX9MCCF1Dkw2IR80H9PICYmwXvVOdDUWlJGvEMcj3s.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'standby,10.10.20.92' (ECDSA) to the list of known hosts.
postgres@standby's password:
.bashrc 100% 562 775.1KB/s 00:00
[postgres@master ~]$

3. 使用 pg_basebackup 复制主节点集簇

切换至postgres 用户，使用 pg_basebackup 客户端工具将主节点数据库集簇拷贝至当前从节点 standby，如下:

[postgres@standby ~]$ pg_basebackup -Fp -Xs -Pv -R -c fast -D $PGDATA -h master -U repluser -p 5532
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_38231"
26289/26289 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

参数解释：

-Fp: 表示以原始平面文件拷贝文件

-Xs: 表示以流复制访问拷贝日志

-Pv: 表示显示过程

-R: 表示创建备库文件和恢复配置信息

-c: 表示拷贝前做一次日志切换

-D: 从节点的数据目录

4. 启动从节点数据库

[postgres@standby ~]$ pg_ctl start -l /tmp/logfile
waiting for server to start.... done
server started

5. 验证从节点进程

从节点如果启动成功，并且如果能够连接到主节点，将会产生一个walreceiver 进程，如下：

[postgres@standby ~]$ ps -ef | grep walreceiver| egrep -v grep
postgres 37094 37088 0 12:44 ? 00:00:00 postgres: walreceiver streaming 0/5000060

主节点也会存在一个wasender 进程，表示流传输进程,在主节点执行如下：

[postgres@master ~]$ ps -ef | grep walsender| egrep -v grep
postgres 38252 37687 0 12:44 ? 00:00:00 postgres: walsender repluser 10.10.20.92(59252) streaming 0/5000148

至此，主从节点环境完成部署。

四、监控主从节点
主从复制架构之间会存在一定的延迟，因此及时监控非常重要，以让开发人员和 DBA 运维管理人员能够及时掌握节点的状态信息。

4.1 主节点监控查询

主节点监控依赖于 pg_stat_replication 视图，延迟信息查询通过以下语句：

#!/bin/bash
cOnninfo="psql -U postgres -d postgres -A -c"
MONITOR_SQL1="
with t as
(
select pg_size_pretty(pg_wal_lsn_diff(sent_lsn,replay_lsn)) unreplay_size
from pg_stat_replication
)
select unreplay_size未落盘数据大小 from t
"
MONITOR_SQL2="
with t as
(
select pg_size_pretty(pg_wal_lsn_diff(sent_lsn,flush_lsn)) unwrite_segment_size
from pg_stat_replication
)
select unwrite_segment_size未写入wal段大小 from t
"
${conninfo} "${MONITOR_SQL1}"
${conninfo} "${MONITOR_SQL2}"

在主节点创建该脚本文件，并执行即可查看，如下：

4.2 从节点监控查询

vi monitor.sh
#!/bin/bash
cOnninfo="psql -U postgres -d postgres -A -c"
MONITOR_SQL1="
with t as
(
select pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(),pg_last_wal_replay_lsn())) unreplay_size
)
select unreplay_size未落盘数据大小 from t
"
${conninfo} "${MONITOR_SQL1}"

在从节点执行查看如下：

PostgreSQL主从架构方案

2.1 云数据库信息查看

2.2 参数配置参考