Initializing Replication Slots

A logical replication slot is the durable Write-Ahead Log (WAL) cursor that makes Change Data Capture (CDC) survivable, and provisioning it correctly is the second operational step in the Logical Replication Setup & Management workflow — once a publication has fixed what leaves the primary, the slot fixes from where the stream resumes and how much WAL the server must retain until a consumer confirms receipt. For database engineers, data platform teams, and Python ETL developers running PostgreSQL 14 through 17, pg_create_logical_replication_slot is not a throwaway one-liner: it decides your pipeline’s restart guarantee, your pg_wal growth ceiling, and whether a stalled consumer degrades gracefully or takes the primary offline.

Get slot initialization wrong and the failure is asymmetric. Create a slot and never attach a consumer, and it silently pins restart_lsn forever — WAL accumulates at the full write rate of the primary until the disk fills and the database refuses to accept commits. Create it with the wrong output plugin and the consumer either fails to decode the stream or, worse, receives a wire format it half-understands. Drop a slot to “free space” during an incident and you have destroyed the only cursor that let the consumer resume without a full re-snapshot. Because a slot lives inside the decoding path on the publisher and its retention is enforced by the checkpointer, each of these mistakes converts directly into unbounded disk usage, replication lag, or an irreversible data gap. This page is the reference for provisioning slots that stay stable under production churn.

Prerequisites & Configuration Objects #

A slot cannot be created — or rather, cannot usefully retain and decode WAL — unless the server-level decoding surface is already in place. These parameters are static: changing wal_level requires a full restart, and max_replication_slots / max_wal_senders require a restart as well, so provision headroom before the first slot is ever needed rather than during an incident.

-- Publisher server prerequisites (wal_level and the slot/sender ceilings 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
-- Cap the WAL a stalled slot can pin so one dead consumer cannot fill the disk (PG 13+).
ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';
-- Optional but recommended: exposes xmin/commit timing for lag and conflict analysis.
ALTER SYSTEM SET track_commit_timestamp = 'on';
SELECT pg_reload_conf();   -- wal_level, max_replication_slots, max_wal_senders still need a restart.

wal_level = logical is non-negotiable and roughly doubles per-row WAL volume for UPDATE/DELETE, because the old-tuple identity is now logged — budget the extra pg_wal throughput before enabling it. The internal batching, spill-to-disk, and reorder-buffer behaviour that governs how the retained WAL is reassembled into transactions is documented in WAL stream mechanics; the operational takeaway is that a slot pins WAL from its restart_lsn forward, and a single long-running transaction on the primary holds that cursor back for every byte written since it began.

Slot creation requires the REPLICATION attribute (or superuser). Provision a dedicated least-privilege role rather than reusing an application account — the full privilege model, including the pg_hba.conf replication database entry a streaming consumer needs, is covered in security boundaries and permissions.

-- Least-privilege replication role for the CDC consumer.
CREATE ROLE cdc_reader WITH LOGIN REPLICATION PASSWORD 'REDACTED';
GRANT USAGE ON SCHEMA public TO cdc_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_reader;  -- needed for the initial copy

Finally, choose the output plugin deliberately. The default pgoutput (built in since PostgreSQL 10) is required for native subscriptions and is the lowest-overhead choice for a Python consumer that speaks the logical replication protocol. wal2json and decoderbufs emit JSON/Protobuf and simplify hand-written parsers at the cost of a required extension and extra CPU per change. The plugin is bound at creation and cannot be altered — the trade-offs are compared in replication slot types.

Step-by-Step Implementation #

The following sequence provisions a production slot idempotently, verifies its reserved state, and hands it to a consumer without losing changes. The exact parameter matrix for the creation function is broken down in the pg_create_logical_replication_slot step-by-step walkthrough.

1. Confirm prerequisites before touching anything. Fail fast if the server was never restarted after wal_level was set.

SELECT name, setting FROM pg_settings
WHERE name IN ('wal_level', 'max_replication_slots', 'max_wal_senders', 'max_slot_wal_keep_size');
-- wal_level must read 'logical'; if it still reads 'replica', the restart has not happened.

2. Create the slot idempotently. Wrapping creation in a guard makes the step safe to re-run from a deployment pipeline and avoids the duplicate_object error on retries.

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_replication_slots WHERE slot_name = 'etl_pipeline_slot'
    ) THEN
        PERFORM pg_create_logical_replication_slot(
            'etl_pipeline_slot',  -- slot name (unique per cluster)
            'pgoutput',           -- output plugin, bound for the slot's lifetime
            false,                -- temporary = false: survive the creating session
            true                  -- two_phase = true (PG 14+): decode prepared transactions
        );
    END IF;
