Prompt and scope
You operate a CDC consumer that reads changes from a production PostgreSQL database. During a planned switchover or an unexpected failure, how would you let the new primary continue serving the same logical replication slot without hiding duplicates, gaps, WAL buildup, or an unjustified zero-loss claim? Explain PostgreSQL 18 failover slots, synchronization checks, consumer reconnects, monitoring, and rollback.
PostgreSQL documents that logical replication slots can be synchronized to a physical standby, but slot synchronization is asynchronous. Before promoting a standby, the required slots must be present and reported as failover_ready. This backend reliability question tests whether you can connect database capabilities, consumer semantics, and failover orchestration into a verifiable process.
What the interviewer evaluates
- Distinguishing physical WAL replication, logical replication slots, and consumer acknowledgements.
- Explaining the boundaries of the
failoverslot option andsynchronized_standby_slots. - Handling planned switchovers and sudden failures with different loss and duplication windows.
- Knowing that a live standby is not proof that every logical slot is ready.
- Designing alerts for WAL retention, slot lag, reconnects, and consumer delay.
- Assigning connection discovery, fencing, and downstream idempotency to explicit components.
Clarifying questions to ask
- Is the consumer another PostgreSQL subscriber or a non-PostgreSQL CDC client such as Debezium? Readiness checks differ.
- Is the target a planned near-zero-loss switch or a bounded recovery point after a crash?
- Can the downstream deduplicate by LSN, transaction ID, event ID, or business key?
- Do primary and standby use the same PostgreSQL major version and output plugin?
- Which coordinator promotes the standby, changes discovery, fences the old primary, and prevents split brain?
30-second answer
I would define the recovery point and duplicate tolerance first, then model slot synchronization, promotion, reconnect, and downstream idempotency as a state machine. I would enable failover for each logical slot that must survive promotion and verify every slot on the standby, including its presence, synchronization state, and failover_ready value. During a switch I would fence the old primary before changing discovery. The consumer would resume from a recorded LSN; replayed transactions would be made harmless by LSN or business-key idempotency. I would monitor slot lag, retained WAL, consumer delay, and reconnect outcomes, and would not treat asynchronous slot synchronization as a zero-loss guarantee.
Step-by-step deep dive
1. Define the data semantics
Write RPO, RTO, and duplicate handling into the design. A planned switchover can wait for required slots to synchronize; a sudden failure can only recover from WAL that reached the promoted standby. The consumer should persist its last confirmed LSN or equivalent position, and side effects should use idempotency keys.
2. Configure failover-capable logical slots
PostgreSQL 18 supports logical slots that can be synchronized to a standby. Enable the failover option when creating the slot or subscription, and maintain an inventory of every slot the consumer fleet needs. Do not forget table-synchronization slots or secondary consumers.
-- Illustrative only: allow a logical slot to synchronize to a standby
SELECT *
FROM pg_create_logical_replication_slot('cdc_orders', 'pgoutput', false, true);The exact signature and permissions must match the deployed PostgreSQL version and plugin. The example cannot replace configuration and compatibility checks.
3. Carry slot state through physical replication
Configure the physical standby to receive WAL and use synchronized_standby_slots where the documented failover workflow requires it. Before promotion, inspect pg_replication_slots on the standby and confirm that every required slot exists, is synchronized, and is failover_ready. Slot copying is asynchronous, so a healthy streaming connection alone is insufficient.
4. Planned switchover
Pause or drain CDC consumers and record their last confirmed positions. Stop writes on the old primary and wait for physical replication and slot synchronization. Reconcile the slot inventory on both nodes, promote only after all required slots are ready, then change connection discovery and resume consumers from the same logical slots. If the boundary is replayed, downstream LSN checks or idempotency remove duplicate effects.
5. Sudden failure and split-brain protection
A crash cannot wait for slot synchronization. Fence the old primary before it can return and write, then calculate the recovery point proven by the promoted node. The coordinator must ensure that only one node is writable. After DNS, VIP, or service-discovery changes, consumers should validate server identity and slot presence. An unsynchronized slot must be reported as an incomplete handoff, with manual recovery or subscription rebuild as an explicit option.
6. Monitor WAL retention and consumer progress
Track each slot's restart_lsn, confirmed_flush_lsn, slot lag, retained WAL, reconnect count, and end-to-end delay. An abandoned slot can prevent WAL recycling and fill the disk. Separate an alert for “slot exists” from “consumer has processed through the target LSN,” and set timeouts, throttles, and human-takeover thresholds for recovery.
7. Rollback, rebuild, and acceptance
If readiness checks fail, do not auto-promote; keep the old primary or enter a declared degraded mode. Exercise planned switchover, sudden power loss, consumer disconnect, delayed slot synchronization, and an old-primary restart. Record the last commit, first event on the new primary, duplicate count, gap count, RTO, and peak disk usage. Trace every gap to an LSN.
High-quality sample answer
I would model the workflow as six states: slots ready, old primary fenced, standby promoted, discovery switched, consumers resumed, and downstream positions confirmed. I would enable failover for required logical slots and inspect synchronized_standby_slots and pg_replication_slots on the standby until every slot is present and failover_ready. Because synchronization is asynchronous, a live standby is not sufficient evidence for safe promotion.
For a planned switch, I would pause consumers, freeze writes on the old primary, wait for physical replication and slot synchronization, and then promote. For a crash, I would fence the old primary and state the recoverable RPO instead of claiming zero loss. Consumers would reconnect and resume from stored LSNs; downstream idempotency would absorb replay. Finally, I would monitor slot lag, WAL retention, consumer delay, reconnects, and gaps, and validate RPO and RTO with power-loss and synchronization-delay drills.
Common mistakes
- Promoting because the standby is online → slot synchronization may still be incomplete → check each slot's presence, sync state, and
failover_ready. - Treating a logical slot as proof that the consumer processed data → slot state differs from downstream acknowledgement → track LSNs and consumer positions separately.
- Promising zero loss for a crash → unsynchronized WAL may be unavailable → state planned and unplanned RPO separately.
- Changing DNS without fencing the old primary → split-brain writes remain possible → fence first, then promote and switch discovery.
- Ignoring a stalled slot → retained WAL can exhaust disk → alert on lag, disk, and maximum retention age.
- Testing only database promotion → consumers, plugins, and downstream effects can fail → run an end-to-end replay drill.
Follow-up questions and responses
Does failover_ready prove that no events will be lost?
No. It says the relevant logical slot is synchronized to the target standby and can continue after promotion. Loss still depends on physical replication at failure time, the consumer acknowledgement position, and downstream processing semantics.
Why pause consumers during a planned switch?
Pausing fixes the last confirmed positions and avoids a consumer reading from old and new primaries at once. A more elaborate coordinator is possible, but it must prove that it prevents split brain, reordering, and ambiguous acknowledgements.
How should a non-PostgreSQL CDC client verify readiness?
It cannot reuse PostgreSQL subscription queries directly. It should maintain its own slot inventory and health checks, verify synchronized slots on the standby, and then validate the new connection and position continuity using its client protocol.
What if a slot is not synchronized but the business must recover?
State the actual RPO and choose a conservative recovery point. Rebuild the slot, take a new snapshot, or compensate from backups if needed. Do not present unverified replication state as lossless recovery.
How do you stop the old primary from returning?
Use coordinator or cloud fencing, network isolation, and write-credential rotation. A DNS change alone cannot stop the old primary from accepting writes.
How do you prove that replay caused no duplicate side effects?
Deduplicate by transaction LSN, event ID, or an idempotency key, then record replay counts, final business totals, and ordering constraints during a drill. Compare auditable positions before and after the switch.