Failover & Slot Recovery

Slot recovery is the part of Replication Topologies & Failover Operations that decides whether a primary switchover costs you a reconnect or a full resnapshot, because a PostgreSQL logical replication slot is a node-local object that physical streaming never copies to a standby. This guide covers exactly what happens to a slot on a planned switchover versus an unplanned failover, the pre-PostgreSQL-17 reality where the slot lives only on the old primary, the PG 16 foundations and PG 17 synchronized-slot mechanism that let the position survive, how Patroni-managed promotions fit in, and the resnapshot fallback when nothing carried the cursor across.

The consequence of getting this wrong is quiet and expensive. A promotion that leaves a slot behind does not raise an error on the database — the new primary is healthy, it accepts writes, and only the CDC consumer notices, often by transparently recreating a fresh slot that starts decoding from the current WAL head. Every transaction committed on the old primary between the consumer’s last acknowledged LSN and the promotion is then invisible to downstream systems forever, unless you detect the gap and rebuild from a snapshot. On PostgreSQL 14 and 15 this is the default outcome of every failover; the entire job of this guide is to make that outcome a deliberate, monitored decision instead of a silent one.

Prerequisites & Configuration Objects #

Slot recovery only works if the objects it depends on were provisioned before the failover, not created in the panic afterward. The non-negotiable base is a publisher running wal_level = logical with enough max_replication_slots and max_wal_senders headroom for every logical consumer plus every physical standby, because a physical failover target consumes a wal_sender too. Beyond that base, the version you run determines which recovery mechanism is even available.

Before PostgreSQL 16 there is no slot-synchronization primitive at all: the only prerequisites you can prepare are an idempotent consumer and a documented resnapshot procedure. PostgreSQL 16 adds standby_slot_names, which lets the primary refuse to advance a physical standby past a named logical slot’s needs, so WAL a downstream logical consumer still requires is retained on the standby — the precondition for a resumable slot. PostgreSQL 17 adds the full mechanism: sync_replication_slots, the failover attribute on slots and subscriptions, and pg_sync_replication_slots().

-- Publisher base (all versions). wal_level change requires a restart.
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = '20';   -- logical consumers + failover-target physical slots
ALTER SYSTEM SET max_wal_senders = '20';
ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';-- bound WAL a lagging slot can pin (PG 13+)
SELECT pg_reload_conf();

-- PG 16+ foundation: keep the physical standby from recycling WAL a logical slot needs.
-- 'standby1' is the physical slot name the failover-target standby streams through.
ALTER SYSTEM SET standby_slot_names = 'standby1';
-- PG 17: turn on automatic slot synchronization to standbys.
ALTER SYSTEM SET sync_replication_slots = 'on';
SELECT pg_reload_conf();

The standby that will be promoted needs hot_standby_feedback = on so its logical-slot synchronization does not let the primary vacuum away catalog rows the synced slot still needs, and it must stream through a named physical slot so standby_slot_names can reference it. The precise interaction of these GUCs — and how to verify pg_replication_slots.synced on the standby — is the whole subject of configuring failover slots in PostgreSQL 17. The slots themselves are created and recovered with the primitives documented in initializing replication slots.

Step-by-Step Implementation #

The procedure differs for a planned switchover you control and an unplanned failover you react to. Both end at the same question — does the promoted node have a usable slot — but a planned switchover lets you make the answer “yes” cheaply.

1. Before any switchover, confirm the slot will survive. On PostgreSQL 17, verify every production slot has failover = true and is synced on the target standby. A slot with failover = false will not be copied and will be gone after promotion, no matter how clean the switchover.

-- On the primary: which logical slots are configured to fail over?
SELECT slot_name, failover, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS flush_lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical' AND NOT failover;   -- rows here = slots that WON'T survive promotion

