Postgres Logical Replication: Publications, Subscriptions and Near-Zero-Downtime Upgrades

Physical (streaming) replication copies the entire database cluster byte for byte to an identical replica. Logical replication decodes the WAL into row changes — insert this row, update that one — and applies them on a separate Postgres server that can have a different major version, different indexes, or only some of the tables. (Postgres Read Replicas)

That flexibility makes it the standard tool for major-version upgrades with minimal downtime, consolidating databases, and feeding a reporting database.

The building blocks

• Publication (on the source): which tables' changes to publish.
• Subscription (on the target): connects to the source, copies the initial data, then applies the stream.
• Replication slot: created on the source for each subscription, retaining WAL until the subscriber confirms it. (Postgres Replication Slots)

Setup

Source (postgresql.conf, needs a restart):

wal_level = logical

Create a replication user and a publication:

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD '...';
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

CREATE PUBLICATION app_pub FOR ALL TABLES;
-- or: FOR TABLE orders, customers;
-- or: FOR TABLES IN SCHEMA public;

Allow the connection in pg_hba.conf. (Postgres Roles and Permissions)

Target: create the schema first — logical replication doesn't copy table definitions:

pg_dump --schema-only -h source -U postgres app | psql -h target -U postgres app

Then subscribe:

CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=source dbname=app user=replicator password=...'
  PUBLICATION app_pub;

The subscription copies existing rows table by table, then switches to streaming changes.

Replica identity: updates and deletes need a key

To apply an UPDATE or DELETE, the subscriber must find the row. By default it uses the primary key. Tables without one can't publish updates/deletes unless you set a replica identity:

ALTER TABLE events REPLICA IDENTITY USING INDEX events_unique_idx;
-- last resort (logs whole old rows, expensive):
ALTER TABLE events REPLICA IDENTITY FULL;

Find tables without primary keys before you start. (Primary Key vs Foreign Key)

What is NOT replicated

• DDL. ALTER TABLE on the source isn't sent. Apply schema changes to the subscriber first (adding columns), then the source — or pause changes during migration.
• Sequences. Sequence values don't move with the data in most current versions; after cutover you must set each sequence past the max ID. Check your version's documentation — this area is improving.
• Large objects, and some other non-table objects.
• TRUNCATE is replicated (it's included by default), which surprises people in the other direction.

Conflicts

The subscriber is a normal, writable database. If something writes conflicting rows there (a unique key that already exists), the apply worker errors and stops, retrying in a loop, while the slot on the source retains WAL. Keep the subscriber read-only for replicated tables, and monitor for apply errors in the subscriber's log and pg_stat_subscription_stats. Newer versions add better conflict detection and logging.

Monitoring

On the source:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots WHERE slot_type = 'logical';

On the subscriber:

SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

Major-version upgrade with a short cutover

1. Build the new-version server; restore the schema.
2. Publication on old, subscription on new; wait for the initial copy and for lag to reach near zero.
3. Verify: row counts, spot checks, run the app's read paths against the new server.
4. Cutover: stop writes to the old database (maintenance mode or stop the app), wait for lag to hit zero.
5. Advance every sequence on the new server (setval to max(id) + margin).
6. Point the app at the new server; start it.
7. After validation, drop the subscription (which normally drops its slot on the old server). Keep the old server temporarily, but plan rollback before allowing writes on the new one: the old server will not receive those new writes unless you explicitly configure replication back to it.

The initial copy happens before the outage, so cutover can be short. Its duration still depends on replication lag, validation, sequence updates and application startup; rehearse and measure it on a copy first. Switching back after new writes requires reconciling that data, not just changing the connection string. (Postgres Major Version Upgrade, Zero-Downtime Deploys)

The summary

• Logical replication streams row changes via publications and subscriptions, across versions.
• Copy the schema yourself; ensure replica identity; sequences and DDL need manual handling.
• Monitor slot lag and apply errors — and drop slots when you're done.

---

EasySpawn runs PostgreSQL on each server with daily backups, and Claude Code can plan and rehearse a logical-replication upgrade on a copy before you touch production. See how it works or join the waitlist.

Related: Change Data Capture in Postgres · The Postgres WAL Explained · Postgres Point-in-Time Recovery · Postgres Backup and Restore