【APM】PGSQL监视器获取复制信息语句

对于部署了 HA 的 PostgreSQL，在 APM 中监控一个固定 IP，主备库分别各有一个不同的 IP。

APM 监控中的“复制”页签可查看主备库复制信息详情。

获取信息的查询语句如下：

PostgreSQL 9.2 及以下版本:

SELECT pid, application_name, client_addr, client_port, state, sync_state, (pg_xlog_location_diff(sent_location,flush_location) / 1024)::bigint as receiving_lag, (pg_xlog_location_diff(flush_location,replay_location) / 1024)::bigint as replaying_lag, (pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) / 1024)::bigint as replication_byte_lag FROM pg_stat_replication

PostgreSQL 9.2 以上版本:

SELECT pid, application_name, client_addr, client_port, state, sync_state, (pg_wal_lsn_diff(sent_lsn,flush_lsn) / 1024)::bigint as receiving_lag, (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::bigint as replaying_lag, (pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) / 1024)::bigint as replication_byte_lag FROM pg_stat_replication