2. For a planned switchover, quiesce the consumer first. Pause the Debezium connector or disable the native subscription so it stops advancing, let the slot’s confirmed_flush_lsn catch up to the write head, then run the switchover. Draining to near-zero lag before promotion shrinks the replay window to nothing.

-- Native subscriber: pause apply cleanly before the switchover.
ALTER SUBSCRIPTION sub_orders DISABLE;
-- ... perform the controlled promotion (pg_ctl promote, or the orchestrator) ...
-- After the new primary is up and the slot is present, repoint and re-enable:
ALTER SUBSCRIPTION sub_orders
  CONNECTION 'host=pg-new-primary dbname=analytics application_name=sub_orders';
ALTER SUBSCRIPTION sub_orders ENABLE;

3. On PostgreSQL 17, force a final slot sync at the moment of promotion. Automatic synchronization runs on an interval, so a manual call just before or just after promotion guarantees the standby holds the freshest position.

-- On the standby, immediately before promoting it: pull the latest slot state.
SELECT pg_sync_replication_slots();
-- Then verify the synced slot is present and reserved:
SELECT slot_name, synced, wal_status, confirmed_flush_lsn
FROM pg_replication_slots WHERE slot_type = 'logical';

4. For an unplanned failover, reconcile before you resume. When the primary is gone you cannot quiesce anything. After the standby is promoted, compare the consumer’s last applied LSN against the new primary’s pg_current_wal_lsn() to decide between resuming, replaying, or resnapshotting. This reconciliation is delicate enough to have its own runbook: recovering logical slots after primary failover.

5. When there is no usable slot, resnapshot deterministically. If the slot is absent (pre-PG 17) or invalidated, recreate the subscription with an initial copy rather than guessing at an LSN. copy_data = true re-seeds the tables from the new primary’s current state, after which streaming resumes from a consistent point.

-- Fallback path: rebuild the subscription with a fresh initial copy.
DROP SUBSCRIPTION IF EXISTS sub_orders;
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=pg-new-primary dbname=analytics application_name=sub_orders'
  PUBLICATION pub_orders
  WITH (copy_data = true, create_slot = true, failover = true);   -- failover: PG 17

6. With Patroni, let the orchestrator drive the promotion but own the slot policy yourself. Patroni promotes the healthiest standby and, on PostgreSQL 17, can be configured to permanent-slot management so it recreates and advances logical slots on the new leader. Declare each logical slot in Patroni’s slots configuration so it is recreated on every member, and confirm the promoted leader shows the slot before re-enabling consumers.

# patroni.yml — declare logical slots so the cluster maintains them on the leader.
bootstrap:
  dcs:
    slots:
      cdc_orders:
        type: logical
        database: analytics
        plugin: pgoutput

Parameter Reference Table #

These are the settings that decide whether a slot survives promotion and how much replay a resume costs. Defaults are PostgreSQL defaults; the behavior column is what the value means specifically for failover.

Diagnostic Queries #

Point these at whichever node is currently the writable primary — after a failover that is the promoted standby, so a fixed-host DSN will read the wrong node. The mechanics of restart_lsn versus confirmed_flush_lsn underneath every threshold here are covered in WAL stream mechanics.

-- Post-promotion health: does every expected slot exist and is it advancing?
-- ALERT: an expected slot_name returns zero rows -> it did not survive the failover.
SELECT slot_name, active, failover, synced, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))         AS retained_wal,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS flush_lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- On the primary before a planned switchover: is the failover target streaming and named?
-- ALERT: sync_state <> 'sync'/'quorum' for a designated target, or it is absent here.
SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS standby_lag_bytes
FROM pg_stat_replication;

-- Reconciliation input after an unplanned failover: the new primary's write head.
-- Compare against the consumer's stored last-applied LSN to choose replay vs resnapshot.
SELECT pg_current_wal_lsn() AS new_primary_head;

