PostgreSQL的同步异步复制简介及互相转换

1. PostgreSQL的同步/异步复制简介及互相转换

一、介绍

1.概念

1.1.同步流复制

同步流复制就是当主库发生变化，比如有一条DML语句产生了WAL日志后，通过后台进程传送到备库，备库必须要应用这个日志，然后向主库返回一个成功应用的信号，主库才可以成功的commit；否则主库会一直等待到备库成功应用后，期间的等待就是主库commit后返回成功的时间段。同步流复制是PostgreSQL9.1后才有的。

1.2.异步流复制

异步流复制与同步流复制相反，即主库产生变化，再将WAL日志传递到备库，不需要等待备库应用完成，只需要成功传递WAL日志即返回commit。

2.优缺点

2.1.同步流复制的优缺点

优点：主备库数据无延迟，适用于负载均衡，读写分离的情况。

缺点：会影响主库的提交性能，如果在一个繁忙的业务系统中备库存在异常问题，备库无法应用或者没有及时的返回commit，主库就会出现长时间的等待或者宕机。而且一主一备环境如果配置了synchronous_standby_names参数后，备库一旦出现异常，无法正常写入，主库写操作也会一直等待，如采用同步流复制建议采用一主多备（大于等于两个备库）。

2.2.异步流复制的优缺点

优点：主库提交不受影响，备库出现异常时主库仍然可以正常运行。

缺点：主备库之间会存在数据延迟情况，主备库切换后可能存在数据丢失，而且备库宕机后无明显提示，如果采用复制槽，主库会积攒大量wal文件，会有磁盘爆满的情况出现。

二、同步/异步相互转换

1.异步流复制转换同步流复制

1.1.主库修改参数

alter system set synchronous_standby_names = 'walreceiver';  -- 同步流复制才配置该值
synchronous_commit = on  --默认值为on，一般不需要修改
select pg_reload_conf();

注：synchronous_standby_names 为同步流复制下从库的application_name。

1.2.备库修改参数

## 添加 application_name
vi postgresql.auto.conf
primary_conninfo = 'application_name=walreceiver user=repl password=postgres channel_binding=disable host=192.168.198.146 port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

ALTER DATABASE postgres SET primary_conninfo = 'application_name=walreceiver user=repl password=postgres channel_binding=disable host=192.168.198.146 port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any';

1.3.重新加载配置

pg_ctl reload

1.4.检查同步

## 主库检查
postgres=# select client_addr,state,sync_state from pg_stat_replication;
  client_addr   |   state   | sync_state 
----------------+-----------+------------
 192.168.198.143 | streaming | sync
(1 row)

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 15941
usesysid         | 10
usename          | postgres
application_name | standby1
client_addr      | 192.168.198.143
client_hostname  | 
client_port      | 41592
backend_start    | 2023-12-14 13:29:51.005534+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/43000998
write_lsn        | 0/43000998
flush_lsn        | 0/43000998
replay_lsn       | 0/43000998
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | sync
reply_time       | 2023-12-14 13:37:46.05816+08

## 备库检查
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------
pid                   | 15677
status                | streaming
receive_start_lsn     | 0/43000000
receive_start_tli     | 2
written_lsn           | 0/43000998
flushed_lsn           | 0/43000998
received_tli          | 2
last_msg_send_time    | 2023-12-14 13:38:06.903124+08
last_msg_receipt_time | 2023-12-14 13:38:26.074047+08
latest_end_lsn        | 0/43000998
latest_end_time       | 2023-12-14 13:34:06.516149+08
slot_name             | 
sender_host           | 192.168.198.146
sender_port           | 5432
conninfo              | user=postgres password=******** channel_binding=disable dbname=replication host=192.168.198.146 port=5432 application_name=standby1 fallback_application_name=walreceiver sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

2.同步流复制转换异步流复制

2.1.主库修改参数

alter system reset synchronous_standby_names;
select pg_reload_conf();

2.2.备库修改参数

## 删除 application_name
vi postgresql.auto.conf
primary_conninfo = 'user=postgres password=postgres channel_binding=disable host=192.168.198.146 port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

1.3.重新加载配置

pg_ctl reload

2.4.检查同步

## 主库检查
postgres=# select client_addr,state,sync_state from pg_stat_replication;
  client_addr   |   state   | sync_state 
----------------+-----------+------------
 192.168.198.143 | streaming | async
(1 row)

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 16296
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 192.168.198.143
client_hostname  | 
client_port      | 41594
backend_start    | 2023-12-14 13:54:40.95684+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/43000D40
write_lsn        | 0/43000D40
flush_lsn        | 0/43000D40
replay_lsn       | 0/43000D40
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-12-14 14:03:17.086516+08

## 备库检查
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------
pid                   | 15972
status                | streaming
receive_start_lsn     | 0/43000000
receive_start_tli     | 2
written_lsn           | 0/43000D40
flushed_lsn           | 0/43000D40
received_tli          | 2
last_msg_send_time    | 2023-12-14 14:03:07.740902+08
last_msg_receipt_time | 2023-12-14 14:03:26.911762+08
latest_end_lsn        | 0/43000D40
latest_end_time       | 2023-12-14 13:59:07.345619+08
slot_name             | 
sender_host           | 192.168.198.146
sender_port           | 5432
conninfo              | user=postgres password=******** channel_binding=disable dbname=replication host=192.168.198.146 port=5432 application_name=standby1 fallback_application_name=walreceiver sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any