postgres-ha-replication

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

1. Establish the topology first: physical or logical, sync or async, cascading, orchestrator (Patroni, repmgr, pg_auto_failover, cloud-managed), and slot usage.
2. For lag, locate the stuck stage using pg_stat_replication LSN columns — the fix differs completely by stage.
3. 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.
4. For failover questions, separate detection, election, fencing, and client rerouting — native PostgreSQL provides none of these; the orchestrator does.
5. 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

1. Reporting lag only in bytes or only in seconds; replay_lag time columns and pg_wal_lsn_diff() bytes answer different questions.
2. Leaving a replication slot behind after decommissioning a standby or a CDC consumer.
3. Enabling synchronous replication with a single standby and no plan for commit-blocking when it dies.
4. Treating logical replication as a full mirror: DDL and (before recent versions) sequences do not replicate; new tables need publication changes.
5. Forgetting REPLICA IDENTITY on tables without primary keys — updates/deletes fail to publish or apply.
6. Promoting a standby without fencing the old primary — split-brain, then a painful pg_rewind or rebuild.
7. 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.