PostgreSQL Change Data Capture (CDC): Real-Time Replication with Logical Decoding

Modern applications rarely run on one datastore. The primary database feeds dashboards, search indexes, caches, analytics warehouses — and every one of them needs to know when a row changes. Change Data Capture (CDC) is the set of techniques for detecting those changes at the source and delivering them to consumers as they happen.

This guide covers PostgreSQL CDC end to end: how the capture methods compare, how to set up logical decoding step by step, the pitfalls that only show up in production, and how to run Postgres CDC without writing your own consumer.

CDC-based data integration is three steps, whatever the tooling:

• capture changes in the source database
• convert them to a format consumers can accept
• deliver them to the consumer or target database

Three ways to capture changes in PostgreSQL

Triggers

Trigger-based CDC creates audit triggers that capture INSERT, UPDATE and DELETE events into a changes table. Triggers can be attached to tables or views, and can also fire for TRUNCATE. Everything happens at the SQL level — no server configuration required — which is why the approach survives. The cost: every data change now performs additional writes inside the transaction, and that overhead scales with write volume.

Transaction logs (WAL)

PostgreSQL writes every committed change to the Write-Ahead Log before the client gets its transaction result. Log-based CDC reads that log instead of touching your tables:

• no impact on the schema and no extra writes per transaction
• no application changes — capture happens below the SQL layer
• works at any transaction volume, which is why it is the default choice for replication

Note that DDL statements (CREATE, ALTER, DROP) are not part of the logical replication stream; TRUNCATE is (PostgreSQL 11+).

To get row-by-row streaming of Postgres changes as they happen, you use logical decoding — the official name of PostgreSQL's log-based CDC, available in every supported Postgres version.

Setting up logical decoding, step by step

Everything below works on PostgreSQL 10+ unless a higher version is noted.

1. Server configuration

In postgresql.conf:

wal_level = logical
max_replication_slots = 5
max_wal_senders = 10

• wal_level = logical makes the WAL record the information logical decoding needs.
• max_replication_slots must be at least the number of CDC consumers plus any other replication slots the database uses.
• max_wal_senders should be roughly double the slot count — it caps concurrent WAL connections.

Restart PostgreSQL to apply. On managed services this is a parameter-group change, not a config file — see the managed-Postgres section below.

2. User privileges

The CDC user needs replication capability and read access to the tables it will decode:

ALTER ROLE cdc_user WITH REPLICATION LOGIN;

-- PostgreSQL 14+: one grant for read access to all tables
GRANT pg_read_all_data TO cdc_user;

-- Before 14: grant per schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_user;

Also check pg_hba.conf allows a replication connection from the consumer's host.

3. Replica identity

UPDATE and DELETE events carry the old row values according to the table's replica identity. The default (primary key) is enough for most pipelines. For tables without a primary key, or when consumers need full old-row images:

ALTER TABLE t REPLICA IDENTITY FULL;

Without this, updates and deletes on PK-less tables either fail to publish or arrive without enough data to apply.

4. Create a replication slot

A slot is the server-side cursor that tracks what a consumer has confirmed:

SELECT pg_create_logical_replication_slot('replication_slot', 'pgoutput');

Slot names use lower-case letters, numbers, and underscores. Verify:

SELECT slot_name, plugin, slot_type, database, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

5. Create a publication

A publication defines which tables (and which operations) are streamed:

CREATE PUBLICATION pub FOR ALL TABLES;
-- or specific tables:
CREATE PUBLICATION pub FOR TABLE table1, table2, table3;
-- or a whole schema (PostgreSQL 15+):
CREATE PUBLICATION pub FOR TABLES IN SCHEMA public;
-- or only some operations:
CREATE PUBLICATION ins_upd_pub FOR TABLE table1 WITH (publish = 'insert, update');

Verify:

SELECT * FROM pg_publication_tables WHERE pubname = 'pub';

6. Watch changes flow

Create a test table and insert rows:

CREATE TABLE t (id int, name text);
INSERT INTO t(id, name)
SELECT g.id, substr(md5(random()::text), 0, 25)
FROM generate_series(1, 10) AS g(id);

