Migrating a Live Postgres Database Without Stopping Writes

A measured PostgreSQL migration at 79 GB: pg_dump and pg_restore, logical replication, and logical replication behind pgbouncer, with the cutover steps and every failure mode found along the way.

I moved the same 79 GB Postgres database from one server to another three different ways, with an application sending reads and writes to it the whole time. Here is what the application saw:

method	writes blocked for	failed requests
1. dump and restore	62 minutes	36,721
2. logical replication, restart the app	23.2 seconds	230
3. logical replication behind pgbouncer	342 ms pause	0

Each method fixes the biggest cost left by the one before it:

1. Dump and restore copies the data while writes are blocked. The bigger the database, the longer the outage.
2. Logical replication copies the data before you block writes. What is left is restarting the application and typing commands.
3. pgbouncer removes the restart. The application never finds out the database moved. Requests wait a moment instead of failing.

The rest of this post covers each method in turn: what I ran, what I measured, and what broke.

How I measured

Setup:

• Two Postgres 18 servers on one machine. The source (the old server) on port 5442, the target (the new one) on 5443.
• A small Express app using node-postgres, sitting in front of the database.
• A load generator hitting the app with about 10 reads and 10 writes a second, logging every request and whether it worked. That write rate is the main limit on how far these numbers transfer to a busier system.

Downtime means the longest gap between two successful requests, measured from the client's side. A request that times out counts as a failure.

Before trusting that number, I tested it on a fault of known size: I stopped the database for exactly 20 seconds, with no migration involved. It measured 20,372 ms. Close enough to trust.

The test database was 79 GB: a table of 260 million rows of filler text, plus a small events table the load generator wrote to. I also ran methods 1 and 2 on a 399 MB copy to see how each one scales.

Method 1: Dump and restore

The obvious approach:

1. Block writes on the source.
2. pg_dump the database to a file.
3. pg_restore that file into the target.
4. Point the app at the target and restart it.

At 79 GB:

pg_dump      9m47s
pg_restore  20m17s
dump file    6.0 GB

That is 30 minutes of copying. Writes were blocked for 62 minutes. The other 30 were me: two restore attempts I had to cancel, password prompts, checking file sizes between steps. That is what a manual migration really looks like, and every one of those minutes is a minute of refused writes.

WRITE   attempts 37,348   succeeded 598   failed 36,750
        longest gap 62 minutes

Reads were fine the whole time (one 3-second blip). Dump and restore is not an outage for readers. It is an outage for writers.

The problem is how it scales. At 399 MB, writes were blocked for at least 175 seconds. At 79 GB, 3,740 seconds. The copy happens inside the outage, so the outage grows with the data.

Two side notes if you are stuck with this method:

• The dump was limited by CPU, spent compressing. pg_dump -Z0 skips compression and trades file size for speed.
• The restore was limited by the server loading rows and rebuilding indexes, while pg_restore itself sat idle. pg_restore -j 4 runs it in parallel. I did not use it, so 20 minutes is the slow case.

Neither changes the basic problem: the app cannot write until the copy is finished.

Method 2: Logical replication

The idea: copy the data while the source keeps working normally, keep the target in sync, and only block writes for the last few seconds.

Three terms first, because the rest of this section uses them:

• WAL (write-ahead log): Postgres's running record of every change. Changes are written here before they reach the tables.
• Logical replication: the source reads its own WAL and sends every row change to the target as it happens.
• Replication slot: a bookmark on the source that says "the target has received everything up to here, keep the rest until it does." Positions in the WAL are called LSNs.

Before you start

The source needs wal_level = logical in postgresql.conf. Changing it needs a restart, so do it well before migration day.

SHOW wal_level;   -- must say 'logical', not 'replica'

If it still says replica after you edited the config, run SHOW config_file. You edited a different file from the one the server is reading.

The target needs the schema already in place. Logical replication copies rows, not table definitions. Create the tables on the target first.

