PostgreSQL 流复制异步转同步的操作

2023-07-07 13:10:03 73

非常重要的synchronous_commit参数

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

单实例环境

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

流复制环境

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

查看同步情况

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

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

配置同步复制

主库配置postgresql.conf文件

[postgres@pg01data]$vipostgresql.conf
synchronous_commit=on
synchronous_standby_names='walreceiver'

synchronous_commit:开篇提到的那个重要参数！

synchronous_standby_names：这里的name填写，刚刚查询到的application_name。

重启主库服务

[root@pg01PG_12_201909212]#servicepostgresql-12restart
Stoppingpostgresql-12service:[OK]
Startingpostgresql-12service:[OK]

再次查看主库字典

postgres=#selectusename,application_name,client_addr,sync_statefrompg_stat_replication;
usename|application_name|client_addr|sync_state
---------+------------------+----------------+------------
repuser|walreceiver|192.168.56.102|sync

数据保护测试

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

[root@pg02~]#servicepostgresql-12stop
Stoppingpostgresql-12service:[OK]

主库尝试进行DML操作

dong=#insertintot1select*fromt1;
Cancelrequestsent
WARNING:cancelingwaitforsynchronousreplicationduetouserrequest
DETAIL:Thetransactionhasalreadycommittedlocally,butmightnothavebeenreplicatedtothestandby.
INSERT08

由于备库已关闭，无法接受从主库传来的WAL，根据同步规则，主库需要一直等待主库接收到WAL的消息。

手动进行了cancel，数据库报错。说明在等待备库reguest相应。

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

补充：PostgreSQL流复制数据同步检查

如何分辨主、备

看进程

主库–walwriter

[root@pg01PG_12_201909212]#ps-ef|grepwal
postgres2115721151015:57?00:00:00postgres:walwriter
postgres2116821151015:57?00:00:00postgres:walsenderrepuser192.168.56.102(38473)streaming0/2A0001C0

备库–walreceiver

[root@pg02~]#ps-ef|grepwal
postgres1338313369014:08?00:00:01postgres:walreceiverstreaming0/2A0001C0

函数方法

一句话判断哪个是主库、哪个是备库，返回的值：

f为主库

t为备库

postgres=#selectpg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1row)

那我这个就是主库喽~

检查流复制同步情况

先确定主库传到哪儿了

在确定备库接收到哪儿了

最后确定备库应用到哪儿了

检查主库传输

确定主库传到什么位置了

postgres=#selectpg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/2A0001C0
(1row)

检查备库恢复

确定备库接收到哪儿了

postgres=#selectpg_last_wal_receive_lsn();
pg_last_wal_receive_lsn
-------------------------
0/2A0001C0
(1row)

确定备库应用到哪儿了

postgres=#selectpg_last_wal_replay_lsn();
pg_last_wal_replay_lsn
------------------------
0/2A0001C0
(1row)

最近事务应用的时间

postgres=#selectpg_last_xact_replay_timestamp();
pg_last_xact_replay_timestamp
-------------------------------
2020-03-0515:20:22.125688+08
(1row)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。