获取当前复制延迟

时间延迟

从备机端查看

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;

随着pg_lsn数据类型的引入，从PostgreSQL 9.4开始，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;