Every migration plan says "validate the data." Very few say how, and the gap shows at sign-off, when someone asks "are we sure PostgreSQL has the same data as MySQL?" and the honest answer is "the row counts match." Row counts catch missing tables and truncated loads. They do not catch a mangled charset conversion, a timestamp shifted by a timezone, a float that rounded differently, or a CDC pipeline that dropped every update to one column.
Validating across two different engines is genuinely harder than validating within one, because the engines disagree about representation in ways that produce false alarms. Most of the work is not comparing the data; it is normalizing the data so a comparison means something. These are my field notes on doing that for MySQL 8.x to PostgreSQL 16/17 migrations, whether the copy came from pgloader, a CDC pipeline, or a custom loader.
Start with row counts, done honestly
Counts are cheap and catch the gross failures, so run them, but run them correctly. Counting a live source while the copy proceeds produces numbers that never match and prove nothing. Either count during a write freeze, or count a bounded slice both sides agree on, rows with an id below a recorded watermark, or created before a recorded timestamp.
Two cautions. Do not use table statistics as counts: MySQL's information_schema TABLE_ROWS is an estimate for InnoDB, and PostgreSQL's reltuples is an estimate too. Use real SELECT count(*) queries, expensive as they are; this is a migration, correctness outranks convenience. And count every table, generated from the catalog, not from memory, because the table someone forgot to include in the pipeline is exactly the one a hand-written list also forgets.
-- PostgreSQL: generate exact counts for every user table
SELECT format(
'SELECT %L AS table_name, count(*) FROM %I.%I;',
c.relname, n.nspname, c.relname
)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname = 'public'
ORDER BY c.relname;Checksums: the idea and the trap
The scalable way to compare content is to hash it: compute a digest per row from its columns, aggregate the digests per chunk of rows, and compare chunk values across engines. Equal chunks need no further attention; unequal chunks get drilled into. This is how you validate a billion rows without shipping a billion rows across the network.
The trap is that any order-dependent aggregation, concatenating hashes in sequence, for example, requires both engines to order rows identically, and MySQL and PostgreSQL do not sort strings the same way. MySQL's default utf8mb4 collations are case- and accent-insensitive; PostgreSQL's collations come from ICU or libc and sort differently, and neither engine guarantees a stable order for equal keys anyway. Two correct copies of the same data can checksum differently purely because of sort order.
The fix is to make the aggregation order-independent: sum (or xor) the per-row hashes instead of concatenating them. Summation is commutative, so row order stops mattering. Take a fixed-width prefix of an MD5 as an integer and sum it per chunk, with chunks defined by id range so both sides slice identically.
Normalize before you hash
The per-row hash input is where cross-engine correctness lives, because the same stored value renders differently by default in each engine. The traps, in the order they usually bite:
- Timestamps and zones. MySQL TIMESTAMP columns render in the session time_zone; PostgreSQL timestamptz renders in the session TimeZone. Set both sessions to UTC and format explicitly to a fixed pattern on both sides. MySQL DATETIME carries no zone, so its correctness depends on the conversion decision made during migration; validate it against that decision, not against a guess.
- Floats. FLOAT and DOUBLE values format differently across engines even when the stored bits match, and if the migration changed precision they may not match bit-for-bit at all. Round to a fixed number of decimals before hashing, or exclude float columns from the hash and compare them separately with a tolerance. Money in floats is its own pre-existing emergency; validate those columns explicitly.
- NULLs versus empty strings. Concatenation helpers that skip NULLs make a NULL column and a missing column hash identically. Coalesce every nullable column to an explicit sentinel token so NULL, empty string, and absent are three different inputs.
- Booleans. tinyint(1) renders as 0/1; PostgreSQL boolean renders as t/f. Normalize both to 0/1 text.
- Trailing spaces and case. CHAR padding and pad-space collations can make MySQL treat 'abc' and 'abc ' as equal in comparisons while the stored bytes differ. Hash the raw value, but expect and investigate diffs on CHAR columns rather than panicking.
Here is the pattern, MySQL side then PostgreSQL side, hashing a chunk of a users table:
-- MySQL (session time_zone = '+00:00')
SELECT id DIV 100000 AS chunk,
count(*) AS n,
sum(cast(conv(left(md5(concat_ws('|',
id,
coalesce(email, '~NULL~'),
coalesce(status, '~NULL~'),
coalesce(date_format(created_at, '%Y-%m-%d %H:%i:%s'), '~NULL~'),
coalesce(cast(is_active AS char), '~NULL~')
)), 8), 16, 10) AS unsigned)) AS chunk_hash
FROM users
GROUP BY id DIV 100000;-- PostgreSQL (SET TimeZone = 'UTC')
SELECT id / 100000 AS chunk,
count(*) AS n,
sum(('x' || left(md5(concat_ws('|',
id::text,
coalesce(email, '~NULL~'),
coalesce(status, '~NULL~'),
coalesce(to_char(created_at AT TIME ZONE 'UTC',
'YYYY-MM-DD HH24:MI:SS'), '~NULL~'),
coalesce((is_active::int)::text, '~NULL~')
)), 8))::bit(32)::bigint) AS chunk_hash
FROM users
GROUP BY id / 100000;Note the deliberate choices: integer division defines identical chunks, both sessions pin UTC, timestamps go through explicit format strings, booleans normalize to integers, and every nullable column gets a sentinel. Getting one of these wrong produces a wall of false mismatches; expect a calibration round where the first diffs you chase turn out to be normalization bugs in the validator, not data bugs in the copy.
Sampling: the check checksums cannot do
Checksums prove the columns you hashed match. They say nothing about columns you skipped, and hashing every column of a wide table is often impractical. So pair chunk checksums with row sampling: pull a few thousand random primary keys per table, fetch the complete row from both engines, and compare field by field in application code, where you can apply per-type comparison rules, tolerance for floats, structural comparison for JSON, byte comparison for binary.
Bias the sample deliberately. Pure random sampling over-represents the boring middle. Include the oldest rows, where charset history lives, the newest rows, where pipeline lag or cutover races live, and rows with NULLs, maximum-length strings, and non-ASCII content. The pathological rows are the point of sampling; a uniform sample mostly re-proves what the checksums already proved.
Reconciliation as a job, not an event
If you are dual-running with CDC before cutover, validation cannot be a one-time gate, because the data keeps moving. Turn it into a scheduled reconciliation job: re-checksum recent windows, rows with updated_at in the last hour, on a rolling basis, plus a slow full sweep that covers every chunk of every table over a few days. Persist the results with timestamps so you can chart mismatch counts trending to zero, and alert when a previously clean chunk goes dirty, which is how you catch a pipeline bug within hours instead of at cutover.
The same watermark discipline applies as with counts: only reconcile rows old enough that the pipeline has certainly processed them, or lag shows up as phantom mismatches. During the final write freeze of a zero-downtime cutover, the reconciliation job becomes the sign-off gate: drain the pipeline, run the full sweep, and require zero mismatches before the application repoints.
What sign-off should actually say
The deliverable of validation is a statement precise enough to be falsifiable: every table's counts matched at watermark X; chunk checksums matched for these tables over these columns with these normalization rules; N sampled rows per table compared clean; these known exceptions exist and are accepted, with owners. That last category is real, most migrations accept a few, like a legacy table with corrupt charset data that was wrong in MySQL too. Writing exceptions down is what separates a decision from a surprise. My migration review checklist has the fuller sign-off structure this slots into.
Day one on PostgreSQL: MonPG picks up where validation stops
Validation proves the data arrived intact. It says nothing about how the workload behaves once real traffic hits the new engine, and that question dominates the first weeks after cutover. Queries that MySQL served happily can pick different plans under PostgreSQL, and without history you cannot tell drift from normal.
MonPG monitors PostgreSQL only, and it is built for exactly this moment: install it before cutover so pg_stat_statements history, wait events, lock chains, autovacuum behavior on the freshly loaded tables, and connection patterns are baselined from hour one. Then your reconciliation job proves the data stayed correct while MonPG proves the workload stayed healthy, and the migration retro cites evidence for both. The PostgreSQL monitoring guide covers the baseline signal set worth having in place before the traffic arrives.