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

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)

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

[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

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

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

[root@pg01 PG_12_201909212]# ps -ef| grep wal
postgres 21157 21151 0 15:57 "htmlcode">

[root@pg02 ~]# ps -ef | grep wal
postgres 13383 13369 0 14:08 "htmlcode">

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



那我这个就是主库喽~
检查流复制同步情况
先确定主库传到哪儿了
在确定备库接收到哪儿了
最后确定备库应用到哪儿了
检查主库传输
确定主库传到什么位置了


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


检查备库恢复
确定备库接收到哪儿了


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


确定备库应用到哪儿了


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


最近事务应用的时间


postgres=# select pg_last_xact_replay_timestamp();
 pg_last_xact_replay_timestamp 
-------------------------------
 2020-03-05 15:20:22.125688+08
(1 row)


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