pg_create_logical_replication_slot step-by-step

pg_create_logical_replication_slot is the single function call that provisions the durable WAL cursor a Change Data Capture (CDC) pipeline resumes from, and this page isolates its exact argument matrix, reserved-state verification, and safe rollout as the creation step within initializing replication slots. Get the arguments wrong and the failure is deferred, not avoided: a mis-chosen output plugin breaks the consumer’s wire decode, a temporary slot silently vanishes on reconnect and forces a full re-snapshot, and an unconsumed slot pins restart_lsn until pg_wal fills the disk and the primary refuses commits.

Everything below assumes you are already operating a production primary on PostgreSQL 14 through 17, have defined the exposure boundary in a publication, and need this slot to survive consumer restarts, WAL churn, and eventual failover. Before the function will usefully retain and decode WAL, the server-level decoding surface must already exist — these are static parameters and changing wal_level requires a full restart, so provision headroom before the first slot is needed, not during an incident:

-- Publisher prerequisites. wal_level and the slot/sender ceilings each need a restart.
ALTER SYSTEM SET wal_level              = 'logical';
ALTER SYSTEM SET max_replication_slots  = 20;      -- >= total logical + physical slots
ALTER SYSTEM SET max_wal_senders        = 24;      -- >= max_replication_slots + background overhead
ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';  -- PG 13+: cap WAL a stalled slot can pin
-- Restart the cluster, then confirm:
SHOW wal_level;               -- must return: logical
SHOW max_replication_slots;   -- must be > concurrent CDC consumer count

Argument Semantics and Slot Behavior #

The function signature is pg_create_logical_replication_slot(slot_name name, plugin name [, temporary boolean, two_phase boolean, failover boolean]) and it returns a single (slot_name, lsn) row, where lsn is the WAL position captured as the slot’s initial restart_lsn. Each argument fixes a different durability or decode property; the table maps every one to the guarantee it buys and the logical-replication behavior it changes.

Two argument interactions cause the most production incidents. First, plugin is fixed at creation: switching a live pipeline from pgoutput to another decoder means dropping and recreating the slot, which resets the cursor and mandates a re-snapshot — treat the plugin choice as permanent. Second, the executing role must hold the REPLICATION attribute and be connected to the target database; a role without it fails with ERROR: must be superuser or replication role to use replication slots, and connecting to postgres instead of the CDC source database silently creates a slot that decodes the wrong database’s WAL.

-- Grant the minimum privileges before the first call.
ALTER ROLE cdc_pipeline_user REPLICATION;
GRANT CONNECT ON DATABASE target_db TO cdc_pipeline_user;

The canonical persistent creation call for a native CDC pipeline:

-- PG 14-17. Connect directly to the CDC source database first.
SELECT pg_create_logical_replication_slot(
    slot_name := 'cdc_etl_pipeline_v1',
    plugin    := 'pgoutput',
    temporary := false,
    two_phase := false
);

-- PG 17+ only: add failover so the slot syncs to a physical standby.
SELECT pg_create_logical_replication_slot(
    slot_name := 'cdc_etl_pipeline_v1',
    plugin    := 'pgoutput',
    temporary := false,
    two_phase := false,
    failover  := true
);

Diagnostic Patterns #

Immediately after creation, verify the slot entered the reserved state with its LSNs anchored where you expect. A freshly created slot that has never had a consumer connect has a non-null restart_lsn (WAL is already being retained from here) but a null confirmed_flush_lsn — that null is normal at creation and only advances once a consumer acknowledges processed changes.

-- Confirm reserved state and initial LSN anchoring right after creation.
SELECT slot_name, plugin, slot_type, active,
       restart_lsn, confirmed_flush_lsn, wal_status
FROM pg_replication_slots
WHERE slot_name = 'cdc_etl_pipeline_v1';

Interpret the state fields against concrete thresholds:

• confirmed_flush_lsn IS NULL for > 300 s after a consumer was supposed to attach — the consumer never connected or never sent feedback. WAL is retained from restart_lsn with nothing draining it.
• wal_status = 'extended' — the slot is retaining more WAL than max_slot_wal_keep_size nominally allows and will be invalidated if the consumer does not advance. Intervene immediately.
• wal_status = 'lost' — WAL the slot needed has already been removed; the slot is invalid and the consumer must re-snapshot.
• active = false for > 300 s on a temporary = false slot — the consumer disconnected but the slot (correctly) persists, so WAL retention continues to grow. Reconnect or drop deliberately.

To separate a healthy-but-idle slot from a slot silently accumulating WAL, measure the retention gap between the current WAL position and restart_lsn:

-- Retention pressure: how much WAL this slot is pinning right now.
SELECT slot_name,
       active,
       wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS pending_retention
FROM pg_replication_slots
WHERE slot_name = 'cdc_etl_pipeline_v1';

Alert when pending_retention exceeds 5 GB (consumer-lag investigation) and page on wal_status = 'extended'. The reusable exporter and alert rules for these metrics belong in asynchronous monitoring integration rather than ad-hoc polling; the LSN arithmetic beneath every threshold here is covered in WAL stream mechanics.

Safe Deployment Sequence #

Slot creation is not idempotent — a second call with an existing name raises ERROR: replication slot "cdc_etl_pipeline_v1" already exists — so provisioning must be guarded, verified, and reversible. Roll it out in five steps with the revert one command away.

