PostgreSQL14流复制部署教程

背景

部署一主一从的PG测试环境。一晃几年过去了，这是很久以前，毕业后的第一份工作用到的pgsql，当时部署留存的笔记。

主从复制实现

• pgsql的主从复制实现是基于日志传送和流复制两种主要方式；
• 日志传送：
  • 依赖于PostgreSQL的预写日志（WAL）机制，主节点上的任何数据变更首先被记录到WAL日志中，然后这些日志文件会被异步传输到一个或多个从节点上，从节点随后应用这些wal日志文件中的变更，以保持与主服务器的数据一致性；
• 流复制：
  • 在9.x版本引入的更高效复制方式，主节点通过TCP/IP流实时的将wal日志记录发送给一个或多个从节点，从节点接收后会按照顺序应用它们，从而实现与主服务器的数据同步；

• 日志传送和流复制的对比：
• 日志传送：
  1. 日志文件在传输前需要关闭，可能会在高峰时段影响数据的实时一致性；
  2. 需要传输大量的日志文件，对网络带宽要求高；如果主节点故障，需要手动干预确保从库能够继续同步；适用于对实时性要求不高，但需要大量数据传输的场景；
  3. 相比流复制有一定延迟；在性能方面，高峰时段时可能对主库产生较大影响；
• 流复制：
  1. 更低的延迟，数据通过网络流实时传输给从节点；
  2. 提供更强的数据一致性保证，数据几乎实时同步；
  3. 通过网络流方式传输，对网络带宽的压力较小；
  4. 主节点故障时，从库可以自动接管读取操作，甚至写入(特点配置下)；
  5. 对主节点性能影响较小，因为允许在不阻塞主库事务的情况下进行数据同步；
  6. 配置简单，其原生支持的特性更容易维护；

过程

服务器两台准备好---->下载软件，上传至服务器---->rpm安装，部署（两台）------>配置流复制---->验证。

主要步骤

1 服务器两台 操作系统：

[root@sera ~]# more /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

2 下载/上传rpm包。

地址：https://www.educity.cn/ucenter2/shipin/list.html
4个rpm包

postgresql-client    libraries and client binaries
postgresql-server    core database server
postgresql-contrib    additional supplied modules
postgresql-devel    libraries and headers for C language development

上传至服务器。
并修改用户资源限制
vi /etc/security/limits.conf  添加
*          soft     nproc          65535
*          hard     nproc          65535
*          soft     nofile         65535
*          hard     nofile         65535
vi  /etc/sysctl.conf   添加
fs.file-max = 65536
sysctl -p 生效
建用户
useradd -d /home/postgres  -m -g root postgres
[root@afofa-dev-app ~]# more 12.sh
echo "Post1234%" | passwd postgres --stdin > /dev/null 2>&1

3 部署PG实例
注意，此处需要配置yum源，用于解决依赖的包。
使用root进行部署

rpm -ivh postgresql14-libs-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-contrib-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-server-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-contrib-14.1-1PGDG.rhel7.x86_64.rpm
yum install libpython3.6m.so.1.0
rpm -ivh postgresql14-contrib-14.1-1PGDG.rhel7.x86_64.rpm

部署完毕后，进行数据初始化（是不是于MySQL的部署很类似）

[root@afofa-dev-app pgsql-14]# /usr/pgsql-14/bin/postgresql-14-setup  initdb
Initializing database ... OK

将数据路径修改到合适的路径下（默认的可能在/下，这不太安全）

建目录/赋权限
[root@afofa-dev-app etc]# mkdir -p /ofa/postgres_data
[root@afofa-dev-app etc]# chown -R postgres:root /ofa/postgres_data
[root@afofa-dev-app etc]# ls -lld /ofa
drwxr-xr-x 3 root root 27 Dec  1 12:28 /ofa
[root@afofa-dev-app etc]# ls -lld /ofa/postgres_data
drwxr-xr-x 2 postgres root 6 Dec  1 12:28 /ofa/postgres_data

vi  /var/lib/pgsql/14/data/postgresql.conf
data_directory='/ofa/postgres_data'
同步数据
rsync -av /var/lib/pgsql/14/data/ /ofa/postgres_data/

重启数据库
systemctl restart postgresql-14

优化参数

