Postgres Major Version Upgrade with Logical Replication

A Postgres major version upgrade with logical replication streams every row from the old cluster into a new one running the target version, then cuts writes over in seconds. The other two routes, pg_upgrade in place and a pg_dumpall restore, both need a full outage. As of November 2026 the choice turns on downtime tolerance, database size, and whether you can run two clusters at once.

The timing is not academic. Postgres 14 receives its final release on November 12, 2026, so a lot of clusters have to move now.

How logical replication upgrades a Postgres cluster

Logical replication decodes committed row changes from the write-ahead log and applies them to a second cluster by table name, which is why the two clusters can run different major versions. The upgrading docs state the payoff plainly. “Such a switch-over results in only several seconds of downtime for an upgrade.”

The mechanism is three objects. A publication on the old cluster names the tables, a subscription on the new cluster connects to it, and a replication slot on the old cluster holds WAL until the subscriber confirms it. Per the architecture page, the subscriber first copies each table through its own synchronization worker, then applies the stream from the point the copy began.

What does not travel is the part that bites, and the restrictions page is direct about the two that matter most. “The database schema and DDL commands are not replicated.” And one line later, “Sequence data is not replicated.”

Step by step with pg_dump, a publication, and a subscription

Eight steps take a database called shop from an old cluster on host old to a new one on host new, and only step six touches production. The commands are adapted from the quick setup and from Dimitri Fontaine’s 2026 walkthrough of a 16 to 18 move.

Step 1. Turn on logical decoding on the old cluster

Set wal_level to logical and leave room for slots and WAL senders, then restart, because the WAL settings say the parameter can only be set at server start.

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
-- restart the old cluster, then confirm

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
-- restart the old cluster, then confirm

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
-- restart the old cluster, then confirm

The restart is the one planned interruption before cutover, so schedule it early.

Step 2. Copy roles and schema to the new cluster

Run the new cluster’s own pg_dump, which the upgrading docs recommend, and restore roles first so ownership survives.

pg_dumpall -h old --roles-only | psql -X -q -h new -d postgres
psql -X -h new -d postgres -c 'CREATE DATABASE shop OWNER shop'
pg_dump -h old -d shop --schema-only | psql -X -q -v ON_ERROR_STOP=1 -h new -d

pg_dumpall -h old --roles-only | psql -X -q -h new -d postgres
psql -X -h new -d postgres -c 'CREATE DATABASE shop OWNER shop'
pg_dump -h old -d shop --schema-only | psql -X -q -v ON_ERROR_STOP=1 -h new -d

pg_dumpall -h old --roles-only | psql -X -q -h new -d postgres
psql -X -h new -d postgres -c 'CREATE DATABASE shop OWNER shop'
pg_dump -h old -d shop --schema-only | psql -X -q -v ON_ERROR_STOP=1 -h new -d

The schema-only dump carries publications and CREATE EXTENSION statements, so each extension installs from the new cluster’s own packages.

Step 3. Give every table a replica identity

A published table needs a primary key or another replica identity before it can take UPDATE or DELETE, per the publication docs. This query lists the tables that will fail.

SELECT n.nspname, c.relname
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)

SELECT n.nspname, c.relname
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)

SELECT n.nspname, c.relname
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)

Fix each one with a key or a unique index, because the same page warns that REPLICA IDENTITY FULL makes the subscriber search very inefficient.

ALTER TABLE audit_log REPLICA IDENTITY USING INDEX audit_log_event_id_key;
ALTER TABLE legacy_notes REPLICA IDENTITY FULL

ALTER TABLE audit_log REPLICA IDENTITY USING INDEX audit_log_event_id_key;
ALTER TABLE legacy_notes REPLICA IDENTITY FULL

ALTER TABLE audit_log REPLICA IDENTITY USING INDEX audit_log_event_id_key;
ALTER TABLE legacy_notes REPLICA IDENTITY FULL

Step 4. Create the publication on the old cluster

One publication covers the database. The CREATE PUBLICATION reference notes that FOR ALL TABLES needs a superuser and that creating a publication starts nothing by itself.

CREATE TABLE cutover_markers (id text PRIMARY KEY);
CREATE PUBLICATION shop_pub FOR ALL

CREATE TABLE cutover_markers (id text PRIMARY KEY);
CREATE PUBLICATION shop_pub FOR ALL

CREATE TABLE cutover_markers (id text PRIMARY KEY);
CREATE PUBLICATION shop_pub FOR ALL

The marker table is for step six. On a very large database, Knock added tables a few at a time, because syncing everything at once held old transaction IDs open and blocked autovacuum.

Step 5. Create the subscription and watch the initial copy

The subscription creates the slot and starts copying.

CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=old dbname=shop user=repuser password=secret'
  PUBLICATION shop_pub;

SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel
ORDER BY 2, 1

CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=old dbname=shop user=repuser password=secret'
  PUBLICATION shop_pub;

SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel
ORDER BY 2, 1

CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=old dbname=shop user=repuser password=secret'
  PUBLICATION shop_pub;

