Zero-downtime Postgres migration with logical replication: the checklist we actually used

Most advice about moving a production database boils down to "schedule a maintenance window and run pg_dump". That works until customers use your product around the clock and six services write to the same database.

That was our situation. The database behind our own Console (every workspace, project and API key) lived on Amazon RDS, and we wanted it on Prisma Postgres, the managed Postgres we run for our users. We moved 83 tables and 74 GB without pausing writes, and we could have switched back to RDS for a full week afterwards without losing a row.

This is the checklist we'd hand a friend about to do the same thing, plus the SQL that mattered and the one thing that bit us.

Why logical replication and not dump/restore

A dump and restore is a snapshot. Anything written after the snapshot is lost unless you stop writes until the restore finishes. On 74 GB that's not a short pause.

Logical replication is built into Postgres and works differently. The old database publishes row changes, the new one subscribes. The subscriber copies each table once, then keeps applying inserts, updates and deletes as they happen. Your app keeps writing to the old database the whole time, and you switch over when the new one has caught up.

It has rules, though, and every gotcha we hit was one of them.

The pre-flight checklist

We went through each of these before creating anything. Skip one and the migration stalls.

1. Logical decoding is on at the source. Postgres needs wal_level = logical to publish row changes. On RDS you get there with the rds.logical_replication parameter. Ours was already set, but check it, because changing it needs a reboot.

2. Every table has a primary key. The subscriber uses it to find the row an update or delete applies to. This query should return nothing:

SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.oid AND i.indisprimary
  );

3. The target already has the schema. Replication copies rows, not tables. We let our normal CI pipeline run all 204 migrations against the new database, so both sides had identical schemas.

4. The target tables are empty, with one exception you need to plan for. Because CI had already migrated the new database, its migration history table was full. Copying the old one on top would fail on duplicate keys. So we published every table by name and left that one out. If you use any migration tool that tracks history in a table, you'll have the same issue.

5. The new database can reach the old one. The subscriber opens the connection, not the other way round. Our RDS security group blocked it. We added the inbound rule in a separate security group so we could delete it cleanly afterwards, then tested the path with dblink before going further.

6. The target stays awake. Ours pauses with no client connections, and a replication stream doesn't count, so a tiny service pinged it every few seconds during the copy.

7. You know where the bytes are. Two tables held about three quarters of the data. The biggest was a log of webhook deliveries with no retention policy. A 30-day retention job before the copy would have saved a lot of waiting. We added it afterwards, naturally.

The SQL that did the work

Here's the condensed version. Hostnames and passwords are placeholders.

On the source, create a dedicated replication login and a publication that lists tables explicitly:

CREATE ROLE migration_repl WITH LOGIN PASSWORD '<password>';
GRANT rds_replication TO migration_repl;
GRANT USAGE ON SCHEMA public TO migration_repl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_repl;

DO $$
DECLARE t text;
BEGIN
  SELECT string_agg(quote_ident(tablename), ', ')
    INTO t
  FROM pg_tables
  WHERE schemaname = 'public'
    AND tablename <> '_prisma_migrations';
  EXECUTE 'CREATE PUBLICATION app_migration FOR TABLE ' || t;
END $$;

On the target, subscribe. This creates the slot on the source and starts the initial copy:

CREATE SUBSCRIPTION app_migration
  CONNECTION 'host=<source> dbname=app user=migration_repl
              password=<password> sslmode=require'
  PUBLICATION app_migration
  WITH (copy_data = true, create_slot = true);

Then watch three things: per-table state on the target, error counters on the target, and how much WAL the slot is holding on the source.

-- target: r = ready
SELECT srsubstate, count(*) FROM pg_subscription_rel GROUP BY 1;
SELECT apply_error_count, sync_error_count FROM pg_stat_subscription_stats;

-- source
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
FROM pg_replication_slots;

All 82 tables reached ready the same day, with both error counters at zero.

Verifying without fooling yourself

Run ANALYZE on the target first. The initial copy doesn't leave planner statistics behind, and your first real queries will plan badly without them.

Then we used two checks. First, one query (through dblink) counted rows in every table on both sides and returned only the tables that differ. An empty result is the signal you want, and it's much easier to trust than scrolling through 82 matching numbers.

Second, an md5 over full rows for a few tables:

SELECT md5(string_agg(x::text, '|' ORDER BY id)) FROM "Organization" x;

Hashes matched on slow-moving tables and differed on two tables touched on every login, simply because the two queries ran seconds apart. Expect that, and don't panic.

The cutover: one merge, every connection

Our rule was that every service, and every connection path inside each service, switches in the same deploy. If one path moves and another doesn't, a service can write a row to one database and then fail to read it from the other.

We made that boring on purpose:

1. Days before, every service learned to read its connection string from a new secret name, falling back to the old one. No behavior change.
2. Minutes before, we created the new secrets. Running services don't pick them up until redeployed, so still no change.
3. We merged one pull request, and the deploy rolled every service onto the new database.

For a few minutes during the deploy, some services wrote to the old database and some to the new one. Forward replication kept carrying old-side writes across. A key collision would show up as an apply error (it stayed at zero). Two sides updating the same row would not show up anywhere, which is why we cared so much about keeping that window short. The last write to RDS landed seven minutes after the merge.

The rollback we kept for a week

This is the part we'd recommend most. After cutover, we turned replication around so the old database followed the new one:

1. Minutes before the merge, create a logical replication slot on the new database. It retains every change from that moment, so the rollback covers the cutover itself. Don't create it days early: an unread slot was holding roughly a gigabyte of WAL per hour for us.
2. Once the old side is quiet (we sampled its write counters for sixty seconds), drop the forward subscription.
3. Subscribe the old database to the new one from that slot with copy_data = false, since it already has every row, and origin = none, so only the application's own writes flow back instead of echoing the replicated ones.

Going back would have meant reverting one commit. After a week of daily row count checks, we dropped the reverse subscription (outside a transaction block, since a slot was attached), snapshotted RDS and shut it down.

The thing that bit us

Logical replication does not replicate schema changes. No ALTER TABLE, no DDL at all. While replication runs in either direction, every migration has to run on both databases.

We knew that. We had one CI migration job per database. But one of our cutover changes accidentally pointed the old database's job at the new one. Fifty-two minutes after cutover, a migration added three columns on the new side only, and the reverse stream stopped at the first row that used them. We applied the same SQL to RDS by hand about fifteen minutes later, and the stream caught up with nothing lost.

The lesson is boring and real: before changing a connection setting, list everything that uses it, including CI jobs.

If we did it again

We'd keep: tables published by name, a dblink connectivity test before subscribing, mismatch-only verification queries, one deploy for every connection path, and a reverse replication rollback.

We'd change: audit who uses each connection string first, and add retention to noisy tables before copying.

We also paired with an AI coding agent: it drafted every query and read the results, and a human ran every statement that touched production. That split worked well.

We wrote up the full story, with every SQL file and a step-by-step timeline, here on our blog. And if you want somewhere to practice this on a real database, managed Postgres hosting from Prisma has a free plan with no card. It covers Prisma Postgres with 200,000 operations a month and 2 GB of storage, plus Prisma Compute with 1M requests, 360 GB-hours of memory, 4 active vCPU-hours and 10 GB of outbound bandwidth a month.

Questions about any step? Ask in the comments.