Database Migration Checklist (Phase by Phase)

A database migration is not a copy job. It is a controlled move of live data, schema, and dependent applications from one home to another, with the business watching the whole time. This checklist walks the eight phases - from inventory to decommission - that separate a boring, successful cutover from a weekend of firefighting.

Why migrations fail

Migrations rarely fail on the raw byte copy. They fail on everything around it. A team schedules a two-hour window, discovers at hour three that a nightly ETL job holds an undocumented connection to the old host, and now half the reports are stale and nobody can say which half. The common failure modes are predictable, which is exactly why a checklist beats improvisation.

Hidden dependencies. Cron jobs, BI tools, replication slaves, and third-party integrations that nobody inventoried keep pointing at the old database after cutover.
Silent data type coercion. A source DATETIME lands in a target column that rounds sub-second precision, or an unsigned value overflows a signed target. The load "succeeds" and the corruption surfaces weeks later.
Underestimated data volume. The full load takes six hours, not the ninety minutes the test subset suggested, and the maintenance window closes with the migration half done.
No reconciliation. The team assumes a completed load means correct data and never proves it with counts and checksums.
No tested rollback. When something does go wrong, the only plan is "roll forward and hope."

Every phase below exists to close one of these gaps before it becomes an incident. If you are moving a genuinely large system, pair this with our guide on working with large databases, because volume changes which strategies are even viable.

Phase 1 - Assess & inventory

You cannot migrate what you have not measured. The assessment phase produces a written inventory that every later decision references. Skipping it is the single most expensive shortcut in the whole process.

Catalogue the schema and its size

Enumerate every object: tables, views, stored procedures, functions, triggers, sequences, and user-defined types. For each table, record row count and on-disk size, because the biggest tables dictate your load window and your strategy. Most engines expose this cheaply from the catalog rather than a full scan.

SQL

-- Postgres: table sizes and estimated row counts, largest first
SELECT relname          AS table_name,
       n_live_tup       AS est_rows,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM   pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Map dependencies and workloads

List everything that connects: application services, scheduled jobs, replication, analytics pipelines, and ad-hoc report users. For each, capture the connection string, the credentials, and who owns it. Then profile the workload - peak queries per second, read/write ratio, and the longest-running transactions - so you can pick a cutover window that hurts least and size the target correctly. If your target engine differs from the source, review the DDL commands you will need to recreate constraints and indexes exactly.

Inventory tip: the objects that break migrations are the ones nobody remembers - a materialized view feeding a dashboard, a trigger enforcing a rule the app assumes. Grep the codebase for connection strings, not just the database catalog.

Phase 2 - Choose a strategy

There are three broad patterns, and the right choice depends on how much downtime the business tolerates and how much complexity your team can safely operate. Do not default to the fanciest option; a well-run big-bang beats a botched parallel-run every time.

Big-bang. Freeze writes, migrate everything in one window, cut over. Simple to reason about, but downtime scales with data size.
Phased. Move one schema, service, or tenant at a time. Spreads risk and downtime, but you run two systems in parallel for weeks and must route traffic carefully.
Parallel-run (near-zero downtime). Stand up the target, seed it, then keep it in sync with change data capture (CDC) or logical replication until you flip a tiny cutover. Lowest downtime, highest operational complexity.

Strategy	Downtime	Risk	Complexity	Best for
Big-bang	High (full window)	Concentrated	Low	Small/medium DBs, generous window
Phased	Medium (per slice)	Spread out	Medium	Multi-tenant or modular systems
Parallel-run + CDC	Minutes	Low if tested	High	24/7 systems, large volumes

The chart below shows the order-of-magnitude difference in cutover downtime you should plan for. It is directional, not a promise - your numbers depend on data size and network throughput.

Typical cutover downtime by strategy (relative)

Big-bangHours

Phased (per slice)~30 min

Parallel-run + CDCMinutes

Zero and low-downtime techniques

When the business cannot tolerate hours of read-only time, you need a technique that keeps the source serving traffic while the target catches up, then flips in seconds. All three patterns below trade operational complexity for a shrunken cutover window. Do not reach for them unless the downtime budget genuinely demands it - each adds moving parts that must be monitored and tested.

Change data capture (CDC)

CDC reads the source database's write-ahead log (the Postgres WAL, MySQL binlog, or SQL Server transaction log) and streams every insert, update, and delete to the target in near real time. You take a consistent snapshot to seed the target, then let CDC apply the ongoing stream until the two systems are within seconds of each other. The final cutover only needs to drain the last few outstanding events, so the freeze is measured in seconds rather than hours.

