获取当前复制延迟

时间延迟

从备机端查看

SELECT clock_timestamp() - pg_last_xact_replay_timestamp() AS replication_lag;
  replication_lag
-----------------------
 1 day 07:34:30.745769
(1 row)

延迟（秒）

SELECT extract(epoch FROM (clock_timestamp() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds

注意：如果主节点没有活动，延迟看起来会增加；请仔细检查主节点/上游的字节延迟（见下文）。

稍微复杂一点，尝试通过仅在接收和回放的WAL 文件之间存在差异时才计算延迟，来考虑主节点/上游缺乏活动的情况

SELECT CASE WHEN (pg_last_xlog_receive_location() = pg_last_xlog_replay_location())
        THEN 0
        ELSE EXTRACT(epoch FROM (clock_timestamp() - pg_last_xact_replay_timestamp()))::INT
       END
         AS lag_seconds

字节延迟

从主节点端查看（由于pg_xlog_location_diff()，PostgreSQL 9.2 及更高版本）

SELECT client_hostname, client_addr, 
       pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location)
          AS byte_lag
  FROM pg_stat_replication;

包含来自pg_stat_replication的最有用列的相同查询

SELECT application_name, client_addr, state, sent_location, write_location, flush_location, replay_location,
       pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) AS byte_lag
  FROM pg_stat_replication;

从PostgreSQL 9.4 开始，引入了pg_lsn 数据类型，pg_xlog_location_diff() 可以用简单的减法替换。

适用于PostgreSQL 10 及更高版本的相同查询

SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_stat_replication.sent_lsn - pg_stat_replication.replay_lsn AS byte_lag
  FROM pg_stat_replication;