Home / Guides
Tip

Database Migration Checklist, with a Template

By · October 4, 2026 · 9 min read

The short version

A database migration checklist is worth having only if every item names a query you run and a result you compare. Before the move, take an inventory of the source: exact row counts, sizes, character sets and collations, triggers, routines, scheduled jobs, foreign keys and the next value of every sequence. After the copy, run the same inventory on the target, compare row counts and a per-table checksum, reset every sequence to the max of its column, and recreate whatever the copy tool skipped. The template at the end is the list I work from, and every query in it was run for this guide against MySQL 8.4 and PostgreSQL 16.

What I ran this against

A throwaway MySQL 8.4.11 database called database_migration_checklist with three tables: customers (500 rows, city names like Łódź and São Paulo), invoices (20,000 rows, DECIMAL(12,2) amounts, a foreign key and a BEFORE UPDATE trigger), and legacy_notes, deliberately created as latin1 the way old tables often are. I copied it into a fresh PostgreSQL 16.14 database with identity columns, using mysql -B piped into COPY, which is roughly what a hand-rolled migration script does. Then I ran each check below on both sides. The Postgres side was queried through psycopg2, so its output is plain |-separated text instead of psql tables.

Step 1: inventory the source, with exact counts

The first query people reach for is information_schema.tables. It is fine for sizes and collations. It is wrong for row counts:

SELECT table_name, engine, table_rows,
       ROUND((data_length + index_length) / 1024) AS kb,
       table_collation, auto_increment
FROM information_schema.tables
WHERE table_schema = 'database_migration_checklist'
ORDER BY table_name;
+--------------+--------+------------+------+--------------------+----------------+
| TABLE_NAME   | ENGINE | TABLE_ROWS | kb   | TABLE_COLLATION    | AUTO_INCREMENT |
+--------------+--------+------------+------+--------------------+----------------+
| customers    | InnoDB |        500 |   48 | utf8mb4_0900_ai_ci |            512 |
| invoices     | InnoDB |      19323 | 1952 | utf8mb4_0900_ai_ci |          32768 |
| legacy_notes | InnoDB |          2 |   16 | latin1_swedish_ci  |              3 |
+--------------+--------+------------+------+--------------------+----------------+

invoices has 20,000 rows, not 19,323, and that was right after ANALYZE TABLE. The MySQL manual says that for InnoDB TABLE_ROWS is an approximation that can be off by 40% to 50%, and to use SELECT COUNT(*) for an accurate count. PostgreSQL's equivalent, pg_class.reltuples, is also only an estimate used by the planner. Record exact counts:

SELECT 'customers' t, COUNT(*) FROM customers UNION ALL
SELECT 'invoices', COUNT(*) FROM invoices UNION ALL
SELECT 'legacy_notes', COUNT(*) FROM legacy_notes;
+--------------+----------+
| t            | COUNT(*) |
+--------------+----------+
| customers    |      500 |
| invoices     |    20000 |
| legacy_notes |        2 |
+--------------+----------+

The AUTO_INCREMENT column tells a second story: 20,000 invoices, next id 32768. Nothing was deleted. In MySQL 8.4 the default innodb_autoinc_lock_mode is 2, and the manual notes that bulk inserts can reserve more auto-increment values than they use, which leaves gaps. Write the next value down anyway: the target's sequences must start at or above it, or at least above the max id.

Step 2: encodings and collations, per column

The database default says nothing about old tables. Ask per column:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'database_migration_checklist'
  AND character_set_name IS NOT NULL
ORDER BY 1, 2;
+--------------+-------------+--------------------+--------------------+
| TABLE_NAME   | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------+-------------+--------------------+--------------------+
| customers    | city        | utf8mb4            | utf8mb4_0900_ai_ci |
| customers    | name        | utf8mb4            | utf8mb4_0900_ai_ci |
| legacy_notes | note        | latin1             | latin1_swedish_ci  |
+--------------+-------------+--------------------+--------------------+

That latin1 column is the one that produces mojibake or a load error later. On the PostgreSQL side, check what the target database was created with:

SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate, datctype
FROM pg_database WHERE datname = current_database();
datname | encoding | datcollate | datctype
database_migration_checklist | UTF8 | en_US.utf8 | en_US.utf8

