MySQL to PostgreSQL: the decisions a data copy does not make for you

What a loader does and what it leaves to you

The open-source loader pgloader converts the schema, copies the data, rebuilds indexes and foreign keys and resets sequences. Its documentation lists default casts: tiny-integer flags to booleans, zero dates to NULL, each enum column to its own type, unsigned integers widened by one size and auto-increment columns to serial types. It also says views and triggers are not migrated. A clean run therefore tells you the rows moved. It does not tell you that the application's queries, lookups and reports behave the same.

• Treat each default cast as a proposal that you accept or change.
• List views, triggers and routines up front; each needs a hand rewrite or a removal you agree.

Case-insensitive lookups are a MySQL default

MySQL's default collation, utf8mb4_0900_ai_ci, makes comparisons of ordinary text columns case-insensitive, so a lookup by email address matches regardless of capitals. PostgreSQL's ordinary text comparison does not do that. Its documentation offers citext, a case-insensitive type, and recommends nondeterministic collations as an alternative, with a caution that case folding depends on the database locale. Which fix is right depends on the column and its index. Find every lookup that relied on the default by searching the application's queries and testing mixed-case inputs.

• Login, search, uniqueness checks and coupon codes are common places.
• A unique constraint that ignored case on MySQL may allow near-duplicates on PostgreSQL.

Zero dates and flags change what the application reads

MySQL's manual says you can store 0000-00-00 as a dummy date, subject to SQL mode. A loader has to decide what to put in its place: pgloader converts it to NULL by default, dropping the matching default or NOT NULL. Code that tested for the zero date now sees a missing value. Tiny-integer flag columns become booleans, so code that compares to 0 and 1 may break in some drivers. List every column that holds either, decide the target value for each and test the code that reads it.

• Ask the business what a zero date meant: unknown, not applicable or not yet.
• Check ORM mappings and raw queries for flags compared to numbers.

Auto-increment keys become sequences

Generated keys on PostgreSQL come from sequences, which are separate objects. pgloader sets each sequence to the current maximum after loading. A bulk load that writes explicit ids does not advance a sequence by itself, so the next insert can collide unless the sequence is moved with setval. PostgreSQL's documentation also says sequence values are not rolled back and cannot give gapless numbering; if invoice numbers must be consecutive, that needs its own design. Prove it with one test insert per table that has a generated key.

• Compare each sequence's next value with the highest id in its table.
• Do not rely on a sequence for numbers that must have no gaps.

What does not fit, and how the fixed job is accepted

This guide does not cover moving MariaDB, redesigning the schema, tuning performance or a move that cannot pause writes. The fixed MySQL to PostgreSQL job covers one database up to 10 GB and 60 tables with up to ten views, triggers or routines. It is accepted when every table's row count and agreed checksums match at the final load, every generated key continues past its highest id, the named queries and case-insensitive lookups return the same rows on both engines, zero-date and flag columns are listed with agreed conversions, the application's tests pass on PostgreSQL with the reverse step rehearsed on a restored copy, and a backup of the MySQL source restores into an isolated instance with the same counts, so the restore point is proven before the cutover. Prices are untested proposals; payment follows the agreed checks.