Backend
Zero-Downtime PostgreSQL Major Upgrades with Logical Replication
July 20269 min read
Photo by Diego Delso via Wikimedia Commons (CC BY-SA 4.0)
pg_upgrade requires stopping the old server while it relinks the data directory, which means a maintenance window and an awkward rollback path. Logical replication runs the new-version server alongside the old one and keeps it continuously in sync, so the only downtime is the few seconds needed to switch the application's connection string. It is the right choice when a database cannot go offline for the duration of pg_upgrade.
No. The numbers stored in serial and identity columns are replicated as ordinary row data, but the sequence objects that generate new values are not. On the subscriber every sequence still reports its start value. You must run setval on the new server during cutover, ideally with a buffer added, or your first insert will fail with duplicate key violations.
pg_createsubscriber, introduced in PostgreSQL 17, converts an existing physical streaming standby into a logical subscriber without re-copying the data. It records an LSN and rewinds the apply position instead of running a full initial COPY. PostgreSQL 18 added an --all flag to set up subscriptions for every database in one command, turning hours of initial sync into seconds.
Only the cutover moment has real downtime, and it is typically sub-second to a few seconds. You stop writes on the old primary, confirm replication lag has reached zero, promote the sequences, and point the application at the new server. Everything before that — schema copy, initial data copy, catch-up streaming — happens while the old database stays fully online.
No. Large objects accessed through the lo API and pg_largeobject are never replicated by logical replication. If your database uses them you must copy them with a separate step, or migrate the data into normal bytea columns beforehand. This is one of the gaps that catches teams who assume logical replication produces a full clone.
Photo by Diego Delso via Wikimedia Commons (CC BY-SA 4.0)
Key Takeaway
To upgrade PostgreSQL across major versions with near-zero downtime, replicate the running database into a new-version instance using logical replication: create a PUBLICATION on the old server, a SUBSCRIPTION on the new one, wait for the lag to reach zero, then cut traffic over. The catch is that logical replication copies table rows but not sequences, large objects, or DDL, so you must sync those by hand during the cutover window.
The classic way to do a PostgreSQL major-version upgrade is pg_upgrade, and it works fine until you have a database that must not go offline. pg_upgrade needs the old server stopped while it relinks the data directory, and even in --link mode you are looking at a maintenance window plus a scary rollback story if something breaks mid-flight. For a service I run that customers hit around the clock, that window is not acceptable. Logical replication gives you a different shape entirely: the new-version server runs alongside the old one, stays continuously in sync, and the only real downtime is the few seconds it takes to flip the application's connection string.
Unlike physical (streaming) replication, logical replication decodes the write-ahead log into row-level change events and replays them as regular INSERT, UPDATE, and DELETE statements on the subscriber. Because it operates at the logical row level rather than the byte level, the publisher and subscriber can run different major versions. That single property is what makes it the right tool for an upgrade. This walkthrough assumes going from a Postgres 15 primary to a Postgres 18 target, but the shape is identical for any major-to-major hop supported by logical decoding.
On the old server, set wal_level to logical and give yourself enough replication slots and WAL senders. This requires a restart, so do it during a routine maintenance beat well before the actual upgrade. Then copy the schema to the new server first, because logical replication replicates data but never DDL. Every table you intend to replicate must already exist on the subscriber with a matching definition, and every table needs a primary key or an explicitly set REPLICA IDENTITY so that UPDATE and DELETE events can find their target rows.
-- On the OLD server (publisher), in postgresql.conf:
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
-- then restart PostgreSQL
-- Copy schema only (no data) from old -> new. Run from a host that can reach both:
pg_dump -h old-host -U postgres --schema-only --no-owner mydb \
| psql -h new-host -U postgres mydb
-- Create the publication on the OLD server:
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;On the new server, create a SUBSCRIPTION pointing at the publisher. By default it does two things: an initial data copy (a parallel COPY of every table's current contents) followed by continuous streaming of new changes. For a large database the initial copy is the slow part and it runs under a replication slot, so the publisher will retain WAL the entire time. Watch disk on the old server here. Once the copy finishes, each table's state flips to r (ready) in pg_subscription_rel and steady-state streaming takes over.
-- On the NEW server (subscriber):
CREATE SUBSCRIPTION upgrade_sub
CONNECTION 'host=old-host dbname=mydb user=repl password=secret'
PUBLICATION upgrade_pub;
-- Watch initial sync progress (all rows should reach state 'r'):
SELECT srrelid::regclass AS table, srsubstate
FROM pg_subscription_rel;
-- Watch replication lag from the OLD server:
SELECT slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;On PostgreSQL 17 and later you can skip the expensive initial COPY entirely with pg_createsubscriber, which converts an existing physical standby into a logical subscriber by recording an LSN and rewinding the apply position. PostgreSQL 18 added an --all flag that sets up subscriptions for every database in the instance in one command. If you already run a streaming standby, this turns hours of initial copy into seconds.
This is where people get burned. Logical replication copies the numbers sitting in your serial and identity columns as ordinary data, but it does not replicate the sequence objects that generate them. On the subscriber, every sequence still reports its start value. Promote the new cluster without fixing this and your very first insert greets you with a wall of duplicate key violations. Large objects (the pg_largeobject / lo API) are also never replicated — if you use them, you need a separate copy step. And because DDL is not replicated, you must freeze schema migrations for the duration of the cutover or apply every change to both servers by hand.
The sequence fix is a script you generate on the publisher and run on the subscriber, and you run it as late as possible in the cutover. Add a safety buffer to each value so that any transactions still in flight during the switch cannot collide with a freshly promoted sequence. A buffer of a thousand is cheap insurance; sequence gaps never matter.
-- Run on the OLD server to GENERATE setval statements, then pipe them
-- to the NEW server during cutover. The +1000 is a collision buffer.
SELECT format(
'SELECT setval(%L, %s);',
schemaname || '.' || sequencename,
COALESCE(last_value, 1) + 1000
)
FROM pg_sequences
WHERE last_value IS NOT NULL;
-- Example of what it produces (run these on the NEW server):
-- SELECT setval('public.orders_id_seq', 84213);
-- SELECT setval('public.users_id_seq', 15044);Do not forget to disable your application's schema-migration step before the cutover window opens. A single ALTER TABLE run against the old primary while replication is live will not reach the new server, and the next row that depends on that column change will break replication with an error that halts the whole subscription until you fix the schema by hand.
The cutover is the only moment with real (sub-second) downtime, so script it and rehearse it on staging first. The goal is to guarantee the subscriber has caught every last change before any client writes to it.
Keep the old primary running and untouched for a day or two after cutover. It is your instant rollback: if something is wrong on the new version, you flip the connection string back. Just remember the old server has not received the writes that landed on the new one, so a rollback after real traffic means reconciling that gap — which is exactly why you smoke-test hard in the first minutes.
| Object | Replicated automatically? | What you must do |
|---|---|---|
| Table rows (INSERT/UPDATE/DELETE) | Yes | Nothing — this is the core job |
| Sequences (serial / identity generators) | No | Run setval with a buffer at cutover |
| Large objects (lo / pg_largeobject) | No | Copy separately, or migrate to bytea |
| Schema / DDL changes | No | Copy schema first; freeze migrations |
| Views, materialized views, foreign tables | No | Recreate on the subscriber by hand |
| TRUNCATE | Yes (with caveats) | Check foreign-key groupings match |
None of this is exotic once you have done it once, but every one of those No rows is a production incident waiting for the person who assumed logical replication is a full clone. It is not — it is a row-change stream, and the objects it skips are the ones you handle deliberately during cutover. Worth noting: PostgreSQL 19 is slated to finally add native sequence synchronization to logical replication, which removes the single nastiest snag in this whole procedure. Until you are running 19, the setval script stays on your cutover checklist.