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.
Step 1: Prepare the publisher and copy the schema
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;Step 2: Create the subscription and let the initial copy run
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.
Step 3: Mind the gaps — sequences, large objects, and DDL
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.
Step 4: The cutover
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.
- Stop application writes to the old primary (put it in read-only, or stop the app / connection pool). This starts the downtime clock.
- Wait for replication lag to reach zero — confirm confirmed_flush_lsn on the publisher equals the current WAL LSN and pg_subscription shows no pending changes.
- Run the generated setval statements on the new server to promote every sequence past the old values.
- Point the application's connection string at the new server and start accepting writes there.
- Verify with a smoke test — an insert that exercises a sequence, a read of recent rows — then DROP the subscription on the new server to detach it cleanly.
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.
What logical replication does and does not carry
| 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.