PostgreSQL 18 CDC in Production: Replication Slots, WAL, and Failover

Logical decoding makes PostgreSQL a capable source for change data capture, but enabling it is the easy part. The difficult work begins when a consumer stalls, a snapshot runs for hours, a table without a stable key receives updates, or the primary fails while the logical slot is still catching up to a standby.

A production design has to protect two systems at once. It must preserve enough source history for the pipeline to recover, while preventing that history from becoming an unbounded operational liability for the database. It must also preserve transaction meaning beyond the source: a slot can identify where decoding should resume, but it cannot by itself guarantee that a destination has applied every change once.

How does PostgreSQL logical decoding produce a CDC stream?

PostgreSQL records database changes in the write-ahead log, or WAL, before modified pages are written to data files. Logical decoding reads those WAL records and reconstructs changes in terms of tables, rows, and transactions. An output plugin converts the decoded records into a protocol a consumer can process. Each change carries a position in the WAL, expressed as a log sequence number, or LSN.

Commit boundaries matter more than individual row arrival. A transaction may modify many tables and remain invisible until commit. A CDC system that emits rows before preserving that boundary can expose downstream consumers to states that never existed in the source. Large transactions create the opposite problem: the consumer may appear current while a substantial uncommitted transaction is still being buffered.

Native PostgreSQL logical replication uses publications and subscriptions. A third-party CDC consumer may instead use a logical slot and output plugin without acting as a native PostgreSQL subscriber. That distinction affects setup, monitoring, initial synchronization, and failover. Teams should document the exact capture path rather than assume that all logical-decoding systems behave like native publication and subscription.

A replication slot records the position required by a consumer and prevents PostgreSQL from removing WAL still needed from that point. It protects continuity at the source. It does not prove that a sink committed the corresponding records, that retries are idempotent, or that multiple destinations observed the same order. Those properties require coordination between source position, connector checkpoint, and sink acknowledgement.

Which settings, privileges, and table properties matter?

The publisher needs wal_level set to logical. It also needs enough replication slots and WAL sender processes for steady-state streams, table synchronization, maintenance, and recovery. Capacity values should be derived from the actual topology; copying a generic setting can leave no headroom during initialization or create unused capacity without addressing WAL risk.

Use a dedicated replication identity and a least-privilege account. Restrict network access, require encrypted transport where appropriate, rotate credentials, and monitor both authentication failures and privilege changes. Publication scope should be deliberate: capturing every table because it is convenient increases data exposure, WAL volume, schema-change surface, and downstream cost.

Replica identity determines how an update or delete identifies its previous row. A primary key is the normal choice. A suitable unique index can also work. REPLICA IDENTITY FULL uses the old row as the identity and can be useful as a fallback, but it increases WAL and may make downstream matching inefficient. It should be a measured exception, not a default.

Why do replication slots cause WAL growth and disk incidents?

Three positions explain most slot incidents. The current WAL position shows how far the source has advanced. confirmed_flush_lsn reflects what a logical consumer has acknowledged. restart_lsn is the oldest WAL the slot may still need. When the consumer stops, the target blocks, or acknowledgements fail, the distance between the current position and the retained position grows.

That distance is a storage obligation, not just an abstract lag metric. An inactive slot can retain WAL while dashboards focused on record throughput show no obvious error. If retained WAL consumes the filesystem, the database—not only the pipeline—can fail.

Guardrails move risk; they do not remove it. A configured limit on slot-retained WAL can protect disk, but the slot may become unusable after required WAL is removed. An idle-slot timeout can invalidate forgotten slots, but a paused consumer may then require reinitialization. The safe values depend on the peak WAL rate, the longest supported outage, available storage, and the time required to create a new snapshot.

A useful planning model is:

required WAL headroom = peak WAL bytes per second × maximum recoverable outage × safety factor

Use a peak observed during business events, maintenance, or batch posting—not a daily average. Alert on retained bytes and the rate at which headroom is being consumed. Time-to-exhaust is usually more actionable than a static disk percentage.

A slot incident decision sequence

1. Confirm whether the consumer is active and whether its checkpoint is advancing.
2. Measure retained WAL and estimate time to the storage guardrail.
3. Identify whether the blocker is capture, network, buffering, sink application, or acknowledgement.
4. Restore consumption or add explicitly approved temporary capacity if the recovery window remains intact.
5. Advance or drop a slot only when the data-loss consequence is understood and an authoritative recovery path exists.
6. If required WAL is gone, stop treating the incident as a retry problem and initiate the resnapshot plan.

How should initial snapshot and stream handoff work?

A historical snapshot and an incremental stream must share one consistency boundary. Otherwise, changes committed while the snapshot is running can be missed or applied twice. A common pattern creates or records a consistent WAL position, starts retaining or reading changes from that point, copies historical rows from the corresponding snapshot, and then applies buffered changes after the base copy is complete.

The boundary must survive restart. Record the snapshot state, source position, per-table progress, and downstream commit state in durable storage. Decide whether recovery can resume a table, a partition or key range, or only the full snapshot. That decision directly affects required WAL retention.

