How to Upgrade PostgreSQL Major Versions With Almost No Downtime

A major version upgrade normally costs a maintenance window that grows with your data. Logical replication changes the shape of that cost. You build the new server while the old one keeps serving, let it catch up over hours or days, and then spend a few seconds pointing the application at it. If the first minute looks wrong, the old server is still running, still consistent, and still able to take traffic back.

PostgreSQL lists replication between different major versions as one of the intended uses of the feature, so this is not a trick. What makes it risky in practice is that logical replication copies row changes and nothing else. The parts it leaves behind are where upgrades go wrong, and they are most of what follows.

Prepare the old server

Logical decoding has to be switched on, and switching it on needs a restart.

# /etc/postgresql/<version>/main/postgresql.conf on the OLD server
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

max_replication_slots must cover the number of subscriptions you expect plus a reserve for table synchronisation, and max_wal_senders should be at least that number plus any physical replicas already connected. On the new server, max_logical_replication_workers needs to cover one apply worker per subscription plus the table sync workers, and max_worker_processes has to be large enough to hold them.

Create a role that can replicate, allow it in pg_hba.conf, then publish.

-- on the OLD server (the publisher)
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

A publication covers inserts, updates, deletes and truncates by default.

Find the tables that cannot replicate

A published table needs a replica identity before an update or a delete can be replicated. The default is the primary key. A table with no primary key and no explicit replica identity will let inserts through and then throw an error on the publisher the first time somebody updates a row. Find them before you start, not during the cutover.

-- on the OLD server: tables that will error on UPDATE or DELETE
SELECT c.oid::regclass AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND c.relreplident = 'd'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.oid AND i.indisprimary
  );

For each one, add a primary key, point the replica identity at a unique index, or fall back to REPLICA IDENTITY FULL. The full option uses the whole row as the key and the documentation is blunt about it being a fallback, because without a suitable index on the subscriber side every update turns into a scan.

Copy the schema by hand

Logical replication does not replicate DDL. The schema has to exist on the new server before the subscription starts, and you have to put it there yourself.

# roles and tablespaces are global objects and are NOT in a pg_dump of one database
pg_dumpall --globals-only --host=old.internal --username=postgres > globals.sql
psql --host=new.internal --username=postgres --dbname=postgres --file=globals.sql

# then the schema of the database itself, without any data
pg_dump --schema-only --host=old.internal --username=postgres --dbname=app > schema.sql
psql --host=new.internal --username=postgres --dbname=app --file=schema.sql

Freeze migrations for the duration. A column added on the old server after this point is not on the new one, and the apply worker will stop the moment a row arrives that mentions it.

Subscribe, then wait

-- on the NEW server (the subscriber)
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old.internal port=5432 dbname=app user=replicator'
  PUBLICATION upgrade_pub;

This cannot run inside a transaction block while it is creating the replication slot, and the role running it needs the privileges of pg_create_subscription plus CREATE on the database. The initial copy starts immediately. Watch it finish per table rather than guessing.

-- on the NEW server: every table must reach state 'r' (ready)
SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r';

The states run i for initialize, d while data is copying, f when the copy finished, s for synchronised and r for normal replication. On the old server, keep an eye on how far behind the slot is and on whether the WAL it is holding is still safe.

-- on the OLD server
SELECT slot_name, active, wal_status,
       pg_current_wal_lsn() - confirmed_flush_lsn AS bytes_behind
FROM pg_replication_slots
WHERE slot_type = 'logical';

A wal_status of lost means the slot can no longer be used and you start again. That is the one failure mode that can bite while you are not looking, because an inactive slot keeps WAL on the old server until the disk fills.

What does not come across

Sequence data is not replicated. The values inside serial and identity columns arrive as ordinary row data, but the sequence object on the new server still sits at its start value. Send traffic there and the first insert collides with a row that already exists.

Large objects are not replicated either, and the documentation offers no workaround beyond storing the data in normal tables. Only tables are supported as replication targets, so views, materialized views and foreign tables have to be recreated from the schema dump and, in the case of materialized views, refreshed.

The cutover

Stop writes to the old database. Then check the slot has caught up, fix the sequences, and move.

-- on the OLD server, AFTER writes have stopped: generate the setval statements
SELECT format('SELECT setval(%L, %s, true);',
              schemaname || '.' || sequencename, last_value)
FROM pg_sequences
WHERE last_value IS NOT NULL;

Run that output on the new server. last_value is the last value written to disk, and where a sequence cache is in use it can be ahead of the last value actually handed out, which leaves a gap rather than a duplicate. A gap is harmless. A duplicate is an outage.

Then run ANALYZE on the new database, drop the subscription so the slot on the old server is released, and repoint the application.

-- on the NEW server, once you are happy
DROP SUBSCRIPTION upgrade_sub;

Keep the old server running and untouched for a day. It is the only rollback you have, and it costs nothing to leave it switched on.

If you would rather have this rehearsed on a copy of your data before it is done for real, our migration services cover exactly this kind of cutover, and infrastructure management keeps the result patched afterwards.