Replication, failover, and upgrades

Physical versus logical

Basic physical observations:

-- primary
SELECT application_name, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

-- standby
SELECT pg_is_in_recovery(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

With no new transactions, time-based replay delay can be null or misleading. Also inspect LSN distance, WAL rate, and service health.

Replication slots

Slots prevent required WAL from disappearing too early, but retain disk indefinitely when a consumer stops. Alert on slot lag and pg_wal capacity; confirm no consumer depends on a slot before dropping it.

SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

Failover is not one command

A runbook confirms the primary is truly unavailable, evaluates unsent WAL, promotes the target, fences the old primary from writes, updates routing, verifies writes and background jobs, and rebuilds redundancy. Without fencing, split brain is possible.

Upgrade paths

• Minor: fixes within a major; generally replace binaries and restart, but read release notes.
• Major: requires pg_upgrade, logical dump/restore, or logical replication; data directories are not forward-compatible.
• You can skip intervening majors, but read every intervening release note and verify extension support.

Major cutover checklist

1. Inventory extensions, collations, types, drivers, and topology.
2. Rehearse on a restored production copy; measure downtime, disk, and ANALYZE time.
3. Run application tests, critical-plan comparisons, and data validation.
4. Make source of truth explicit during freeze or dual-write.
5. Before cutover, confirm catch-up, clear long transactions, and retain the rollback window.
6. After cutover, refresh statistics and inspect invalid objects, errors, performance, and backups.

For the major currently in testing, use the PostgreSQL 19 release and 18-to-19 upgrade guide to check Beta status, incompatibilities, and the pg_upgrade --check workflow.