Zero-Downtime Postgres Major-Version Upgrades with Logical Replication

What you'll build / learn

You'll upgrade a live PostgreSQL 16 database to PostgreSQL 18 by streaming changes to a new server with logical replication, then cutting over with only a seconds-long write pause — reads never stop, and pg_upgrade's full outage never happens.

Prerequisites

Verified against PostgreSQL 18.6 and 16.15 (the current minor releases as of August 2026) on Ubuntu 24.04.

• Old server (10.0.0.10): PostgreSQL 16.x running your production database, called appdb here. This is the publisher.
• New server (10.0.0.20): Ubuntu 24.04 with sudo access, reachable from and to the old server on port 5432. This becomes the subscriber.
• Superuser (postgres) access on both.
• Every replicated table needs a primary key (or a replica identity) or UPDATE/DELETE won't replicate. Find offenders before you start:

SELECT n.nspname, c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (SELECT FROM pg_constraint
                  WHERE conrelid = c.oid AND contype = 'p');

Fix each hit with a real primary key, or ALTER TABLE t REPLICA IDENTITY FULL; as a last resort (it's slower — every update ships the whole old row).

Know the limits going in: logical replication does not copy schema/DDL, sequences, or large objects. We handle schema and sequences explicitly below; if you use large objects (lo_*), stop here — move them to bytea first.

1. Configure the old server as a publisher

Logical decoding requires wal_level = logical. On the old server, edit /etc/postgresql/16/main/postgresql.conf:

wal_level = logical

The defaults of max_wal_senders = 10 and max_replication_slots = 10 are plenty for one subscription. This change needs a restart — schedule it in a quiet window; it's the only restart the old server takes:

sudo systemctl restart postgresql@16-main

Create a replication role and grant it read access for the initial table copy:

CREATE ROLE repl_user WITH LOGIN REPLICATION PASSWORD 'a-strong-password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;

Allow the new server to connect in /etc/postgresql/16/main/pg_hba.conf. Note this is a normal database entry — logical replication connections match the database name, not the replication keyword (that's physical replication only):

host  appdb  repl_user  10.0.0.20/32  scram-sha-256

sudo systemctl reload postgresql@16-main

2. Install PostgreSQL 18 on the new server

Use the PGDG repository — Ubuntu 24.04's own packages don't ship 18:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt install -y postgresql-18

This is your chance to bake in the config tuning you've been putting off (shared_buffers, etc.) in /etc/postgresql/18/main/postgresql.conf — the new server takes restarts freely until cutover.

3. Copy the schema

Logical replication moves rows, not DDL, so dump the schema yourself. Run this on the new server so you're using 18's pg_dump, which is built to read older servers:

sudo -u postgres createdb appdb
sudo -u postgres pg_dump "host=10.0.0.10 dbname=appdb user=postgres" \
  --schema-only --no-publications | sudo -u postgres psql -d appdb

--no-publications keeps the publication you're about to create from being copied onto the subscriber. From this moment until cutover, freeze schema migrations — DDL applied to the old server won't replicate, and drift breaks the apply worker.

4. Create the publication

On the old server, as postgres (superuser is required for FOR ALL TABLES):

CREATE PUBLICATION pg18_upgrade FOR ALL TABLES;

5. Create the subscription

On the new server:

CREATE SUBSCRIPTION pg18_upgrade
  CONNECTION 'host=10.0.0.10 dbname=appdb user=repl_user password=a-strong-password'
  PUBLICATION pg18_upgrade;

This creates a replication slot named pg18_upgrade on the publisher, snapshots every table (copy_data defaults to true), then streams ongoing changes. The initial copy runs max_sync_workers_per_subscription (default 2) tables at a time; a multi-hundred-GB database can take hours, and that's fine — production keeps writing the whole time.

6. Watch it sync

On the new server, watch tables march to state r ("ready", meaning streaming normally; d means the initial copy is still running):

SELECT srsubstate, count(*) FROM pg_subscription_rel GROUP BY 1;

Once everything is r, check replication lag on the old server:

SELECT slot_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_name = 'pg18_upgrade';

Under steady traffic, lag_bytes should hover near zero. Let this run for at least a day and rehearse the next step before doing it for real.

7. Cut over

The only write-unavailability in the whole process is this block — seconds, not minutes.

Stop writes on the old server. Guard against stragglers by making it read-only:

ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

Wait for lag to hit zero — re-run the lag_bytes query on the old server until it reads 0.

Sync sequences. They don't replicate, so serial/identity columns on the new server would hand out already-used IDs. Copy every sequence's position (run on the new server):

sudo -u postgres bash -c "psql 'host=10.0.0.10 dbname=appdb user=postgres' -Atc \
  \"SELECT format('SELECT setval(%L, %s, true);',
                  schemaname || '.' || sequencename, last_value)
    FROM pg_sequences WHERE last_value IS NOT NULL\" | psql -d appdb"

Repoint your application at 10.0.0.20 (flip the connection string, DNS record, or pooler backend) and re-enable writes there.

Tear down replication on the new server — this also drops the slot on the publisher, so WAL stops accumulating:

DROP SUBSCRIPTION pg18_upgrade;

Keep the old server running read-only for a few days as a rollback target.

Verify it works

On the new server, before dropping the subscription, confirm all tables are streaming:

SELECT srsubstate, count(*) FROM pg_subscription_rel GROUP BY 1;

 srsubstate | count
------------+-------
 r          |    38

Any state other than r means sync isn't done. After cutover, prove writes land on 18:

SELECT version();
INSERT INTO events (payload) VALUES ('cutover-check') RETURNING id;

PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+1) on x86_64-pc-linux-gnu ...
  id
------
 91245

A returned id above the old server's max proves the sequence sync worked. Spot-check row counts on your largest tables against the old server; they should match exactly once writes are stopped there.

Troubleshooting

ERROR: logical decoding requires wal_level >= logical — the subscription can't create its slot because step 1's restart didn't happen or didn't stick. Check SHOW wal_level; on the publisher; it must say logical, and it only changes at server start.

ERROR: cannot update table "foo" because it does not have a replica identity and publishes updates — raised on the publisher, failing your application's own UPDATEs the moment the publication exists. A table without a primary key slipped through the prerequisite check. Fix immediately: add a primary key, or ALTER TABLE foo REPLICA IDENTITY FULL;.

ERROR: duplicate key value violates unique constraint "foo_pkey" in the subscriber's log — the target table already had rows when the initial copy started (usually from a test import). The sync worker crashes, respawns, and retries in a loop while lag climbs. Truncate that table on the subscriber and the next automatic retry completes the copy cleanly.

FATAL: no pg_hba.conf entry for host "10.0.0.20", user "repl_user", database "appdb" — a classic here because people add a replication-keyword line, which logical replication ignores. Your pg_hba.conf entry must name the actual database (or all), then sudo systemctl reload postgresql@16-main.

Next steps

• Put PgBouncer in front of the database and use PAUSE/RESUME around cutover — clients block for the pause instead of erroring, making the switch invisible.
• Set up reverse replication (publication on 18, subscription on 16, with origin = none to avoid loops) before cutover for an instant rollback path.
• Read the official logical replication chapter — row filters and column lists let you trim or reshape what you carry to the new server.
• Rehearse the whole thing against a restored backup first; the restrictions page is the checklist of what else (e.g., materialized views) needs manual handling.