【PostgreSQL】PostgreSQL复制延迟该检查哪些内容

 

1.在主节点查看

select * from pg_stat_replication;

2.在从节点查看

版本10之前：

select pg_is_in_recovery(),pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp();

　　

SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;

　　

从版本10开始：

select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

　　

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;

3.如果有延迟

查看主库当前的日志位置：

#版本10之前
select pg_current_xlog_location();

#版本10开始
SELECT pg_current_wal_lsn();

查看从库当前的日志位置：

select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

4.查看主从之间的延迟日志有多少

函数：​

pg_wal_lsn_diff(lsn pg_lsn, lsn pg_lsn)
pg_xlog_location_diff (location pg_lsn, location pg_lsn)

　　

postgres=# select pg_wal_lsn_diff('0/925D7E70','0/2705BDA0');                                                                                                                                     
-[ RECORD 1 ]---+-----------
 
pg_wal_lsn_diff | 1800913104

postgres=# select round(1800913104/pow(1024,3.0),2) missing_lsn_GiB;
 
-[ RECORD 1 ]---+-----
 
missing_lsn_gib | 1.68