Engineering problem

PostgreSQL High Availability: Failover and Backup

Design a PostgreSQL high-availability cluster around RTO and RPO. Compare synchronous and asynchronous failover and rehearse point-in-time recovery.

Editorial update

Decision in brief

Build PostgreSQL high availability around one writable primary, a promotable standby, a mechanism that prevents the old primary from writing, and a client endpoint that reconnects to the new primary. Maintain independent backups and WAL so you can restore after corruption or an accidental change. Set and test separate recovery objectives for failover and restore.

Drill two failures: Primary loss — Test failover. Data corruption — Test restore. In both drills — Time recovery.
Measure failover and restore separately, including application verification and reconnection.

Choose a topology by failure mode

Recovery time objective (RTO) limits service interruption. Recovery point objective (RPO) limits how much committed history can be lost. Include client reconnection and application verification when you measure RTO. Define the failure scope, such as one node, one zone or the whole region, for each target.

Failure objectiveStart withMain test
Fast recovery from node loss with some data loss toleranceAsynchronous physical standby in another failure domain, controlled promotion and a stable writer endpointUnder peak write load, compare acknowledged transactions with the promoted timeline and measure reconnection time.
Protect acknowledged commits through a specified single failureSynchronous acknowledgement from an eligible standby or quorumTest commit latency and behavior if all required acknowledgers are unavailable. Confirm promotion picks a node with the acknowledged WAL and check settings overrides.
Recovery from bad writes, accidental deletion or cluster lossIndependent base backups, continuously archived WAL and a tested point-in-time restoreRestore to immediately before a bad transaction, validate business records and time the restore through application readiness.
CDC continuity through failoverVersion-appropriate failover slots and verified subscriber configurationTrack slot readiness, event gaps or duplicates, and consumer catch-up after promotion.

A high-availability cluster example and its limits

For a single-region service, put a primary and physical standby in separate zones. Use a managed writer endpoint or an orchestrator with coordination across failure domains. Ensure the old primary is fenced from writes before making a new primary writable, and verify the driver, DNS and connection pool follow the change. For self-managed failover, verify old-primary rejoin with pg_rewind where its prerequisites hold, or rebuild it from a fresh base backup.

Asynchronous streaming can lose acknowledged writes that never reached the promoted standby. With matching settings, synchronous commit can wait for durable flush on selected standbys. The promotion policy must still choose a standby containing that WAL, and required acknowledgements can stall writes during degraded operation. If fresh reads from a standby matter, test replay visibility and read routing explicitly.

Put PostgreSQL backup artifacts and WAL in a separately controlled failure domain with access and retention appropriate to your recovery requirements. Rehearse restoring from the actual retained chain. Keep restore infrastructure and monitoring ready even when the failover cluster meets its availability target.

What Hacker News cases add

In the Debezium failover thread, gunnarmorling distinguished PostgreSQL 16 standby slots from PostgreSQL 17 failover slots. Test the deployed version and actual slot readiness on the promotion target, then verify which event IDs the consumer reads after rerouting.

In the RestoreProof submission, nareshcodes described restoring a copy in a disposable container and checking schema hashes and row counts. Use those checks as a baseline. Also validate representative business totals and run an application read against the restored copy.

Measure failover and point-in-time restore separately

Use isolated staging with a synthetic write stream that assigns every operation a unique ID and records each acknowledgement outside the database. An example gate might be 60 seconds to resume successful writes and zero lost acknowledged transactions for your declared single-node failure scenario. Set the targets from your service requirements.

  1. Start representative writes and keep the application's real pool and routing path in the test. Trigger a controlled primary outage and separately simulate a network partition. Check that only one node can write at any point.
  2. Measure from failure to the first sustained successful client writes. Compare acknowledged operation IDs against the promoted database and reconcile uncertain responses with the same operation IDs. Verify read behavior, old-primary rejoin and CDC catch-up.
  3. In a separate run, record a recovery target before a deliberate change to synthetic data. Restore an independent cluster from the base backup and archived WAL. Check record contents, application smoke tests and elapsed restore time against the disaster-recovery targets.

Related articles