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;