PostgreSQL Logical Replication: Architecture & Setup

The Engine Behind Near-Zero-Downtime Cutover

If you want to move to PostgreSQL — or between major versions — with seconds of downtime instead of hours, logical replication is how. It’s worth understanding properly, because it’s a different tool from the physical replication most people meet first.

Logical vs physical replication

• Physical (streaming) ships byte-exact WAL to a standby that is a read-only clone of the entire cluster, same version. Perfect for HA hot-standbys.
• Logical streams row-level changes for the tables you choose, into a target that is writable and can even run a different major version.

That second combination — selective, writable target, cross-version — is exactly what a migration cutover needs.

Architecture & setup: publish / subscribe

-- PUBLISHER (source)
--   postgresql.conf:  wal_level = logical   (needs a restart)
CREATE PUBLICATION mig FOR ALL TABLES;   -- or FOR TABLE a, b, c

-- SUBSCRIBER (new database)
CREATE SUBSCRIPTION mig
  CONNECTION 'host=old-db dbname=app user=repl password=…'
  PUBLICATION mig;
-- initial data is copied, then changes stream continuously

The subscriber does an initial data copy, then applies changes as they happen. Watch the lag on pg_stat_subscription; when it’s near zero you’re ready to cut over.

The limitations you must plan around

• DDL is not replicated. Schema changes must be applied on both sides yourself. Freeze DDL during the migration window.
• Sequences are not advanced on the subscriber. Sync them at cutover (set each to the source’s current value) or new inserts collide.
• Updates/deletes need a replica identity — a primary key (or an explicit REPLICA IDENTITY) so the change can be matched on the target.

The cutover, in one paragraph

Set up the publication and subscription, let the subscriber catch up, and run both in parallel. At the window: stop writes to the source, let the last changes drain, sync sequences, point the application at PostgreSQL. Downtime is the few seconds between “stop old” and “start new” — not the length of a data load.