Seed then stream. Record the log position at snapshot time, load the snapshot, then replay all changes from that position forward - this guarantees no gap and no double-apply.
Watch replication lag. If the target falls behind under peak write load, your "seconds" window quietly grows. Alert on lag before cutover day.
Handle schema drift. A DDL change on the source mid-migration can break the stream; freeze schema changes once CDC is running.

Dual-write

In a dual-write approach the application writes to both the old and new databases for a transition period. It is conceptually simple but deceptively hard to get right: the two writes are not in one transaction, so a crash between them leaves the systems inconsistent. Dual-write works best as a complement to CDC (write to source, let CDC carry the rest) rather than as the sole sync mechanism. If you do dual-write directly, you need idempotent writes and a reconciliation job to catch the divergences.

Blue-green cutover

Blue-green keeps two complete environments: blue (current) and green (the migrated target kept in sync by CDC). You route all production traffic to blue while green stays warm and validated. Cutover is a single routing change from blue to green, and rollback is the reverse change - instant, because blue is untouched and still current. This is the safest low-downtime pattern precisely because the rollback path is symmetric and already proven.

Low-downtime reality check: "zero downtime" almost always means "a few seconds of write freeze," not literally none. Be honest with stakeholders about the true number, and confirm the application tolerates a brief read-only or reconnect blip.

Phase 3 - Schema conversion

If source and target are the same engine and version, schema conversion is largely mechanical. The moment engines or major versions differ, data type mapping becomes the phase most likely to introduce silent corruption. Convert deliberately, column by column, and never trust an automated converter without review.

Source (MySQL)	Naive target (Postgres)	Gotcha & correct choice
`TINYINT(1)`	`SMALLINT`	Often means boolean - map to `BOOLEAN` if the app treats it that way
`DATETIME`	`TIMESTAMP`	No timezone in either, but check whether you need `TIMESTAMPTZ`
`ENUM('a','b')`	`VARCHAR`	Loses the constraint - use a `CHECK` or a native enum type
`INT UNSIGNED`	`INTEGER`	Values above 2.1B overflow - use `BIGINT`
`TEXT` / `BLOB`	`TEXT` / `BYTEA`	Collation and encoding must match or sorts change
`AUTO_INCREMENT`	`SERIAL`	Reset the sequence to `MAX(id)+1` after load or you get duplicate-key errors

Write the target DDL explicitly rather than accepting a tool's guess. Being precise about types, nullability, and defaults now saves a reconciliation nightmare later.

DDL

-- Target schema, converted deliberately (Postgres)
CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT      NOT NULL,
    status      VARCHAR(20) NOT NULL
                CHECK (status IN ('new', 'paid', 'shipped')),
    is_gift     BOOLEAN     NOT NULL DEFAULT false,
    total_cents BIGINT      NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Create tables and primary keys first, but defer secondary indexes and foreign keys until after the bulk load - building them up front makes the load far slower.

The mappings that cause the most pain

A few categories of type mismatch cause the majority of post-migration data bugs, and they are worth calling out beyond the table above because the failures are silent:

Numeric precision and money. Never store currency in FLOAT or DOUBLE; binary floating point cannot represent 0.10 exactly, so sums drift by cents. Map to DECIMAL/NUMERIC with explicit precision, or store integer minor units (cents). A source DECIMAL(10,2) narrowed to DECIMAL(8,2) on the target will silently truncate the largest values.
Timezones and timestamps. MySQL DATETIME is wall-clock with no zone, while TIMESTAMP is stored as UTC and converted on read. Mapping the wrong one shifts every value by the server's offset. Decide explicitly whether each column is "an instant in time" (use TIMESTAMPTZ) or "a local calendar value" (use TIMESTAMP/DATE).
Character sets and collation. A source in latin1 loaded as utf8 without transcoding corrupts every accented character. Confirm the source encoding, transcode during export, and match collation on the target - a different collation changes sort order and, worse, which rows a case-insensitive UNIQUE constraint considers duplicates.
Booleans and flags. MySQL has no real boolean; TINYINT(1) is a convention. If the app stores 2 or -1 in that column anywhere, a strict BOOLEAN target rejects the row. Audit the actual distinct values before you narrow the type.
Auto-increment sequences. After loading explicit id values, the target's sequence still starts at 1, so the next insert collides. Always reset every sequence to MAX(id)+1 as part of the load, not as an afterthought.
NULL versus empty string versus default. Some engines coerce NULL into an empty string or a zero default on insert. If the target enforces NOT NULL where the source allowed NULL, the load fails; if it silently substitutes a default, your reconciliation checksums will diverge.

The safe habit is to generate the target DDL, then diff it against the source schema field by field and justify every change. Automated schema converters are a starting draft, never the final answer.

