Moving a live database to a new home — different engine, different region, different account, different schema — without downtime. Three patterns dominate; pick by how much downtime you can tolerate and how much application change you’re willing to ship.
Pattern 1 — Snapshot + replay
How: snapshot the source at time T. Restore to target. Replay the source’s transaction log from T forward until target catches up. Cut over.
Tools: AWS DMS (Full Load + CDC), native logical replication, MySQL binlog + tools like Maxwell/Debezium.
Pros:
- Application doesn’t change — it points at one database before, another after.
- Cutover is fast (seconds) once CDC lag is zero.
Cons:
- Requires the source to expose a usable change stream (binlog, WAL, etc.).
- CDC has to keep up with source’s peak write rate, indefinitely.
- Schema must be compatible; structural changes need an extra step.
Best for: moves where the target is the same shape as the source (lift-and-shift, region migration, account migration).
Pattern 2 — Dual write
How: the application writes to both old and new databases for every transaction. Reads still go to the old. Backfill historical data once. Verify reads return the same answer from both. Flip reads to the new database. Stop dual-writing.
Pros:
- No CDC infrastructure needed.
- Easy to verify (read both, diff).
- Rollback is trivial — just flip reads back.
Cons:
- Application code change.
- Doubled write load.
- Inconsistency window: a write to old that fails on new (or vice-versa) needs handling — fail the whole transaction, queue the laggard, etc.
- Distributed-transaction problem in disguise.
Best for: schema-changing migrations where you control the application code.
Pattern 3 — Backfill + tail
How:
- Backfill — copy existing data from source to target with a batch job (one-time).
- Tail — set up a CDC stream for changes after the backfill cutoff.
- Once tail is real-time, cut reads/writes over.
This is what DMS does internally; you can also build it yourself with custom backfill + Debezium/Kafka tail.
Pros:
- Decouples bulk move from ongoing replication.
- Backfill can be parallelised hard, tail handles the steady state.
Cons:
- Two systems to monitor (the backfill job and the tail).
- Boundary handling — the tail must start exactly at the timestamp the backfill froze, or you double-write or miss writes.
Best for: large one-time migrations where downtime is unacceptable.
Comparison
| Downtime | App change | Risk | Tooling cost | |
|---|---|---|---|---|
| Snapshot + replay | Seconds | None | Low | Medium (DMS / CDC tooling) |
| Dual write | Zero | Yes | Medium (consistency) | Low |
| Backfill + tail | Seconds | None | Medium (boundary) | High |
Cross-cutting concerns
- Schema drift during migration — freeze schema changes on the source for the migration window, or replicate DDL too (harder, sometimes impossible).
- Sequences and identity columns — bump target sequences past highest migrated values before resuming writes.
- Foreign keys / triggers — disable during bulk load, re-enable after.
- Validation — run row counts and content diffs (e.g.,
pg_comparator, DMS validation, custom checksums) before cutover. - Rollback plan — what happens if the target misbehaves an hour after cutover? Keep the source running and writable for at least a day.
See also
References
- Pattern: Strangler Fig (Martin Fowler) — applies to data migrations too
- Debezium — open-source CDC