Resolving subscription initialization failures

Subscription initialization is the deterministic handshake — slot creation, publication negotiation, and the initial per-table COPY — that runs the instant CREATE SUBSCRIPTION executes, and this page isolates exactly why that handshake fails and how to recover it without stranding downstream state. When it stalls, the open replication slot pins WAL on the publisher while every consumer reads stale or half-copied rows, so a failed init is simultaneously a disk-exhaustion risk on the primary and a data-correctness risk on the subscriber. This is the catalog-level triage layer beneath the broader subscription sync procedures workflow, validated against PostgreSQL 14 through 17.

Treat every initialization failure as one of four vectors: publisher decoding surface not enabled, a connection or worker-slot ceiling exhausted, a publication/schema mismatch the subscriber cannot resolve, or a network fault that corrupts the large initial COPY mid-stream. The sections below map each vector to a concrete signature, a diagnostic query, and a revert-safe recovery.

Initialization Phase Semantics #

Initialization failures overwhelmingly originate in publisher-side configuration that was never validated before the subscription was provisioned. The logical decoding subsystem will not spawn an apply worker until the publisher’s decoding surface and connection ceilings are in place, and PostgreSQL 13+ ships wal_level = replica, which explicitly blocks logical decoding. The following table maps each governing parameter to what it gates at CREATE SUBSCRIPTION time and the exact signature you get when it is under-provisioned.

Adjust the affected parameters in postgresql.conf (or via ALTER SYSTEM), reload with SELECT pg_reload_conf(); for the non-restart knobs, and confirm allocation with SHOW max_wal_senders; and SELECT slot_name, slot_type, active FROM pg_replication_slots; before retrying. The publication named in the subscription must also exist with exact casing, and every published table must already exist on the subscriber with a compatible REPLICA IDENTITY — logical replication never ships DDL, so schema parity is your responsibility.

Once prerequisites pass, initialization is a per-relation state machine tracked in pg_subscription_rel.srsubstate. The subscription is only fully consistent when every relation reads r; a single relation wedged at i or d means the initial COPY is blocked even though the subscription reports enabled.

Diagnostic Patterns #

State lives across three catalogs — pg_subscription (definition), pg_subscription_rel (per-table state), and pg_stat_subscription (live worker + lag). A subscription can read enabled in the first while a relation is wedged in the second, so query all three during triage.

-- 1. Apply-worker liveness. A NULL pid on an enabled subscription means the
--    launcher never spawned the worker: auth rejection, missing REPLICATION
--    privilege, or a publication-name mismatch on the publisher.
SELECT subname, pid, relid::regclass AS syncing_table,
       received_lsn, latest_end_lsn,
       last_msg_send_time, last_msg_receipt_time
FROM pg_stat_subscription
WHERE subname = 'orders_sub';

-- 2. Per-table init progress. Any row below 'r' is not yet consistent.
--    i=init  d=data copy  f=finished  s=synced  r=ready(streaming)
SELECT srrelid::regclass AS table_name, srsubstate, srsublsn
FROM pg_subscription_rel sr
JOIN pg_subscription s ON s.oid = sr.srsubid
WHERE s.subname = 'orders_sub' AND sr.srsubstate <> 'r'
ORDER BY sr.srsubstate;

-- 3. Publisher-side slot retention. A frozen restart_lsn while the sync is
--    incomplete is the disk-exhaustion warning: WAL cannot recycle until COPY finishes.
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_name = 'orders_sub';

Threshold guidance: a NULL pid on an enabled subscription is an immediate paging condition; a relation stuck below srsubstate = 'r' for longer than its benchmarked COPY time (roughly 1 GB/min on commodity storage) signals a blocked tablesync worker; and retained_wal climbing past 1 GB with active = false means the slot is bloating WAL and must be advanced or dropped. When a relation hangs in d, cross-reference pg_stat_activity for wait_event_type = Lock or wait_event = LogicalReplicationTableSyncWorker to find the blocking transaction. Map any concrete error text through the matrix below.

Authentication and reachability failures — the most common cause of a NULL apply-worker pid — are governed by the publisher’s pg_hba.conf; the full replication-role and host-based auth model is covered in setting up pg_hba.conf for replication users.

Safe Recovery Sequence #

When initialization fails mid-copy, PostgreSQL leaves the slot inactive and marks the affected pg_subscription_rel rows at i or d. Never delete rows from system catalogs by hand — that desynchronizes the subscription from its slot and forces a full re-seed. Follow this revert-safe sequence instead.

1. Halt the apply worker so no partial changes land while you triage.

ALTER SUBSCRIPTION orders_sub DISABLE;

