Recovering Logical Slots After Primary Failover

When a primary dies and a standby is promoted, the promoted node almost always has no logical replication slot or a stale one, and recovering the Change Data Capture position means reconciling the consumer’s last applied LSN against the new primary’s write head before you resume. Get that reconciliation wrong and you either lose committed changes forever or double-apply them into a downstream system, so this runbook — part of Failover & Slot Recovery — makes the replay-versus-resnapshot decision explicit and deterministic.

Recovery Decision Semantics #

The whole recovery hinges on two LSNs: the consumer’s durably recorded last applied LSN, and the promoted node’s pg_current_wal_lsn(). Because physical replication before promotion is typically asynchronous, the standby can be behind the old primary at the instant it crashes, which means the new primary’s write head can legitimately sit behind an LSN your consumer already applied. That inversion is the dangerous case, and the table below maps each combination to the correct action.

The reason the “consumer ahead” branch forces a resnapshot rather than a rewind is that logical decoding cannot manufacture changes that no longer exist in the new primary’s WAL. If the consumer already told a warehouse that order 4711 moved to shipped, but that commit lived only on the crashed primary and never reached the standby, no amount of replay reconciles the two — the only correct state is to rebuild the affected relations from the surviving node. Recording last_applied_lsn durably on the consumer side, transactionally with the data it applies, is therefore the single prerequisite that makes this decision possible at all.

The fourth row — a surviving slot whose restart_lsn sits far behind the write head — is the quiet trap. The slot is valid and the consumer can resume, but replaying a large WAL backlog re-pins WAL on the new primary from that old restart_lsn forward, and if the backlog exceeds max_slot_wal_keep_size the freshly recovered slot can invalidate before it catches up. Treat a resume with more than a few gigabytes of retained_bytes as a capacity event: confirm the new primary has WAL headroom, watch retained_bytes drain rather than grow, and be ready to fall back to a resnapshot if the slot cannot close the gap faster than new WAL accumulates.

Diagnostic Patterns #

Run every query against the promoted node, because a DSN pinned to the old primary’s hostname now points at a dead or fenced machine. First establish whether a usable slot exists at all:

-- On the promoted node: does the expected slot exist and is it usable?
-- Zero rows for an expected slot_name = it did not survive the failover.
SELECT slot_name, active, wal_status, restart_lsn, confirmed_flush_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical' AND slot_name = 'cdc_orders';

Then capture the write head to reconcile against the consumer’s stored progress. The comparison decides the branch:

-- New primary write head, for comparison with the consumer's last_applied_lsn.
SELECT pg_current_wal_lsn() AS head;
-- Byte distance from the consumer's last ack to the head (paste the stored LSN):
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/1A2B3C4D'::pg_lsn) AS replay_bytes;
-- replay_bytes >= 0  -> consumer is behind/at head; resume is safe.
-- replay_bytes <  0  -> consumer is AHEAD of the new primary; resnapshot.

On the consumer side, confirm the apply worker actually reconnected after you repointed it and is not silently erroring on a schema or slot mismatch:

-- Subscriber liveness after repoint. ALERT: pid NULL, or apply_error_count climbing.
SELECT s.subname, ss.pid, ss.received_lsn, ss.latest_end_lsn,
       st.apply_error_count
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+

Safe Deployment Sequence #

Execute recovery in a fixed order so a bad branch never ships silently. Keep the resnapshot fallback one command away throughout.

1. Fence the old primary. Confirm the orchestrator has demoted or isolated the crashed node so it cannot come back writable and create a split brain. Do not repoint any consumer until fencing is confirmed.

2. Read the two LSNs. Capture pg_current_wal_lsn() on the promoted node and the consumer’s durably stored last_applied_lsn. This is the input to the decision tree above; write both into the incident record.

3. If no usable slot exists, recreate and resnapshot. Rebuild the subscription with an initial copy from the new primary rather than guessing an LSN. The initial-copy mechanics are covered in subscription sync procedures.