SELECT srrelid::regclass AS table_name, srsubstate
FROM pg_subscription_rel
ORDER BY 2, 1

Each table moves through the states the catalog docs list, i for initialize, d for copying, f for finished copy, s for synchronized, and r for ready. Every row at r means the stream is live.

Step 6. Freeze writes on the old cluster and confirm catch-up

Make new transactions read-only by default, terminate the application’s sessions, and write one marker row from your own session, which overrides the default. The client settings define default_transaction_read_only and the admin functions define pg_terminate_backend.

ALTER DATABASE shop SET default_transaction_read_only = on;
SELECT count(pg_terminate_backend(pid))
FROM pg_stat_activity
WHERE datname = 'shop' AND usename = 'shop';
SET default_transaction_read_only = off;
INSERT INTO cutover_markers (id) VALUES ('old-to-new')

ALTER DATABASE shop SET default_transaction_read_only = on;
SELECT count(pg_terminate_backend(pid))
FROM pg_stat_activity
WHERE datname = 'shop' AND usename = 'shop';
SET default_transaction_read_only = off;
INSERT INTO cutover_markers (id) VALUES ('old-to-new')

ALTER DATABASE shop SET default_transaction_read_only = on;
SELECT count(pg_terminate_backend(pid))
FROM pg_stat_activity
WHERE datname = 'shop' AND usename = 'shop';
SET default_transaction_read_only = off;
INSERT INTO cutover_markers (id) VALUES ('old-to-new')

On the new cluster, poll for the marker.

SELECT EXISTS (SELECT 1 FROM cutover_markers WHERE id = 'old-to-new')

SELECT EXISTS (SELECT 1 FROM cutover_markers WHERE id = 'old-to-new')

SELECT EXISTS (SELECT 1 FROM cutover_markers WHERE id = 'old-to-new')

The marker beats an LSN comparison, as Fontaine found, because the freeze is itself a commit the subscriber never sees.

Step 7. Copy the sequences

Generate one setval per sequence from the frozen old cluster and run the output on the new one. The pg_sequences view exposes last_value, and the three-argument setval sets the is_called flag so the next nextval advances first.

psql -X -At -h old -d shop -c "
SELECT format('SELECT setval(%L, %s, true);',
              quote_ident(schemaname) || '.' || quote_ident(sequencename), last_value)
FROM pg_sequences WHERE last_value IS NOT NULL;" | psql -X -q -h new -d

psql -X -At -h old -d shop -c "
SELECT format('SELECT setval(%L, %s, true);',
              quote_ident(schemaname) || '.' || quote_ident(sequencename), last_value)
FROM pg_sequences WHERE last_value IS NOT NULL;" | psql -X -q -h new -d

psql -X -At -h old -d shop -c "
SELECT format('SELECT setval(%L, %s, true);',
              quote_ident(schemaname) || '.' || quote_ident(sequencename), last_value)
FROM pg_sequences WHERE last_value IS NOT NULL;" | psql -X -q -h new -d

Teams that cannot freeze completely add a buffer, such as the 1,000 values pgEdge uses.

Step 8. Switch the application, then drop the subscription

Point the application at the new cluster. Once traffic is healthy, drop the subscription, which the DROP SUBSCRIPTION reference says also drops the slot on the old cluster, then build planner statistics.

psql -X -h new -d shop -c 'DROP SUBSCRIPTION shop_sub'
vacuumdb -h new --all --analyze-in-stages

psql -X -h new -d shop -c 'DROP SUBSCRIPTION shop_sub'
vacuumdb -h new --all --analyze-in-stages

psql -X -h new -d shop -c 'DROP SUBSCRIPTION shop_sub'
vacuumdb -h new --all --analyze-in-stages

Leave the old cluster running read-only as the rollback.

How to verify the upgrade worked

Three checks prove the move before you drop anything. Every table ready, no errors counted, and no WAL backlog.

-- new cluster, every table must be in state r
SELECT count(*) FILTER (WHERE srsubstate <> 'r') AS not_ready FROM pg_subscription_rel;

-- new cluster, both counters must be zero
SELECT apply_error_count, sync_error_count FROM pg_stat_subscription_stats;

-- old cluster, the slot must be active with no WAL backlog
SELECT slot_name, active, wal_status,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS bytes_behind
FROM

-- new cluster, every table must be in state r
SELECT count(*) FILTER (WHERE srsubstate <> 'r') AS not_ready FROM pg_subscription_rel;

-- new cluster, both counters must be zero
SELECT apply_error_count, sync_error_count FROM pg_stat_subscription_stats;

-- old cluster, the slot must be active with no WAL backlog
SELECT slot_name, active, wal_status,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS bytes_behind
FROM

-- new cluster, every table must be in state r
SELECT count(*) FILTER (WHERE srsubstate <> 'r') AS not_ready FROM pg_subscription_rel;

-- new cluster, both counters must be zero
SELECT apply_error_count, sync_error_count FROM pg_stat_subscription_stats;

