PostgreSQL 流复制异步转同步

非常重要的synchronous_commit参数

流复制的同步方式，有主库配置文件postgresql.conf，中的synchronous_commit控制着。所以理解该参数的配置十分重要。

单实例环境

参数值	说明	优点	缺点
on 或 local	当事务提交时，WAL先写入WAL buffer 再写到 WAL文件（落盘）中。设置为on表示提交事务时需要等待本地WAL最终落盘后，才向客户端返回成功。	非常安全	数据库性能有损耗
off	当事务提交时，不需要等待WAL先写入WAL buffer 再写到 WAL文件（落盘）中。	提升数据库性能	数据库宕机是最新提交的少量事务可能丢失

流复制环境

参数值	说明	优点	缺点
remote_write	当主库提交事务后，需等待备库接收主库发送的WAL日志流并写入WAL buffer， 就向客户端返回成功	只有主库的WAL是落盘的	事务响应时间快
on	当主库提交事务后，需等待备库接收主库发送的WAL日志流并写入WAL buffer 以及写入WAL文件， 就向客户端返回成功	主、备库WAL均落盘，有两份持有化文件保护	事务响应时间相对较慢
remote_apply	当主库提交事务后，需等待备库接收主库发送的WAL日志流并写入WAL buffer 以及写入WAL文件， 同时备库apply之后， 就向客户端返回成功	数据保护最好	影响事务性能

查看同步情况

在主库执行以下SQL ， sync_state字段为async表示异步同步方式

postgres=# select usename , application_name , client_addr,sync_state from pg_stat_replication;
 usename | application_name |  client_addr   | sync_state 
---------+------------------+----------------+------------
 repuser | walreceiver      | 192.168.56.102 | async
(1 row)

配置同步复制

• 主库配置postgresql.conf文件

[postgres@pg01 data]$ vi postgresql.conf 
synchronous_commit = on
synchronous_standby_names = 'walreceiver'

synchronous_commit : 开篇提到的那个重要参数！
synchronous_standby_names： 这里的name填写，刚刚查询到的application_name。

• 重启主库服务

[root@pg01 PG_12_201909212]# service postgresql-12 restart
Stopping postgresql-12 service:                            [  OK  ]
Starting postgresql-12 service:                            [  OK  ]

再次查看主库字典

postgres=# select usename , application_name , client_addr,sync_state from pg_stat_replication;
 usename | application_name |  client_addr   | sync_state 
---------+------------------+----------------+------------
 repuser | walreceiver      | 192.168.56.102 | sync

数据保护测试

1. 关闭备库。模拟备库宕机无法正常接收WAL

[root@pg02 ~]# service postgresql-12 stop
Stopping postgresql-12 service:                            [  OK  ]

2. 主库尝试进行DML操作

dong=# insert into t1 select * from t1;




Cancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 8

由于备库已关闭，无法接受从主库传来的WAL，根据同步规则，主库需要一直等待主库接收到WAL的消息。
手动进行了cancel， 数据库报错。说明在等待备库reguest相应。

所以，sync同步模式虽然可以很好的保护数据，但同时也带来了性能的影响，需慎重