Synthetic MySQL to PostgreSQL reconciliation: counts, checksums, sequences and one failing lookup

What this example is

This is a synthetic rehearsal record for an invented shop database with four tables. The numbers are made up to show the shape of the evidence. No database was moved for this example and no customer is involved. It shows what a proposal should promise to produce, not a result we obtained.

• The source is an invented MySQL 8.4 database; the target is an invented PostgreSQL database.
• Rehearsal figures are taken at a frozen moment, so any difference is a finding, not a live write.

Per-table counts and checksums

Each table has a row count on both sides and, for the largest tables, a checksum over agreed columns. Matching counts say rows arrived; matching checksums say the agreed values did too. The audit_log table is not in the checksum list because it holds free text the owner chose not to compare.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

table      | MySQL rows | PostgreSQL rows | checksum (agreed columns)
customers  |      4,210 |           4,210 | match
orders     |     18,774 |          18,774 | match
coupons    |         61 |              61 | match
audit_log  |    240,115 |         240,115 | not compared

Generated keys and zero dates

After a load, each generated key's next value must exceed the highest existing id, shown by one test insert that is rolled back. The orders.shipped_at column held zero dates in the source, which pgloader's documented defaults convert to NULL. Rows with that value are listed and the owner decides whether NULL means 'not yet shipped' for the application.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

table     | highest id | next sequence value | test insert
customers |      4,210 |               4,211 | ok
orders    |     18,774 |              18,775 | ok
coupons   |         61 |                  62 | ok

orders.shipped_at: 3,902 rows were 0000-00-00 in MySQL; 3,902 are NULL in PostgreSQL.
Decision needed: does the app read NULL as 'not yet shipped'? (owner)

A lookup that fails, and what happens next

The named query 'find coupon by code' returns one row on MySQL for the input spring10 and none on PostgreSQL, where the stored code is SPRING10. MySQL's default collation makes such comparisons case-insensitive and PostgreSQL's ordinary text comparison is not. The record does not call this a pass. The owner chooses between a case-insensitive column type, an index on lowercased values or changing the query, and the choice is written down and tested with mixed-case inputs.

• Every case-insensitive lookup in the application is tested the same way.
• An unexplained difference stops the cutover plan.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

query                    | input    | MySQL rows | PostgreSQL rows | result
find coupon by code      | SPRING10 |          1 |               1 | same
find coupon by code      | spring10 |          1 |               0 | DIFFERENT: decide and retest

What this does not prove

Even a table with every line matching shows only that these rows and these queries agree at one frozen moment. It does not prove that every future query behaves the same, that the load is fast enough or that writes during a cutover will not be lost. Those need the write-freeze plan and the rehearsed reverse step described in the fixed job.

• Use it to judge what a proposal commits to showing.