The target must be empty. I skipped this once when re-running a test. The copy landed on top of the old rows, the target grew to 125 GB from a 79 GB source, and replication then crashed on duplicate keys:

ERROR:  conflict detected on relation "public.events": conflict=insert_exists
DETAIL:  Key already exists in unique index "events_pkey"

Check the target is empty before every attempt, not just the first. Do not use count(*) for this: on a big table it is a full scan and takes minutes. This returns straight away:

SELECT EXISTS (SELECT 1 FROM mytable);   -- must be false

Test the connection string on its own. The subscription will connect with it, so make sure it works first:

psql "host=SOURCE port=5442 dbname=mydb user=migrator password=..." -c "select 1"

Debugging it alone takes a minute. Debugging it inside CREATE SUBSCRIPTION takes an hour.

Check the replication role has no statement_timeout. This one cost me an hour. I had set statement_timeout = '30s' on the role as a safety default. The initial copy connects as that role, and copying 79 GB takes a lot longer than 30 seconds:

ERROR:  could not receive data from WAL stream: ERROR:  canceling statement
        due to statement timeout
CONTEXT:  COPY ballast, line 4355224

The copy was cancelled, restarted from zero, and cancelled again, forever. Nothing in the error says "your role has a timeout." Remove it from the role the subscription connects as. If your app needs a timeout, give the app its own role and set it there.

Step 1: Publish on the source

CREATE PUBLICATION mypub FOR ALL TABLES;

FOR ALL TABLES needs superuser. A publication that names its tables does not, as long as you own them.

Every table needs a primary key first. A table without one replicates inserts fine, then refuses updates:

ERROR:  cannot update table "no_pk" because it does not have a replica
        identity and publishes updates

Why: replication sends row changes, not SQL. To apply an UPDATE, the target has to know which row changed, and by default it uses the primary key to find it. No primary key, no way to find the row, so Postgres refuses the update on the source before it happens.

The trap is that inserts need no key, so the table looks healthy until someone runs the first update, possibly weeks later. Two ways out, both forms of REPLICA IDENTITY:

ALTER TABLE t REPLICA IDENTITY USING INDEX idx;   -- a unique, NOT NULL index (preferred)
ALTER TABLE t REPLICA IDENTITY FULL;              -- whole row as the key (slow)

FULL always works but is expensive: every update writes every column twice, and the target has to scan to find the row.

Step 2: Subscribe on the target

CREATE SUBSCRIPTION mysub
  CONNECTION 'host=SOURCE port=5442 dbname=mydb user=migrator password=...'
  PUBLICATION mypub;

If it fails on permissions:

GRANT pg_create_subscription TO migrator;

You run this on the target, but it creates the replication slot on the source. Keep that in mind: it is the source's disk that holds on to WAL for it.

The initial copy starts straight away in the background. At 79 GB, with the source serving writes the whole time:

09:03  subscription created
09:05  target at  9.9 GB
09:10  target at 45 GB
09:17  target at 80 GB, copy done

About 14 minutes, with zero downtime. Dump and restore took 30 minutes on the same data, all of it downtime. Replication has no compression step and no dump file to write and read back. I did not measure how much of the difference that accounts for.

To watch progress:

-- on the target
SELECT * FROM pg_stat_subscription;   -- non-null pid = worker alive
SELECT pg_size_pretty(pg_total_relation_size('mytable'));

During the copy you will see a second slot on the source named something like pg_16398_sync_16385_..., showing active = f. It looks abandoned. Do not drop it. It is a temporary per-table copy slot, and it disappears once its table finishes.

I expected the main slot to pile up WAL for the whole 14 minutes. It stayed flat at 25–48 kB. The temporary slot peaked at about 2 MB. The WAL a slot holds grows with how fast you write, not how big the database is.

If you add a table after subscribing, the target will not pick it up on its own:

ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;

Step 3: Confirm it is streaming

The copy finishing does not prove new changes are flowing. Test one:

