pg 设置同步复制模式

数据库版本：

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

01，检查数据库同步状态：

目前同步状态为sync_state       | async；

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 762
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 36328
backend_start    | 2021-10-24 13:56:31.967546+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000148
write_lsn        | 0/3000148
flush_lsn        | 0/3000148
replay_lsn       | 0/3000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-10-24 16:21:32.74575+08

02，备数据库修改： postgresql.auto.conf

添加：application_name=standby01

-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'application_name=standby01 user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=disable host=127.0.0.1 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
-bash-4.2$

重启备库：

-bash-4.2$ pg_ctl -D /data/s001 restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2021-10-24 16:24:50.825 CST [753] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2021-10-24 16:24:50.826 CST [753] LOG:  listening on IPv6 address "::1", port 5433
2021-10-24 16:24:50.826 CST [753] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-10-24 16:24:50.835 CST [753] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2021-10-24 16:24:50.883 CST [754] LOG:  database system was shut down in recovery at 2021-10-24 16:24:50 CST
2021-10-24 16:24:50.886 CST [754] LOG:  entering standby mode
2021-10-24 16:24:50.903 CST [754] LOG:  redo starts at 0/3000060
2021-10-24 16:24:50.903 CST [754] LOG:  consistent recovery state reached at 0/3000148
2021-10-24 16:24:50.903 CST [754] LOG:  invalid record length at 0/3000148: wanted 24, got 0
2021-10-24 16:24:50.905 CST [753] LOG:  database system is ready to accept read only connections
2021-10-24 16:24:50.926 CST [758] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
 done
server started

03，修改主数据库参数

synchronous_commit = on

synchronous_standby_names = 'standby01'

postgres=# alter system set synchronous_commit = on;
ALTER SYSTEM
postgres=# alter system set synchronous_standby_names = 'standby01';
ALTER SYSTEM
postgres=# show synchronous_standby_names;
 synchronous_standby_names
---------------------------

(1 row)

postgres=#
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 on
(1 row)

04，重新加载配置文件，并检查数据库同步状态

#已经修改为sync.

-bash-4.2$ pg_ctl -D /data/master reload
server signaled
-bash-4.2$ psql
psql (13.4)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 759
usesysid         | 10
usename          | postgres
application_name | standby01
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 48124
backend_start    | 2021-10-24 16:24:50.919185+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000148
write_lsn        | 0/3000148
flush_lsn        | 0/3000148
replay_lsn       | 0/3000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | sync
reply_time       | 2021-10-24 16:29:01.384411+08

#测试完成，备数据库重启，主数据库重新reload配置文件即可修改完成。