Why PostgreSQL CDC Breaks in Production

Keeping two PostgreSQL databases in sync looks easy at first. Logical replication is on, changes flow, the target looks current.

A few days later something is off:

• rows are missing;
• some updates arrive twice;
• replication lag jumps for no clear reason;
• a small schema change stops the pipeline;
• after a restart, nobody is sure where it resumed.

Most CDC guides stop at "enable logical replication, create a slot". That part works. PostgreSQL writes every committed change to the WAL, in order.

The failures happen after that. Below are the seven I see most often. Each one: what happens, what you see, how to prevent it.

1. A forgotten slot fills the source disk

What happens. A replication slot remembers the last position (LSN) its consumer confirmed. PostgreSQL keeps every WAL file after that position, so the consumer can resume without losing changes.

If the consumer stops and nobody removes the slot, WAL keeps piling up.

WAL:  ──[slot stopped here]───────────────────────────►  writes continue
            │◄──────── all of this stays on disk ───────►│
              day 1: 50 GB     day 2: 100 GB     day 3: disk full

What you see. The disk of the source database fills. PostgreSQL stops accepting writes. Not just CDC is down: the application is down.

This is the only CDC failure on this list that takes down the source itself.

Prevention.

• Alert when a slot is inactive: active = false in pg_replication_slots.
• Alert on how much WAL a slot holds (query below).
• Set max_slot_wal_keep_size. Past this limit PostgreSQL invalidates the slot instead of filling the disk. CDC then needs a fresh snapshot, but the database stays up. A re-sync is cheaper than an outage.
• Drop slots that no pipeline owns.

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

In DBConvert Streams. The connection page lists every replication slot on the server: which stream owns it, whether it is active, and how much WAL it holds.

An inactive slot over 1 GB is flagged, and so is a missing max_slot_wal_keep_size. A leftover slot no stream owns is dropped in one click.

2. The gap between initial load and CDC

What happens. CDC streams changes from a point in time. It does not copy rows that already exist. So a new target first needs an initial load, then CDC.

If these are two separate steps, there is a gap:

• CDC starts after the load's point in time: changes in between are lost.
• CDC starts before it: changes are applied twice, or old updates overwrite newer rows.

What you see. A few missing or stale rows. Nothing fails, so nobody notices until a report doesn't add up.

Prevention. Load and CDC must share one exact WAL position:

1. Create the slot with an exported snapshot. PostgreSQL returns the snapshot name and the slot's starting LSN.
2. Read the initial load from that snapshot (SET TRANSACTION SNAPSHOT ...).
3. Start CDC from the slot's LSN.

The copy then ends exactly where CDC begins. Anything else is a guess.

In DBConvert Streams, Initial Load + CDC runs as one stream. The run monitor shows the handoff: the snapshot completes, then CDC applies from the handoff LSN.

To catch rows a gap would lose, Verify compares source and target after the load and reports how many rows differ, and in which columns.

3. The checkpoint moves before the write

What happens. The pipeline saves its position (checkpoint) so it can resume after a restart. If it saves the position before the target write is confirmed:

1. Read the event at LSN X.
2. Save checkpoint X.
3. Target write fails, process crashes.
4. Restart resumes after X. The event is skipped.

What you see. Nothing. The system believes the event was delivered. This is silent data loss.

Prevention. Fix the order:

1. Read the event.
2. Write to the target.
3. Wait for the target to confirm.
4. Only then save the checkpoint.

A crash between steps 2 and 4 replays the event. That leads to the next problem, which is easy to solve.

4. Replays create duplicates

What happens. With the safe checkpoint order, a crash replays events that may already be in the target. This is called at-least-once delivery. It is normal, not a bug.

What you see if the target write is not ready for replays:

• duplicate inserts;
• counters incremented twice;
• repeated audit rows.

Prevention. Make every target write safe to apply twice (idempotent):

• inserts as upserts on the primary key;
• updates that set values, never increment them;
• deletes that don't fail when the row is already gone.

Safe checkpoint order plus idempotent writes give correct recovery. Production will hit the retry path sooner or later.

In DBConvert Streams, Upsert, the recommended CDC write mode, writes by primary key, so a replay overwrites a row instead of duplicating it. The start plan refuses an upsert into a table without a primary key.

5. Parallel workers break the order

What happens. WAL is in commit order. Once the pipeline runs several workers in parallel, events can overtake each other:

• UPDATE customer 42, then DELETE customer 42: the delete lands first, the update recreates or fails.
• INSERT parent, then INSERT child: the child lands first and violates the foreign key.

What you see. Rows that should be gone, foreign key errors, values that flip back.

Prevention. Keep order where it matters:

• changes to one table, or one key, go through one stream;
• parallelism only across independent tables or keys;
• retries never reorder events.

Decide the ordering guarantee first, then add speed.

DBConvert Streams applies changes in source commit order, so an UPDATE never overtakes the DELETE that follows it.

6. Long transactions look like lag

What happens. A change can be replicated only after its transaction commits. A long transaction sends nothing for minutes, then everything at once.

BEGIN;
UPDATE orders
SET status = 'archived'
WHERE created_at < '2024-01-01';
-- runs for several minutes; CDC sees nothing yet
COMMIT;
-- now all rows arrive at once

What you see. The pipeline looks stuck, then lag spikes and workers face a sudden backlog. The pipeline is fine; the source transaction is the cause.

Prevention. CDC cannot change this. Split bulk updates into smaller transactions, and check for long transactions in pg_stat_activity before blaming the pipeline.

7. Schema changes stop the stream

What happens. Someone adds a column, changes a type, renames a table. CDC carries data changes, not a plan for the target schema. The pipeline must decide: the column isn't there, the type doesn't map. Stop or continue?

What you see. Either the pipeline stops, or it keeps running and writes wrong data.

Prevention.

• detect schema mismatch;
• fail loudly when the target write is unsafe;
• let people coordinate the change on both sides.

A stopped pipeline is honest. One that keeps running incorrectly is worse.

In DBConvert Streams, the start plan checks every table before the stream starts. A conflict with the schema policy, or a CDC table without a primary key, blocks the start with the reason. It does not follow schema changes after the start.

What DBConvert Streams handles, and what it doesn't

DBConvert Streams keeps load, handoff, replication and resume in one workflow, so the state between them is explicit.

Since version 2.1:

• Initial Load → CDC hands off automatically from a saved position.
• CDC resumes from the last durable checkpoint after Stop or restart.
• Eligible large loads continue from saved progress instead of starting from zero.

Each stream owns its own replication slot and publication. Deleting the stream drops both, and the connection page shows any slot left behind.

Not handled automatically:

• schema evolution;
• exactly-once delivery across source, pipeline and target;
• lag caused by long source transactions;
• repairing a target after manual changes.

When you don't need CDC

Use a scheduled full reload instead if:

• data changes rarely;
• latency does not matter;
• the target can be rebuilt cheaply.

A 10-minute reload that is easy to verify beats a CDC pipeline nobody fully understands. CDC is worth it when freshness matters and the source cannot be reloaded again and again.

Final thought

CDC is hard because it carries state: snapshot, WAL position, checkpoint, target commits, retries, schema. When that state is implicit, CDC breaks in strange ways. When it is explicit, CDC becomes boring. Boring is what production replication should be.

• Docs: Initial Load + CDC
• Docs: Resumable Load
• Install DBConvert Streams