The short version
A zero downtime database migration copies the data while the application keeps writing, streams every later change to the new database with change data capture (CDC), and then cuts over in a window of seconds: stop writes on the old database, wait until the replication lag is zero, compare counts and checksums, reset the sequences on the new side, and point the application at it. In PostgreSQL the CDC part is built in as logical replication (CREATE PUBLICATION and CREATE SUBSCRIPTION), but it needs wal_level = logical, which only takes effect after a server restart. Where you cannot restart, a trigger that writes each change to a log table does the same job at a measurable cost to writes. Keep a reverse change stream to the old database as your rollback.
Zero downtime migration in practice: a write pause of seconds
"Zero downtime" means reads never stop and writes stop only for the cutover: the time between the last write on the old database and the first on the new one. Microsoft describes the same online pattern: applications keep running during the copy, replication follows, and a cutover gives "minimal downtime" (Azure: migration service overview).
I ran the whole sequence on PostgreSQL 16.14: a source and a target database, an orders table with 200,000 rows, and four threads acting as the application, writing the whole time.
Logical replication needs wal_level = logical, and the QA server had replica
The PostgreSQL way is two statements, from the logical replication quick setup: a publication on the old database, a subscription on the new one. The subscription takes a snapshot of the published tables, copies them, then applies changes as they commit (PostgreSQL: Logical Replication). The publisher must have wal_level set to logical (Configuration Settings). On my server:
=> SHOW wal_level
[('replica',)]
=> CREATE PUBLICATION orders_pub FOR TABLE orders
CREATE PUBLICATION
WARNING: wal_level is insufficient to publish logical changes
HINT: Set wal_level to "logical" before creating subscriptions.
=> CREATE SUBSCRIPTION orders_sub CONNECTION 'host=localhost port=5432 dbname=..._src user=postgres password=...' PUBLICATION orders_pub
ProtocolViolation: could not create replication slot "orders_sub": ERROR: logical decoding requires wal_level >= logical
=> SELECT pg_create_logical_replication_slot('orders_slot', 'test_decoding')
ObjectNotInPrerequisiteState: logical decoding requires wal_level >= logical
=> SELECT pg_create_logical_replication_slot('orders_slot', 'pgoutput')
ObjectNotInPrerequisiteState: logical decoding requires wal_level >= logical
=> SELECT name, setting, context FROM pg_settings WHERE name = 'wal_level'
[('wal_level', 'replica', 'postmaster')]
replica is the default, and the parameter "can only be set at server start" (PostgreSQL: Write Ahead Log settings), which is what context = postmaster says. The publication is accepted with only a warning, so a script that checks for errors carries on. The server is shared, so I could not restart it, and the test_decoding and pgoutput slot functions are blocked by the same setting.
Check this on your source weeks ahead: the restart is the one step that needs downtime. On Amazon RDS it is the rds.logical_replication parameter, which AWS calls a static parameter that "requires a reboot of the DB instance to take effect" (AWS DMS: PostgreSQL as a source).
CDC without logical decoding: a trigger and a change log
So for this run the CDC is trigger-based: every insert, update and delete on orders writes a row to cdc_log in the same transaction, with the full new row as JSON. This is the auditing pattern from the PL/pgSQL trigger docs, plus the transaction ID:
CREATE TABLE cdc_log (
seq bigserial PRIMARY KEY,
xid xid8 NOT NULL DEFAULT pg_current_xact_id(),
op char(1) NOT NULL,
id bigint NOT NULL,
row_data jsonb,
captured_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE FUNCTION cdc_capture() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO cdc_log (op, id) VALUES ('D', OLD.id);
ELSE
INSERT INTO cdc_log (op, id, row_data) VALUES (left(TG_OP, 1), NEW.id, to_jsonb(NEW));
END IF;
RETURN NULL;
END $$;
CREATE TRIGGER orders_cdc AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION cdc_capture();
The price is paid by every write on the source. A 100,000-row INSERT ... SELECT, three runs each, on a copy of the table without and with the trigger:
bench bench_plain: 100000 inserts in 0.18s
bench bench_plain: 100000 inserts in 0.14s
bench bench_plain: 100000 inserts in 0.14s
bench bench_cdc: 100000 inserts in 0.84s
bench bench_cdc: 100000 inserts in 0.78s
bench bench_cdc: 100000 inserts in 0.81s
About five times slower for a bulk insert on this box. I did not measure single-row writes separately; time your own workload with the trigger on a staging copy before installing it in production. Logical replication reads the WAL instead and adds no trigger to the write path.
Initial copy without losing changes made during the copy
The trap is the boundary. A change that commits while the copy runs must be applied once, including one whose transaction was still open when the copy started. seq does not settle it: it is taken when the row is written, and transactions commit in a different order.
The fix is to record the snapshot the copy reads from. In one REPEATABLE READ transaction on the source:
SELECT pg_current_snapshot(); -- 44885:44885:
COPY orders TO STDOUT; -- streamed into COPY orders FROM STDIN on the target
Then remove the log rows whose transaction the copy already saw:
DELETE FROM cdc_log WHERE pg_visible_in_snapshot(xid, '44885:44885:');
pg_visible_in_snapshot answers whether a transaction "was completed before the snapshot was taken" (PostgreSQL: System Information Functions). Everything left in the log gets applied. The writers ran throughout:
[ 6.1s] initial copy: 200727 rows from snapshot 44885:44885: in 0.6s
[ 6.1s] dropped 1793 log rows already inside the snapshot
Those 1,793 changes were captured before the copy started and are already in the 200,727 copied rows.
Applying live changes and measuring lag
The applier reads the log in seq order, upserts row images into the target with INSERT ... SELECT * FROM jsonb_populate_record(NULL::orders, $1) ON CONFLICT (id) DO UPDATE, deletes by key for D, commits, then deletes the applied rows from cdc_log. A crash between the two commits replays a batch, which full-row upserts tolerate. Two writes to the same row are ordered by the row lock, so their seq values are in commit order.
Lag is two numbers from the log:
SELECT count(*) AS backlog_rows,
extract(epoch FROM clock_timestamp() - min(captured_at)) AS oldest_change_s
FROM cdc_log;
I started the applier ten seconds late, to stand in for a copy that takes hours on a real table, and let it catch up while the four writers kept going:
time backlog_rows oldest_change_s applied_total app_writes
[ 16.3s] 10646 10.44 500 12965
[ 17.4s] 8671 7.62 4000 14496
[ 18.5s] 6295 5.06 8000 16123
[ 19.6s] 4766 3.32 11000 17604
[ 20.6s] 2731 1.89 14500 19075
[ 21.6s] 1024 0.87 17500 20379
[ 22.6s] 3 0.00 19930 21795
[ 23.6s] 3 0.00 21422 23295
...
[ 38.7s] 2 0.00 43044 45100
[ 39.7s] 2 0.00 44571 46642
The application did about 1,450 writes a second. The backlog of 10,646 changes drained in six seconds, then sat at a handful of rows committed just before each sample. A backlog that never drops means the applier is slower than the writers, and no cutover window will be short.
How do you measure lag with PostgreSQL logical replication?
With a real subscription the same two numbers come from the server. On the publisher, pg_stat_replication.replay_lag is the time between flushing WAL locally and the subscriber reporting it applied (PostgreSQL: Cumulative Statistics). For bytes, compare the slot's position with the current WAL position:
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS bytes_behind
FROM pg_replication_slots;
confirmed_flush_lsn is how far the slot's consumer "has confirmed receiving data" (pg_replication_slots) and pg_wal_lsn_diff returns the difference in bytes (System Administration Functions). I could not run these here: no slot can exist at wal_level = replica. After the writes stop, the cutover check is bytes_behind = 0.
The cutover sequence
The order I ran, with the output. Put it in the runbook as numbered steps with an owner, next to the database migration checklist.
- Stop writes on the source. New sessions become read-only, then the application's open sessions are ended (database names shortened):
ALTER DATABASE src SET default_transaction_read_only = on;
SELECT count(pg_terminate_backend(pid)) FROM pg_stat_activity
WHERE datname = 'src' AND application_name = 'app';
default_transaction_read_only only sets the default for new transactions (Client Connection Defaults), and any session can switch it off again. It stops a well-behaved application and nothing more. The application saw:
(40.0, 'src', 'OperationalError', 'server closed the connection unexpectedly')
(40.1, 'src', 'ReadOnlySqlTransaction', 'cannot execute INSERT in a read-only transaction')
(40.1, 'src', 'ReadOnlySqlTransaction', 'cannot execute UPDATE in a read-only transaction')
(40.2, 'src', 'ReadOnlySqlTransaction', 'cannot execute DELETE in a read-only transaction')
The application has to retry or queue these; that is the window users feel.
- Wait for lag 0:
[ 40.0s] backlog 0 after 0.01s; applied total 45029
- Verify. Count, sum and an order-independent checksum of every row, on both sides:
source: count, sum(amount), checksum = (219122, Decimal('207706192.66'), Decimal('126328334092529222431920'))
target: count, sum(amount), checksum = (219122, Decimal('207706192.66'), Decimal('126328334092529222431920'))
The checksum is sum(('x' || substr(md5(o::text), 1, 15))::bit(60)::bigint) over the table. Both databases are on the same server here, so the row text is identical; across engines it takes more care, covered in how to validate data after a migration. On a large table a full checksum may not fit the window: run it before cutover, and at cutover check counts and the recently changed key ranges.
- Resync sequences on the target before any application write. Nothing fails until the first insert:
target orders_id_seq before: (1, False)
probe insert: UniqueViolation duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(1) already exists.
setval -> 223659
The copy wrote explicit ids, so the target's sequence never moved. Logical replication has the same gap: "Sequence data is not replicated", and before a switchover "the sequences would need to be updated to the latest values" (Logical Replication Restrictions). The fix, per table, is SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders)); after the two-argument setval the next nextval returns the value plus one (Sequence Manipulation Functions).
- Switch the application to the target:
[ 40.4s] app pointed at target
[ 40.4s] writes blocked for 0.41s in total
That 0.41 seconds is my harness, where switching meant changing one variable. A real switch of connection string, DNS name or pooler target adds its own time to the window.
Schema changes do not travel by either method. The restrictions page says "The database schema and DDL commands are not replicated", so freeze DDL from the initial copy to cutover.
Rollback plan: a reverse change stream, ready before cutover
Restoring a backup loses every write made on the new database. Instead, install capture on the target before the switch, so each write after cutover is logged for the trip back. I did, let the application write to the target for five seconds, then ran the drill: stop writes on the target, disable the capture trigger on the source so the replayed changes do not loop back into its own log, apply the target's log to the source, reset the source's sequence:
[ 45.4s] app writes on target: 5262; errors total 28
[ 45.4s] ROLLBACK DRILL: stop writes on target, replay its log into the source
[ 48.1s] replayed 5215 changes back to source
source: count, sum(amount), checksum = (221279, Decimal('208537947.85'), Decimal('127607096499870559836695'))
target: count, sum(amount), checksum = (221279, Decimal('208537947.85'), Decimal('127607096499870559836695'))
5,215 log rows for 5,262 statements: an update or delete of an id that no longer exists changes nothing and fires no row trigger.
With logical replication the reverse stream is a publication on the new database and a subscription on the old one, created with copy_data = false because the data is already there (CREATE SUBSCRIPTION). Each subscription keeps a replication slot on its publisher, and a slot nobody drops will "continue to reserve WAL and might eventually cause the disk to fill up" (Logical Replication: Subscription). Decide in advance how long the old database is kept in sync and who can call a rollback; after that, drop the subscription and check pg_replication_slots on both servers.
AWS DMS and Azure for managed migrations
Not run; this is what their documentation says. AWS DMS has a "full load plus CDC" task type that "migrates existing data and then updates the target database based on changes to the source database". For a PostgreSQL source it "sets up logical replication slots and uses the test_decoding plugin", and it notes that DMS CDC "does not provide real-time replication" and that there "are no SLAs for CDC latency" (AWS DMS: ongoing replication). Its PostgreSQL source page says CDC is supported for tables with primary keys, and that without one DMS cannot apply updates unless you change the table's replica identity (AWS DMS: PostgreSQL as a source).
On Azure, PostgreSQL moves to the migration service in Azure Database for PostgreSQL, which Microsoft compares with Azure Database Migration Service (Classic), noting it has no equivalent of Classic's 1-TB data limit. It lists online migration from on-premises, Azure VMs, Amazon RDS and Aurora, and Google Cloud SQL (Azure: migration service overview). Online mode uses logical replication with pgoutput and needs publication rights on the source; a table with no primary key and the default replica identity cannot replicate updates or deletes (Azure: required permissions).
Both leave the cutover itself to you, with the same steps as above.
Sources
- PostgreSQL: Logical Replication
- PostgreSQL: Logical Replication Quick Setup
- PostgreSQL: Logical Replication Configuration Settings
- PostgreSQL: Logical Replication Restrictions
- PostgreSQL: Logical Replication Subscription
- PostgreSQL: CREATE SUBSCRIPTION
- PostgreSQL: Write Ahead Log settings
- PostgreSQL: Client Connection Defaults
- PostgreSQL: Cumulative Statistics System
- PostgreSQL: pg_replication_slots
- PostgreSQL: System Administration Functions
- PostgreSQL: System Information Functions
- PostgreSQL: Sequence Manipulation Functions
- PostgreSQL: Trigger Functions in PL/pgSQL
- AWS DMS: Creating tasks for ongoing replication
- AWS DMS: Using a PostgreSQL database as a source
- Azure: Migration service in Azure Database for PostgreSQL
- Azure: Required permissions for the migration service