-- No usable slot: rebuild from a consistent snapshot of the new primary.
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

4. If a slot exists and the consumer is not ahead, resume. Repoint the consumer at the new primary and re-enable it; idempotent apply absorbs any overlap between restart_lsn and the last applied LSN.

-- Slot survived and consumer is in range: repoint and resume.
ALTER SUBSCRIPTION sub_orders
  CONNECTION 'host=pg-new-primary dbname=analytics application_name=sub_orders';
ALTER SUBSCRIPTION sub_orders ENABLE;

5. If the consumer is ahead, resnapshot only the affected relations. Truncate-and-reload the tables whose changes may have diverged, rather than rebuilding the whole subscription, to bound the blast radius. When you recreate the slot manually, do so with the primitives in initializing replication slots.

6. Verify convergence before declaring recovery complete. Re-run the slot and subscriber queries above; confirm active = true, apply_error_count flat, and retained WAL draining rather than growing. Only then re-enable downstream alerting to normal thresholds.

Pipeline Integration #

The recovery decision is only deterministic if the consumer persisted its position transactionally. In a Python pipeline, write last_applied_lsn in the same transaction as the applied rows and upsert on the primary key so a post-failover replay converges instead of duplicating:

# Idempotent apply that stores the LSN atomically with the data it commits.
def apply_change(conn, row, source_lsn):
    with conn.transaction():
        conn.execute(
            """
            INSERT INTO orders (id, status, total, updated_at)
            VALUES (%(id)s, %(status)s, %(total)s, %(updated_at)s)
            ON CONFLICT (id) DO UPDATE
              SET status = EXCLUDED.status,
                  total  = EXCLUDED.total,
                  updated_at = EXCLUDED.updated_at
              WHERE orders.updated_at < EXCLUDED.updated_at
            """,
            row,
        )
        # Same transaction: advance the durable high-water mark.
        conn.execute(
            "UPDATE cdc_progress SET last_applied_lsn = %s WHERE slot = 'cdc_orders'",
            (source_lsn,),
        )

On reconnect after a failover, the consumer reads last_applied_lsn back, compares it to the new primary’s head, and either resumes from that LSN or triggers the resnapshot branch — never re-requesting from an arbitrary earlier position. Wrap the reconnect in backoff with jitter so a promotion that briefly leaves no writable node does not turn into a reconnect storm, and emit the reconcile outcome (resume, replay, resnapshot) as a metric so on-call can see which branch fired. For a Debezium-based pipeline the equivalent is the connector resuming from its committed Kafka offset once the slot is present, with the same idempotent, LSN-aware apply on the CDC consumer side.

After a resnapshot, prove convergence before you close the incident rather than assuming the copy was clean. A partial resnapshot — where only some relations were reloaded — can leave a mixed state if a foreign-key parent was rebuilt but a child was not, so reload dependency groups together and verify with a row-count and checksum comparison against the new primary for each affected table. Keep the reconcile decision and its inputs (both LSNs, the chosen branch, the tables reloaded) in the incident record; the most common way a silent divergence ships to analytics is an operator resuming a consumer that was actually ahead, so an auditable branch decision is the durable defense.

Authoritative references #

• PostgreSQL: pg_replication_slots — the slot state view, including wal_status, restart_lsn, and the PG 17 failover/synced columns.
• PostgreSQL: WAL functions (pg_current_wal_lsn, pg_wal_lsn_diff) — the LSN arithmetic behind the reconciliation.
• PostgreSQL: ALTER SUBSCRIPTION — repointing, disabling, and re-enabling a subscription during recovery.

Related #

• Configuring Failover Slots in PostgreSQL 17 — prevent this recovery entirely by synchronizing the slot before the failover.
• Failover & Slot Recovery — the parent guide covering planned switchover and the full slot-survival picture.
• Subscription sync procedures — the initial-copy flow the resnapshot branch reuses.
• Initializing replication slots — recreating a durable slot cleanly on the promoted node.