# Postgres Logical Replication for Zero-Downtime Major Upgrades

URL: https://www.devopsness.com/blog/postgres-logical-replication-zero-downtime-upgrades
Author: Kiril Urbonas
Published: 2026-06-09
Updated: 2026-10-31
Categories: Infrastructure
Tags: Postgres, Replication, Database, Upgrades, SQL

Summary: pg_upgrade is fast but takes downtime; logical replication lets you cut over while the old DB still serves traffic. The runbook, the gotchas, and the post-cutover checklist.

---
# Postgres Logical Replication for Zero-Downtime Major Upgrades

For a zero-downtime Postgres major upgrade, run the new version beside the old one and subscribe it to a `FOR ALL TABLES` publication. Once initial sync finishes and lag is near zero, bump the sequences, disable the subscription and repoint the app. Downtime shrinks to the connection swap, usually seconds, instead of a `pg_upgrade` window.

> [!VERIFIED]
> PostgreSQL 18.6 and PostgreSQL 19 RC 1, against the postgresql.org logical replication docs (restrictions, upgrade) and the `CREATE SUBSCRIPTION` reference. Checked October 2026.

`pg_upgrade` is the official answer for major version upgrades. It works, it's fast, and it takes downtime: your DB is unavailable from "stop the old server" to "start the new one." For a single instance that's minutes. For a busy production DB with replicas and connection pools to drain, it can be an hour-plus maintenance window. Logical replication is our default wherever that window is unacceptable. This is the runbook and what tends to go wrong.

## The mental model

Logical replication decodes the WAL into row-level changes (INSERT/UPDATE/DELETE) rather than physical block changes, and those can be applied to a different major version.