For a quick look at what the WAL contains, the SQL interface is enough (this uses the test_decoding plugin, which emits human-readable text):

SELECT pg_create_logical_replication_slot('peek_slot', 'test_decoding');
SELECT * FROM pg_logical_slot_get_changes('peek_slot', NULL, NULL);

    lsn    | xid  | data
-----------+------+-----------------------------------------------------------
 0/19EA2C0 | 1045 | BEGIN 1045
 0/19EA2C0 | 1045 | table public.t: INSERT: id[integer]:1 name[text]:51459cbc...
 ...
 0/19EA5B0 | 1045 | COMMIT 1045

pg_logical_slot_peek_changes returns the same rows repeatedly without consuming them; pg_logical_slot_get_changes consumes — the second call returns nothing, and the slot's confirmed_flush_lsn advances.

7. Drop slots you no longer need

SELECT pg_drop_replication_slot('peek_slot');

An abandoned slot pins WAL forever — see pitfalls below. This is not housekeeping advice; it is the most common way logical decoding takes a production database down.

Choosing an output plugin

The output plugin decides the wire format a slot emits.

pgoutput output is binary — pair it with a consumer that speaks the protocol. A wal2json update event looks like:

{"change": [{
  "kind": "update",
  "schema": "public",
  "table": "t",
  "columnnames": ["id", "name"],
  "columnvalues": [1, "New Value"],
  "oldkeys": {"keynames": ["id"], "keyvalues": [1]}
}]}

Production pitfalls

The setup above works in an afternoon. These are the things that page you at night.

Slots pin WAL — monitor them

Postgres keeps every WAL segment a slot has not confirmed. A consumer that stops (or a slot nobody uses) grows pg_wal until the disk fills. Watch slot lag:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

Set a ceiling so a dead consumer cannot take the database down (PostgreSQL 13+):

max_slot_wal_keep_size = 10GB

When the ceiling is hit the slot is invalidated (wal_status = 'lost') instead of the instance dying — the consumer then needs a fresh slot and a re-sync, which is the right trade.

Restarts can re-emit changes

After a Postgres restart, a slot may re-send changes the consumer already saw. Consumers must be idempotent — apply with upsert semantics or track LSNs, don't assume exactly-once delivery.

Failover loses slots (before PostgreSQL 17)

Replication slots live on the primary and are not carried over by physical failover. After promoting a standby, your CDC pipeline has no slot and no position. PostgreSQL 17 added failover slot synchronization (sync_replication_slots) to close exactly this gap; on older versions, plan for re-creating slots and re-syncing after failover.

TOAST columns

Large values Postgres stores out-of-line (TOAST) are not included in an update event if the column didn't change. Consumers must treat "column absent" as "unchanged", or the table needs REPLICA IDENTITY FULL to force full row images — at the cost of larger WAL.

Managed Postgres specifics

Logical decoding works on RDS, Aurora, Cloud SQL, Neon, Supabase, DigitalOcean and Azure — but configuration goes through provider parameters, not postgresql.conf. On RDS/Aurora set rds.logical_replication = 1 in the parameter group and reboot; the provider manages wal_level for you. Some providers restrict superuser-only operations, so always create the slot with the provider's replication role.

The initial load problem

A replication slot only streams changes made after it was created. The rows already in your tables never appear in the WAL stream. Every real pipeline therefore has two phases:

1. Snapshot the existing data consistently, noting the LSN.
2. Stream changes from that LSN on, applying them with upsert semantics so the overlap between snapshot and stream converges instead of conflicting.

Getting the handoff right — no gap, no permanent duplicates — is the hardest part of CDC in practice, and it is the main reason to use an existing tool rather than a bare consumer. How DBConvert Streams runs this two-phase sequence is documented in Initial Load + CDC, with a step-by-step example in the Initial Load and CDC walkthrough.

Build a consumer, or use one

A consumer is any application that ingests the logical decoding stream. For testing, pg_recvlogical (shipped with Postgres) can manage slots and dump the stream to stdout. For a real pipeline, a consumer has to do all of this correctly:

