PostgreSQL Logical Replication + CDC

What is logical replication and why it matters

Logical replication solves a specific, recurrent need: keep a downstream database or system synchronized with a PostgreSQL source without coupling to physical replication or the cost of polling. Traditional physical streaming replication offers high availability and read scaling, but it requires binary compatibility (same major version, same architecture), replicates every byte (no selectivity), and creates true replicas (not suitable for ETL or transformation). Polling (periodically asking 'what changed since I last looked?') is simpler to build but introduces lag, inefficiency, and missed changes on failure. Logical replication strikes a middle ground: it reads the WAL (the same authoritative change log physical replication uses) and decodes it into logical row-level events (insert/update/delete), allowing a consumer to subscribe to specific tables, transform them, and apply them to a different target—without the overhead of triggers or polling.

The defining use cases are CDC (feeding downstream systems like Kafka, search indexes, or data warehouses with database changes), cross-version migration (upgrading from PostgreSQL 12 to 16 by replicating to the new version in parallel), selective replication (replicating only critical tables to a read-only replica or analytics database), and multi-region synchronization (keeping data consistent across geographically distributed PostgreSQL instances without the latency of distributed transactions). Logical replication is not a replacement for physical replication for high-availability standby—it is higher-latency, requires more CPU on the publisher, and is not suitable for zero-downtime failover—but it is the right tool for database-to-pipeline and database-to-database synchronization when the target is different in some way (version, schema, location, or type).

Architecture: publications, subscriptions, replication slots, and logical decoding

The logical replication pipeline has four moving parts. Publications define what to replicate: a publication on the source database specifies tables and (optionally) row filters and column lists. Think of it as 'publish everything from the users table where status != ''deleted''' or 'publish the id and name columns, not the password hash'. A publication is connected to the WAL—any INSERT, UPDATE, or DELETE that matches the publication's rules is eligible to be streamed.

Replication slots are the reliability mechanism. A slot tracks a consumer's position in the WAL, ensuring the database retains WAL from that position forward. If a subscriber disconnects, it reconnects to the same slot and resumes from where it left off—no missed changes. The tradeoff is the slot-lag hazard: a slow or stopped consumer prevents WAL from being discarded, causing the WAL to accumulate and eventually fill the database's disk. Logical replication adds a replication slot for each subscription, and monitoring slot lag is a critical operational concern.

Logical decoding is the engine that reads the WAL and transforms it. The WAL contains physical records (byte-level changes to disk pages), but consumers care about logical changes (rows inserted, updated, deleted). Logical decoding interprets the WAL into logical events: it reconstructs which row changed, what the before-image and after-image were (if relevant), which table, which columns, and the transaction boundary. This decode happens on the source, and the events are streamed to subscribers over network connections.

Subscriptions are the consumer side. A subscription on a target database (often the same PostgreSQL version, sometimes different, or a Debezium connector pulling into Kafka) connects to a publication on a source and applies the decoded changes. The subscriber can be configured to apply changes immediately, or to skip initial snapshot and stream only incremental changes. Multiple subscribers can pull from the same publication, and a subscription can have filters or column lists to narrow the schema further.

Setup: configuring WAL, publications, and subscriptions

To enable logical replication, first set wal_level = logical on the source database. This is the primary one-time configuration change: it increases WAL verbosity to include enough information for logical decoding (physical replication uses wal_level = replica, which logs less). Restart the database to apply it.

On the source, create a publication specifying tables and filters:

CREATE PUBLICATION my_pub FOR TABLE users, orders WHERE (status != 'archived');

This publication will stream inserts, updates, and deletes on the users and orders tables (except rows where status is 'archived'). Logical replication is selective at the table level; row filters are a PostgreSQL 15+ feature.

On the target (subscriber), create a subscription pointing to the publication on the source:

CREATE SUBSCRIPTION my_sub CONNECTION 'host=source dbname=mydb user=repl password=...' PUBLICATION my_pub;

The subscription immediately attempts to connect to the source and begin replicating. If it is the first subscription for this publication, the source will snapshot the tables (copy the current state) to the subscriber, then stream incremental changes. If snapshots are not desired (the subscriber already has a copy), you can disable snapshots with copy_data = false.

Both source and subscriber need a replication-capable database user with REPLICATION attribute and sufficient SELECT/INSERT/UPDATE/DELETE permissions on the affected tables. Network access must be configured (typically a dedicated replication user in pg_hba.conf).

How logical replication differs from physical replication

Physical streaming replication (using wal_level = replica) ships the entire WAL to a standby, which replays it byte-for-byte, creating a binary-identical copy. Logical replication decodes the WAL into logical events and allows the subscriber to filter, transform, or apply changes selectively. The differences matter:

Physical replication is the default for failover; logical replication is the building block for CDC and multi-target synchronization. Many setups use both: physical replication for an HA replica, and logical replication for CDC pipelines feeding other systems.

