# Zero-Downtime PostgreSQL Major Upgrades with Logical Replication

> How to move a live PostgreSQL database to a new major version with sub-second downtime using publications and subscriptions — and the sequence, large-object, and DDL gaps that will bite you at cutover if you forget them.

URL: https://www.matthewswong.com/en/blog/postgres-logical-replication-zero-downtime-upgrade/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2026-07-14
- Updated: 2026-07-14
- Category: Backend

> **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.

## 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 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.

> 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.

1. Stop application writes to the old primary (put it in read-only, or stop the app / connection pool). This starts the downtime clock.
2. 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.
3. Run the generated setval statements on the new server to promote every sequence past the old values.
4. Point the application's connection string at the new server and start accepting writes there.
5. 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.

## Sources

- PostgreSQL 18 Documentation — Logical Replication Restrictions
- PostgreSQL 18 Documentation — pg_createsubscriber
- PostgreSQL 18 Documentation — Logical Replication

## Source links

- https://www.postgresql.org/docs/current/logical-replication-restrictions.html
- https://www.postgresql.org/docs/current/app-pgcreatesubscriber.html
- https://www.postgresql.org/docs/current/logical-replication.html

## FAQ

### Why use logical replication instead of pg_upgrade for a major version upgrade?

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.

### Does PostgreSQL logical replication copy sequences automatically?

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.

### What is pg_createsubscriber and how does it help?

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.

### How much downtime does a logical-replication upgrade actually require?

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.

### Are large objects replicated by PostgreSQL logical replication?

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.

---

- HTML version: https://www.matthewswong.com/en/blog/postgres-logical-replication-zero-downtime-upgrade/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/postgres-logical-replication-zero-downtime-upgrade/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json