PostgreSQL Logical Replication & CDC: Publications & Subscriptions

PostgreSQL Logical Replication & CDC

Logical replication streams individual row-level changes (inserts, updates, deletes) out of PostgreSQL as a logical stream of events, rather than copying raw disk blocks. This makes it the foundation for change data capture (CDC) — feeding your database's changes into other systems in near real time: data warehouses, search indexes, caches, event streams, and other databases — as well as selective replication and zero-downtime major-version upgrades.

It contrasts with physical (streaming) replication, which copies the write-ahead log byte-for-byte to produce an identical standby. Physical replication is for high availability and read scaling; logical replication is for moving specific data changes somewhere useful, possibly transformed, across versions, or into non-Postgres systems. Understanding which one to use — and how logical replication's publications/subscriptions work — is key to building modern data pipelines on Postgres.

TL;DR

Logical replication streams row-level changes (decoded from the WAL) — you can replicate specific tables, to different Postgres versions, or into other systems.
Physical replication copies raw WAL blocks to make a byte-identical standby — for HA and read replicas, not selective/cross-version.
Native logical replication uses publications (what to publish, on the source) and subscriptions (what to consume, on the target).
It's the basis of CDC: tools like Debezium tap the logical stream to feed Kafka, warehouses, search, and caches in near real time.
Key use cases: zero-downtime major upgrades, selective/partial replication, consolidating databases, and streaming changes to non-Postgres systems.
Caveats: it needs wal_level = logical, replicated tables need a replica identity (usually a primary key), and DDL is not replicated automatically.

Quick Example

Native logical replication is a publication on the source and a subscription on the target:

Only the named tables replicate, the target can run a different Postgres version, and the target is a live, writable database — none of which physical replication allows.

Core Concepts

Logical vs Physical Replication

Physical replication answers "I need an identical hot standby"; logical replication answers "I need these changes somewhere else." They're complementary, and many systems use both.

Publications and Subscriptions

Native logical replication (built into Postgres) has two halves:

A publication on the source declares what to replicate — specific tables, all tables, or filtered rows/columns (in recent versions). CREATE PUBLICATION.
A subscription on the target connects to a publication and consumes the stream, applying changes to matching tables. CREATE SUBSCRIPTION.

The target's tables must exist with a compatible schema (logical replication copies data changes, not schema), and each replicated table needs a replica identity — normally its primary key — so updates and deletes can be matched to the right target row. Tables with no primary key need REPLICA IDENTITY FULL or won't replicate updates/deletes.

Change Data Capture (CDC)

The broader, more powerful use is CDC: tapping Postgres's logical decoding stream to feed other systems. Instead of a Postgres subscription, a CDC tool (Debezium is the most common) connects as a logical replication client and turns the change stream into events on Kafka or another bus. From there, changes flow to search indexes, data warehouses, caches, analytics, and microservices — in near real time, without dual-writes or polling. CDC is how "keep the search index / cache / warehouse in sync with the database" is solved robustly.

Use Cases

Zero-downtime major upgrades. Because logical replication crosses versions, you can replicate from an old-version primary to a new-version database, let it catch up, then cut over — avoiding the downtime of an in-place pg_upgrade. See PostgreSQL Upgrades.
Selective / partial replication. Replicate only certain tables (or rows/columns) to another database — for a reporting DB, a regional copy, or data sharing.
Streaming to other systems (CDC). Keep search, warehouse, cache, and downstream services in sync with the database in near real time.
Database consolidation / migration. Merge multiple databases into one, or migrate data between environments with minimal downtime.

Best Practices

Enable Prerequisites Deliberately

Logical replication requires wal_level = logical (a restart to change), sufficient max_replication_slots and max_wal_senders, and a role with replication privileges. Set these up before you build on it, and understand that wal_level = logical slightly increases WAL volume.

Ensure Every Replicated Table Has a Primary Key

Updates and deletes need a replica identity to locate the target row — normally the primary key. Tables without one either need REPLICA IDENTITY FULL (heavier) or won't replicate modifications. Give replicated tables primary keys.

Handle DDL Changes Explicitly

Logical replication does not replicate schema changes (DDL). If you ALTER TABLE on the source, you must apply the compatible change on the target too, in the right order, or replication breaks. Coordinate schema migrations across both sides. See Database Migrations.

Monitor Replication Slots and Lag

A logical replication slot on the source retains WAL until the consumer reads it. If a subscriber falls behind or disconnects, WAL accumulates and can fill the disk. Monitor slot lag and disk usage, and drop orphaned slots — an abandoned slot is a classic cause of a source running out of space.

Use a Purpose-Built CDC Tool for Integration

For feeding non-Postgres systems, use a mature CDC platform (Debezium and similar) rather than hand-rolling a logical decoding client. They handle schema, offsets, restarts, and format conversion, and integrate with Kafka and stream processors.

Common Mistakes

Expecting DDL to Replicate

No Primary Key on a Replicated Table

Without a replica identity, updates and deletes can't be applied to the target. Add a primary key, or set REPLICA IDENTITY FULL (which logs the whole old row and is heavier). Don't leave replicated tables identity-less.

Ignoring an Abandoned Replication Slot

A subscriber that stops consuming leaves its slot holding WAL indefinitely, which can fill the source's disk and take the primary down. Monitor slots and remove ones no longer in use.

FAQ

What's the difference between logical and physical replication?

Physical (streaming) replication copies raw WAL blocks to produce a byte-identical, read-only standby of the whole cluster — for high availability and read replicas, and only within the same major version. Logical replication decodes the WAL into row-level change events and replicates chosen tables to an independent, writable target that can run a different version or even be a non-Postgres system. Physical is for identical standbys; logical is for moving specific changes somewhere useful.

What is change data capture (CDC)?

CDC is capturing a database's row-level changes as a stream and feeding them to other systems in near real time. In Postgres it works by tapping the logical decoding stream — commonly with Debezium — and emitting change events (usually onto Kafka), which then flow to search indexes, warehouses, caches, and services. It keeps downstream systems in sync with the database without dual-writes or polling.

How do publications and subscriptions work?

They're the two halves of native logical replication. On the source, a publication declares which tables (or filtered rows/columns) to replicate. On the target, a subscription connects to that publication and applies the incoming changes to matching tables. The target's schema must already exist and be compatible, and replicated tables need a replica identity (usually the primary key) so updates and deletes find the right row.

Can I use logical replication to upgrade Postgres with no downtime?

Yes — it's a primary use case. Because logical replication works across major versions, you replicate from the old-version primary to a new-version database, let it fully catch up, then switch the application over to the new database. This avoids the downtime of an in-place pg_upgrade. It takes more setup and care (schema, sequences, coordination) but achieves near-zero-downtime major upgrades. See PostgreSQL Upgrades.

What are the main gotchas?

DDL isn't replicated — schema changes must be applied on both sides in a compatible order, or replication breaks. Replicated tables need a primary key (or REPLICA IDENTITY FULL) for updates/deletes to apply. It requires wal_level = logical and enough replication slots/WAL senders. And an abandoned replication slot retains WAL and can fill the source's disk — monitor slots and lag.