Replication slots and the slot-lag hazard

A replication slot is a bookmark in the WAL. When a subscriber connects and begins consuming changes, a slot is created with a restart_lsn (the WAL position from which the subscriber can resume). The database never discards WAL up to that position, ensuring no changes are lost. If the subscriber disconnects and reconnects within the retention window, it resumes from its last confirmed position. If it disconnects for longer than the retention window (or the slot is left unused), the WAL wraps around, but the slot still prevents discard—and WAL starts accumulating.

This is the slot-lag hazard: a subscriber that is slow, down for maintenance, or crashed will cause the source database to retain WAL indefinitely. On a busy database, WAL can grow rapidly (tens of GB per hour), and an unmanaged slot can fill the source's disk in hours, bringing the database to a halt. The solution is monitoring: query pg_replication_slots to check slot lag, alerting if any slot is far behind, and either fixing the consumer (restarting the subscriber, scaling Debezium, etc.) or dropping the slot if it is truly abandoned.

SELECT slot_name, restart_lsn, restart_lsn_age FROM pg_replication_slots;

The restart_lsn_age column shows how old the WAL being retained is. If it grows over time, the subscriber is falling behind. Set up monitoring (Prometheus, New Relic, DataDog) to watch slot lag and alert on thresholds (e.g., >10 GB).

Change data capture (CDC) with Debezium

Logical replication is the foundation for CDC, but it requires an application to consume the subscription and apply changes. Debezium is the popular open-source CDC platform that automates this: it uses logical decoding to capture changes and streams them to Kafka, where downstream consumers (analytics, search indexes, notification systems) pull changes and act on them.

The Debezium PostgreSQL connector connects to a publication on a source database, snapshots the tables (storing the current state in a snapshot topic), then streams incremental changes. Each row change becomes a Kafka message with the before-image, after-image, source metadata (database, table, transaction ID), and operation type (insert, update, delete). Downstream consumers subscribe to topics and react: updating a search index when a product changes, triggering notifications, feeding a data warehouse, or synchronizing a cache.

Debezium abstracts the details of logical replication (slot management, WAL retention, offset tracking) and handles schema evolution (propagating DDL changes downstream, versioning event schemas). For production CDC pipelines, Debezium is the standard choice—it is battle-tested, actively maintained, and integrates with Kafka, Confluent Cloud, and other platforms.

Practical use cases: migration, disaster recovery, and ETL

Cross-version database migration is a classic use case. To migrate from PostgreSQL 12 to 16 without downtime, set up logical replication from the old instance to the new one in parallel. The new instance receives a snapshot of all data, then continues receiving incremental changes as they occur on the old instance. Once the new instance is caught up and validated, cut over: redirect application traffic to the new instance. Logical replication handles the version difference seamlessly (schema differences are rare, but logical replication can tolerate minor structural changes because it operates at the row level, not the WAL level).

Selective disaster recovery: replicate only critical tables (e.g., users, orders, payments) to a backup database in another region, using publications to filter out less critical tables (logs, temporary data, etc.). This reduces RPO (recovery point objective) for critical data and RTO (recovery time objective) by keeping a warm standby without the overhead of replicating the entire database.

Analytics and ETL: stream operational data into an analytics database or data warehouse in real time. A subscription on an analytics PostgreSQL database (or Debezium pushing to Snowflake, BigQuery, etc.) keeps the warehouse synchronized with production data, eliminating stale batch jobs and enabling real-time dashboards and reports.

Event-driven microservices: use Debezium to stream database changes to a Kafka topic, where services subscribe and react. A new user signup triggers a welcome email service, a payment completion triggers a notification, an order change triggers inventory updates—all driven by database events, decoupling services from a single database and enabling eventual consistency across a microservices architecture.

Configuration best practices and avoiding pitfalls

Always enable wal_level = logical at initialization: changing it later requires a database restart and WAL truncation.

Use publication row and column filters sparingly: they reduce WAL overhead on the subscriber but not on the source (the source still decodes the full row). If you need to replicate only 10% of rows, consider a separate publication for that subset or filter at the subscriber level.

Monitor replication lag closely: use pg_stat_replication or pg_replication_slots to track write_lsn, apply_lsn, and slot lag. Set up alerts for lag > 1 minute or slot size > 5 GB.

Plan for schema changes: adding a non-nullable column without a default will break logical replication on the subscriber. Add columns with defaults, or drop and re-add the column with a default. For major schema changes, suspend the subscription, update schema on both sides, then resume.

Subscription slots are single-threaded: replication from a single subscription applies changes serially. For very large tables, parallelize by creating multiple subscriptions with different row filters (e.g., id ranges).

Snapshot overhead: the initial snapshot copies all data from the source. For large tables (100s of GB), the snapshot can take hours and consume significant bandwidth. Run snapshots during low-traffic windows or skip snapshots if the subscriber already has a copy of the data.