show shared_buffers;  设置为物理内存的25%左右，shared_buffers是最有效的用于调优的参数
show wal_buffers; postgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。
show effective_cache_size 提供可用于磁盘高速缓存的内存量的估计值，更高的数值会使得索引扫描更可能被使用. 更低的数值会使得顺序扫描更可能被使用.默认4G
show work_mem; 此配置用于复合排序。强烈建议在会话级别修改此参数值
show maintenance_work_mem; 是用于维护任务的内存设置。默认值为64MB。设置较大的值对于VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。
show synchronous_commit; 此参数的作用为在向客户端返回成功状态之前，强制提交等待WAL被写入磁盘。这是性能和可靠性之间的权衡。如果应用程序被设计为性能比可靠性更重要，那么关闭synchronous_commit。这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。
show checkpoint_timeout;
show checkpoint_completion_target;
用户可以在需要时随时发出CHECKPOINT指令，或者通过PostgreSQL的参数checkpoint_timeout和checkpoint_completion_target来自动完成。

max_connections
允许客户端连接的最大数目

4 配置主从
修改监听地址
psql -c "ALTER SYSTEM SET listen_addresses TO '*';"
查看配置文件位置
postgres=# select name, setting from pg_settings where category='File Locations' ;
       name        |                setting
-------------------+----------------------------------------
 config_file       | /var/lib/pgsql/14/data/postgresql.conf
 data_directory    | /ofa/postgres_data
 external_pid_file |
 hba_file          | /var/lib/pgsql/14/data/pg_hba.conf
 ident_file        | /var/lib/pgsql/14/data/pg_ident.conf
(5 rows)
改配置文件
more /var/lib/pgsql/14/data/postgresql.conf
listen_addresses='*'
创建复制用户
createuser --replication -P -e replicator
密码；5chHEGfC
复制用户添加到配置文件pg_hba.conf中
vi /var/lib/pgsql/14/data/pg_hba.conf
host    replication     replicator      10.50.110.1xx/24         md5
重启
systemctl  restart postgresql-14

配置从服务器
备份旧数据，删掉，然后备份主数据数据
cp -R /ofa/postgres_data/ /ofa/postgres_data.bak
cd /ofa/postgres_data
rm -rf *
使用pg_basebackup工具进行备份
pg_basebackup -h 10.110.115.108 -D /ofa/postgres_data -U replicator -P -v -R -X stream -C -S pgstandby1
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 replication slot "pgstandby1"
26940/26940 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000138
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
当备份结束后，从数据库的data_directory已经有数据，并且一个standby.signal被创建。
[postgres@afofa-dev-app postgres_data]$ ls -lltrh standby.signal
-rw------- 1 postgres root 0 Dec  1 14:03 standby.signal

这表示一个从数据库运行在hot standby node[配置文件中默认参数hot_standby=on]
回到主数据库，通过查询视图进行验证
[postgres@afofa-dev-app ~]$  psql -c "SELECT * FROM pg_replication_slots;"
 slot_name  | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------
 pgstandby1 |        | physical  |        |          | f         | f      |            |      |              | 0/2000000   |                     | reserved   |               | f
(1 row)

启动从服务器

验证streaming replication
 一旦主从复制链接上，就可以在从服务器上看到一个WAL receiver进程，用如下视图查看
[postgres@afofa-dev-app ~]$  psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"
Expanded display is on.
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 47155
status                | streaming
receive_start_lsn     | 0/3000000
receive_start_tli     | 1
written_lsn           | 0/3000148
flushed_lsn           | 0/3000148
received_tli          | 1
last_msg_send_time    | 2021-12-01 14:19:36.95701+08
last_msg_receipt_time | 2021-12-01 14:19:36.957251+08
latest_end_lsn        | 0/3000148
latest_end_time       | 2021-12-01 14:18:06.815422+08
slot_name             | pgstandby1
sender_host           | 10.110.115.108
sender_port           | 5432
conninfo              | user=replicator password=******** channel_binding=prefer dbname=replication host=10.110.115.108 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
在主库上有一个WAL sender进程，state是streaming， sync_state是async,如下：
[postgres@afofa-dev-app ~]$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;"
Expanded display is on.
-[ RECORD 1 ]----+------------------------------
pid              | 48001
usesysid         | 16384
usename          | replicator
application_name | walreceiver
client_addr      | 10.110.115.109
client_hostname  |
client_port      | 54090
backend_start    | 2021-12-01 14:18:06.799575+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000148
write_lsn        | 0/3000148
flush_lsn        | 0/3000148
replay_lsn       | 0/3000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-12-01 14:21:47.241286+08
async表示异步复制，后面将会将如何调整为同步复制