Phase 4 - Data migration

Now move the data. The two rules that matter most: load in the right order, and do not fight the database's write path. Bulk-load utilities (COPY, LOAD DATA, bcp) are an order of magnitude faster than row-by-row INSERT statements because they bypass per-row overhead.

Order and constraints

Load parent tables before children, or disable foreign key checks during the load and re-enable them afterward. Drop non-critical indexes before loading and rebuild them after - maintaining a b-tree on every inserted row is pure waste when you are loading millions of rows. Understanding how this interacts with locking is worth reviewing in our notes on transactions and concurrency.

SQL

-- Fast bulk load pattern (Postgres)
ALTER TABLE orders DISABLE TRIGGER ALL;   -- skip FK/trigger checks

COPY orders (id, customer_id, status, is_gift, total_cents, created_at)
FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

ALTER TABLE orders ENABLE TRIGGER ALL;

-- rebuild indexes AFTER data is in, and fix the sequence
CREATE INDEX idx_orders_customer ON orders (customer_id);
SELECT setval(pg_get_serial_sequence('orders', 'id'),
              (SELECT MAX(id) FROM orders));

For large tables, chunk the load by primary key range so a failure restarts from the last committed batch instead of the beginning. Log every batch with its row count; that log becomes your first reconciliation artifact.

Phase 5 - Validation & reconciliation

A finished load is a hypothesis, not a fact. Prove the target matches the source with three escalating checks: row counts, aggregate checksums, and targeted spot checks. Run them before you let a single user touch the new system.

Row counts first

SQL

-- Cheapest check: do the counts match, table by table?
SELECT 'orders' AS tbl, COUNT(*) AS n FROM orders
UNION ALL
SELECT 'customers',    COUNT(*) FROM customers;

Then checksums and set difference

Counts catch missing rows but not altered values. Hash each row and sum the hashes per table, then compare source and target totals - any mismatch means at least one value changed. For high-value tables, run a true set comparison with EXCEPT: if both directions return zero rows, the sets are identical.

SQL

-- Aggregate hash total: run on BOTH source and target, compare
SELECT SUM(('x' || md5(id || '|' || status || '|' || total_cents))::bit(32)::bigint)
FROM orders;

-- Exact set difference: rows in source that are missing/different in target
SELECT id, status, total_cents FROM src.orders
EXCEPT
SELECT id, status, total_cents FROM orders;

Finish with spot checks a human can reason about: the newest ten orders, the largest account balance, a known edge-case customer. If your team runs formal health checks, fold these into the same routine you use for a SQL database health check.

Testing strategy and dry runs

The single best predictor of a calm cutover is how many times you have already done it in a safe environment. Every step above should be scripted, versioned, and rehearsed end to end before it touches production. A migration you have run once in a dry run is an experiment; one you have run three times with the same result is a procedure.

Build a full dress rehearsal

Restore a recent production backup into a staging target and run the complete pipeline against it: schema conversion, bulk load, index rebuild, sequence reset, and the full reconciliation suite. This surfaces the real numbers you cannot guess - how long the load actually takes, which foreign key ordering breaks, where the checksums diverge. Time every phase so your cutover window is based on measured durations, not optimism.

Use production-scale data. A rehearsal against a 1% subset tells you nothing about a load that is I/O-bound at full volume. If the full dataset is impractical, at least test with the largest few tables intact.
Test the rollback path too. A dry run that only proves the forward migration is half a rehearsal. Practice reverting to the source and confirm how long it takes.
Automate the validation. Wrap the counts, checksums, and EXCEPT comparisons in a script that returns pass or fail. On cutover night you want a green light, not a human squinting at row counts at 2 a.m.
Repeat until boring. Run the whole rehearsal enough times that the result is identical and predictable. Surprises in the dress rehearsal are cheap; surprises in production are not.

Green means go: define your go/no-go criteria before the window - reconciliation passes, smoke tests pass, lag under N seconds. If any criterion is red, you do not proceed. Deciding this in advance removes emotion from the call.

Stakeholder communication and cutover runbook

A migration is as much a coordination exercise as a technical one. The teams that hit trouble are usually the ones where the DBA knew the plan but the on-call engineer, the support desk, and the product owner did not. Communication is a phase, not an afterthought.

Who needs to know what

Audience	What they need	When
Engineering & on-call	The runbook, their assigned steps, rollback trigger	Days before + at the bridge
Product / business owners	Window timing, expected impact, go/no-go outcome	Week before + on completion
Support / customer success	User-facing message, known limitations during freeze	Day before
End users	Maintenance notice with start and expected end time	Day before + when done

The runbook

