How to migrate a database with minimal downtime

Why this matters

A database migration is the one part of a move where the clock is visible to everyone. Every minute of write freeze is a minute your users get errors, your queues back up and support starts answering tickets. The target is not zero downtime. It is downtime short enough that nobody outside the team notices.

The common mistake is treating the whole migration as the risky window. Most of the work happens while the old database still serves traffic: copying the bulk of the data, replaying changes as they happen, comparing the two sides. The freeze covers only the last few seconds of change capture and the connection string swap. If your freeze is long, you built the plan backwards.

Two migrations look different but follow the same shape. Moving to a new server on the same engine lets you lean on native replication and a similar type system. Moving to a different engine or a managed service means change data capture, type mapping and much more verification. Same engine work takes days. Cross engine work takes weeks of rehearsal.

Keep the old database alive after cutover, read only if you can. That is not waste. It is your rollback, and it is cheaper than restoring from a backup while the site is down.

Before you start

Measure the write profile first. You want rows per second at peak, the largest table by size, the longest running transaction and the busiest hour of the week. A 2 TB table that takes 40 writes a second is an easy migration. A 200 GB database with 3,000 writes a second spread over 40 tables is harder, because replication lag reacts to write bursts, not to size.

List everything that is not table data: roles and grants, extensions, stored procedures, triggers, views, scheduled jobs, foreign servers and sequences. These are the items people forget, and they cause the first hour of pain after cutover.

Decide your freeze budget in minutes and write it down where the product owner can see it. Then design the cutover to fit inside a third of that number. A budget of 10 minutes usually means a real freeze of 2 to 3 minutes, which is enough for a drain, a sequence fix and a config reload.

Check the plumbing. Every replicated table needs a primary key or a replica identity setting. The source needs enough write ahead log retention or binlog retention to survive a pause in replication. The network path must be open on the right port, and the target must have disk throughput to absorb both the snapshot load and the change stream. Rehearse twice on a restored copy and time each phase with a stopwatch.

How to do it

The pattern is always the same: bulk copy, change capture, reconciliation, short freeze, switch, watch. The order matters because each phase shrinks the amount of data the next one has to handle. By the time you freeze, replication should be seconds behind and you should already know the two databases match.

Freeze writes in a way you can reverse in one action. A maintenance flag in your application config is better than revoking database permissions, because it stops writers before they hit the database and it does not leave you with half applied grants to clean up. Put the flag behind your secrets or config service so you do not need a deploy.

After the switch, unfreeze in stages. Turn on internal traffic, or one region, or the read only paths, and watch error rates for a few minutes. Then open everything. Keep reverse replication running, or at least keep the old database available for queries, until you have run a full business day on the new one.

Write the cutover script down as a numbered list with the exact commands and the exact order. During the freeze nobody should be improvising, and two people should have run the script end to end in rehearsal.

1. Snapshot the source into the target. Take a consistent snapshot with pg_dump, mysqldump and single transaction mode, or your provider's native export. Restore it with indexes and foreign keys built after the data load, which is far faster than maintaining them during it. Write down the log sequence number or binlog position the snapshot corresponds to, because that is where change capture starts.
2. Start change capture from that position. Use logical replication on PostgreSQL, a binlog reader such as Debezium for MySQL, or the managed data migration service for your platform. Confirm the stream is running before you relax, and confirm it is actually applying rows, not just connected. Never start capture from the current position instead of the snapshot position, or you will silently lose the changes in between.
3. Watch lag in seconds and bytes. Set up a dashboard and an alert on replication lag, not on job status. A stream that is running but four hours behind looks healthy to a status check. Measure the gap between the source's current position and the last position applied on the target.
4. Reconcile before you freeze anything. Compare row counts, max primary keys and checksums on a stable key range for every table. Fix differences while the source is still live and writes are cheap to replay. Do not freeze until the numbers agree and lag is near zero.
5. Freeze writes and drain the queue. Flip the maintenance flag, stop the background workers and let in flight requests finish. Then wait for replication lag to hit zero and stay there. Confirm the gap closes before moving on, because the next step is irreversible for a few minutes.
6. Move sequences and counters forward. Set every sequence, auto increment counter or identity column above the max id on its table, with headroom for concurrent inserts. Do this after the freeze so no new rows arrive behind you. Check the result with a query that compares each sequence value to its table.
7. Switch the connection string and unfreeze. Change the host, port and credentials in your config or secrets service, then let the pool recycle or restart the app cleanly. Turn traffic back on in stages and watch the error rate. Keep the old database reachable so a rollback is a config change, not a restore.
8. Run reverse replication during the rollback window. If your engine supports it, replicate changes from the new database back to the old one until you are confident. It gives you a real rollback instead of a backup restore. Drop the reverse stream on a fixed date, and write that date down.

How to check it worked

Start with counts and checksums per table. Compare count and max primary key on both sides, then hash a stable key range, for example every row where id is between two values, and compare the results. Hash the whole table only on small tables, because a full hash on a big table can run for hours and hold a snapshot open.

Then check the things that break silently. Compare the next value of every sequence against max id on its table. If the sequence is behind, the first insert after cutover fails with a duplicate key error. Look for tables where the target has no index that the source had, and read the slow query log for the first hour after cutover. Run a write, read it back through a fresh connection, and delete it to confirm the full path works.

Repeat the same checks against the application. Hit the real read paths, not just a ping. Check the error rate, the p95 latency and the connection pool counters. Confirm that background workers, cron jobs and admin tools are pointing at the new host, because they are the usual source of ghost writes to the old database.

Keep watching for a week. Run ANALYZE or the equivalent after the initial load so the planner has fresh statistics, then monitor autovacuum, table bloat and the slow query log. Plans that were fine on the old server can change when the data layout and statistics change.

Mistakes to avoid

Forgetting the sequences is the most common failure. People copy rows perfectly, switch the connection string and then get duplicate key errors on the first order. Set every sequence or auto increment counter above the current max id with headroom before you unfreeze.

Ignoring the other writers is the second. Your application is not the only client. Analytics jobs, admin panels, reporting tools and a second service with its own connection string all write to that database. Inventory them, and either point them at the new host in the same change or stop them for the duration of the freeze.

Long running transactions on the source will pin log retention and stall replication. A report that runs for two hours, or an idle transaction left open by a connection pool, can hold the log position back until the disk fills. Check for them before you start change capture and set a statement timeout on the source.

Avoid schema changes during the replication window. A column added on the source and not on the target will break change capture at the worst moment. Freeze schema, not just writes. Test the rollback path in rehearsal, and do not let dual writes to both databases survive past cutover, because they always drift and nobody owns the cleanup.

If you want help scoping a migration, Cloudverg sells a two week Discovery and roadmap sprint at a fixed price, with the fee credited to phase one if you start within 60 days. It is one option among several, and it suits teams that need the target architecture and the phased plan written down before anyone touches production.

Frequently asked questions