PostgreSQL Streaming Replication and Failover Cheat Sheet

SHOW server_version;
SHOW wal_level;
SHOW max_wal_senders;
SHOW max_replication_slots;
SHOW archive_mode;

CREATE ROLE standby_rep LOGIN REPLICATION;
\password standby_rep

Note: Add a narrow pg_hba.conf rule such as a hostssl replication entry for the standby address and SCRAM authentication. Never store the real password in this SQL.

pg_basebackup \
  --host=primary.example \
  --username=standby_rep \
  --pgdata=/pgdata/18 \
  --write-recovery-conf \
  --wal-method=stream \
  --create-slot --slot=standby_a \
  --checkpoint=fast --progress --verbose

Note: Run only against an approved source and an empty target. --checkpoint=fast can increase primary I/O. Use PostgreSQL 18 client tools for a PostgreSQL 18 cluster and supply credentials outside the command line.

SELECT slot_name, active, restart_lsn, wal_status, safe_wal_size, invalidation_reason,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
WHERE slot_type = 'physical'
ORDER BY slot_name;

SHOW wal_keep_size;
SHOW max_slot_wal_keep_size;

Note: A null or low safe_wal_size requires investigation. Do not drop or advance a slot as a disk-space shortcut unless the standby is intentionally being rebuilt.

SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_gap,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) AS flush_gap,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_gap,
       write_lag, flush_lag, replay_lag, reply_time
FROM pg_stat_replication
ORDER BY application_name;

SELECT pg_is_in_recovery() AS is_standby,
       pg_last_wal_receive_lsn() AS received,
       pg_last_wal_replay_lsn() AS replayed,
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_queue_bytes,
       pg_last_xact_replay_timestamp() AS last_replayed_commit;

SELECT status, sender_host, sender_port, slot_name, written_lsn, flushed_lsn
FROM pg_stat_wal_receiver;

SHOW synchronous_standby_names;
SHOW synchronous_commit;

SELECT application_name, state, sync_state, sync_priority,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication
ORDER BY sync_priority, application_name;

Note: A configured name is not proof that the required standby is healthy. Confirm sync_state and rehearse the latency and availability behavior when candidates disappear.

SHOW hot_standby;
SHOW hot_standby_feedback;
SHOW max_standby_streaming_delay;

SELECT datname, confl_tablespace, confl_lock, confl_snapshot,
       confl_bufferpin, confl_deadlock, confl_active_logicalslot
FROM pg_stat_database_conflicts
ORDER BY datname;

Note: Conflict counters are cumulative. Track rates and correlate them with replay delay, canceled-query logs, and dead-tuple growth on the primary.

-- Candidate, before promotion: must return true.
SELECT pg_is_in_recovery();
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

-- Only after the old primary is fenced and the candidate is selected.
SELECT pg_promote(wait => true, wait_seconds => 60);

-- After promotion: must return false.
SELECT pg_is_in_recovery();

Note: These SQL functions are not a failover system. Promotion is a state-changing operation; never run it merely because a monitoring check timed out.

pg_rewind \
  --target-pgdata=/pgdata/18 \
  --source-server='host=new-primary.example dbname=postgres user=rewind sslmode=verify-full' \
  --dry-run --progress

Note: The target must be stopped and the source connection appropriately privileged. A successful dry-run is not permission to proceed; back up configuration and follow the exact PostgreSQL 18 pg_rewind runbook.