pgstream is a Go CLI I built for moving large MySQL databases to PostgreSQL. It streams tables in bounded memory, checkpoints progress so an interrupted migration resumes instead of restarting, loads data through the PostgreSQL COPY protocol, and keeps a live web UI over every run. That is the feature list. The more interesting story is what happened when I stopped testing it against demo schemas and pointed it at a real production backup: 83 tables, millions of rows, and a decade of accumulated decisions.
A migration tool that only handles clean data is a demo. Real databases are where the design gets honest.
The data is never what the schema says
Every one of these came from actual rows in that backup, and each one initially stopped a migration cold:
- Zero dates. MySQL happily stores
0000-00-00 00:00:00, a date that does not exist in any calendar and that PostgreSQL rightly rejects. pgstream now migrates them as NULL when the target column allows it, and fails with the exact query to find the offending rows when it does not. - The empty-string enum marker. When an invalid value lands in a MySQL enum outside strict mode, MySQL silently stores
''— a label that exists in no declared enum. pgstream detects it in the data, counts the rows, and adds the label so the copy stays lossless. - UUID-shaped columns that are not UUIDs. A
varchar(36)primary key usually holds UUIDs — until one table stores namespaced string keys instead. Converting by shape corrupts the migration, so pgstream scans the data first and decides per foreign-key-connected group, keeping both ends of every constraint on the same type. - Orphaned foreign-key rows. Rows inserted with constraint checks disabled reference parents that no longer exist. pgstream counts them up front, skips the impossible constraint, and saves the exact DDL for after the data is repaired.
- NUL bytes in text. Legal in MySQL strings, impossible in PostgreSQL text, and previously the cause of a cryptic mid-copy failure. They are now removed with a per-column warning and an inspect query.
- MySQL TIME is secretly a duration. It ranges from -838:59:59 to 838:59:59, which no clock-time type can hold. Out-of-range values now stop the run with a pointer to
--cast 'table.column=interval', which stores the full range. - Unsigned bigint keys. The lossless mapping is
numeric(20), but numeric cannot back an identity sequence — which breaks the single most common auto-increment pattern in modern schemas. pgstream now scans the data, and when a column's foreign-key group provably fits the signed 64-bit range, it maps tobigintinstead.
Fail closed, but hand over the fix
The guiding rule became: never silently substitute a different schema, and never fail with an error the user has to reverse-engineer. Every stop ships its own remediation — the SQL to find the bad rows, the exact ALTER to reconcile a drifted target, the flag that opts into a different mapping.
The same rule produced --dry-run as a real preflight: it does not just translate the schema, it scans the data for everything above and reports blocking issues before a single write happens. A migration that will fail in hour three should say so in second five.
Performance is usually the network
The largest table — about two million rows — initially took 23 minutes while the CLI sat at a few percent CPU. That idleness was the diagnosis: the bottleneck was the wire, not the tool. Enabling MySQL wire compression and pipelining source reads against target writes brought the same table to just under 10 minutes, from roughly 1,500 to 3,500 rows per second, over the same WAN link.
Two habits kept that work honest. First, I measured before optimizing: every completed table now logs a time breakdown showing whether source reads, target writes, or transformation dominated. Second, I published the numbers I actually got — the benchmarks include the configurations where parallel workers barely help, because a single dominant table bounds the wall clock no matter how many workers run in parallel.
The payoff compounded. With five parallel workers on lock-free aligned snapshots, the complete database — all 83 tables, 8.5 million rows — now migrates end to end in ten minutes at about 24,000 rows per second, with zero manual intervention: every safeguard described above fired on real data in that single run, and the wall clock landed within seconds of the largest table's copy time, exactly as the benchmarks predicted.
Standing on pgloader's shoulders
pgloader has a decade of MySQL migrations behind it, so I studied both its documentation and its issue tracker and took what was worth taking: user-overridable cast rules, schema-only and data-only phases, comment migration, and load-time session tuning. Its issues read like a catalog of everything real data does to a migration tool — several of the edge cases above were confirmed there before I hit them myself.
The remaining difference is philosophical. pgloader defaults to recovering around bad rows; pgstream stops and says exactly what to fix. For a one-time production cutover, I want the second behavior — a migration should be boring, inspectable, and reproducible, in that order.
pgstream is open source under GPL-3.0. If you are staring down a MySQL-to-PostgreSQL move with data you do not fully trust, the --dry-run output is the fastest way to find out what your database has been hiding.