The flow:
1. Stand up an empty Postgres instance at the new version (e.g., 18 if you're on 15; 18.6 is the current minor as of October 2026, and 19 is at release candidate).
2. Copy the schema across (no data yet).
3. Create a publication on the old DB (the source) and a subscription on the new DB (the target).
4. Initial sync copies all existing rows; replication then keeps the new DB current with the old.
5. When the new DB is caught up and verified, point the application at it. Cutover is a few seconds: the time to swap a DNS record or pooler target and recycle connections.

## Pre-flight: what logical replication doesn't replicate

The first surprise: not everything is in the replication stream. You have to handle these manually:

**Sequences.** Sequence values (the `nextval` state for SERIAL and identity columns) are not replicated. If you cut over and the new DB's sequence is at 1 while the old was at 1,000,000, the next INSERT collides. Solution: bump sequences manually after sync, before cutover. PostgreSQL 19 adds `ALTER SUBSCRIPTION ... REFRESH SEQUENCES`, but the docs say it requires a publisher on 19 or later, so it does nothing for an upgrade off 15, 16, 17 or 18.

**Schema changes.** DDL (CREATE TABLE, ALTER TABLE, etc.) is not replicated. If you ALTER TABLE on the source during replication, you have to apply the same change on the target manually. We freeze schema changes during the cutover window.

**Large objects (`lo_*` API).** Not replicated. If you use them (rare), use file storage instead.

**Tables without a primary key.** Replication can't UPDATE/DELETE rows without a unique identifier. Either add a PK before starting (preferred) or set REPLICA IDENTITY to FULL (logs the entire old row in the WAL, which is heavy).

For most modern Postgres schemas, only sequences are an issue.

## Step 1: prep the source

On the old DB:

```sql
-- Enable logical replication
ALTER SYSTEM SET wal_level = logical;
-- Restart required for wal_level
```

```sql
-- Create a publication for all tables
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
```

You can publish specific tables if you're doing partial migration; for a full upgrade, `FOR ALL TABLES` is cleanest.

Confirm the publication:
```sql
SELECT * FROM pg_publication;
```

## Step 2: stand up the target

Install Postgres 18 (or your target version) on a new host. Create the database with the same name, owner, and encoding as the source.

Dump the schema (no data):
```
$ pg_dump -h source-db --schema-only --no-owner --no-privileges mydb > schema.sql
```

Apply to target:
```
$ psql -h target-db mydb < schema.sql
```

Apply roles separately (`pg_dumpall --roles-only`).

## Step 3: create the subscription

On the target:

```sql
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=source-db user=replicator dbname=mydb password=...'
  PUBLICATION upgrade_pub;
```

This starts the initial sync immediately, and on a multi-hundred-gigabyte database it runs for hours. During this time the source is under additional load (WAL retention + read load from the sync workers). Plan for it.

Monitor sync progress:
```sql
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_subscription_rel WHERE srsubstate != 'r';
```

`srsubstate = 'r'` means "ready" (caught up). All tables at `r` = initial sync complete.

## Step 4: monitor lag

After initial sync, replication continues. Check lag on the source:

```sql
SELECT 
  application_name,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS sent_lag,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag
FROM pg_stat_replication;
```

`replay_lag` should be near zero in steady state. Watch WAL retention on the source too: a logical slot prevents WAL recycling, so a stalled subscriber can fill the disk. If it's growing, the target can't keep up, usually because the target hardware is undersized or because a slow query is blocking the apply worker. Investigate.

## Step 5: pre-cutover verification

Before cutting over, verify:

- **Row counts match** on a sample of tables. Pick the 10 tables you care most about and run `SELECT count(*)` on both.
- **Sequences are bumped.** For each sequence:
  ```sql
  -- On source:
  SELECT last_value FROM my_sequence;
  -- On target:
  SELECT setval('my_sequence', <last_value + safety_margin>);
  ```
  We add a 1000-buffer to account for in-flight transactions during cutover.
- **Replication lag is < 1 second.** If lag is high at cutover time, the application will see missing recent writes.

## Step 6: the cutover

1. Drain the application connection pool (or scale to zero if you can).
2. Verify replication lag is 0; wait briefly if needed.
3. **DISABLE** the subscription on the target: `ALTER SUBSCRIPTION upgrade_sub DISABLE;` This prevents further replication.
4. Point the application at the target (DNS, config map or secret).
5. Restart connection pools / app pods.
6. Watch for errors.

If the app uses PgBouncer, swap PgBouncer's `databases` config to point at the target and `RELOAD CONFIG`. App connections through PgBouncer don't need to restart; pooler handles the swap.

## Step 7: post-cutover

The old DB is now stale; the new DB is authoritative.

- Keep the old DB running, read-only, for 24-48 hours as a safety net for fast rollback.
- Run vacuum + analyze on the new DB to refresh statistics.
- Reset `pg_stat_statements` and point monitoring at the new DB.

If something goes wrong in the first hour, you can swap back to the old DB, but any writes that happened on the new DB will be lost. The window for safe rollback is tight; verify aggressively in the first 30 minutes.

## What we got wrong

**Underestimating initial sync time.** Plans routinely assume a fraction of the real duration. Sync is bounded by network + target disk write throughput; benchmark on a non-prod copy first.

**Forgetting sequences.** The classic first cutover: the app immediately fails on INSERT with a duplicate-key error because the sequence is at 1. Roll back, set the sequences, try again. That's why it's a named step in the runbook above.

**Replicating into a DB with existing data.** Don't. Colliding INSERTs fail and stall the apply worker.

**Big tables without WHERE clauses.** Logical replication does the initial sync as essentially a `SELECT *` from each table. A table of several hundred gigabytes is a long-running query. Consider replicating in batches by partition if your tables are partitioned.

## Questions people ask

### Can logical replication replicate between different Postgres major versions?

Yes, and that's the point. Logical replication decodes WAL into row-level INSERT, UPDATE and DELETE changes rather than shipping physical blocks, so a subscriber on PostgreSQL 18 can apply changes from a publisher on 15. Physical streaming replication needs the same major version on both sides. The schema must already exist on the subscriber, because DDL is not replicated.

### Does logical replication copy sequences?

Not on any released version as of October 2026. The PostgreSQL 18 docs say sequence data is not replicated, so the subscriber still shows each sequence's start value. Before cutover, read `last_value` on the source and `setval()` it on the target with a safety margin. PostgreSQL 19 adds `ALTER SUBSCRIPTION ... REFRESH SEQUENCES`, but only when the publisher itself runs 19 or later.

### Is pg_upgrade or logical replication better for a major upgrade?

Use `pg_upgrade --link` when a maintenance window of minutes is acceptable: it's simpler and has fewer moving parts. Use logical replication when the window must be seconds, or when you want to move hosts and verify the new server under real writes before switching. From a 17+ old cluster, `pg_upgrade` also preserves logical slots and subscriptions.

## What to read next

- [PgBouncer in front of RDS](/blog/postgres-connection-pooling-pgbouncer): what handles the cutover for connection-heavy apps
- [Postgres replication lag and failover practice](/blog/postgres-replication-lag-failover-practice): adjacent operational discipline
- [pg_stat_statements query analysis](/blog/postgres-pg-stat-statements-query-analysis): the baseline you re-establish after upgrade
- [Testing database backup restores](/blog/database-backup-restoration-testing-restores): the safety net you should already have
- [Expand-contract migrations](/blog/zero-downtime-postgres-migrations-expand-contract): how to keep schema changes zero-downtime once the freeze lifts

## The call we'd make

Logical replication adds operational complexity for the duration of the migration. For a busy production DB where a 90-minute maintenance window is unacceptable, it's still the path we'd take: empty target, schema-only dump, `FOR ALL TABLES` publication, sequences bumped by hand, and the old server kept read-only for a day as the rollback. Expect to refine the runbook for your shape, and rehearse it on a copy before the real one.

## Sources

- [PostgreSQL 18 Docs: Logical replication restrictions](https://www.postgresql.org/docs/current/logical-replication-restrictions.html)
- [PostgreSQL 18 Docs: Upgrade (logical replication)](https://www.postgresql.org/docs/current/logical-replication-upgrade.html)
- [PostgreSQL Docs: CREATE SUBSCRIPTION](https://www.postgresql.org/docs/current/sql-createsubscription.html)
- [PostgreSQL versioning policy](https://www.postgresql.org/support/versioning/)