默认情况下,我们搭建的主从是异步模式。如果希望搭建同步模式,需要主库修改:
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;