Collation changes query results, not only characters. The same lookup on both sides after the copy:

SELECT COUNT(*) FROM customers WHERE city = 'lodz';
MySQL (utf8mb4_0900_ai_ci):  125
PostgreSQL (en_US.utf8):     0

MySQL's _ai_ci collation matched Łódź to lodz; PostgreSQL did not. Any search box, login lookup or unique index that leaned on that behaviour needs a decision before cutover, and a test. If old files are involved, /guides/old-data-odd-encodings-cp1251-cp866-latin-1/ covers the decoding side.

Step 3: the objects a copy tool leaves behind

Tables are the easy part. List everything else:

SELECT trigger_name, event_manipulation, event_object_table, action_timing
FROM information_schema.triggers WHERE trigger_schema = 'database_migration_checklist';
SELECT routine_type, COUNT(*) FROM information_schema.routines
WHERE routine_schema = 'database_migration_checklist' GROUP BY 1;
SELECT COUNT(*) AS events FROM information_schema.events
WHERE event_schema = 'database_migration_checklist';
SELECT table_name, constraint_name, referenced_table_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 'database_migration_checklist';
+----------------+--------------------+--------------------+---------------+
| TRIGGER_NAME   | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_TIMING |
+----------------+--------------------+--------------------+---------------+
| invoices_touch | UPDATE             | invoices           | BEFORE        |
+----------------+--------------------+--------------------+---------------+
+--------+
| events |
+--------+
|      0 |
+--------+
+------------+-----------------+-----------------------+
| TABLE_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME |
+------------+-----------------+-----------------------+
| invoices   | invoices_ibfk_1 | customers             |
+------------+-----------------+-----------------------+

The routines query printed nothing: this database has none.

Then the same on the target:

SELECT tgrelid::regclass AS tbl, tgname FROM pg_trigger WHERE NOT tgisinternal;
SELECT conrelid::regclass AS tbl, conname, contype
FROM pg_constraint WHERE connamespace = 'public'::regnamespace ORDER BY 1, 2;
tbl | tgname
tbl | conname | contype
customers | customers_pkey | p
invoices | invoices_customer_id_fkey | f
invoices | invoices_pkey | p
legacy_notes | legacy_notes_pkey | p

Keys and the foreign key made it, because I wrote them into the DDL. The trigger did not: the trigger query returned no rows, and invoices.updated_at would silently stop changing. A row count would never catch that. If you move MySQL to MySQL with mysqldump, triggers are included by default, while stored routines and events are only dumped when you pass --routines and --events; the flags are covered in /guides/mysqldump-options-for-a-clean-migration/.

Step 4: sequences, the check everyone skips

Loading rows with explicit ids does not move an identity sequence. This is the first insert the application made after my copy:

INSERT INTO customers (name, city) VALUES ('New customer', 'Oslo');
ERROR: duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.

pg_sequences shows why: last_value was empty for all three sequences. The fix is one statement per identity or serial column. pg_get_serial_sequence finds the sequence behind the column, and the two-argument setval makes the next nextval return the value after the one you set:

SELECT setval(pg_get_serial_sequence('customers', 'id'), (SELECT max(id) FROM customers));
INSERT INTO customers (name, city) VALUES ('New customer', 'Oslo') RETURNING id;
setval
500
id
501

The longer story of that error is in /guides/duplicate-key-violates-unique-constraint-after-import/.

Step 5: row counts plus a checksum on both sides

Equal counts prove only that nothing was lost or doubled. To prove the values match, hash every row in a canonical text form, in primary key order, on both engines. PostgreSQL:

SELECT count(*) AS n,
       md5(string_agg(concat_ws('|', id, customer_id, amount, issued, updated_at),
                      E'\n' ORDER BY id)) AS checksum
FROM invoices;
n | checksum
20000 | 4c0c51ad53ee4f11e7c637b1e7785d2b

MySQL, the same expression:

SET SESSION group_concat_max_len = 1024 * 1024 * 64;
SELECT COUNT(*) AS n,
       MD5(GROUP_CONCAT(CONCAT_WS('|', id, customer_id, amount, issued, updated_at)
                        ORDER BY id SEPARATOR '\n')) AS checksum