Write the runbook as an ordered, timed list of atomic steps, each with a named owner, the exact command or action, the expected result, and how to verify it. During cutover, someone reads it aloud and marks each step done - no improvisation. Keep a shared timeline document open so everyone sees progress against the plan in real time.

Name an incident commander. One person owns the go/no-go decision and the rollback call, so the team is never debating who decides while the clock runs.
Open a single communication channel. A dedicated bridge or chat room where every step, result, and anomaly is logged. This log is also your post-mortem record.
Set explicit checkpoints. Mark points in the runbook where you pause, confirm the last block succeeded, and get an explicit "continue" before proceeding.
Pre-write the messages. Draft the "maintenance starting," "completed successfully," and "we have rolled back" notices before the window so you are not composing under pressure.

If your team is thin on this kind of coordination, it is a good moment to bring in help to run the bridge while your engineers focus on the database.

Phase 6 - Cutover

Cutover is the short, rehearsed moment when traffic moves. Everything before this was reversible; treat this step as the point of highest care. A written runbook with named owners and time estimates for each line item is non-negotiable.

Freeze writes

Put the source into read-only mode or stop the writing services. Announce the freeze so no one is surprised by a locked application.

Final delta sync

Apply the last batch of changes since your seed load - the CDC catch-up, or a final incremental load of rows changed after the snapshot.

Re-validate

Re-run row counts and checksums on the now-frozen data. This is the last chance to catch a gap before users return.

Switch connections

Flip the DNS record, connection string, or load-balancer target to the new host. Keep the TTL low in advance so the change propagates fast.

Smoke test

Run a scripted set of read and write transactions against the live target: log in, place an order, run a report. Only then lift the freeze.

Watch out: never begin cutover without a tested rollback. "We will figure it out if it breaks" is how a two-hour window becomes a two-day outage. Rehearse the rollback in staging until it is boring.

Phase 7 - Rollback plan

The rollback plan must exist and be tested before cutover, not drafted in a panic during one. It answers a single question: if the target is wrong or unavailable after we flip, how do we get back to a working system, and how fast?

Keep the source intact. Do not decommission or overwrite the old database on cutover day. It is your fastest rollback path.
Define the trigger. Write down the exact conditions - smoke test fails, error rate above a threshold, reconciliation mismatch - that mean "roll back now" instead of "keep debugging."
Preserve new writes. If users wrote to the target before you rolled back, you need a plan to capture and replay those rows to the source, or you lose them.
Set a decision deadline. Agree in advance the latest time you can roll back and still finish inside the window. Past that point you are committed to rolling forward.

A rollback that has only ever existed on paper is not a rollback. Execute it end to end in staging so you know it works and how long it takes.

Phase 8 - Post-migration

The migration is not done when traffic flips; it is done when the new system is stable and the old one is safely gone. The days after cutover are where you turn a working migration into a well-tuned one.

Monitor closely. Watch error rates, slow-query logs, connection counts, and replication lag for the first few days. New engines have different performance cliffs than the one you left.
Refresh statistics and re-tune. Run ANALYZE (or the engine's equivalent) so the optimizer has real distributions. Indexes that were optimal on the old engine may need rethinking; a fresh health check is a good baseline.
Reconcile once more, live. After a day of production traffic, re-run counts on the busiest tables to confirm the application is writing correctly.
Decommission deliberately. Only after a defined stability period - a week or two - take a final backup of the source and retire it. Update runbooks and remove the old credentials so nothing reconnects by accident.

If your team lacks the specialist bandwidth for tuning a freshly migrated system, it can be worth bringing in a SQL developer for the first weeks rather than learning the new engine's quirks under production load.

The master checklist

Here is the whole process as a single sequence. Print it, assign an owner to each phase, and do not skip ahead.

Assess & inventory

Catalogue schema, sizes, dependencies, and workloads. Nothing moves until it is written down.

Choose a strategy

Match big-bang, phased, or parallel-run to your downtime tolerance and operational maturity.

Convert the schema

Map data types deliberately, write explicit DDL, defer secondary indexes and FKs.

Migrate the data

Bulk-load in dependency order with checks disabled, chunk large tables, rebuild indexes after.

Validate & reconcile

Row counts, checksums, and spot checks. Prove correctness before anyone connects.

Cut over

Freeze, final sync, re-validate, switch connections, smoke test, then lift the freeze.

Hold the rollback

Keep the source live and the tested rollback ready until stability is confirmed.

Stabilise & decommission

Monitor, re-tune, reconcile again, then retire the old system with a final backup.

Migrations reward patience and punish improvisation. Work the phases in order, prove each one before moving on, and the cutover itself becomes the least dramatic part of the whole project. If you get stuck, our team is happy to talk through your plan.