MySQL to PostgreSQL Migration
February 13, 2026
|
PostgreSQL
MySQL
Migration
pgloader, gotchas, and dual-write strategy.
Migrating from MySQL to PostgreSQL
PostgreSQL offers advanced features like JSONB, CTEs, window functions, and superior ACID compliance that make it attractive for growing applications. But migration isn't just about moving data — it requires handling schema differences, data type mismatches, and application query changes.
Migration Tools
pgloader (Recommended)
pgloader is purpose-built for MySQL → PostgreSQL migration. It handles schema conversion, data type mapping, and data loading in a single step:
pgloader mysql://user:pass@mysql-host/mydb \
postgresql://user:pass@pg-host/mydbKey Data Type Differences
| MySQL | PostgreSQL | Notes |
|---|---|---|
| INT AUTO_INCREMENT | SERIAL / GENERATED ALWAYS | Use IDENTITY columns in PG 10+ |
| TINYINT(1) | BOOLEAN | pgloader handles this automatically |
| DATETIME | TIMESTAMP | Consider TIMESTAMPTZ for timezone awareness |
| ENUM('a','b') | CREATE TYPE ... AS ENUM | PostgreSQL enums are user-defined types |
| TEXT / LONGTEXT | TEXT | PostgreSQL TEXT has no size limit |
| JSON | JSONB | JSONB is binary and indexable |
SQL Compatibility Gotchas
- Quoting: MySQL uses backticks (
`table`), PostgreSQL uses double quotes ("table") - String comparison: PostgreSQL is case-sensitive by default. Use
ILIKEfor case-insensitive matching. - GROUP BY: PostgreSQL requires all non-aggregated columns in GROUP BY (stricter than MySQL)
- LIMIT syntax: Both support
LIMIT n OFFSET m, but MySQL'sLIMIT m, ndoesn't work in PG - Boolean handling: PostgreSQL uses TRUE/FALSE, not 1/0
Migration Strategy: Dual-Write
For zero-downtime migration:
- Set up pgloader for initial data sync
- Implement dual-write in the application (write to both MySQL and PostgreSQL)
- Validate data consistency between both databases
- Gradually shift reads to PostgreSQL
- Once confident, cut over writes and decommission MySQL
Eazy SaaS Tip: Start by running your test suite against PostgreSQL. Fix SQL compatibility issues before migrating data. This typically uncovers 80% of the application changes needed.