END $$;

The temporary and two_phase arguments were added in PostgreSQL 14; on 13 and earlier the function takes only the name and plugin. A non-temporary slot persists across server restarts and creating-session disconnects — which is exactly what a durable CDC pipeline needs, and exactly why a forgotten one leaks WAL indefinitely.

3. Verify the slot is reserved. Immediately after creation the slot is active = false with a set restart_lsn; WAL is now being retained but nothing is being decoded until a consumer attaches. This gap is intentional for bootstrapping but must be short and monitored.

SELECT slot_name, plugin, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots WHERE slot_name = 'etl_pipeline_slot';

4. Attach the consumer. For a native path, CREATE SUBSCRIPTION ... WITH (create_slot = false, slot_name = 'etl_pipeline_slot') binds an existing slot so slot provisioning and subscription creation stay decoupled and independently recoverable. For a Python worker, open a LogicalReplicationConnection and begin streaming from the slot. The consumer must advance the cursor by sending standby status updates; without them PostgreSQL treats the consumer as alive but idle and never recycles WAL past restart_lsn.

import psycopg2
from psycopg2.extras import LogicalReplicationConnection

conn = psycopg2.connect(
    "host=primary dbname=app user=cdc_reader replication=database",
    connection_factory=LogicalReplicationConnection,
)
cur = conn.cursor()
cur.start_replication(
    slot_name="etl_pipeline_slot",
    decode=False,  # pgoutput is binary; parse it or use options for a text plugin
    options={"proto_version": "1", "publication_names": "etl_pub"},
)

def consume(msg):
    process(msg.payload)          # idempotent upsert keyed on primary key + LSN
    msg.cursor.send_feedback(flush_lsn=msg.data_start)  # advance confirmed_flush_lsn

cur.consume_stream(consume, keepalive_interval=10)  # feedback at least every 10 s

5. Confirm the cursor is advancing. Within a few seconds of the consumer connecting, active flips to true and confirmed_flush_lsn begins tracking restart_lsn forward. If confirmed_flush_lsn stays frozen while the primary keeps writing, the consumer is reading but never acknowledging — the single most common cause of runaway WAL growth. Durable-state handling and initial-snapshot coordination continue in the subscription sync procedures.

At scale, none of this should be executed by hand. The idempotent SQL above is designed to drop straight into a provisioning role — automating slot creation with Ansible shows how to make slot lifecycle a versioned, converging part of your infrastructure-as-code rather than a manual runbook step.

Parameter Reference Table #

PG 17 note: the failover slot option and pg_sync_replication_slots() close a long-standing gap where a publisher failover orphaned every logical slot and forced consumers to re-seed; on 16 and earlier, slot recovery after a primary switch is a manual runbook step.

Diagnostic Queries #

Slot state, retained WAL, and lag in one pass. Run this first for any slot incident — a frozen restart_lsn with active = false is the WAL-bloat signature.

SELECT slot_name, plugin, slot_type, active,
       restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       wal_status,          -- PG 13+: reserved | extended | unreserved | lost
       safe_wal_size        -- PG 13+: bytes left before invalidation (NULL if unbounded)
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Threshold callout: alert when retained_wal for any single slot exceeds ~25% of the pg_wal volume, or when wal_status reads extended (the slot has crossed max_slot_wal_keep_size and is on borrowed time before lost).

Consumer liveness and end-to-end lag from the sender’s perspective — join to see whether an active slot is actually keeping up.

SELECT s.slot_name, s.active, r.application_name, r.state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), r.sent_lsn))   AS send_lag,
       pg_size_pretty(pg_wal_lsn_diff(r.sent_lsn, r.flush_lsn))            AS flush_lag,
       (now() - r.reply_time)                                             AS since_last_reply
