Prompt and Applicable Roles
A PostgreSQL primary streams order changes to a warehouse and a search service through logical replication. The primary may fail while consumers are behind. Design planned and unplanned failover, explain how to verify that logical slots on the standby can take over, how to avoid duplicate or skipped LSN ranges, and how to recover when slot continuity cannot be proven.
This fits backend, database-platform, data-infrastructure, and SRE interviews. Distinguish PostgreSQL subscribers from non-PostgreSQL consumers such as Debezium: the former can use built-in failover settings, while the latter still needs independent evidence across connector state, slots, and reconciliation. “The standby is synchronized” does not prove that every logical consumer can continue seamlessly.
What the Interviewer Is Testing
A strong answer states verifiable invariants: the logical stream on the new primary must not move backward; a consumer resumes from a confirmed position, with duplicates allowed but silent gaps forbidden; and failover is gated on slot synchronization and consumer progress. The candidate should separate planned failover, where writes can be paused and consumers drained, from an unplanned failure, where a backup, full snapshot, and reconciliation may be the only safe repair. Pointing DNS at the standby does not address slots, WAL retention, or non-PostgreSQL consumers.
Questions to Clarify Before Answering
- What are the consumers? PostgreSQL subscribers, Debezium, warehouse loaders, and custom decoders have different progress and recovery interfaces.
- Are duplicates acceptable? At-least-once delivery is usually acceptable when targets deduplicate by LSN, transaction ID, or an idempotency key; a script cannot promise exactly once by assertion.
- Is this planned or disaster failover? Planned switching can pause writes and wait for the standby; after an unplanned failure, the last committed and consumer-confirmed LSN may be unknowable.
- Do cross-table transaction boundaries matter? If the downstream requires an atomic order and order-line view, propagate transaction boundaries instead of comparing one-row LSNs.
- How much WAL can be retained? A lagging slot blocks WAL cleanup, risking primary disk exhaustion; an invalidated slot can create an unrecoverable gap.
- Can consumers be rebuilt from a snapshot? If continuity cannot be proved, prepare a full snapshot, version reconciliation, and a degradation or maintenance plan.
30-Second Answer Framework
“I would make the invariant explicit: the new primary’s slot position covers the consumer’s last confirmed position, and the recovered source LSN moves monotonically. Duplicate events may be replayed, but no range may be silently skipped. For planned failover, pause or throttle writes, verify failover slots are synchronized to the standby and that the standby is ahead of consumers, stop connectors after recording safe LSNs, promote the standby, and resume consumers.
PostgreSQL 17 can asynchronously synchronize failover slots to a standby, but the switch must check that each slot exists, is synchronized, is non-temporary, and has no invalidation reason. Debezium and other non-PostgreSQL consumers must independently verify their slot and LSN. After an unplanned failure, if continuity is unknown, do not create a fresh slot and pretend to continue; rebuild from a trusted backup or snapshot and reconcile by key, transaction, and LSN.”
Step-by-Step Deep Dive
Step 1: Model slots, LSNs, and consumer progress
A logical replication slot represents a change stream that can be replayed in source order. restartlsn is the earliest WAL position that still must be retained; the consumer’s acknowledged progress is commonly represented by confirmedflush_lsn or a connector-specific offset. They answer different questions: the first controls WAL reclamation, while the second says how far the consumer has processed.
Each independent consumer should have an independent slot or an explicit broadcast layer. Multiple consumers competing for one single-consumer slot can make the consumers that did not receive a change silently incomplete. Monitor retained WAL, confirmed position, read delay, oldest unprocessed transaction, and disk headroom, not only the application queue.
Step 2: Create a provable safe point for planned failover
Planned failover can use a short read-only or drain window. Record every consumer’s last safe offset, wait until the standby’s physical replay covers the required slot state, and then check that the failover slots are usable on the standby. PostgreSQL requires confirmation that slots are synchronized; failover_ready means the slot is synchronized, non-temporary, and has no invalidation reason.
freeze_or_throttle_writes()
stop_consumers_after_recording_offsets()
wait_until(standby_replay_lsn >= required_slot_positions)
assert all(required_slots on standby are synced and valid)
promote(standby)
verify_slot_positions_are_monotonic()
restart_consumers_from_last_safe_offsets()
reconcile_sampled_rows_and_transactions()A PostgreSQL subscriber can use subscription or slot failover settings. A Debezium connector must preserve its offset, confirm the matching slot exists on the new primary, and prove that it can continue from the same LSN. A script’s success code is not a substitute for those state checks.
Step 3: Handle unplanned failure and duplicate delivery
When the primary disappears, the standby may contain an earlier WAL position, and a consumer may have received an event without durably recording its offset. Allow a bounded overlap and deduplicate downstream by source LSN, transaction ID, or a domain idempotency key. The new primary’s slot must come from a synchronized state; if continuity is unknown, creating a slot at the current WAL position cannot recover discarded LSNs.
If the old primary returns, do not let it accept writes alongside the new primary. Isolate it, establish the authoritative timeline, and rebuild physical or logical replication. Any reattachment must start from a consistent snapshot or explicit log position and reconcile transactions, deletes, and cross-table boundaries during the window.
Step 4: Contain slot invalidation and WAL growth
A slot that remains behind retains WAL and threatens primary disk and transaction-ID safety. Protect the primary first, then decide whether the slot can still recover: pause non-critical consumers, limit writes, or add temporary storage only with an explicit budget. If the slot was dropped, invalidated, or lacks the required LSN, creating a same-named slot cannot restore history.
Rebuild the consumer from the newest consistent backup or full snapshot, record the snapshot position, and consume changes after that position. Reconcile by key version, transaction ID, delete tombstone, and expected counts. Keep the consumer degraded until reconciliation passes; “the consumer connected” is not data completeness.
Step 5: Verify failover with drills and measurements
Drill planned write-free switching, a lagging consumer, delayed slot synchronization, sudden primary loss, duplicate messages, accidental old-primary return, slot invalidation, schema changes, and a large transaction. Record the primary and standby timelines, slot state, restartlsn, confirmedflush_lsn, consumer offsets, transaction boundaries, and business counts.
Acceptance checks include monotonic LSNs, duplicate and missing-event counts, oldest unprocessed transaction age, retained WAL, failover RTO/RPO, consumer recovery time, and sampled version differences between the database and downstream. The smallest inconsistency found after fault injection should be replayable from a saved backup or log position; that demonstrates a real repair path.
High-Quality Sample Answer
“I would define continuity first: the new primary’s slot covers the consumer’s last safe position and source LSNs increase monotonically. Duplicates are acceptable, silent gaps are not. Each non-PostgreSQL consumer gets its own slot, and connector offset, slot state, and business reconciliation form one evidence set.
For planned failover, I pause or throttle writes, record every consumer offset, wait for the standby’s physical replay to cover the slot state, and verify that every failover slot is synchronized, non-temporary, and valid. I stop connectors, promote the standby, verify monotonic positions, and resume from the last safe offset. PostgreSQL subscribers can use built-in failover settings; Debezium consumers must independently verify the new slot and LSN.
For an unplanned failure, I allow a bounded overlap and make targets idempotent by LSN or transaction ID. If slot continuity is not provable, I do not create a new slot and pretend to continue. I rebuild from a consistent backup or full snapshot, consume the following changes, and reconcile keys, deletes, and transaction boundaries. Drills inject slot-sync delay, duplicates, old-primary return, and large transactions while measuring retained WAL, failover RPO, duplicates or gaps, and downstream version differences.”
Common Mistakes
- Only switching DNS or a connection string → logical slots and consumer offsets do not become continuous automatically → gate failover on slot, LSN, and consumer state.
- Treating physical standby sync as logical-slot readiness → slot synchronization is asynchronous and may lag consumers → check every slot’s synced, valid, and replay state.
- Sharing one slot among consumers → single-consumer delivery silently leaves others incomplete → use one slot per independent consumer or a broadcast layer.
- Creating a fresh slot on the new primary → an already lost LSN range cannot be recovered → rebuild from a snapshot when continuity is unknown.
- Treating
confirmedflushlsnasrestart_lsn→ one is consumer progress and the other controls WAL retention → monitor and explain them separately. - Promising exactly once as a failover property → crash windows still create duplicates → use at-least-once plus idempotent side effects and measure duplicates.
- Reconnecting the old primary immediately → dual writers create divergent timelines and LSNs → isolate it, choose the authority, and rebuild on the new timeline.
- Drilling only database availability → slot invalidation, long transactions, and WAL growth expose data risk → inject consumer lag, slot buildup, and large transactions.
- Declaring recovery when the consumer connects → connection success does not prove rows or deletes were complete → reconcile keys, transaction boundaries, and business counts.
Follow-Up Questions and Responses
Follow-up 1: Must planned failover stop writes?
Not always, but continuing writes enlarges the proof window. If the database and slot-sync mechanism prove that the standby covers every required position, the read-only window can be short. Otherwise, briefly throttling writes is safer than replacing an LSN gate with “it is usually fast.” Wait for committed transactions to finish and record the boundary.
Follow-up 2: How does a non-PostgreSQL consumer know the new slot is continuous?
Preserve the connector offset and source LSN. Before switching, confirm the standby slot is synchronized at or beyond that position; after switching, read the new slot start and check monotonicity. If the offset contains only message time and no LSN, continuity is unproven; rebuild or add source-position metadata.
Follow-up 3: What about a large transaction during failover?
Distinguish committed, uncommitted, and partially decoded states. If the downstream needs transaction atomicity, propagate BEGIN/COMMIT boundaries and discard incomplete fragments after recovery. If row-level eventual consistency is sufficient, state the intermediate visibility and replay rules. Arrival order of individual rows does not prove a complete transaction.
Follow-up 4: Can you delete a slot when WAL fills the disk?
Only after confirming the consumer no longer needs the history and a snapshot rebuild is ready. Dropping the slot frees space but permanently abandons unread changes. Save the position, backup, and reconciliation plan first; after rebuilding, prove there is no gap from the new snapshot to the current source position.