1. Verify prerequisites and privileges. Confirm SHOW wal_level; returns logical, that free slot headroom exists (SELECT count(*) FROM pg_replication_slots; well under max_replication_slots), and that the executing role has REPLICATION. Do this before the change window, not inside it.

2. Create the slot idempotently. Wrap the call so a re-run converges instead of erroring — essential when the same deploy script runs across environments or retries:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_replication_slots WHERE slot_name = 'cdc_etl_pipeline_v1'
  ) THEN
    PERFORM pg_create_logical_replication_slot('cdc_etl_pipeline_v1', 'pgoutput');
  END IF;
END $$;

3. Validate reserved state. Run the first diagnostic query above and confirm slot_type = 'logical', a non-null restart_lsn, and wal_status = 'reserved'. Do not attach a consumer until this passes — an unverified slot with a wrong plugin only fails once the consumer tries to decode.

4. Attach the consumer and confirm the cursor moves. Start the CDC consumer, then re-query and confirm active = true and confirmed_flush_lsn has advanced past its initial null. A slot that stays at null under load means the consumer is not sending feedback — treat it as a failed rollout.

5. Keep the revert ready. Dropping the slot is the revert, and it is destructive: it discards restart_lsn so the consumer can no longer resume without a fresh snapshot. Never drop a slot to “free disk” during an incident — prefer letting max_slot_wal_keep_size invalidate it deliberately. Only run this once you accept a re-snapshot:

SELECT pg_drop_replication_slot('cdc_etl_pipeline_v1');   -- destroys the cursor; re-snapshot required

Pipeline Integration #

A native subscription consumes the slot for you, but Python ETL consumers must speak the logical replication protocol directly and, critically, advance the confirmed LSN themselves. Using psycopg2’s LogicalReplicationConnection, the consumer sends periodic standby status update messages with the last processed LSN; if it never sends these acknowledgments, confirmed_flush_lsn stalls and the slot bloats WAL exactly as an idle slot would.

# Resume-from-last-ack consumer with retry/backoff. Safe against reconnects.
import time, random, psycopg2
from psycopg2.extras import LogicalReplicationConnection

DSN = "host=primary-db dbname=cdc_source user=replicator replication=database"

def stream_changes():
    backoff = 0.5
    while True:
        try:
            conn = psycopg2.connect(DSN, connection_factory=LogicalReplicationConnection)
            cur = conn.cursor()
            cur.start_replication(
                slot_name='cdc_etl_pipeline_v1',
                options={'proto_version': '1', 'publication_names': 'cdc_pub'},
                decode=False,
            )

            def on_change(msg):
                apply_idempotent(msg.payload)              # INSERT ... ON CONFLICT DO UPDATE
                cur.send_feedback(flush_lsn=msg.data_start)  # advances confirmed_flush_lsn

            cur.consume_stream(on_change)
            backoff = 0.5
        except psycopg2.errors.ObjectInUse:
            # active = true elsewhere; only one consumer may hold a slot.
            time.sleep(backoff); backoff = min(backoff * 2, 30)
        except psycopg2.OperationalError:
            time.sleep(backoff + random.random() * 0.25)   # jitter avoids thundering herd
            backoff = min(backoff * 2, 30)

Three integration rules are specific to a freshly created slot:

• Idempotent apply. Because a consumer can reconnect and replay from the last acknowledged LSN, every change must converge on replay — key upserts on the deterministic primary key (ON CONFLICT DO UPDATE, or PG 15+ MERGE), never blind inserts.
• Reconcile, don’t recreate, after a stall. If the consumer processed changes but crashed before acknowledging, use SELECT pg_replication_slot_advance('cdc_etl_pipeline_v1', '<lsn>'); to move the cursor forward rather than dropping and recreating the slot, which would force a full re-snapshot.
• Failover handling. Before PostgreSQL 17, logical slots do not migrate on primary failover: the promoted standby has no slot, and the consumer’s ERROR: replication slot "cdc_etl_pipeline_v1" does not exist is the trigger to recreate the slot on the new primary and drive a snapshot-based catch-up via subscription sync procedures. On PG 17+, a slot created with failover := true and synced through pg_sync_replication_slots() survives promotion — verify it exists on the standby before you promote, because a slot that was never synced is gone the moment the old primary is lost.

Authoritative references #

• PostgreSQL: Replication Management Functions — the canonical signature and per-argument semantics for pg_create_logical_replication_slot, pg_drop_replication_slot, and pg_replication_slot_advance.
• PostgreSQL: pg_replication_slots view — every column read by the diagnostic queries above, including wal_status and restart_lsn.
• PostgreSQL: Logical Decoding — protocol message formats, the pgoutput plugin, and LSN acknowledgment rules the Python consumer depends on.

Related guides #

• Initializing replication slots — the parent workflow this creation step belongs to, with the full slot lifecycle and retention model.
• Automating slot creation with Ansible — make the idempotent guard above a converging part of your infrastructure-as-code.
• Replication slot types — how logical, physical, temporary, and failover slots differ underneath the function call.
• Asynchronous monitoring integration — export pending_retention and wal_status with the alert thresholds referenced here.
• Logical Replication Setup & Management — the management layer this slot provisioning step sits within.