-- on the source
INSERT INTO mytable (col) VALUES ('probe');
-- on the target, a second later
SELECT * FROM mytable WHERE col = 'probe';

Then look at the slot on the source:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

Two columns matter:

• confirmed_flush_lsn: how far the target has caught up. You wait on this at cutover.
• restart_lsn: the oldest WAL the source must keep. This is what fills your disk.

While you wait: two things that will bite

You might leave replication running for hours or days before cutting over. Two things can go wrong in that time.

1. Do not change the schema on the source. I added one column on the source, and the target's replication worker crashed:

ERROR:  logical replication target relation "public.events" is missing
        replicated column: "note"

It restarted every 5 seconds, hit the same change, and crashed again, forever. Meanwhile the source kept holding WAL for it, about 150 kB a minute on a nearly idle system.

Undoing the change on the source does not help. I tried. The WAL already recorded rows with that column, and WAL cannot be edited. The fix has to go on the target:

-- on the TARGET
ALTER TABLE events ADD COLUMN note text;

Within one 5-second cycle it caught up. The rule: add columns on the target first, and drop them there last.

2. Watch how much WAL the slot is holding.

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

A slot tells the source to keep WAL until the target has read it. There is no expiry. With the target switched off and the app completely idle, retained WAL still grew from 56 bytes to 187 kB, just from Postgres's own background work.

That is correct behaviour: otherwise a reconnecting target could not know what it missed. But a stuck or forgotten target will slowly fill the source's disk, and when the WAL disk is full, the source cannot commit anything.

• Set max_slot_wal_keep_size if you would rather lose the replica than the main database.
• Alert on retained, not on active. During the crash loop above, active flipped between true and false every few seconds and told me nothing.

Step 4: Cut over

Wait until the target has nearly caught up, then do all of this in one go:

1. Block writes on the source.

REVOKE INSERT ON mytable FROM appuser;

2. Wait for the last changes to arrive. Check the gap, don't just sleep:

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots;

It may not reach exactly 0, because Postgres keeps writing a little WAL in the background. Wait for small and steady.

3. Fix the sequences. This is the step people forget.

Sequences (the counters behind auto-increment ids) are not replicated. The target has every row, but its counters are still at the start:

source: last_value = 3480
target: last_value = 1
target: max(id)    = 3515

The first insert after cutover gets an id that already exists, and so does every insert after it until the counter passes 3,515. And every check you would think to run passes first: row counts match, data matches, lag is zero.

Notice the source's counter (3480) is lower than the real highest id (3515), because Postgres hands out ids in cached blocks. So copying the source's value across still collides. Use the target's own data:

-- on the TARGET
SELECT setval('mytable_id_seq', (SELECT max(id) FROM mytable));

Setting it too high wastes a few ids and costs nothing. Too low breaks every insert.

4. Point the app at the target and restart it. Then ask the server, not your config file, which one you are on:

SELECT inet_server_port();   -- should be 5443

You do not need to undo the REVOKE: permissions are not replicated either, so the target never had it.

Method 2 results

WRITE   attempts 12,806   succeeded 12,576   failed 230
        longest gap 23.2 seconds

Afterwards, on the target: no duplicate rows, no missing writes, sequence correct.

At 399 MB the same process took 12.4 seconds. Data grew 200×; the outage did not even double:

	399 MB	79 GB	growth
dump and restore, writes blocked	≥175 s	3,740 s	21×
logical replication, writes blocked	12.4 s	23.2 s	1.9×

So where did the 23 seconds go? Not the copy. That happened earlier, with no downtime. What was left:

• the app process restarting, and
• me typing four commands by hand.

Neither is a database problem, so the next fix is not a database change.

Method 3: Logical replication behind pgbouncer

pgbouncer is a connection pooler: a small proxy that sits between the app and Postgres. The app connects to pgbouncer as if it were Postgres, and pgbouncer passes queries on to the real server.

before:  app ──────────────► Postgres (source)