Long snapshots affect the source. They can increase read I/O, hold resources, interact with vacuum, and extend the period for which WAL must remain available. Throttle by measured database guardrails rather than by a fixed connector rate. Test DDL, large transactions, table additions, and consumer restarts during the snapshot—not only after it.

How does PostgreSQL 18 logical replication failover work?

Logical replication cannot simply reconnect to any promoted standby. The standby must possess the logical slot state needed by the consumer, and it must have received the corresponding WAL. PostgreSQL can synchronize failover-enabled logical slots from a primary to a physical standby. The synchronization is asynchronous, so a configured slot is not automatically a failover-ready slot.

For a planned switchover, verify that every required logical slot exists on the candidate standby, is marked as synchronized, is not temporary, and has no invalidation reason. Also verify that the standby is ahead of each consumer position it must serve. The configuration connects three controls: a failover-enabled logical slot, a standby that synchronizes replication slots, and physical standby slots identified so WAL is not consumed before it is safely present.

PostgreSQL 18 adds useful logical-replication improvements, including support for generated-column values in publications, changes around two-phase behavior, and a command-line option to create failover-enabled slots with pg_recvlogical. These are version-specific operational improvements; failover still spans four independent steps: database promotion, endpoint discovery, connector reconnection, and sink-side duplicate handling.

An unplanned failover deserves a separate test. The candidate may not have the latest synchronized slot state at the moment of failure. The runbook must define how to compare source and consumer positions, determine whether safe continuation is possible, and choose between replay, reconciliation, or reinitialization. A successful database promotion alone is not a successful CDC recovery.

How should schema changes and edge cases be handled?

Schema support has four layers: the source must record enough information, the decoder must express the change, the event contract must carry it, and the destination must apply or quarantine it. Saying a pipeline “supports DDL” is incomplete unless all four are defined.

Classify changes before automating them. Adding a nullable column is usually less risky than renaming or dropping a column. Type changes can be widening, representationally incompatible, or dependent on destination behavior. Generated columns need explicit treatment, especially when producers and consumers run different PostgreSQL versions.

Test tables without stable keys, partitioned tables, truncate operations, large objects, very large transactions, and two-phase commits according to the actual scope of the system. For a large transaction, observe memory or spill, time until commit becomes visible, recovery after a restart, and p99 end-to-end freshness. Average event latency can hide the operational impact.

Which metrics belong on a PostgreSQL CDC dashboard?

Start at the database. Track WAL generation rate, WAL retained by each slot, archive backlog, filesystem headroom, slot activity, restart and confirmed positions, and invalidation state. Convert retained bytes into time-to-exhaust under the current generation rate.

Then track the pipeline: records and bytes per second, queue depth, oldest transaction age, largest transaction, retry rate, checkpoint age, and sink errors. Finally, measure end-to-end freshness from source commit to the point at which a consumer can query or act on the record. Connector lag is not a substitute for this measure.

Correctness indicators belong on the same operational surface: rejected events, schema changes awaiting action, duplicate detections, reconciliation mismatches, and dead-letter volume. A green process with a growing mismatch is not healthy.

What belongs in a production-readiness and failure-test checklist?

• Stop and restart the capture process while the source remains busy.
• Interrupt the network and make the target unavailable for longer than a routine retry.
• Drive WAL retention toward the warning threshold and verify alerts and ownership.
• Invalidate a non-production slot and execute the reinitialization path.
• Restart a snapshot, introduce a large transaction, and apply representative DDL.
• Exercise updates and deletes on tables with and without stable keys.
• Run both a planned switchover and an unplanned primary failure.
• For every test, record expected and actual RPO, RTO, source position, duplicates, gaps, manual actions, and owner.

How should upgrades and configuration changes be controlled?

Treat PostgreSQL, connector, and destination upgrades as changes to one replication system. Before an upgrade, record every slot, plugin, publication, table selection, replica identity, checkpoint, and target mapping. Confirm that the new version preserves the required logical-decoding behavior and that rollback will not strand a slot at an incompatible position. Keep enough WAL and destination capacity for the entire change window, including a failed first attempt.

Run a rehearsal with representative transactions before production. The test should include a transaction open across the upgrade boundary, an update and delete for each replica-identity pattern, a schema change, a connector restart, and a destination retry. After the change, compare source and target control totals and verify that slot positions advance normally. Remove temporary slots only after the reconciliation owner signs off. This discipline is especially important when PostgreSQL 18 features are introduced into an estate that still contains earlier major versions.

Where does this architecture fit?

Fit: PostgreSQL CDC is a strong option when downstream systems need continuous committed changes, the source can support logical decoding, and the organization can operate slots, retention, recovery, and reconciliation as first-class database concerns.

Not a fit: It is a poor choice when a periodic export already meets the business freshness target, when the source cannot provide a stable change identity for required operations, or when no team owns WAL risk and recovery testing.

Next step: Inventory your PostgreSQL versions, HA topology, peak WAL rate, largest transaction, key coverage, destinations, and recovery objectives. Then test the full failure path on representative data. For a broader foundation, read Change Data Capture Architecture and Schema Evolution Without Downtime, or talk to an architect about your topology.