Data Interview: How does PostgreSQL 18 continue logical replication after failover?

Prompt and use case

A PostgreSQL 18 primary sends changes through logical replication to an analytics cluster and external subscribers. The primary may fail over to a physical standby. Design the slots, settings, subscriber checks, and cutover order so logical replication resumes from the correct position without confusing “standby synchronized” with “subscriber caught up.”

What the interviewer is testing

• Whether you distinguish logical slots, physical slots, WAL retention, and subscriber-confirmed positions.
• Whether you understand that failover-slot synchronization is asynchronous and must be proven ready before cutover.
• Whether you can use pg_replication_slots, LSNs, and subscription state to prove a safe switch.
• Whether you handle invalid slots, lagging subscribers, planned cutovers, and unexpected failure.

Questions to clarify before answering

• Are subscribers PostgreSQL or external systems, and can they reconnect to the new primary?
• Is the allowed RPO zero, a bounded LSN gap, or a rebuildable subscription snapshot?
• Are physical replication slots and synchronous standby constraints configured between primary and standby?
• Is cutover automated or manual, and who freezes writes and updates connection routing?

A 30-second answer framework

I would enable failover for each logical slot that must survive a switch so its state is synchronized to the hot standby. I would also configure physical synchronization constraints so a subscriber cannot observe progress that the takeover standby has not persisted. Before cutover, verify every required slot on the standby is synced, non-temporary, and not invalidated. Promote the standby, update connections, verify subscribers consume from the new primary, and then unfreeze writes. If asynchronous synchronization is behind, delay the switch or accept the documented RPO and rebuild cost.

Step-by-step deep dive

1. What a logical replication slot stores

A logical slot stores decoding progress and the WAL retention boundary not yet confirmed by subscribers. A slot is not a health signal for a subscriber; a stopped subscriber can make the primary retain WAL and consume disk.

2. Creating a failover slot

PostgreSQL 18 supports setting failover when creating a logical slot, and a corresponding option when creating a subscription. The SQL below is illustrative; verify exact arguments and privileges against the target version and deployment method.

SELECT *
FROM pg_create_logical_replication_slot('analytics_slot', 'pgoutput', false, true);

The failover flag allows slot state to synchronize to the standby, but it does not prove synchronization has completed.

3. Standby synchronization settings

The standby must enable receiving and applying logical-slot synchronization, such as sync_replication_slots. The primary can use synchronized_standby_slots to require particular physical slots to catch up first, preventing logical subscriber progress from passing the standby that would take over.

4. Why asynchronous readiness needs a separate check

Slot synchronization copies state asynchronously. At a primary failure, the standby may have the data pages but not the latest slot position. Promoting immediately can leave a subscriber without its starting point or cause duplicates and gaps.

5. Checking slot state before cutover

Query pg_replication_slots on the candidate standby. Confirm each required slot is synchronized, not temporary, and has no invalidation reason, then match the result against the subscriber inventory.

SELECT slot_name,
       synced,
       temporary,
       invalidation_reason,
       confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

Only when every required slot passes should the standby be marked ready to take over.

6. Extra checks for PostgreSQL subscribers

For PostgreSQL subscribers, also confirm that the subscriber has consumed a position compatible with the synchronized slot. Physical primary-to-standby lag alone is insufficient; combine subscription state, last received LSN, and business lag.

7. Switching external subscribers

External systems usually cannot interpret PostgreSQL slot state automatically. The cutover orchestrator should freeze or pause consumption, promote the standby, update the connection and slot name, then use idempotent events and an application offset to prove no data was skipped.

8. Failure and recovery paths

A planned switch can wait until all slots are synchronized. An unexpected failure requires an RPO decision: accept a gap, delay traffic, or rebuild the subscription. A recovered old primary must not rejoin the write path immediately; isolate it, rebuild physical replication, and recheck slot and subscription state.

Trade-offs and boundaries

• Failover slots improve logical-replication recoverability but add slot-sync and WAL-monitoring complexity.
• Waiting for full slot synchronization reduces gaps but can lengthen failover time.
• synchronized_standby_slots constrains logical progress relative to the physical standby; it does not provide end-to-end zero loss for every external subscriber.
• Invalid slots, near-full storage, and long-stopped subscribers need alerts and cleanup, not only a cutover script.

Implementation plan and evidence

1. Create a failover logical slot in a PostgreSQL 18 test cluster and verify primary/standby settings and privileges.
2. Inject subscriber stoppage, WAL buildup, unsynchronized slots, and abrupt primary loss; record LSNs and recovery results.
3. Automate the pre-cutover pg_replication_slots query and compare its slot inventory with subscribers.
4. Drill pause, promotion, reconnect, catch-up, and rollback on planned and unexpected-failure paths.
5. Review parameters and limits against PostgreSQL 18 Logical Replication Failover, Logical Decoding, and Streaming Replication documentation.

Common mistakes and follow-up questions

Mistake 1: Assuming standby data means logical takeover is ready

Slot state is synchronized asynchronously. Data pages being caught up does not prove the slot position is usable; inspect synchronization and invalidation fields.

Mistake 2: Monitoring only physical replication lag

Also monitor subscriber consumption, confirmed slot LSN, retained WAL, and business lag. Logical backlog can grow while physical replication looks healthy.

Mistake 3: Treating the failover option as automatic switching

It enables slot-state synchronization; it does not promote the standby, update connections, or validate external subscribers. Cutover still needs orchestration and drills.

Follow-up: Can you promote before a slot is synchronized?

Only with an explicit RPO, gap, or rebuild decision. The default gate should wait for synchronization and record the result.

Follow-up: How do you avoid duplicate consumption?

Persist an event id or source LSN, resume from a verifiable position after cutover, and use idempotent handling plus reconciliation for replay.