pg12主从搭建(同步搭建)以及注意问题

默认情况下，我们搭建的主从是异步模式。如果希望搭建同步模式，需要主库修改：

synchronous_standby_names：指定需要同步的机器

synchronous_commit：on

其中synchronous_standby_names对应的是从库中的stb2：

primary_conninfo = 'application_name=stb2 host=172.30.78.7  port=5432 user=repuser password=repuser'

主库执行：

alter system set synchronous_standby_names='stb2'

ostgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 119983 usesysid | 18828 usename | repuser application_name | stb2 client_addr | 172.30.78.2 client_hostname | client_port | 40044 backend_start | 2020-10-20 18:06:19.578585+08 backend_xmin | 2053276310 state | streaming sent_lsn | 103/A9F3570 write_lsn | 103/A9F3570 flush_lsn | 103/A9F3570 replay_lsn | 103/A9F3570 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2020-10-20 18:29:11.648517+08

注意问题：

  之前sync_status一直都是async(异步模式)，无法修改为同步模式。后面发现是因为主库和从库的postgres.aout.conf中相关参数不正确。比如：主库synchronous_standby_names和从库中rimary_conninfo = 'application_name=stb2 。这个需要注意

 

主从同步延迟查询：

SELECT pg_wal_lsn_diff(A .c1, replay_lsn) /(1024 * 1024) AS slave_latency_MB, pg_wal_lsn_diff(A .c1, sent_lsn) /(1024 * 1024) AS send_latency_MB, pg_wal_lsn_diff(A .c1, flush_lsn) /(1024 * 1024) AS flush_latency_MB, state, backend_start, now()::timestamp with time zone FROM pg_stat_replication, pg_current_wal_lsn() AS A(c1) ORDER BY slave_latency_MB, send_latency_MB DESC LIMIT 1;