Zero-Downtime Postgres Migrations with Logical Replication

What you'll build / learn

You'll move a live PostgreSQL 16 database to a PostgreSQL 18 instance with a changed table schema, using built-in logical replication to keep the new instance in sync while the old one keeps serving writes, then cut over in seconds. The same procedure works for a pure major-version upgrade or a pure schema migration.

Prerequisites

• Docker 24+ (verified with Docker Engine 29.2). We'll run both servers as containers so you can rehearse the whole thing locally; in production the commands are identical, you just point at real hosts.
• Images verified: postgres:16-alpine (16.14) as the source and postgres:18-alpine (18.4) as the target. Current stable minors are 16.15 and 18.6; any 10+ → 16+ pair works since logical replication shipped in Postgres 10.
• A source table with a primary key (or REPLICA IDENTITY). Without it, UPDATE/DELETE on the publisher fail once a publication exists.
• Disk headroom on the publisher: WAL is retained from the moment you create the subscription until the subscriber catches up.
• Linux or macOS shell. All SQL runs through docker exec ... psql, so you don't need a local psql.

What logical replication does not carry, per the restrictions page: DDL, sequence values, and large objects. Steps 3 and 7 handle the first two.

1. Start the source and target servers

docker network create pgmig

docker run -d --name pg-old --network pgmig -e POSTGRES_PASSWORD=secret \
  -p 5433:5432 postgres:16-alpine -c wal_level=logical

docker run -d --name pg-new --network pgmig -e POSTGRES_PASSWORD=secret \
  -p 5434:5432 postgres:18-alpine

-c wal_level=logical is the one publisher setting that requires a restart, so set it at boot. Defaults for max_replication_slots and max_wal_senders are both 10, which is plenty for one subscription. On an existing production server, set wal_level = logical in postgresql.conf and restart during a maintenance window before you start.

2. Seed the source with a live table

