Stop 06 · Database ServicesTopics · 6/10

Database Migration Basics

In short

Moving a live database is like changing a train's engine while it's running. Done badly, you lose orders or take the app down for a day. Done well, users don't even notice.

Moving data from one database (on-premise, another cloud, or a different engine) into a cloud-managed database — using managed migration services to minimize downtime and data loss risk.

The blob character carries a cardboard box towards a moving truck full of boxes.

Think of it like this

Packers and movers

Good movers label every box, shift the big furniture first and set up the new flat while you still live in the old one. On the last day you just carry the toothbrush across. Database migration works the same way.

Watch it happen

A low-downtime migration

Assess

Schema

Full load

Sync (CDC)

Cutover

1/5List tables, size, engine version, extensions and how much downtime the business can accept.

The big ideas

What you need to know

Homogeneous vs heterogeneous

Same engine (MySQL to MySQL) is simpler. Different engines need schema and code changes too.

Managed tools

AWS DMS, Azure Database Migration Service and Google's Database Migration Service do full load plus ongoing sync.

Plan the downtime

A small database can use dump and restore with a short outage. Big or busy ones need ongoing sync.

Have a way back

Keep the old database untouched until you're confident. Write down how you'd switch back.

See it in code

Walk through the code

bash

1# Small database, short downtime: dump and restore2pg_dump -h old-db.internal -U app -d shop -Fc -f shop.dump3 4pg_restore -h new-db.xxxxx.ap-south-1.rds.amazonaws.com \5  -U app -d shop --no-owner --jobs 4 shop.dump6 7# Sanity check: row counts should match on both sides8psql -h old-db.internal -U app -d shop -c "SELECT count(*) FROM orders;"9psql -h new-db.xxxxx.ap-south-1.rds.amazonaws.com -U app -d shop -c "SELECT count(*) FROM orders;"

1/3Dump in custom format (-Fc), which pg_restore can load in parallel.

Dump and restore suits small databases where a short maintenance window is fine. For large or busy ones, use a migration service with ongoing sync.

Save yourself the pain

Common mistakes

Oops

Doing the first-ever test migration on cutover night.

Do this instead

Rehearse at least once on a copy, timing every step. The rehearsal finds the surprises.

Oops

Deleting the old database the morning after cutover.

Do this instead

Keep it read-only for a while. If a problem shows up days later, you'll be glad it's there.

Your mission

Migrate a toy database

~45 min

Quick check

Why use change data capture (CDC) during a big migration?

Go deeper

1Read a conceptual overview of a managed database migration service's workflow.
2Learn why schema conversion is often the hardest part of a cross-engine migration (e.g. Oracle to PostgreSQL).
3Understand the trade-off between one-time migration and continuous replication during a cutover.