Reading collection

PostgreSQL Replication: Failover and Read Scaling

Decide what replication must protect, set stale-read and write-loss limits, and rehearse failover across the application and database.

Editorial update

Decision in brief

Choose replication for an explicit failure or read-scaling requirement. Prefer asynchronous replicas when the workload tolerates delayed reads and defined data loss on promotion. Consider synchronous acknowledgment when the write-loss budget requires it, and test the added latency and behavior when acknowledgments become unavailable. Keep a separately recoverable backup for accidental or bad writes.

Plan for failure: Async — Allow stale reads. Sync — Measure write delay. Backup — Test restore.
Set loss and latency budgets. Keep a separately recoverable backup for accidental or bad writes.

Map the failure to the replication design

Write a recovery time objective and a recovery point objective for loss of a process, a host, or a location. State where the next primary may be promoted and which dependencies could fail with it. A read replica, a standby eligible for promotion, and a logical change consumer serve different jobs.

PostgreSQL streaming replication is asynchronous by default. Synchronous settings define where configured standbys must acknowledge progress before a commit returns. In MySQL asynchronous replication, a replica can be behind the source. MySQL semisynchronous replication adds acknowledgment that a replica received and logged events, while applying those events is a separate step. A timeout can return the source to asynchronous operation, so check whether that fallback fits your write-loss limit. Confirm the commit and read semantics for the exact version and configuration you use.

GoalDesign to evaluateFailure to include
Serve reports with acceptable delayA read replica and an explicit staleness limitPause replay and verify the application routes read-after-write requests to a current source.
Recover quickly after primary failureA promotion-ready standby in a separate failure domainRemove the primary and time reconnect, promotion, and acceptance of correct writes.
Limit loss of acknowledged writesSynchronous acknowledgment where available and correctly configuredMeasure commit p95 during replica loss and reconcile acknowledged writes after promotion.
Feed analytics or downstream systemsA logical publication or another scoped change feedPause and resume the consumer, then account for duplicates, missing events, and retained logs.

Two HN accounts that change the failure drill

lukaslalinsky described running sizable self-hosted PostgreSQL systems and emphasized checking that streaming replication keeps working. Give the on-call owner a concrete alarm for stalled replay or a standby that loses its promotion readiness.

exabrial recalled a MySQL split-brain incident that required identifying individual transactions and replaying them against the new primary. Add a rehearsal that proves the old primary is isolated before clients resume writes and that every acknowledged test transaction can be reconciled.

Measure replica lag, promotion, and client recovery

  1. In an isolated test topology, seed synthetic data and generate timestamped write IDs. Record the acknowledged IDs outside the database and decide how much loss, staleness, and downtime the application can tolerate.
  2. Under representative load, stop one standby in staging. Measure commit latency, receive and replay lag, and whether the alert arrives within the operator response budget.
  3. Run the intended failover procedure after isolating the old primary. Time the first healthy application read and write, check client reconnection and background consumers, and reconcile acknowledged IDs. Rebuild or rejoin the old node through the documented procedure.
  4. Approve only if the old primary stays excluded, no unexplained transaction gap remains, and application-visible downtime and write loss meet target. Stop when two writers can be active or a replica or consumer cannot resume safely.

Related articles