PG12 流复制搭建主备高可用环境

PostgreSQL 12中的流复制是一种主从复制机制，备服务器通过TCP流从主服务器中同步相应的数据，主服务器在WAL记录产生时即将它们以流式传送给备服务器，而无需等到WAL文件被填充。这种方式可以实现同步和异步两种复制方式。

在物理复制方式下，备库与主库之间的复制更类似于Oracle的物理数据 Guard。其优点在于物理层面完全一样，保证了数据的一致性，可靠性较高，且延迟较低。在进行事务操作时，一旦事务产生REDO record，就会实时地在备库中应用，使得事务结束时备库中就能见到数据。

如果需要删除从库中的数据文件，可以通过将主库中的数据文件拉取到从库中来实现。需要注意的是，在PostgreSQL 9.0之前的版本中，流复制的备库需要通过在$PGDATA目录中创建recovery.conf文件来进行标识。而在PostgreSQL 9.0及之后的版本中，这个文件不再是流复制部署的重要文件，因为流复制备库的启动和运行是由主服务器进行控制的。

总的来说，PostgreSQL 12中的流复制是一种高效的主从复制机制，能够保证数据的一致性、可靠性和低延迟性，同时提供了多种复制方式以满足不同的需求。

一、环境介绍

操作系统：CentOS 7.6
PostgreSQL:12.2
主库IP:192.168.18.12
备库IP:192.168.18.11
基目录：/data/pg12/base
数据目录：/data/pg12/data

二、主备机参数配置
1. 配置前，查看数据库集簇状态

[postgres@test2 pg12]$ pg_controldata
pg_control version number: 1201
Catalog version number: 201909212
Database system identifier: 7111963389498958789
Database cluster state: in production
pg_control last modified: Thu 23 Jun 2022 08:37:37 PM CST
Latest checkpoint location: 0/EE9B3A8
Latest checkpoint's REDO location: 0/EE9B370
Latest checkpoint's REDO WAL file: 00000001000000000000000E
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:732
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 479
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 732
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Thu 23 Jun 2022 08:37:37 PM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 16384
Blocks per segment of large relation: 65536
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 4044
Size of a large-object chunk: 4096
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 2efc2c7120fecb63b62a112ab4a032205717729b9adec1e40e53fd99dd180bc3

2. 修改配置文件postgresql.conf，主备配置一致

wal_level = replica # minimal, replica, or logical
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = '/usr/bin/lz4 -q -z %p /data/pg12/data/archive_dir/%f.lz4'
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 512 # in logfile segments; 0 disables
hot_standby = on # "off" disallows queries during recovery

wal_level
wal_level参数控制WAL日志信息的输出级别，有三种模式:minimal,replica,logical
replica:会记录支持WAL归档、复制和备库中启用只读查询等操作所需的WAL信息
此参数默认值是replica
调整此参数需要重启数据库生效
开启流复制至少需要设置此参数为replica

archive_mode
控制是否启用归档
on表示启用归档并使用archive_command参数配置命令将WAL日志归档到归档存储上
调整此参数需要重启数据库生效
通常设置为on

archive_command
设置WAL归档命令
可以将WAL归档到备机目录，也可以归档到远程其他主机

max_wal_sender
控制主库上的最大WAL发送进程数，通过pg_basebackup命令在主机上做基准备份时也会消耗WAL进程，此参数不能比max_connections参数值高，默认值为10，一个流复制备库通常只需要消耗流复制主库一个WAL发送进程

wal_keep_segmengts
设置主库pg_wal目录保留的最小WAL日志文件数，以便备库落后主库时可以通过主库保留的WAL进行追回，这个参数设置得越大，理论上备库在异常断开时追平主库的几率越大

3. 配置pg_hba.conf文件

主库备库pg_hba.conf配置完全一致（主备库会发生切换）

host replication repuser 192.168.18.12/32 md5
host replication repuser 192.168.18.11/32 md5

4、重启主机

[postgres@test2 ~]$ pg_ctl stop
waiting for server to shut down....2022-06-24 04:27:24.857 CST [1233] LOG: received fast shutdown request
2022-06-24 04:27:24.860 CST [1233] LOG: aborting any active transactions
2022-06-24 04:27:24.862 CST [1233] LOG: background worker "logical replication launcher" (PID 1241) exited with exit code 1
2022-06-24 04:27:24.862 CST [1235] LOG: shutting down
2022-06-24 04:27:24.968 CST [1233] LOG: database system is shut down
done
server stopped
[postgres@test2 ~]$ pg_ctl -D $PGDATA -s -l $PGHOME/logfile -o "-p 1525" start

