获取当前的复制延迟

时间延迟

从备用服务器看到的

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 文件和重放的 WAL 文件之间存在差异时才计算延迟（请参阅 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

字节延迟

从主服务器看到的（PostgreSQL 9.2 及更高版本，因为有 pg_xlog_location_diff()）

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;