PostgreSQL CDC Tutorial: Stream Database Changes in Real-Time

PostgreSQL CDC Streams Every Change Without Touching Your Queries

You can capture every insert, update, and delete from PostgreSQL in real-time by reading the Write-Ahead Log (WAL), the same log PostgreSQL already writes for crash recovery. No polling. No full-table scans. Minimal performance impact on your production database.

This tutorial walks through the complete setup: configuring PostgreSQL for logical replication, creating publications, monitoring replication slots, and production hardening. By the end, you'll have a working CDC pipeline from first principles. If you want the conceptual grounding first, start with What is Change Data Capture (CDC)? or CDC vs ETL.

Why This Matters

Data warehouse sync. Your transactional PostgreSQL is the source of truth. CDC streams every change to your warehouse in real-time, eliminating expensive full-table scans and keeping analytics fresh. This is what a database CDC source automates end to end.

Microservices decoupling. Instead of services querying your database directly, CDC streams changes to a message queue. Services consume asynchronously. Better resilience, better scalability. The outbox pattern formalizes this so a service's database write and its published event never drift apart.

Real-time dashboards. Business dashboards reflect current state within seconds, not stale hourly snapshots.

Search index sync. Elasticsearch or Algolia indices stay current automatically. No polling jobs.

The common thread: moving data at the speed your business requires without hammering your production database.

Three Foundational Concepts

Write-Ahead Log (WAL)

PostgreSQL guarantees committed transactions survive crashes by writing every change to the WAL before writing to main data files. This sequential, immutable log is the perfect CDC source: every committed change passes through it.

Logical Replication Slots

A replication slot is a bookmark: "I'm reading the WAL from position X." PostgreSQL ensures WAL segments aren't deleted until all active slots have consumed them. This guarantees no changes are lost, but stalled slots can cause WAL bloat, a problem we'll address in production best practices.

Publications

A publication defines what to replicate. Specify exactly which tables and which operations (INSERT, UPDATE, DELETE) get streamed. Exclude sensitive columns directly at the replication layer.

Prerequisites

• PostgreSQL 10+ (logical replication introduced in v10)
• Superuser or replication role privileges
• Network connectivity between PostgreSQL and your CDC consumer
• WAL archiving space (1-2 GB initially)
• Access to postgresql.conf

For managed services (AWS RDS, Azure Database for PostgreSQL), verify logical replication is enabled via parameter groups.

Step 1: Configure PostgreSQL

postgresql.conf

# Enable logical decoding
wal_level = logical

# WAL sender processes (one per replication slot minimum)
max_wal_senders = 10

# Simultaneous logical replication slots
max_replication_slots = 10

# WAL size bounds
min_wal_size = 100MB
max_wal_size = 1GB

# Standby feedback interval
wal_receiver_status_interval = 10s

Restart PostgreSQL:

sudo systemctl restart postgresql

Verify:

SHOW wal_level;           -- Should return "logical"
SHOW max_wal_senders;     -- Should return >= 10
SHOW max_replication_slots; -- Should return >= 10

Create a Dedicated Replication User

CREATE ROLE cdc_user WITH LOGIN REPLICATION PASSWORD 'secure_password_here';
GRANT CONNECT ON DATABASE your_database TO cdc_user;
GRANT USAGE ON SCHEMA public TO cdc_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO cdc_user;

Configure pg_hba.conf

host  replication  cdc_user  192.168.1.100/32  scram-sha-256

Reload:

SELECT pg_reload_conf();

Step 2: Create a Publication

All tables:

CREATE PUBLICATION all_tables_pub FOR ALL TABLES;

Specific tables (more common):

CREATE PUBLICATION orders_and_customers_pub FOR TABLE
  public.orders,
  public.customers,
  public.order_items;

Filtered operations (INSERT and UPDATE only):

CREATE PUBLICATION orders_pub FOR TABLE public.orders
  WITH (publish = 'insert, update');

Column-level filtering (PostgreSQL 13+):

CREATE PUBLICATION sensitive_data_pub FOR TABLE
  public.users (id, name, email),
  public.orders (id, user_id, amount, created_at);

Verify:

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables WHERE pubname = 'orders_and_customers_pub';

Step 3: Monitor Replication Slots

Once your CDC consumer connects, it creates a logical replication slot. Monitoring slot health is critical: a stalled slot causes WAL bloat that can fill your disk.

Check Status

SELECT
  slot_name,
  slot_type,
  active,
  restart_lsn,
  confirmed_flush_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) as bytes_retained
FROM pg_replication_slots;

Detect Stalled Slots

SELECT
  slot_name,
  flush_lsn,
  extract(epoch from (now() - stat_reset)) as seconds_since_stat_reset
FROM pg_stat_replication_slots;

Drop Unused Slots

SELECT pg_drop_replication_slot('your_slot_name');

Only drop slots you no longer need. Dropping an active slot loses your stream position.

Approach Comparison

If polling instead of reading the WAL fits your constraints better, a direct pull source implements query-based capture without any of the WAL configuration below.