三、主库创建流复制用户

create user repuser
replication
login
connection limit 5
encrypted password 're12a345';

四、拷贝数据文件方式同步数据

1. 主库发起在线备份

postgres=# select pg_start_backup('test_bk1');
pg_start_backup
-----------------
1/86000060
(1 row)
2. 打包data目录
tar czvf data.tar.gz data
[postgres@test2 pg12]$ ll
total 129992
drwxr-xr-x. 6 postgres postgres 71 Jun 22 15:30 base
drwx------. 21 postgres postgres 4096 Jun 24 04:30 data
-rw-r--r--. 1 postgres postgres 106427201 Jun 24 04:32 data.tar.gz
drwxrwxrwx. 6 postgres postgres 4096 Jun 22 15:23 postgresql-12.2
-rw-r--r--. 1 postgres postgres 26669211 Feb 20 2021 postgresql-12.2.tar.gz

3. 将数据文件拷贝到备节点

[postgres@test2 pg12]$ scp ./data.tar.gz postgres@test1:/data/pg12
postgres@test1's password:
data.tar.gz 100% 101MB 43.6MB/s 00:02
[postgres@test2 pg12]$

4. 如果备机已安装数据库，将备机停止，改数据文件夹名称

[root@test1 pg12]# mv data data.old

5. 备机解压data

[root@test1 pg12]# tar -zxvf data.tar.gz

6.主机停止在线备份

postgres=# select pg_stop_backup();
NOTICE: all required WAL segments have been archived
pg_stop_backup
----------------
0/10000170
(1 row)

postgres=#

7. 备机配置recovery.conf

[postgres@test1 data]$ more recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.18.12 port=1525 user=repuser'
[postgres@test1 data]$

recovery_target_timeline
设置恢复的时间线
latest恢复到最近的时间线

standby_mode
是否启用数据库为备库
on：备库会不停地从主库上获取WAL日志流，直到获取主库上最新的WAL日志流

primary_conninfo
设置主库的连接信息

8. 配置.pgpass隐藏文件

[postgres@test1 data]$ cd
[postgres@test1 ~]$ touch .pgpass
[postgres@test1 ~]$ chmod 0600 .pgpass
[postgres@test1 ~]$ vi .pgpass
192.168.0.12:1525:replication:repuser:re12a345
192.168.0.11:1525:replication:repuser:re12a345

4、启动备库

pg_ctl -D $PGDATA -s -l $PGHOME/logfile -o "-p 1525" start

五、基于pg_basebackup的数据同步

1、使用pg_basebackup 同步数据

pg_basebackup -D /data/pg12/data -Fp -Xs -v -P -h 192.168.18.12 -p 1525 -U repuser

2、启动备库

pg_ctl -D $PGDATA -s -l $PGHOME/logfile -o "-p 1525" start

3、查看复制状态
主库

test=# \x
Expanded display is on.
test=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 6792
usesysid | 16568
usename | repuser
application_name | walreceiver
client_addr | 192.168.18.11
client_hostname |
client_port | 24309
backend_start | 2022-06-24 05:46:23.986066+08
backend_xmin |
state | streaming
sent_lsn | 0/1E02E8D8
write_lsn | 0/1E02E8D8
flush_lsn | 0/1E02E8D8
replay_lsn | 0/1E02E8D8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2022-06-24 05:58:13.635549+08

test=#

备库

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 27764
status | streaming
receive_start_lsn | 0/1E000000
receive_start_tli | 1
received_lsn | 0/1E000148
received_tli | 1
last_msg_send_time | 2022-06-24 05:50:31.811292+08
last_msg_receipt_time | 2022-06-24 05:50:53.74157+08
latest_end_lsn | 0/1E000148
latest_end_time | 2022-06-24 05:48:01.555934+08
slot_name |
sender_host | 192.168.18.12
sender_port | 1525
conninfo | user=repuser passfile=/home/postgres/.pgpass dbname=replication host=192.168.18.12 port=1525 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any

4 、验证同步
主库

postgres=# create database test;
CREATE DATABASE
postgres=# \c test;
You are now connected to database "test" as user "postgres".
test=# create table t1 (id int);
CREATE TABLE
test=# insert into t1 values(1);
INSERT 0 1
test=# \x=

备库

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 |
template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 |
(4 rows)

postgres=#
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from t1;
id
----
1
(1 row)