Database Operational Runbooks

Physical standby must report in_recovery = true. Unexpected results indicate topology or role shifts.

SELECT clock_timestamp() AS sampled_at, current_setting('server_version') AS server_version, pg_is_in_recovery() AS in_recovery;

Inspect byte diffs across sent, write, flush, and replay stages in pg_stat_replication.

SELECT clock_timestamp() AS sampled_at, application_name, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS current_to_sent_bytes, pg_wal_lsn_diff(sent_lsn, write_lsn) AS sent_to_write_bytes, pg_wal_lsn_diff(write_lsn, flush_lsn) AS write_to_flush_bytes, pg_wal_lsn_diff(flush_lsn, replay_lsn) AS flush_to_replay_bytes, write_lag, flush_lag, replay_lag FROM pg_stat_replication;

Inspect received vs replayed bytes and verify replay paused state.

SELECT clock_timestamp() AS sampled_at, pg_last_wal_receive_lsn() AS received_lsn, pg_last_wal_replay_lsn() AS replayed_lsn, pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS received_to_replayed_bytes, pg_is_wal_replay_paused() AS replay_paused, pg_last_xact_replay_timestamp() AS last_replayed_xact_at;

Correlate with network packet loss, standby storage latency, recovery conflicts, and batch write spikes.

SELECT count(*) AS active_long_queries FROM pg_stat_activity WHERE state != 'idle' AND now() - query_start > interval '5 minutes';