-- old cluster, the slot must be active with no WAL backlog
SELECT slot_name, active, wal_status,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS bytes_behind
FROM

The confirmed_flush_lsn and wal_status columns are documented in the replication slots view, and the error counters in the statistics reference.

What to do when it breaks

Five failures account for most stalled upgrades, and each has an exact string to search for.

logical decoding requires wal_level >= logical

Step one did not take, usually because the restart never happened, and the message comes from the logical decoding source. On managed services the knob has another name, rds.logical_replication on Amazon RDS and cloudsql.logical_decoding on Cloud SQL.

cannot delete from table because it does not have a replica identity

A table without a key is in a publication that publishes deletes, so the old cluster now refuses the delete. The publication docs describe the rule, and step three is the fix, so run that check before the application errors.

logical replication target relation is missing replicated column

Someone ran ALTER TABLE ADD COLUMN on the old cluster only, and the apply worker stopped. The restrictions page advises applying additive schema changes to the subscriber first. Apply the same DDL on the new cluster, and for new tables run ALTER SUBSCRIPTION shop_sub REFRESH PUBLICATION.

The old cluster’s disk fills with WAL

A slot whose subscriber is gone keeps every segment, because max_slot_wal_keep_size defaults to unlimited. Watch safe_wal_size in pg_replication_slots, and drop an orphaned slot with pg_drop_replication_slot. Azure’s flexible server goes read-only at 95 percent storage for exactly this reason.

duplicate key value violates unique constraint after cutover

Step seven was skipped or ran before the freeze. The sequences still sit at their start values, so the first insert collides with a replicated row. Rerun the setval script from the old cluster, which still holds the right numbers.

Other ways to upgrade and what they cost you

Logical replication wins on downtime and loses on effort, and the table shows where each other path is the better call.

Fontaine measured link mode at about 1.6 seconds on a five-million-row table, the same as on a near-empty one. What pg_upgrade cannot do is move hosts, change operating systems without a collation rebuild, or let you test the new version under production load first.

Managed services reshape the choice, because most run pg_upgrade for you and gate logical replication behind their own flags.

Next steps

The zero-downtime migration walkthrough applies the same cutover to a host move, and the logical replication guide goes deeper on slots and publications. Replication lag explains the catch-up you wait for in step six, and the WAL archiving guide covers the segments a slot retains.

Frequently asked questions

What is the fastest way to upgrade a Postgres major version?

For the shortest outage, logical replication. The old cluster keeps serving writes while the new one catches up, and the Postgres docs put the switch at several seconds. For the least work, pg_upgrade in link or clone mode finishes in minutes on one host, but every client is disconnected for the whole run.

Can I skip major versions when upgrading Postgres?

Yes. The PostgreSQL versioning policy says you can upgrade from one major version to another without passing through the versions in between, as long as you read every intervening release note. Both pg_upgrade and logical replication support the jump, and published upgrades have gone from 11 to 15 and from 12 to 17 in one move.

How does Postgres logical replication work across major versions?

Postgres logical replication works across major versions because it ships row changes decoded from the write-ahead log, not the binary data files. A publication on the old cluster and a subscription on the new one connect through a replication slot, and the subscriber applies inserts, updates, deletes, and truncates by table name. Postgres 10 is the oldest publisher.

What is the difference between pg_upgrade and pg_dump for an upgrade?

pg_dump and pg_dumpall rewrite every object as SQL and reload it, which works anywhere but takes hours or days on a large database and needs the whole outage. pg_upgrade builds new system catalogs and reuses the existing data files, so it runs in minutes, but both clusters must sit on the same host and nobody can be connected.

Why are my sequences wrong after a logical replication cutover?

Because sequence values are not replicated. The rows in serial and identity columns arrive, but each sequence on the new cluster still sits at its start value, so the first insert after cutover raises a duplicate key error. Read last_value from pg_sequences on the old cluster after the write freeze and apply it on the new one with setval.

How do I know the subscriber has caught up before I cut over?

Freeze writes on the old cluster, insert one marker row there from a session that overrides the read-only default, and wait for it to appear on the new cluster. Comparing LSNs alone misleads, because latest_end_lsn in pg_stat_subscription updates when a message is received rather than applied, and the freeze itself is a commit the subscriber never sees.

Do I need to uninstall the old Postgres version after upgrading?

Not right away. After a logical replication upgrade the old cluster is a complete, read-only copy of the data at cutover, which makes it the cheapest rollback you will ever have. Keep it until the new cluster has run through a full business cycle, then drop the leftover publication, stop the service, and remove the old packages and data directory.

Do extensions upgrade automatically during a major version upgrade?

Not on most paths. pg_upgrade keeps the old extension version and writes a script that runs ALTER EXTENSION UPDATE later, and Amazon RDS says most extensions are not upgraded during an engine upgrade. On the logical replication path the schema dump runs CREATE EXTENSION on the new cluster, which installs the version that ships with the new major, so check pg_available_extension_versions first.