Logical Replication in PostgreSQL: Selective, Cross-Version, Zero-Downtime

Logical vs Physical: Pick the Right Tool

Physical (streaming) replication copies the entire cluster, byte for byte, same version only. Logical replication works at the level of rows and tables: it decodes the WAL into logical changes (INSERT/UPDATE/DELETE) and replays them via SQL. That unlocks three things physical cannot do:

• Replicate some tables, not the whole database.
• Replicate across major versions (15 → 16), the foundation of zero-downtime upgrades.
• Replicate into a database that also accepts its own writes (consolidation, sharding).

1. On the Publisher

-- postgresql.conf
wal_level = logical

-- then create a publication
CREATE PUBLICATION sales_pub FOR TABLE orders, order_items;
-- or everything:  CREATE PUBLICATION all_pub FOR ALL TABLES;

2. On the Subscriber

The target tables must already exist with a matching schema (logical replication does not copy DDL):

CREATE SUBSCRIPTION sales_sub
  CONNECTION 'host=pub.internal dbname=app user=replicator password=secret'
  PUBLICATION sales_pub;

On creation the subscriber performs an initial copy of existing data, then streams ongoing changes. Watch progress with:

SELECT * FROM pg_stat_subscription;
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel;

srsubstate = 'r' means that table is fully synced and now replicating live.

Zero-Downtime Major Upgrade

The killer use case: upgrade 15 → 16 with seconds of downtime.

1. Build a new PostgreSQL 16 cluster.
2. Recreate the schema there (pg_dump --schema-only).
3. Set up logical replication 15 → 16; let the initial copy + catch-up finish.
4. When lag is ~0, stop writes, let the last changes drain, repoint the app to 16.

Limitations to Plan Around

• No DDL replication. Schema changes must be applied to both sides manually, in the right order.
• Sequences are not advanced on the subscriber — fix them before promoting it.
• Replica identity: UPDATE/DELETE need a primary key or REPLICA IDENTITY FULL on the source table.
• Each subscription holds a replication slot on the publisher — an abandoned subscriber will pin WAL forever.

The Slot That Eats Your Disk

That last point is the number-one logical replication incident: a subscriber goes away, its slot stops advancing, and the publisher retains WAL until pg_wal fills the disk and the primary halts. PG Monitoring tracks every replication slot's retained-WAL size and inactivity, and alerts on a stuck slot long before it threatens the primary.