PostgreSQL 之异步流复制转同步流复制
异步流复制转同步流复制
使用pg_basebackup进行搭建流复制后，会自动生成一个postgresql.auto.conf文件，默认是异步流复制。
在转为同步流复制过程中，更改postgresql.conf参数后，无法生效，需要对postgresql.auto.conf中的参数进行修改。
环境背景
| 数据库版本 | IP | 端口 | |
|---|---|---|---|
| 主库 | 13.2 | 192.168.60.190 | 5432 |
| 备库 | 13.2 | 192.168.60.191 | 5432 |
查看复制状态
主库
postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 99423 usesysid | 41760 usename | replixs application_name | walreceiver client_addr | 192.168.60.191 client_hostname | client_port | 39690 backend_start | 2022-01-10 14:24:11.976964+08 backend_xmin | state | streaming sent_lsn | 3/2E036AA8 write_lsn | 3/2E036AA8 flush_lsn | 3/2E036AA8 replay_lsn | 3/2E036AA8 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2022-01-10 15:34:02.582614+08 postgres=#
备库
postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) postgres=# select * from pg_stat_wal_receiver ; -[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pid | 12307 status | streaming receive_start_lsn | 3/2E000000 receive_start_tli | 1 written_lsn | 3/2E036AA8 flushed_lsn | 3/2E036AA8 received_tli | 1 last_msg_send_time | 2022-01-10 15:35:22.602035+08 last_msg_receipt_time | 2022-01-10 15:35:22.602883+08 latest_end_lsn | 3/2E036AA8 latest_end_time | 2022-01-10 15:24:21.931948+08 slot_name | sender_host | 192.168.60.190 sender_port | 5432 conninfo | user=replixs password=******** channel_binding=disable dbname=replication host=192.168.60.190 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any postgres=#
异步转同步
参数查看
主库
postgres=# select name,setting from pg_settings where name in('synchronous_commit','synchronous_standby_names'); name | setting ---------------------------+--------- synchronous_commit | on synchronous_standby_names | (2 rows) postgres=#
备库
postgres=# select name,setting from pg_settings where name in('synchronous_commit','synchronous_standby_names','primary_conninfo'); name | setting ---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- primary_conninfo | user=replixs password=replixs channel_binding=disable host=192.168.60.190 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_ attrs=any synchronous_commit | on synchronous_standby_names | (3 rows) postgres=#
备库参数文件
[postgres@pgs1 data]$ cat postgresql.conf |grep primary_conninfo
#primary_conninfo = '' # connection string to sending server
[postgres@pgs1 data]$ cat postgresql.auto.conf |grep primary_conninfo
primary_conninfo = 'user=replixs password=replixs channel_binding=disable host=192.168.60.190 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
[postgres@pgs1 data]$
在PostgreSQL中通过pg_basebackup进行基础备份的时候（由于加了-R参数），默认就会在postgresql.auto.conf文件中加入primary_conninfo参数。
但是，该文件中只有primary_conninfo参数，当我们想要将流复制搭建为同步的方式的时候，必须配置两个参数：synchronous_standby_names和synchronous_commit。
修改参数
主库
[postgres@lyp data]$ more postgresql.conf |grep synchronous_standby_names
#synchronous_standby_names = '' # standby servers that provide sync rep
[postgres@lyp data]$ echo 'synchronous_standby_names = 'pgs1'' >> postgresql.conf
[postgres@lyp data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-01-10 16:12:23.337 CST [105850] LOG: redirecting log output to logging collector process
2022-01-10 16:12:23.337 CST [105850] HINT: Future log output will appear in directory "pg_log".
done
server started
[postgres@lyp data]$ psql
psql (13.2)
Type "help" for help.
postgres=# select name,setting from pg_settings where name in('synchronous_commit','synchronous_standby_names');
name | setting
---------------------------+---------
synchronous_commit | on
synchronous_standby_names | pgs1
(2 rows)
postgres=#
备库
postgres=# alter system set primary_conninfo = 'application_name=pgs1 user=replixs password=replixs channel_binding=disable host=192.168.60.190 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'; ALTER SYSTEM postgres=# \q [postgres@pgs1 data]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2022-01-10 16:14:40.949 CST [20063] LOG: redirecting log output to logging collector process 2022-01-10 16:14:40.949 CST [20063] HINT: Future log output will appear in directory "pg_log". done server started [postgres@pgs1 data]$ psql psql (13.2) Type "help" for help. postgres=# select name,setting from pg_settings where name in('synchronous_commit','synchronous_standby_names','primary_conninfo'); name | setting ---------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- primary_conninfo | application_name=pgs1 user=replixs password=replixs channel_binding=disable host=192.168.60.190 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=po stgres target_session_attrs=any synchronous_commit | on synchronous_standby_names | (3 rows) postgres=#
检查复制状态
主库
postgres=# select pg_is_in_recovery(); -[ RECORD 1 ]-----+-- pg_is_in_recovery | f postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 105991 usesysid | 41760 usename | replixs application_name | pgs1 client_addr | 192.168.60.191 client_hostname | client_port | 39696 backend_start | 2022-01-10 16:14:40.988759+08 backend_xmin | state | streaming sent_lsn | 3/2F0001C0 write_lsn | 3/2F0001C0 flush_lsn | 3/2F0001C0 replay_lsn | 3/2F0001C0 write_lag | 00:00:00.000516 flush_lag | 00:00:00.000811 replay_lag | 00:00:00.000951 sync_priority | 1 sync_state | sync reply_time | 2022-01-10 16:17:24.674737+08 postgres=#
备库
postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) postgres=# \x Expanded display is on. postgres=# select * from pg_stat_wal_receiver ; -[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ pid | 20069 status | streaming receive_start_lsn | 3/2F000000 receive_start_tli | 1 written_lsn | 3/2F0001C0 flushed_lsn | 3/2F0001C0 received_tli | 1 last_msg_send_time | 2022-01-10 16:17:54.702462+08 last_msg_receipt_time | 2022-01-10 16:17:54.703663+08 latest_end_lsn | 3/2F0001C0 latest_end_time | 2022-01-10 16:17:24.673016+08 slot_name | sender_host | 192.168.60.190 sender_port | 5432 conninfo | user=replixs password=******** channel_binding=disable dbname=replication host=192.168.60.190 port=5432 application_name=pgs1 fallback_application_name=walreceiver sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any postgres=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