FROM invoices;
+-------+----------------------------------+
| n     | checksum                         |
+-------+----------------------------------+
| 20000 | 4c0c51ad53ee4f11e7c637b1e7785d2b |
+-------+----------------------------------+

Identical, and the same held for customers (b4b0895db2b2e448d6d7c9069b94eb0f on both), so Łódź and São Paulo survived the trip byte for byte. Do not skip the SET: GROUP_CONCAT truncates its result to group_concat_max_len, which defaults to 1024, and a truncated string still hashes without complaint. The effective maximum is also capped by max_allowed_packet, which is why big tables need the next trick.

When a checksum differs, bucket it. I changed one amount by a cent in PostgreSQL (id = 12345) and ran both sides grouped by id / 5000 (id DIV 5000 in MySQL):

bucket | n | checksum                            MySQL
0 | 4999 | a7fd62a401efa59657dee3ec05874e1c      a7fd62a401efa59657dee3ec05874e1c
1 | 5000 | 138e8c43f86619d9e837292e91aeb63f      138e8c43f86619d9e837292e91aeb63f
2 | 5000 | 9556b5cba611c7a822a505941a41c9e5      20ff4c994ca4c7819a0b15a4354440da
3 | 5000 | 8848160d59e9b71bd6d1ed20134a7c9e      8848160d59e9b71bd6d1ed20134a7c9e
4 | 1    | 895991d1d69f145e4711c39975fca9f4      895991d1d69f145e4711c39975fca9f4

(I put the two outputs side by side; each came from its own query.) Bucket 2 is ids 10000 to 14999, and you keep halving until the row is found. This works across engines only when both sides turn each value into the same text. Here they did: DECIMAL(12,2) and numeric(12,2), DATE and date, and the all-NULL updated_at went through concat_ws identically, which is what the matching hash proves. A float column or a timestamp with a time zone is where I would format explicitly on each side before trusting the hash. Cheap business checks are worth adding next to the hash; SUM(amount) was 9675700.00 on both before my edit. More methods are in /guides/how-to-validate-data-after-a-migration/.

Step 6: statistics after the load

After the copy I ran ANALYZE. The PostgreSQL manual says autovacuum normally analyzes tables when they are first loaded, but it does not process partitioned tables, and I would rather not have the first production queries run while it gets around to it. Afterwards reltuples showed 500, 20000 and 2. Index rebuilds and statistics on other engines are in /guides/update-statistics-and-rebuild-indexes-after-you-migrate/.

The database migration checklist template

Copy this into the ticket. Each line ends with a recorded result.

## Before the migration (source)
- [ ] Exact row count per table with COUNT(*), saved to a file (not TABLE_ROWS / reltuples)
- [ ] Size per table (data + index) to plan disk and copy time
- [ ] Character set and collation per column; list every non-UTF-8 column
- [ ] Case/accent-insensitive collations: lookups and unique indexes tested on the target
- [ ] Triggers, stored procedures, functions, views, scheduled events/jobs: names and counts
- [ ] Foreign keys and check constraints: names and counts
- [ ] Next AUTO_INCREMENT / sequence value and max(id) per table
- [ ] Baseline checksum per table (md5 of rows in PK order) and SUM of money columns
- [ ] Columns with float, timezone or zero dates that need explicit formatting
- [ ] Users, roles and grants the application needs
- [ ] Backup taken and a restore of it actually tested

## During the copy
- [ ] Writes stopped or captured (maintenance window or CDC)
- [ ] Copy tool flags checked: routines, events, triggers included
- [ ] Load errors and rejected rows logged, count = 0 or explained

## After the copy (target)
- [ ] Same inventory queries run on the target, diffed against the source file
- [ ] Row counts match per table
- [ ] Checksums match per table; mismatches bucketed down to the row
- [ ] Triggers, routines and jobs recreated and counted
- [ ] Every sequence / identity set to max(id) with setval; one test insert
- [ ] ANALYZE (or the engine's statistics update) run
- [ ] Application smoke test: login, one read, one write, one report
- [ ] Rollback path written down: how to point the app back at the source

Who signs off, the timeline and the rollback decision belong in the plan rather than in the checklist; /guides/database-migration-plan-a-template-that-gets-used/ has that template. If the source cannot stop taking writes, read /guides/zero-downtime-database-migration-with-cdc/ before choosing a window.

Sources