-- Subscriber side: has the apply worker come back and is it erroring after repoint?
-- ALERT: pid IS NULL (worker down) or apply_error_count rising after re-enable.
SELECT s.subname, ss.pid, st.apply_error_count, st.stats_reset
FROM pg_subscription s
LEFT JOIN pg_stat_subscription       ss ON ss.subname = s.subname
LEFT JOIN pg_stat_subscription_stats st ON st.subname = s.subname;   -- stats view: PG 15+

Failure Modes & Gotchas #

Slot missing after promotion. Signature: the consumer reconnects to the new primary and its slot query returns zero rows, or the client silently creates a new slot decoding from now. Root cause: the slot was failover = false or the server predates PostgreSQL 17, so it existed only on the old primary. Remediation: on PG 17 recreate slots with failover enabled and verify synced = true before switchover; otherwise reconcile and resnapshot via the recovery runbook, and never let a client auto-create a replacement slot silently — that is how a gap ships to analytics undetected.

Synced slot invalidated on the standby. Signature: synced = true but wal_status = 'lost' or the slot is unusable after promotion. Root cause: the standby recycled WAL the synced slot needed because standby_slot_names was unset or hot_standby_feedback = off. Remediation: set both, confirm the physical slot the standby streams through is named in standby_slot_names, and re-verify after the next sync.

Split-brain double-advance. Signature: two nodes both believe they are primary and both advance a slot, or a resumed consumer double-applies. Root cause: an unfenced promotion left the old primary writable. Remediation: rely on the orchestrator’s fencing (Patroni demotes the old leader via the DCS lock) before repointing any consumer, and key every apply on the primary key plus an LSN high-water mark so a bounded replay is idempotent.

Consumer resumes from a stale LSN behind the new primary. Signature: after failover the consumer requests changes from an LSN the new primary never generated, and either errors or gets nothing. Root cause: under asynchronous physical replication the standby was behind the old primary at crash time, so its write head sits behind the consumer’s last applied LSN. Remediation: reconcile the two LSNs explicitly and resnapshot the affected relations if the consumer applied changes the new primary never persisted — the deterministic procedure is in recovering logical slots after primary failover.

Patroni recreates the slot but not the position. Signature: after a Patroni failover the slot name exists on the new leader but decodes from the current WAL head, skipping committed changes. Root cause: on versions or configurations without true slot synchronization, Patroni’s slot management recreates the object without its confirmed_flush_lsn. Remediation: on PostgreSQL 17 pair Patroni with failover = true slots and sync_replication_slots, so the position, not just the name, is carried across.

Integration Touchpoints #

Failover recovery is the operational hinge between the topology design in Replication Topologies & Failover Operations and the day-to-day consumer work everywhere else on the site. The slots you recover here are the same durable cursors provisioned in initializing replication slots, and the resnapshot fallback reuses the initial-copy flow from subscription sync procedures. The presence-and-advancement signals you alert on come from the collector built in async monitoring integration, extended with the failover and synced columns.

On the downstream side, a Debezium connector reading a failover-enabled slot resumes from its committed Kafka offset once the slot is present on the new primary, which is why an idempotent, LSN-aware consumer — the pattern built across the CDC pipeline implementation — turns a messy failover into a bounded replay. The privilege model that must hold on every promotable node follows security boundaries and permissions, and the slot durability semantics underneath all of it are catalogued in replication slot types.

Frequently Asked Questions #

Related guides #

• Recovering Logical Slots After Primary Failover — the concrete reconcile-and-rebuild runbook for a promoted node with a missing or stale slot.
• Configuring Failover Slots in PostgreSQL 17 — the failover, sync_replication_slots, and standby_slot_names mechanics and their verification queries.
• Bidirectional Replication & Conflict Resolution — the active-active topology whose slots need the same failover discipline on both nodes.
• Online Schema Change Management — coordinating DDL so a failover does not compound with a schema desync.
• Replication Topologies & Failover Operations — the parent guide framing every multi-node operation this recovery work supports.