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

 

1.在主节点查看

?

1	select * from pg_stat_replication;

2.在从节点查看

版本10之前：

?

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

　　

?

1 2 3 4

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开始：

?

1	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();

　　

?

1 2 3 4

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.如果有延迟

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

?

1 2 3 4 5

#版本10之前 select pg_current_xlog_location();   #版本10开始 SELECT pg_current_wal_lsn();

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

?

1	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.查看主从之间的延迟日志有多少

函数：​

?

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

　　

?

1 2 3 4 5 6 7 8 9 10

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