What Is Database Migration? A Plain-English Guide

Database migration is the process of moving data from one database to another — along with the structure that gives the data meaning: tables, columns, types, keys, indexes, and constraints. The destination might be a different database engine entirely (MySQL to PostgreSQL), a newer version of the same engine, a different server, or another cloud. The goal is always the same: everything that was true in the old database is still true in the new one, and nothing went missing on the way.

Why teams migrate

• Cost — leaving expensive commercial licenses for open-source engines (the classic Oracle or SQL Server → PostgreSQL move).
• Scale or features — the current engine can't do what the product now needs: JSON queries, full-text search, horizontal scaling, a document model.
• Infrastructure moves — on-premises to cloud, between cloud providers, or consolidating many databases into one.
• End of life — the running version is out of support and upgrading in place isn't an option.
• Environment copies — production data (usually masked) into staging, testing, or analytics systems.

Why it's harder than copying files

Databases aren't files — they're structure plus data plus behavior, and every engine speaks its own dialect. A migration has to translate between them. Three things make that genuinely hard:

• Types don't match one-to-one. One engine's unsigned integer, ENUM, or 38-digit NUMBER has no exact twin on the other side. Every type needs a mapping decision, and bad mappings fail silently — values clip, round, or truncate while the migration reports success.
• Order matters. Foreign keys mean tables can't load in arbitrary order. Auto-increment counters must be reset after load or the first new insert collides with migrated data.
• The source keeps moving. A production database takes writes during the hours your copy runs. Without a plan for catching up, the target is stale the moment it finishes.

Schema migration vs. data migration

One terminology trap: developers also use "database migration" for schema migrations — the small versioned scripts (add a column, create an index) that evolve one database over time, run by tools like Flyway or Django migrations. This guide is about the other meaning: data migration, moving entire databases between systems. Related ideas, very different jobs — schema-migration tools don't move data between engines.

The five phases of a migration

• 1. Assess — inventory the source: tables, types, keys, views, procedures, data volumes. Find what won't convert automatically before it finds you.
• 2. Convert the schema — translate structure into the target's dialect: type mappings, key and index definitions, naming rules.
• 3. Transfer the data — bulk-copy in batches, usually with constraints deferred and indexes rebuilt after load for speed.
• 4. Validate — prove the copy is faithful: row counts per table at minimum; checksums of actual values and foreign-key integrity if you're serious. This is the step teams skip and regret.
• 5. Cut over — point the application at the new database, with a rollback path held open until confidence is earned. Zero-downtime cutovers use change data capture to keep the target synced until the switch.

What can go wrong

The dangerous failures are silent: a decimal that rounded through a float, a timestamp shifted by a timezone, a row that failed to insert and was skipped without a trace. Loud failures get fixed; silent ones ship. That's why validation isn't optional bureaucracy — it's the half of the migration that tells you whether the first half worked. (We wrote a deeper dive: Why Row Counts Aren't Enough.)

Hand-rolled scripts vs. migration tools

Small one-off moves can be scripted by hand. The script grows type-mapping cases, retry logic, batching, and validation until it's a fragile in-house migration tool nobody wants to own. Dedicated tools exist because the hard parts — dialect translation, constraint ordering, resume-on-failure, verification — are the same for everyone and worth solving once, properly.

Where to go next

• Big-Bang, Trickle, or CDC — picking the migration strategy that fits your downtime budget.
• MySQL to PostgreSQL: The Checklist That Actually Matters — the most common migration, step by step.
• Rehearse the Migration — dry runs, quarantine, and keeping the exit open.