Approach	Best For
Native logical replication (this tutorial)	Teams with PostgreSQL expertise and custom consumers. No external dependencies.
Debezium	Organizations using Kafka. Excellent schema change handling via Schema Registry. Additional operational complexity.
Managed platforms (SchemaBounce, AWS DMS)	Teams wanting minimal ops. Automatic WAL management, schema detection, built-in monitoring. Higher cost than self-hosted.

Production Best Practices

Monitor WAL Disk Usage

CREATE OR REPLACE VIEW wal_monitor AS
SELECT
  slot_name,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 as mb_retained,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 / 1024 as gb_retained,
  CASE
    WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 / 1024 > 10 THEN 'CRITICAL'
    WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 / 1024 > 5 THEN 'WARNING'
    ELSE 'HEALTHY'
  END as status
FROM pg_replication_slots;

Set alerts when WAL retention exceeds thresholds.

Track Consumer Lag

SELECT
  slot_name,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) / 1024 / 1024 as mb_lag
FROM pg_replication_slots
WHERE slot_type = 'logical' AND active;

High lag means your consumer can't keep up. Scale its resources or investigate bottlenecks.

Batch Large Transactions

A single transaction updating millions of rows blocks WAL cleanup until fully replicated.

Bad:

UPDATE users SET last_updated = NOW() WHERE id < 1000000;

Better: batch into smaller transactions:

DO $$
DECLARE
  _batch_size INT := 10000;
  _updated INT := 0;
BEGIN
  LOOP
    UPDATE users SET last_updated = NOW()
    WHERE id < 1000000 AND id >= _updated
    LIMIT _batch_size;

    _updated := _updated + _batch_size;
    EXIT WHEN _updated >= 1000000;

    COMMIT;
  END LOOP;
END $$;

Schema Evolution

Adding columns is safe: CDC consumers see the new column in new/updated rows. Removing or renaming columns is risky. Coordinate schema changes with your CDC consumer. Use a schema registry for automatic versioning.

Security

Use SCRAM-SHA-256, not MD5:

password_encryption = scram-sha-256

Never grant superuser to your CDC user:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_user;
ALTER ROLE cdc_user WITH REPLICATION;

Use SSL for replication across networks:

hostssl replication cdc_user 0.0.0.0/0 scram-sha-256

Common Pitfalls

WAL bloat from inactive slots. A slot with no active consumer prevents WAL cleanup. Monitor slot activity. Set a retention limit (PostgreSQL 13+):

ALTER SYSTEM SET max_slot_wal_keep_size = '5GB';
SELECT pg_reload_conf();

Schema changes breaking replication. Notify your CDC consumer before schema changes. Use Debezium's auto-detection or a schema registry. Test in staging first.

Large transactions stalling the pipeline. Break bulk operations into smaller transactions. Monitor transaction duration.

Replication user lacking permissions. Verify with:

SELECT * FROM pg_roles WHERE rolname = 'cdc_user';

Confirm rolreplication = true and rolcanlogin = true.

Slot position never advancing. Consumer is connected but confirmed_flush_lsn stays static. Check consumer logs, verify network connectivity, look for blocking locks.

The Managed Alternative

This tutorial demonstrates the mechanics. The operational reality (WAL management, slot monitoring, schema versioning, consumer coordination, continuous tuning) is demanding in production.

SchemaBounce abstracts these complexities while preserving the benefits:

• Zero WAL management: replication slot lifecycle handled automatically
• Automatic schema detection and evolution via Kolumn IaC
• Python transforms at the replication layer
• Pre-built connections to Snowflake, BigQuery, Kafka, S3, and the rest of the connector reference
• Built-in lag tracking, health checks, and alerting
• Connect your database and start replicating in minutes

Instead of the configuration above, with SchemaBounce you:

1. Connect your PostgreSQL database
2. Select tables to replicate
3. Choose destination
4. Optionally add Python transforms
5. Start replicating

Learn more about SchemaBounce PostgreSQL CDC

The Bottom Line

PostgreSQL CDC is foundational infrastructure for modern data engineering. Whether you implement it natively (as detailed here) or use a managed platform, the principles are the same:

1. Understand the mechanism. WAL, replication slots, and publications are your tools.
2. Monitor relentlessly. WAL retention, slot lag, and consumer health are non-negotiable metrics.
3. Plan for scale. What works for 1 GB daily may not work for 100 GB.
4. Test in staging. Schema changes, failovers, and consumer failures should be rehearsed, not discovered in production.

Start with this tutorial to understand the mechanics. If the operational overhead is too much, explore SchemaBounce for a managed alternative. For a platform-by-platform comparison once you're ready to pick one, see Best CDC Tools in 2026.

Further Reading

• PostgreSQL Logical Replication Documentation
• Understanding PostgreSQL WAL
• Debezium PostgreSQL Connector
• What is Change Data Capture (CDC)?

Related reading

• What is Change Data Capture (CDC)?
• CDC vs ETL: What's the Difference and When to Use Each
• Best CDC Tools in 2026
• Database CDC sources
• Outbox pattern
• Direct pull sources