FROM pg_replication_slots s
LEFT JOIN pg_stat_replication r ON r.pid IS NOT DISTINCT FROM s.active_pid
WHERE s.slot_type = 'logical';

Threshold callout: since_last_reply climbing past your keepalive_interval (10 s in the example above) means the consumer has stopped sending feedback even though the connection is open — treat it as a stalled consumer, not a healthy idle one.

Blocking transaction check — because a slot cannot advance restart_lsn past the oldest running transaction the decoder still needs.

SELECT pid, state, now() - xact_start AS xact_age, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 5;   -- a very old xact_start pins WAL for every logical slot on the cluster.

Exporting these signals as time series with SLO alerting — rather than running them by hand during an incident — is the job of asynchronous monitoring integration.

Failure Modes & Gotchas #

Orphaned slot exhausts the disk. Signature: pg_wal grows steadily; one slot shows a frozen restart_lsn and active = false. Root cause: a slot was created and its consumer never attached, or the consumer died without dropping the slot. Remediation: restart the consumer, or SELECT pg_drop_replication_slot('etl_pipeline_slot') if it is genuinely abandoned. Prevention: set max_slot_wal_keep_size, which trades a bounded re-seed for a full disk, and alert on active = false lasting more than a few minutes.

Slot invalidated after crossing max_slot_wal_keep_size. Signature: wal_status = 'lost', and the consumer’s next connection fails with requested WAL segment ... has already been removed. Root cause: retention exceeded the cap while the consumer was down, so PostgreSQL invalidated the slot to protect the disk (PG 13+). Remediation: the slot is dead — drop it, recreate it, and re-seed the consumer from a fresh snapshot. There is no way to rewind. Tune the cap against your worst tolerable consumer outage.

Consumer connected but WAL never recycles. Signature: active = true, yet confirmed_flush_lsn stays frozen and retained_wal climbs. Root cause: the consumer streams changes but never sends standby feedback (send_feedback / flush_lsn never called). Remediation: fix the feedback loop so it fires at least every keepalive_interval; confirm with diagnostic query 1 that confirmed_flush_lsn starts moving.

Wrong plugin bound at creation. Signature: the consumer errors on the very first message, or a native subscription refuses to attach a wal2json slot. Root cause: the plugin is fixed for the slot’s lifetime and does not match the consumer’s decoder. Remediation: drop and recreate the slot with the correct plugin — pgoutput for native subscriptions and protocol-speaking Python workers, a text/JSON plugin only for parsers built around it.

Slot lost after publisher failover (PG 16 and earlier). Signature: after promoting a standby, no logical slots exist and every consumer requests WAL the new primary never retained. Root cause: logical slots were not synchronized to standbys before PG 17. Remediation: on 17, create slots with failover = true and run pg_sync_replication_slots(); on 16 and earlier, script slot recreation plus a re-snapshot into the promotion runbook as a mandatory step.

Integration Touchpoints #

A slot is one half of a contract whose other half lives across several adjacent topics. Upstream, the publication it is bound to by a subscriber determines which decoded changes actually flow through it, and the topology reasoning behind that binding — fan-out, cascading, and partial datasets — sits in the logical replication architecture fundamentals. Downstream, the cursor this slot maintains is the resume point that subscription sync procedures build the initial snapshot against, and the LSN it confirms is the number asynchronous monitoring integration exports for lag SLOs.

When the consumer is an event-streaming pipeline rather than a native subscriber, the same slot is opened by the Debezium connector, and its decoded changes are fanned out through the Kafka event routing integration — at which point offset management and idempotency move out of the SQL apply worker and into the streaming layer, while the slot on the publisher keeps enforcing the same WAL-retention contract described here.

Frequently Asked Questions #

Related guides #

• pg_create_logical_replication_slot step-by-step — the full argument matrix and reserved-state verification for the creation function.
• Automating slot creation with Ansible — make idempotent slot lifecycle a converging part of your infrastructure-as-code.
• Creating publications — define the exposure boundary a slot streams against.
• Subscription sync procedures — drive and recover the initial snapshot from the cursor this slot maintains.
• Asynchronous monitoring integration — export slot retention and apply-lag metrics with SLO alerting.
• Replication slot types — how physical, logical, temporary, and failover slots differ under the hood.
• Logical Replication Setup & Management — the management layer this slot provisioning step belongs to.