PostgreSQL 流复制搭建与维护

前言

PostgreSQL 流复制（Streaming Replication）是 9.0 提供的一种新的 WAL 传递方法。使用流复制时，每当 Primary 节点 WAL 产生，就会马上传递到 Standby 节点，流复制提供 异步 和 同步 两种模式，同步模式可以保障数据 0 丢失。

1. 配置环境

1.1 环境介绍

PostgreSQL 版本：PostgreSQL 12.2
操作系统：CentOS Linux release 7.8.2003 (Core)

1.2 主库白名单

Master 节点配置 pg_hba.conf 表示接受流复制的用户连接：

host    replication     all             0/0                     md5
1

上面这条 SQL 语句的含义是允许任意用户从任何网络（0/0）网络上发起到本数据库的流复制连接，使用MD5的密码认证。

1.3 主库参数配置

# 监听
listen_addresses = '*'

# 流复制客户端的最大并发数，设置为 0 表示禁用复制
max_wal_senders = 10

# WAL 日志级别
wal_level = replica
1
2
3
4
5
6
7
8

上面的参数需要重启 PostgreSQL 服务后生效。

2. 流复制搭建

使用 pg_basebackup 将主库数据备份恢复到 Standby 节点，搭建 异步/同步 流复制，步骤归纳：

1. 准备环境 PostgreSQL 主节点和备节点。
2. 参数调整 pg_hba.conf、postgresql.conf，创建复制用户。
3. 备份主节点的数据，恢复到备节点。
4. 修改 primary_conninfo 启动备库。
5. 检查是否启动成功。

2.1 备份恢复

在主库执行全量备份：

pg_basebackup -D /data/pgsql12/backup  -v -P -X stream -Upostgres -h 127.0.0.1 -p5432 -R
1

将备份 SCP 到备库节点：

scp -r ./backup/ root@172.16.104.56:/data/backup
1

关闭 Standby 节点，清空 Standby 节点的数据文件，或者使用 mv 修改目录名：

# 备份数据目录，或者可以直接清空
mv /data/pgsql12/data /data/pgsql12/data_bak
# 将备份文件转移到数据目录
mv /data/pgsql12/backup /data/pgsql12/data
# 修改文件属组
chown -R postgres:postgres /data/pgsql12
1
2
3
4
5
6

2.2 创建复制用户

主库创建专用于流复制的用户：

CREATE ROLE repl REPLICATION LOGIN PASSWORD 'repl123';
1

2.3 参数修改

PostgreSQL 使用 standby.signal 文件表示实例为 Standby 节点。在使用 pg_basebackup 备份添加 -R 参数（write configuration for replication）会在 postgresql.auto.conf 文件中写入 primary_conninfo 参数信息，这里我们需要按照实际情况调整：

primary_conninfo = 'host=172.16.104.7 port=5432 user=repl password=repl123'
1

2.4 启动并检查

参数配置完成后，启动 Standby 节点即可：

pg_ctl -D /data/pgsql12/data/ -l /data/pgsql12/logs/start.log start
1

在主节点，可通过下方 SQL 查询流复制的监控信息：

postgres=# \x
Expanded display is on.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 27408
usesysid         | 24865
usename          | repl
application_name | walreceiver
client_addr      | 172.16.104.56
client_hostname  | 
client_port      | 40990
backend_start    | 2023-09-05 14:11:56.978627+08
backend_xmin     | 
state            | streaming
sent_lsn         | 6/4001BB0
write_lsn        | 6/4001BB0
flush_lsn        | 6/4001BB0
replay_lsn       | 6/4001BB0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-09-05 15:03:58.408518+08
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

其中 state = streaming 表示流复制状态正常，如果有异常，可以查看 error log 中的信息，或者启动的时候就 tail -f error_log，实时关注输出的异常信息。

2.5 同步流复制

上面 2.1～2.4 是介绍如何搭建 异步 流复制。

PostgreSQL 异步流复制的缺点是当主库损坏的时候，激活备库可能会丢失一部分数据，这于 MySQL 异步复制相同，主库只管发送增量日志，挂掉后可能有部分日志从库还没有接收到，此时发生切换就会出现数据丢失，同步复制可以解决该类问题。不过需要注意的是，如果配置同步复制 Standby 节点挂掉，会导致 Priamry 节点卡住，所以一般会有多个 Standby 节点，至少保障 WAL 同步到一个 Standby 节点。

同步复制配置多加一个 synchronous_standby_names 参数，有 3 种配置方法：

synchronous_standby_names = 's1,s2,s3'
1

在这个例子中，如果有 s1、s2、s3 三台 Standby 节点在运行，意味着 s1 为同步节点，其他节点均为潜在同步节点，即 WAL 只需传递给 s1 节点就可以提交。

synchronous_standby_names = 'FIRST 2 (s1, s2, s3)'
1

在这个例子中，如果有四个后备服务器 s1、s2、s3 和 s4 在运行，两个后备服务器 s1 和 s2 将被选中为同步后备，因为它们出现在后备服务器名称列表的前部。s3 是一个潜在的同步后备，当 s1 或 s2 中的任何一个失效， 它就会取而代之。s4 则是一个异步后备因为它的名字不在列表中。

synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
1

在这个例子中，如果有四台后备服务器 s1、s2、s3 以及 s4 正在运行，事务提交将会等待来自至少其中任意两台后备服务器的回复。s4 是一台异步后备，因为它的名字不在该列表中。

