What breaks in a MySQL to PostgreSQL migration?
In a MySQL to PostgreSQL migration, the rows are the easy part. What breaks is behaviour: MySQL compares text without caring about case by default, PostgreSQL does not, and the SQL for upserts, quoting and dates differs. Most of the work is in the application queries.
MySQL’s default server collation is utf8mb4_0900_ai_ci: accent-insensitive and case-insensitive. So in MySQL, "a", "A" and "à" compare as equal by default. In PostgreSQL they don’t.
From my work
A move is not the end of the database work. On one project that runs on PostgreSQL, the client saw a spike in the AWS bill. CloudWatch led me to queries against an old logs table of more than 24 GB. Most queries pulled by date, so I partitioned the table by quarter. I also found and deleted EC2 instances nobody used any more.
Reasons to switch databases
Usually for the features: richer types, JSON support, extensions, row-level security, and a licence with no commercial edition behind it. Sometimes because the rest of the company already runs PostgreSQL and one database is cheaper to run than two, and easier to be on call for.
The differences that break queries
- Case in comparisons. A login lookup that matched "Ana@x.com" against "ana@x.com" in MySQL stops matching. PostgreSQL offers citext or nondeterministic collations for this. Its own docs say nondeterministic collations are often the better choice.
- Case in names. PostgreSQL folds unquoted names to lower case, and quoted names become case-sensitive. A table created as "UserAccounts" must be quoted that way forever.
- Quoting. MySQL backticks become double quotes. A find-and-replace job, until a backtick turns up inside a string.
- Upserts. INSERT ... ON DUPLICATE KEY UPDATE becomes INSERT ... ON CONFLICT ... DO UPDATE, which needs a named conflict target.
- Booleans and dates. TINYINT(1) flags become booleans, and MySQL zero dates such as 0000-00-00 have no PostgreSQL value.
Tools: pgloader and what it leaves to you
pgloader reads the MySQL schema and builds matching PostgreSQL tables, indexes and foreign keys. Its default rules turn TINYINT(1) into boolean, zero dates into NULL, and AUTO_INCREMENT into serial or bigserial. It lower-cases names by default. Triggers are not migrated, so any logic in triggers, and in stored procedures, has to be rewritten by hand. First someone has to remember the triggers are there.
Steps for a MySQL to PostgreSQL migration
- Inventory the SQL: ORM queries, raw queries, reports, triggers, procedures. Include the report nobody owns but finance still opens every month.
- Run pgloader into a staging PostgreSQL and run the full test suite against it. Expect red. That is what staging is for.
- Fix the case, upsert and date issues in the application.
- Plan the cutover: a short write freeze and a final sync, or a change-data-capture tool if you can’t stop writes.
- Keep MySQL read-only for a while after the switch, as a way back.
What drives the cost
How much raw SQL the app has, how much logic lives in triggers and procedures, and how long you can freeze writes. I found no reliable market price for MySQL to PostgreSQL work, so I don’t quote one. The 14-day Trial ($995) covers the assessment and the first fixes, and ends with a written plan for your database.
Read next
Sources
- MySQL 8.4 Reference Manual, server character set and collation, read 17 Nov 2026
- MySQL 8.4 Reference Manual, Unicode character sets, read 17 Nov 2026
- PostgreSQL 18 docs, identifiers and key words, read 17 Nov 2026
- PostgreSQL 18 docs, INSERT, read 17 Nov 2026
- PostgreSQL 18 docs, citext, read 17 Nov 2026
- pgloader docs, migrating from MySQL, read 17 Nov 2026