Replication (physical)

Note that queries may need to be executed on the source or target instances.

Source: Current WAL LSN

SELECT pg_current_wal_lsn();

Target: Last WAL LSN Received and Applied

SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()

Pause WAL Replay/Apply

SELECT pg_wal_replay_pause();

Resume WAL Replay/Apply

SELECT pg_wal_replay_resume();

Replication Lag (in bytes)

-- pgMonitor Exporter (ccp_replication_lag_size)
SELECT 
    client_addr as replica, client_hostname as replica_hostname, client_port as replica_port,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) as bytes
FROM 
    pg_catalog.pg_stat_replication

Replication Lag (in seconds)

-- pgMonitor Exporter (ccp_replication_lag)
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())::INTEGER
     END AS replay_time,
     EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER AS received_time

Replication Slots

-- pgMonitor Exporter (ccp_replication_slots)
SELECT 
    slot_name, active::int, pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn) AS retained_bytes 
FROM 
    pg_catalog.pg_replication_slots;

Replication Status

Gap between pg_current_wal_lsn and sent_lsn may indicate heavy load on source system. Gap between sent_lsn and pg_last_wal_receive_lsn on standby may indicate network delay or standby under heavy load.

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()
 
-- Source:
select pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, backend_xmin,
       state, pg_current_wal_lsn() as current_wal_lsn, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag,
       replay_lag, sync_priority, sync_state, reply_time
from pg_stat_replication;
 
-- Target:
select * from pg_stat_wal_receiver;

Replication Lag

Execute on replication target.

SELECT now() - pg_last_xact_replay_timestamp()