现在我们的架构是一个 Primary 节点一个 Standby 节点，现在通过修改参数调整为 同步流复制 修改主库参数：

# 其中 walreceiver 为 Standby 节点的名字，由 primary_conninfo 中的 application_name 设置
synchronous_standby_names = 'walreceiver'
1
2

修改该参数不需要重启数据库，使用 reload 重新加载配置即可：

pg_ctl reload -D /data/pgsql12/data/
1

在 Primary 节点查询流复制的状态信息：

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 13561
usesysid         | 24865
usename          | repl
application_name | walreceiver
client_addr      | 172.16.104.56
client_hostname  | 
client_port      | 42126
backend_start    | 2023-09-06 17:18:48.297466+08
backend_xmin     | 
state            | streaming
sent_lsn         | 6/50007D0
write_lsn        | 6/50007D0
flush_lsn        | 6/50007D0
replay_lsn       | 6/50007D0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | sync
reply_time       | 2023-09-06 17:45:00.706196+08
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

其中 sync_state 由 async 变为 sync 表示为同步模式。

2.6 同步复制级别

影响同步复制还需要关注一个参数 synchronous_commit 用来设置事务的同步级别：

postgres=# select * from pg_settings where name = 'synchronous_commit';
-[ RECORD 1 ]---+------------------------------------------------------
name            | synchronous_commit
setting         | on
unit            | 
category        | Write-Ahead Log / Settings
short_desc      | Sets the current transaction's synchronization level.
extra_desc      | 
context         | user
vartype         | enum
source          | default
min_val         | 
max_val         | 
enumvals        | {local,remote_write,remote_apply,on,off}
boot_val        | on
reset_val       | on
sourcefile      | 
sourceline      | 
pending_restart | f
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• local：WAL 日志被本地持久化后（不用管远程）事务 commit 就可以返回。
• remote_write：WAL 日志被传到备库的内存中（不必等其被持久化）事务 commit 才返回。
• remote_apply：WAL 日志被传到备库并被 apply，事务 commit 才返回。
• on：WAL 日志被传到备库并被持久化（不必等其被 apply）事务 commit 才返回。
• off：不必等 WAL 日志被本地持久化，也不管是否传到远程，事务 commit 都可以立即返回。

对于同步复制，可选的值有 remote_write、remote_apply、on。

3. 流复制监控

3.1 角色判断

select pg_is_in_recovery();
1

判断数据库是否为主库 f 表示是主库，t 表示属于备库角色。

3.2 主库查看流复制

查看流复制信息，可以在主库查看 pg_stat_replication 视图，可以查看流复制的状态信息：

• sent_lsn：发送 WAL 的位置。
• write_lsn：备库已接收到这部分日志，但还没有刷到磁盘中。
• flush_lsn：备库已把 WAL 写入到磁盘中。
• replay_lsn：备库应用 WAL 的位置。
• sync_state：同步模式。
• state：流复制状态。

select * from pg_stat_replication;
1

输出结果：

postgres=# \x
Expanded display is on.

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 13561
usesysid         | 24865
usename          | repl
application_name | walreceiver
client_addr      | 172.16.104.56
client_hostname  | 
client_port      | 42126
backend_start    | 2023-09-06 17:18:48.297466+08
backend_xmin     | 
state            | streaming
sent_lsn         | 6/50007D0
write_lsn        | 6/50007D0
flush_lsn        | 6/50007D0
replay_lsn       | 6/50007D0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | sync
reply_time       | 2023-09-07 10:06:18.000504+08
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

3.3 延迟监控

使用下方 SQL 可以查看 Standby 节点落后主库多少字节 WAL 日志：

select pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) from pg_stat_replication; 
1

3.4 备库查询复制信息

在备库也可以通过查询 pg_stat_wal_receiver 视图，获得流复制的监控信息：

• pid：WAL 接收进程 ID。
• status：流复制状态，只有 streaming 为正常状态。
• receive_start_lsn：WAL 接收进程启动时使用的第一个 WAL 日志的位置。
• receive_start_tli：WAL 接收进程启动时使用的第一个时间线编号。
• received_lsn：已经接收到并且已经被写入磁盘的最后一个 WAL 日志的位置。
• received_tli：已经接收到并且已经被写入磁盘的最后一个 WAL 日志的时间线编号。
• last_msg_send_time：接收到最后一条 WAL 日志消息后，向主库发回确认消息的发送时间。
• last_msg_receipt_time：备库接收到最后一条 WAL 日志消息的接收时间。
• slot_name：使用复制槽的名称。
• conninfo：连接主库的连接串，密码等安全相关的信息会被隐去。

select * from pg_stat_wal_receiver;
1

postgres=# \x
Expanded display is on.
postgres=# 
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 113395
status                | streaming
receive_start_lsn     | 6/5000000
receive_start_tli     | 3
received_lsn          | 6/50007D0
received_tli          | 3
last_msg_send_time    | 2023-09-07 10:20:00.207856+08
last_msg_receipt_time | 2023-09-07 10:20:00.20971+08
latest_end_lsn        | 6/50007D0
latest_end_time       | 2023-09-06 17:19:46.661221+08
slot_name             | 
sender_host           | 172.16.104.7
sender_port           | 5432
conninfo              | user=repl password=******** dbname=replication host=172.16.104.7 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19