PostgreSQL / Advanced: evidence and reliable operations / Lesson 17

Replication, consistency and failover

Compare physical standby and logical replication contracts, then observe receipt and replay independently. Define write ownership and recovery requirements before failover.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Interpret receive, replay and synchronous-commit boundaries.
Explain slots, logical identity and promotion risks.

A physical standby replays cluster history

Physical replication sends WAL from a primary to a compatible standby that replays cluster-level changes. A hot standby can serve read-only queries, but its view may lag the primary. Receiving WAL, writing it, flushing it and applying it are distinct stages. Asynchronous replication can lose recently acknowledged primary work if the primary is lost before the required history reaches a surviving server. Define stale-read tolerance and read-your-writes behavior instead of treating every replica endpoint as interchangeable with the writer.

Observe recovery and replay separately

On a training standby, pg_is_in_recovery identifies recovery mode and the receive and replay LSN functions expose different positions. NULL values require context, including whether streaming is active and which endpoint was queried. pg_last_xact_replay_timestamp describes the latest replayed transaction timestamp, not a universal live lag clock: a quiet primary can make an old timestamp entirely normal. Compare relevant positions and application freshness evidence over time. The example neither connects to a server nor fabricates healthy status.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(),
 pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

Interpret on the intended standby with appropriate permissions. Idle systems and non-recovery endpoints need different interpretation.

Durability settings and retention have costs

Synchronous replication can make a commit wait for selected standby acknowledgments, with the exact guarantee depending on synchronous_commit and standby configuration. It does not automatically make every standby read current; remote_apply has a different boundary from remote flush acknowledgment. Replication slots retain required history so a consumer can catch up, but an inactive or stalled consumer can retain substantial WAL and threaten disk capacity. Monitor retention and failure scenarios; a slot is not unlimited storage and replication is not a backup against logical mistakes.

Read sender progress without changing topology

On the primary, pg_stat_replication can describe connected WAL senders and their write, flush and replay positions. Monitoring visibility depends on privileges, and absent rows may mean no matching connection rather than an application data problem. Lag interval columns have documented semantics and may be NULL or become stale when idle. Correlate sender state with standby receipt, replay and slot retention. This is read-only evidence for a runbook, not permission to reset slots, skip history or promote a server.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT application_name, state, sync_state,
 sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication ORDER BY application_name;
SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots ORDER BY slot_name;

Actual topology and positions are unspecified. Protect operational identifiers when sharing diagnostics.

Logical replication has a different scope

Logical replication publishes selected table changes and requires a suitable replica identity for relevant updates and deletes. It can support some cross-major migrations, but does not automatically copy schema DDL, sequence state or every database object. Plan those separately for the exact release. Promotion must fence the old primary and establish one accepted writer; two writable histories create divergence. PostgreSQL provides replication building blocks, not a universal automatic failover decision or zero-loss guarantee. Rehearse routing, client retries, backup continuity and the last safe rollback boundary.

Common errors and fixes

A recently written record is absent on a standby

Why it happens: The required WAL has not yet been applied at that endpoint.
How to fix it: Use the service freshness policy and compare receive/replay progress.
Prevent it next time: Document which reads can tolerate lag and how read-your-writes is satisfied.

WAL storage grows behind an inactive slot

Why it happens: A consumer is not advancing retained history.
How to fix it: Investigate the consumer and retention budget through a reviewed recovery plan.
Prevent it next time: Alert on retained WAL and slot inactivity before disk exhaustion.

Logical subscribers have missing schema or sequence state

Why it happens: Table-change replication was mistaken for complete cluster cloning.
How to fix it: Migrate the excluded objects and validate cutover requirements separately.
Prevent it next time: Inventory logical replication limitations for the exact versions.

Check your understanding

1. The last replayed transaction timestamp is an hour old, but the primary has accepted no writes for an hour. Is one-hour lag proven?

Show worked solution

No. The timestamp may simply identify the last real transaction. Compare WAL positions and a known application freshness boundary rather than subtracting the timestamp from the wall clock alone. Confirm the intended endpoint and recovery state. Idle-time behavior must be represented in alerts so quiet workloads are not mistaken for stalled replay.

2. Why must a failover runbook fence the old primary before accepting writes on a promoted standby?