2. Confirm the slot is inactive on the publisher before touching its position.

-- On the publisher.
SELECT slot_name, active, restart_lsn
FROM pg_replication_slots
WHERE slot_name = 'orders_sub';

3. Advance a stale slot to the current WAL position only if the slot retains old LSNs and you intend to re-seed rather than resume. This prevents immediate WAL bloat on restart. Skip this step if you plan to resume an in-progress COPY, because advancing past unconsumed WAL discards changes.

-- On the publisher. Advancing discards WAL between restart_lsn and now — re-seed after.
SELECT pg_replication_slot_advance('orders_sub', pg_current_wal_lsn());

4. Reset subscriber-side state so the apply worker re-plans which relations still need COPY.

ALTER SUBSCRIPTION orders_sub REFRESH PUBLICATION WITH (copy_data = true);

5. Re-enable and watch every relation converge to r using diagnostic query 2 above.

ALTER SUBSCRIPTION orders_sub ENABLE;

Revert / escalation. If the slot cannot be salvaged — orphaned by a partitioned CREATE SUBSCRIPTION, or bloating WAL past max_slot_wal_keep_size — the clean path is DROP SUBSCRIPTION orders_sub; (which drops the publisher slot) and recreate. For a subscriber that already holds a byte-identical baseline (for example restored from the same pg_dump the slot was created against), recreate with copy_data = false to skip the COPY entirely and stream from the slot’s current position; using false when the baseline does not match leaves those tables permanently empty with no error.

Pipeline Integration #

Python ETL controllers and event-streaming consumers must treat CREATE SUBSCRIPTION as a fallible, retryable operation, never a fire-and-forget DDL call. When driving initialization from psycopg2 or asyncpg, wrap creation so a partially-applied attempt is idempotent on retry, and resume from the last acknowledged LSN rather than re-requesting an arbitrary earlier position.

# Idempotent init with bounded backoff + jitter. Safe to re-run after a
# transient publisher restart without leaving orphaned slots.
import time, random, psycopg2
from psycopg2 import errors

def ensure_subscription(dsn: str, ddl: str, attempts: int = 6) -> None:
    backoff = 0.5
    for _ in range(attempts):
        try:
            with psycopg2.connect(dsn) as conn:
                conn.autocommit = True
                with conn.cursor() as cur:
                    cur.execute(ddl)          # CREATE SUBSCRIPTION ... WITH (...)
            return
        except errors.DuplicateObject:
            return                            # already created — idempotent success
        except (errors.ObjectInUse, psycopg2.OperationalError):
            time.sleep(backoff + random.random() * 0.25)  # jitter avoids thundering herd
            backoff = min(backoff * 2, 30)    # cap at 30 s across cluster restarts
    raise TimeoutError("subscription did not initialize within retry budget")

Export initialization health to your monitoring layer so a stalled COPY pages before it exhausts publisher disk: alert when pg_stat_subscription.pid IS NULL on an enabled subscription, when any pg_subscription_rel.srsubstate <> 'r' persists beyond the relation’s benchmarked COPY window, or when last_msg_send_time < NOW() - INTERVAL '5 minutes'. The reusable postgres_exporter queries, Prometheus alert rules, and Grafana panels for these signals live in asynchronous monitoring integration. Once a subscription reaches steady state, its apply durability is governed separately by synchronous_commit for logical replication.

When the consumer is an event-streaming pipeline rather than a native subscription, the same publication and slot are read by the Debezium connector or a custom reader — in which case the initial-snapshot and offset-tracking responsibilities described here move into the connector’s own state store, and the low-level frame handling is covered in parsing pgoutput format with psycopg2. The LSN and cursor arithmetic that every threshold on this page depends on is detailed in WAL stream mechanics.

Authoritative references #

• PostgreSQL: CREATE SUBSCRIPTION — parameter precedence, copy_data/create_slot semantics, and transactional guarantees for the init handshake.
• PostgreSQL: Logical Replication — Subscription — apply-worker and tablesync lifecycle behind the srsubstate machine.
• PostgreSQL: pg_replication_slots — the retention columns behind the disk-exhaustion thresholds above.

Related guides #

• Subscription sync procedures — the parent workflow; drive, monitor, and re-synchronize a subscription once it clears initialization.
• Initializing replication slots — provision and recover the durable WAL cursor a subscription attaches to at init.
• Configuring max_replication_slots safely — size the slot ceiling that causes the slot limit reached failure above.
• Asynchronous monitoring integration — export the init-health signals and alert thresholds referenced here.
• Logical Replication Setup & Management — the management layer this triage procedure belongs to.