after:   app ──► pgbouncer ──► Postgres (source)
                           └─► Postgres (target, after the switch)

The app only knows pgbouncer's address. So to move databases, you change where pgbouncer points, and the app never restarts.

pgbouncer also has two admin commands that make this work:

• PAUSE: stop sending queries to Postgres. Apps stay connected, and their queries wait in a queue.
• RESUME: send the queued queries on.

This is the key difference from method 2. REVOKE made writes fail. PAUSE makes them wait.

The setup

pgbouncer config, pointing at the source:

[pgbouncer]
pool_mode = transaction
max_client_conn = 500

[databases]
mydb = host=127.0.0.1 port=5442 dbname=mydb

Two setup notes:

• Use 127.0.0.1, not localhost. localhost can resolve to the IPv6 address ::1, and if Postgres only listens on IPv4, connections hang for 60 seconds instead of failing straight away.
• In transaction mode, pgbouncer rejects most settings sent when the app connects, such as statement_timeout. Move them onto the database role instead, which is how I ended up with the role timeout that broke the initial copy (see Before you start).

Put pgbouncer in place as its own change, before migration day, and check it passes traffic through cleanly: SELECT inet_server_port() through pgbouncer should return 5442.

Replication is set up exactly as in method 2. Only the cutover changes.

The cutover

This time I ran it from a script instead of typing it. Here is the timeline:

16:10:02.990  PAUSE                                  (queries start queueing)
16:10:03.119  target caught up, lag 336 bytes         129 ms
16:10:03.199  sequence set on target                  80 ms
16:10:03.274  pgbouncer repointed to the target       75 ms
16:10:03.332  RESUME                                  58 ms

342 ms from PAUSE to RESUME. I expected reloading pgbouncer's config to be the slow part. It took 75 ms.

The steps are the same as method 2. Only the first and last change:

1. PAUSE in pgbouncer's admin console, instead of REVOKE.
2. Wait for the target to catch up.
3. setval the sequences on the target.
4. Change the port in pgbouncer's config to the target, and reload.
5. RESUME, instead of restarting the app.

Method 3 results

READ    attempts 18,342   succeeded 18,342   failed 0   longest gap 613 ms
WRITE   attempts 18,342   succeeded 18,342   failed 0   longest gap 611 ms

Zero failed requests, reads or writes. On the target afterwards: no duplicates, no missing writes, sequence correct.

But I would not call this "zero downtime". The outage still happened. It just showed up as slow requests instead of errors. The slowest ones:

1630 ms, 1529 ms, 1432 ms, 1430 ms, 1399 ms

These are in the order they arrived, and each one is a bit faster than the last. That is the queue emptying: requests that arrived during the pause were all released together at RESUME, and the earliest ones had waited longest.

The slowest request (1.6 s) took longer than the pause (0.34 s). The extra time is a cold cache on the target. It had never served this app before, so the first queries after the switch had to read from disk. That is not downtime, but users would notice it.

The honest summary: zero failed requests, a 342 ms pause, and a peak latency of 1.6 seconds.

The catch: every layer has to wait

A pause only turns into a slow request if everything between the user and the database waits longer than the pause. If any one layer gives up first, the request fails anyway. The shortest timeout decides the result.

I found this out by failing three times. For testing I held the pause for 5 seconds, to make problems obvious:

pause	failed	what gave up first
5 s	81	the client's 2-second request timeout
5 s	77	the app's connection pool (20 connections) ran out, then timed out waiting
5 s	121	pgbouncer's own limit of 100 client connections
1 s	0	nothing (slowest request 910 ms)

In none of these was pgbouncer's queue the problem. Each time, something in front of it gave up before the query reached it.

The settings that gave zero failures:

layer	setting	value
HTTP client	request timeout	15 s
app connection pool	max	200
app connection pool	connectionTimeoutMillis	30 s
app queries	query_timeout	30 s
pgbouncer	max_client_conn	500
pgbouncer	query_wait_timeout	120 s (default)