docker exec -i pg-old psql -U postgres -v ON_ERROR_STOP=1 <<'SQL'
CREATE DATABASE shop;
\c shop
CREATE TABLE orders (
  id           bigserial PRIMARY KEY,
  customer     text NOT NULL,
  amount_cents integer NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (customer, amount_cents)
SELECT 'cust-' || g, (random() * 10000)::int FROM generate_series(1, 100000) g;
SQL

Note the -i on docker exec; without it the heredoc never reaches psql.

3. Create a replication role and publication on the source

docker exec -i pg-old psql -U postgres -d shop -v ON_ERROR_STOP=1 <<'SQL'
CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'replpass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl;
CREATE PUBLICATION shop_pub FOR ALL TABLES;
SQL

SELECT is needed because the initial copy reads the tables as repl. The official image's pg_hba.conf already allows password auth from any host; on a hand-managed server add a host replication-style line for the repl role and the subscriber's IP.

4. Copy the schema to the target and apply your change

Run pg_dump from the newer server; a newer pg_dump always understands older servers and emits DDL the new server accepts.

docker exec -e PGPASSWORD=secret pg-new pg_dump -h pg-old -U postgres -d shop \
  --schema-only --no-owner --no-privileges --no-publications --no-subscriptions \
  > schema.sql

docker exec pg-new psql -U postgres -c "CREATE DATABASE shop;"
docker exec -i pg-new psql -U postgres -d shop -v ON_ERROR_STOP=1 -q < schema.sql

Now make the schema change you're migrating to. Logical replication matches columns by name, so the subscriber can have extra columns as long as they have defaults or allow NULL:

docker exec pg-new psql -U postgres -d shop \
  -c "ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';"

Renaming or dropping a column that the publisher still sends will break apply, so do additive changes here and drop old columns after cutover.

5. Subscribe and let the initial copy run

docker exec pg-new psql -U postgres -d shop -c "CREATE SUBSCRIPTION shop_sub \
  CONNECTION 'host=pg-old port=5432 dbname=shop user=repl password=replpass' \
  PUBLICATION shop_pub;"

Expected:

NOTICE:  created replication slot "shop_sub" on publisher
CREATE SUBSCRIPTION

This creates the slot, snapshots every table, and then streams changes. Watch per-table state until everything is r (ready):

docker exec pg-new psql -U postgres -d shop \
  -c "SELECT srrelid::regclass AS table, srsubstate AS state FROM pg_subscription_rel;"

 table  | state
--------+-------
 orders | r

States are i initialize, d copying, f copy finished, s synchronized, r ready. On 100k rows this takes a second or two; on hundreds of GB it takes hours, and the app keeps writing the whole time.

6. Confirm changes stream while the app is live

Write to the old server and read from the new one:

docker exec pg-old psql -U postgres -d shop -c "INSERT INTO orders (customer, amount_cents) VALUES ('live-1', 4200);" \
  -c "UPDATE orders SET amount_cents = 1 WHERE id = 1;" -c "DELETE FROM orders WHERE id = 2;"

docker exec pg-new psql -U postgres -d shop \
  -c "SELECT id, customer, amount_cents, status FROM orders WHERE id IN (1,2,100001) ORDER BY id;"

   id   | customer | amount_cents | status
--------+----------+--------------+---------
      1 | cust-1   |            1 | pending
 100001 | live-1   |         4200 | pending

Row 2 is gone, row 1 updated, the new row picked up the status default. Lag check, run on the publisher:

docker exec pg-old psql -U postgres -d shop -c "SELECT pg_current_wal_lsn() = replay_lsn AS caught_up, replay_lag \
  FROM pg_stat_replication WHERE application_name = 'shop_sub';"

You want caught_up = t before the next step.

7. Cut over

The only "downtime" is the few seconds between freezing writes on the old server and pointing the app at the new one. Sequence:

# 1. Freeze writes on the old database and kick existing sessions
docker exec pg-old psql -U postgres -d shop \
  -c "ALTER DATABASE shop SET default_transaction_read_only = on;" \
  -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
      WHERE datname = 'shop' AND pid <> pg_backend_pid() AND backend_type = 'client backend';"

# 2. Wait for the last WAL to apply
docker exec pg-old psql -U postgres -d shop -c "SELECT pg_current_wal_lsn() = replay_lsn AS caught_up \
  FROM pg_stat_replication WHERE application_name = 'shop_sub';"

# 3. Sequences don't replicate: set each one past the max id on the new server
docker exec pg-new psql -U postgres -d shop \
  -c "SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));"

# 4. Detach: drops the slot on the publisher so WAL stops piling up
docker exec pg-new psql -U postgres -d shop -c "DROP SUBSCRIPTION shop_sub;"

Now repoint your application's connection string at pg-new (port 5434 here). Use a connection pooler or DNS swap in production so clients reconnect automatically; pg_terminate_backend already forced them off the old server.

Verify it works

Counts and high-water marks should match on both sides, and the new server should accept writes with the new column:

docker exec pg-old psql -U postgres -d shop -Atc "SELECT count(*), max(id) FROM orders;"
docker exec pg-new psql -U postgres -d shop -Atc "SELECT count(*), max(id) FROM orders;"
docker exec pg-new psql -U postgres -d shop \
  -c "INSERT INTO orders (customer, amount_cents, status) VALUES ('post-cutover', 999, 'paid') RETURNING id, status;"

100000|100001
100000|100001
   id   | status
--------+--------
 100002 | paid
(1 row)

The insert getting id = 100002 proves the setval worked; without it you'd get a duplicate-key error on id = 1. For larger datasets, compare a checksum per table instead of a count, for example SELECT md5(string_agg(t::text, '' ORDER BY id)) FROM orders t.

Clean up the rehearsal with docker rm -f pg-old pg-new && docker network rm pgmig.

Troubleshooting

ERROR: could not create replication slot "shop_sub": ERROR: logical decoding requires wal_level >= logical on CREATE SUBSCRIPTION. The publisher is still on wal_level = replica. Set wal_level = logical in postgresql.conf (or the -c flag) and restart; the setting can't be changed at runtime. You'll also see WARNING: wal_level is insufficient to publish logical changes when creating the publication, which is your early hint.

ERROR: cannot update table "nokey" because it does not have a replica identity and publishes updates on the publisher, the moment FOR ALL TABLES goes live. A table without a primary key is now blocking your app's updates. Fix fast with ALTER TABLE nokey REPLICA IDENTITY FULL; (expensive, whole row is the key) or add a primary key/unique index, or scope the publication to named tables instead of FOR ALL TABLES.

ERROR: logical replication target relation "public.orders" is missing replicated column: "note" in the subscriber's log, and replication stalls. Someone ran DDL on the publisher after the schema copy; DDL isn't replicated. Apply the same ALTER TABLE on the subscriber and the apply worker retries automatically. The sibling error logical replication target relation "public.nokey" does not exist means a whole table was created on the publisher; create it on the subscriber too.

ERROR: role "repl" does not exist while restoring schema.sql. You dumped without --no-privileges, so the dump includes GRANT ... TO repl. Either re-dump with --no-owner --no-privileges as shown, or create the role on the target first.

Next steps

• Rehearse the cutover with your real dataset and measure the initial copy time. Raise max_sync_workers_per_subscription on the subscriber to parallelise the copy across tables.
• Keep the old server around for a rollback window: you can reverse the direction (publish from new, subscribe on old) immediately after cutover so the old server tracks the new one.
• Read the logical replication configuration and CREATE SUBSCRIPTION pages for disable_on_error, streaming = parallel, and binary = true, which matter at scale.
• For multi-gigabyte tables with no primary key, look at publish_via_partition_root and row filters to split the publication.