| name | postgres-ha-replication |
| description | PostgreSQL replication and high availability: streaming replication, physical standbys, logical replication, publications, subscriptions, replication slots, WAL shipping, failover, Patroni, repmgr, pg_auto_failover, synchronous commit, and disaster recovery. |
PostgreSQL HA And Replication
Diagnose lag by separating WAL generation, send, write, flush, replay, slots, and apply conflicts. For HA advice, identify whether the system uses native replication only or an orchestrator such as Patroni.
How To Approach A Replication Request
- Establish the topology first: physical or logical, sync or async, cascading, orchestrator (Patroni, repmgr, pg_auto_failover, cloud-managed), and slot usage.
- For lag, locate the stuck stage using
pg_stat_replication LSN columns — the fix differs completely by stage.
- For slot problems, check
pg_replication_slots for inactive slots and wal_status; an abandoned slot is a disk-full and bloat incident in progress.
- For failover questions, separate detection, election, fencing, and client rerouting — native PostgreSQL provides none of these; the orchestrator does.
- For logical replication, verify identity requirements (
REPLICA IDENTITY), what is not replicated (DDL, sequences before PG 17-era workarounds), and conflict state.
Lag Stage Diagnosis
pg_stat_replication on the primary shows the pipeline. Find the first stage where the LSN stops advancing:
| Gap | Meaning | Typical cause |
|---|
pg_current_wal_lsn() → sent_lsn | Primary can't send fast enough | WAL sender saturation, network egress, max_wal_senders |
sent_lsn → write_lsn | Standby slow to receive/write | Network bandwidth/latency, standby disk write |
write_lsn → flush_lsn | Standby fsync slow | Standby storage sync latency |
flush_lsn → replay_lsn | Standby replay behind | Single-threaded recovery vs heavy write load, standby CPU/I/O, replay conflicts, big vacuums/DDL on primary |
Replay lag with healthy receive is the most common pattern: recovery is single-process, so a primary with many parallel writers can outrun one replayer. Query conflicts (max_standby_streaming_delay) also pause replay while queries run.
Slot Health
- A slot pins WAL (
restart_lsn) and, for logical slots, the catalog xmin. Inactive slots retain WAL without bound unless max_slot_wal_keep_size caps them.
wal_status in pg_replication_slots: reserved is fine, extended means over wal_keep_size, unreserved means at risk, lost means the consumer can never catch up and the slot must be recreated.- Always set
max_slot_wal_keep_size on primaries with slots; unbounded retention turns a dead consumer into a primary outage.
- Physical slots do not survive failover to a standby before PG 17 (
sync_replication_slots / failover slots); plan slot recreation into failover runbooks on older versions.
Synchronous Replication Levels
synchronous_commit | Primary waits for | Data-loss window on failover |
|---|
off | Nothing (local WAL flush deferred) | Recent commits even without failover |
local | Local WAL flush | Everything not yet sent to standby |
remote_write | Standby received + written (not fsynced) | Standby OS crash |
on (default with sync standby) | Standby WAL flushed | None for acknowledged commits |
remote_apply | Standby replayed (visible to reads) | None; enables read-your-writes on standby |
synchronous_standby_names controls quorum (FIRST n, ANY n). A sync standby going down blocks commits — always plan for that mode of failure before enabling.
Common Pitfalls
- Reporting lag only in bytes or only in seconds;
replay_lag time columns and pg_wal_lsn_diff() bytes answer different questions.
- Leaving a replication slot behind after decommissioning a standby or a CDC consumer.
- Enabling synchronous replication with a single standby and no plan for commit-blocking when it dies.
- Treating logical replication as a full mirror: DDL and (before recent versions) sequences do not replicate; new tables need publication changes.
- Forgetting
REPLICA IDENTITY on tables without primary keys — updates/deletes fail to publish or apply.
- Promoting a standby without fencing the old primary — split-brain, then a painful
pg_rewind or rebuild.
- Relying on
hot_standby_feedback = on without accounting for the bloat it exports to the primary.
References
references/replication-ha.md - replication modes, slot management, failover procedure, orchestrators, and logical replication limits.../postgres/references/architecture.md - WAL internals and recovery behavior.../postgres/references/versions/postgresql-17.md - failover slots and logical replication updates.
Scripts
scripts/01-replication-status.sql - primary-side streaming replication.scripts/02-replication-slots.sql - slot retention and risk.scripts/03-standby-status.sql - standby replay and receive status.scripts/04-logical-replication.sql - publications, subscriptions, and subscription workers.scripts/05-replication-conflicts.sql - standby conflicts and recovery settings.scripts/06-wal-archive-posture.sql - WAL archiving, sender, receiver, and retention settings.scripts/07-failover-readiness.sql - sync replication, slots, replay delay, and key HA settings.
Cross-Skill Routing
- Base backups, PITR, and archive testing go to
postgres-operations.
- WAL volume, checkpoint, and network/storage sizing go to
postgres-infrastructure.
- Provider-managed replicas and multi-AZ behavior go to
postgres-cloud.
- Slot-induced bloat and vacuum blockage go to
postgres-operations.
- Replication connection auth and certificates go to
postgres-security.