Connections to pgbouncer are cheap, so a large app pool is fine. pgbouncer keeps its own small pool of real connections to Postgres.

A real cutover pause is well under a second, so you may not need numbers this high. But you do need to check every layer, because the one you forget is the one that fails.

Two more reasons to use a pooler

Several app servers switch at the same moment. Without a pooler, each app server has to be repointed and restarted, and they switch at slightly different times. For a few seconds some are writing to the old database and some to the new one, and there is no clean way to merge the two afterwards. With pgbouncer, no app server holds a database address, so this cannot happen.

Rolling back is one line, and it loses every write that landed on the target. The line is: point pgbouncer back at the source and reload. Replication only runs one way, so nothing written to the target after the switch ever reached the source. If you need a safe rollback, set up replication in the other direction before cutting over. I did not test that.

One more thing to know: a forgotten PAUSE looks exactly like a hung database. If everything is stuck, run RESUME first.

All three at 79 GB

method	writes blocked	failed requests	what the outage was
dump and restore	62 min	36,721	copying the data
replication + app restart	23.2 s	230	app restart, typing
replication + pgbouncer, scripted	342 ms pause	0	slow requests, peak 1.6 s

The first row grows with your data. The other two don't.

Checklist

Before you start:

• Every table has a primary key, or a replica identity index.
• The replication role has no statement_timeout.
• The target has the schema and no rows.
• pgbouncer is already in front of the app, and every timeout between the user and it is longer than your pause.
• max_slot_wal_keep_size is set, and you alert on retained WAL.
• Nobody changes the schema until you are done.

At cutover:

1. PAUSE pgbouncer.
2. Wait until the lag is small and steady.
3. setval every sequence from the target's max(id).
4. Point pgbouncer at the target and reload.
5. RESUME.
6. Check inet_server_port() and that writes are landing on the target.

Script it. The script should give up and RESUME on the source if the target does not catch up within a time limit. Nothing interactive belongs inside the pause: no password prompts, no sudo asking for a password.

Two runs that measured nothing

Before I got the measurement right, two runs gave numbers that looked fine and meant nothing:

• Writes were already blocked when the load generator started. No write ever succeeded, so there was no gap to measure. It reported 0 ms.
• I stopped the load generator before the switch finished. It reported 103 ms, which was just the slowest request before the outage started.

"Longest gap between successes" only means something if the log shows three phases: working, broken, working again. For the same reason, the 175 seconds for dump and restore at 399 MB is a minimum. That log ended before the switch completed.

What I did not test

The target catching up is not guaranteed. My load was about 10 writes a second. At that rate the target always caught up, so there was always a moment when lag was near zero and I could cut over. At production write rates, the target may apply changes more slowly than the source produces them. Lag then grows without limit and that moment never comes. This method does not work until the write rate drops or the target gets faster. This is the failure that kills large migrations, and my setup could not produce it. At scale, watch whether lag is trending down, not just whether it is small right now.

At real scale, migration stops being an event. A sharded system does not have one cutover. It has thousands of small ones, each pausing writes for the users on one shard for a fraction of a second, running all the time in the background for rebalancing and hardware refresh. Something that happens a thousand times a day gets engineered until it is fast and boring. Something done once every three years gets a runbook, a 2 a.m. window, and an apology email. The 342 ms above is the second kind, done carefully.

Caveats

• Both servers ran on one machine and shared one disk.
• About 10 writes a second. WAL retention grows with write rate, so production numbers will be bigger.
• One app instance.
• pg_restore ran without -j, so dump and restore could be faster, though the shape of the comparison would not change.
• Methods 1 and 2 were typed by hand. Method 3 was scripted.
• The filler data compresses far better than real data (13:1), so a real dump file would be bigger.
• Rolling back with reverse replication was not tested.

Postgres 18.6, two local clusters, pgbouncer in transaction mode, Express with node-postgres. Downtime measured from the client side using a log of every request, and checked against a known 20-second stop.