PostgreSQL Logical CDC

Notes on PostgreSQL logical replication based Change Data Capture (CDC). Applicable to Debezium, Kafka Connect, Microsoft Fabric, Flink CDC, Airbyte, AWS DMS and most other PostgreSQL logical replication based CDC tools.

Why Use CDC?

• Near real-time data synchronization
• Data warehouse / lakehouse ingestion
• Event driven architecture
• Audit and analytics
• Search indexing
• Microservice integration

Use CDC when real-time changes are required, a full database dump is not feasible, analytics need incremental changes, or integration with Kafka / Fabric / EventHub / Eventstream / lakehouse is required.

Do not use CDC for backup, disaster recovery (use physical replication) or high availability (use streaming replication).

Replication Types

Physical and logical replication can run simultaneously. wal_level=logical supports both.

PostgreSQL Configuration

# Filename: postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Restart PostgreSQL after changing wal_level (the other parameters just need a reload).

Add a pg_hba.conf entry allowing the CDC user to open a replication connection:

# Filename: pg_hba.conf
# TYPE  DATABASE     USER      ADDRESS          METHOD
host    replication  cdc_user  10.0.0.0/8       scram-sha-256

CDC User

CREATE ROLE cdc_user LOGIN PASSWORD '********' REPLICATION;

-- Grant database access
GRANT CONNECT ON DATABASE example TO cdc_user;

GRANT pg_read_all_data TO cdc_user;

pg_read_all_data (built-in since PostgreSQL 14) grants SELECT privileges on all tables, views, and materialized views in all schemas (including any new ones created in the future). It also grants USAGE on all schemas, allowing the user to look inside them.

Publication

-- Entire database
CREATE PUBLICATION cdc_pub FOR ALL TABLES;

-- Specific tables
CREATE PUBLICATION cdc_pub FOR TABLE table1, table2;

-- Entire schema
CREATE PUBLICATION cdc_pub FOR TABLES IN SCHEMA public;

-- Add / remove a table later
ALTER PUBLICATION cdc_pub ADD TABLE table_name;
ALTER PUBLICATION cdc_pub DROP TABLE table_name;

-- Tear down
DROP PUBLICATION cdc_pub;

Future tables only join the publication automatically if it was created FOR ALL TABLES. Otherwise each new table needs an explicit ALTER PUBLICATION ... ADD TABLE.

Logical replication only replicates row data (INSERT / UPDATE / DELETE); DDL (ALTER TABLE, CREATE TABLE, etc.) is not captured.

Replica Identity

Logical replication needs a replica identity to represent the old row for UPDATE and DELETE.

Replica identity is not inherited by new tables — a primary key, unique index, or REPLICA IDENTITY FULL must be set when each table is created.

When to Use REPLICA IDENTITY FULL

Use it only when there is no primary key, no suitable unique index, and the schema cannot be modified.

Avoid it on large tables or tables with large TEXT/JSONB/BYTEA columns: the entire old row is written into the WAL, which generates more WAL, larger CDC payloads, and can exceed CDC connector message size limits.

-- Single table
ALTER TABLE schema.table REPLICA IDENTITY FULL;

-- Rollback
ALTER TABLE schema.table REPLICA IDENTITY DEFAULT;

Enable FULL for every table in a schema:

DO $
DECLARE r RECORD;
BEGIN
  FOR r IN
    SELECT n.nspname, c.relname
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  LOOP
    EXECUTE format('ALTER TABLE %I.%I REPLICA IDENTITY FULL', r.nspname, r.relname);
  END LOOP;
END$;

Generate FULL only for tables without a primary key (swap FULL for DEFAULT to generate the rollback):

SELECT
  'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname)
  || ' REPLICA IDENTITY FULL;'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint pk ON pk.conrelid = c.oid AND pk.contype = 'p'
WHERE c.relkind = 'r'
AND pk.oid IS NULL
AND n.nspname NOT IN ('pg_catalog', 'information_schema');

Initial Snapshot

Most CDC tools take an initial snapshot of existing data, then start streaming WAL changes. In a Debezium payload, op=r with snapshot=true means initial snapshot; op=c / op=u / op=d means live CDC.

Common Errors

cannot update table because it does not have a replica identity — add a primary key, unique index, or REPLICA IDENTITY FULL.

Producer send failed due to record too large — caused by large rows combined with REPLICA IDENTITY FULL. Add a primary key, normalize large columns, or avoid FULL where possible.

WAL growing continuously — usually an inactive replication slot holding back WAL. Check pg_replication_slots and, only after confirming no CDC connector uses it, drop the stale slot:

SELECT pg_drop_replication_slot('slot_name');

Slot shows active = false — the connector may be stopped, the snapshot may have failed, the connector may have been recreated, or it's simply an old unused slot.

Monitoring

-- Replication slots (watch active, restart_lsn, confirmed_flush_lsn)
SELECT * FROM pg_replication_slots;

-- WAL retained per slot
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
FROM pg_replication_slots;

-- Publications and their tables
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

-- Physical replication
SELECT * FROM pg_stat_replication;

-- WAL statistics
SELECT * FROM pg_stat_wal;

-- Active replication connections
SELECT * FROM pg_stat_activity WHERE backend_type = 'walsender';

References

• PostgreSQL Logical Replication
• PostgreSQL Publications & Replica Identity