• speak the pgoutput binary protocol and track relation metadata
• send standby status updates so the slot's confirmed_flush_lsn advances — or WAL piles up
• survive restarts and re-emitted changes idempotently
• handle the snapshot-to-stream handoff for pre-existing data
• map types, schemas, and DDL differences onto the target

That is months of engineering before the first byte reaches your target database — we know because we built it: our replication engine is a logical replication client written in Go on the same protocol described above. Which brings us to the part where you don't build any of this.

Postgres CDC with DBConvert Streams

DBConvert Streams is our replication platform built on exactly the mechanics this post describes: it connects as a logical replication client, reads the WAL through pgoutput, and applies changes to the target — no Kafka, no Debezium, no consumer code. Targets: PostgreSQL, MySQL, S3-compatible storage, and local files.

A Postgres CDC stream, end to end:

1. Add the Postgres connection and open the database in Data Explorer. The CDC readiness card shows wal_level, available replication slots, and WAL senders — if wal_level is not logical, it says so before you build anything.
2. Create a stream, pick Stream (Change Data Capture) as the transfer mode. The wizard re-checks the source and refuses CDC mode with a specific warning if the source isn't ready.
3. If the source tables already contain rows, enable Initial Load + CDC and use upsert target write mode — the snapshot/stream handoff from the previous section, handled for you.
4. Start the stream and open Monitor. CDC Continuity shows the bootstrap finishing and the switch to ongoing WAL apply.

Checkpoints advance only after the target acknowledges the write, so stopping and restarting a stream resumes from the saved position (as long as the source retains the WAL range — see the slot pitfalls above; the same rules apply to any consumer, including ours).

And the target does not have to be another Postgres. The same WAL stream feeds heterogeneous targets: replicate PostgreSQL to MySQL for a cross-engine migration that stays in sync, or to S3 as compressed Parquet/CSV for analytics and archival — schema and type mapping between engines is handled by the platform.

Setup guides: PostgreSQL CDC source configuration, what is CDC mode.

FAQ

Which PostgreSQL versions support CDC?
Logical decoding exists since 9.4 (test_decoding) and the pgoutput plugin since 10. Practically: use 10+ with pgoutput. PostgreSQL 14 adds the pg_read_all_data grant, 15 adds schema-level publications, 17 adds failover slot synchronization.

Does logical decoding slow down the source database?
There are no table scans and no per-row triggers; the database is already writing the WAL. wal_level = logical records somewhat more WAL data, and REPLICA IDENTITY FULL increases it further on updates. The real operational risk is not CPU — it is disk, from an unconsumed slot pinning WAL.

Can I replicate PostgreSQL to a different database engine?
Yes — the logical decoding stream is engine-agnostic once decoded. DBConvert Streams applies it to MySQL, another PostgreSQL, or S3-compatible storage, converting schemas and types between engines automatically.

Does Postgres CDC work on RDS, Aurora, Cloud SQL, Neon, or Supabase?
Yes. Enable logical replication through the provider's parameter mechanism (rds.logical_replication = 1 on RDS/Aurora) and use the provider's replication role.

Do I need Kafka or Debezium?
No. Debezium-into-Kafka is one architecture, appropriate when Kafka is already your event backbone. A direct logical-replication consumer — such as DBConvert Streams with its embedded message layer — replicates database-to-database without either.

Are schema changes replicated?
No. DDL (ALTER TABLE, CREATE INDEX, …) is not in the logical replication stream — coordinate schema migrations between source and target yourself. TRUNCATE is streamed (PostgreSQL 11+).

How do I replicate the data that's already in the table?
A slot only streams changes made after its creation. You need a consistent snapshot first, then CDC from the snapshot's LSN — see "The initial load problem" above.

Conclusion

Logical decoding turns PostgreSQL from a database you poll into a database that pushes: every committed change, in order, in milliseconds, from the log it was already writing. The setup is seven SQL statements; the engineering is in the production edges — slot retention, restarts, failover, TOAST, and the initial load. You can build a consumer for those edges, or let DBConvert Streams run the same mechanics with the edges already handled.