MySQL to PostgreSQL Migration: A Step-by-Step Playbook for 2026
The most common direction in 2026. GitLab completed this over 2017-2020. Here is the practical playbook, with tooling notes and failure modes.
The 8-step migration playbook
Inventory and scope
List every schema, every stored procedure, every trigger, every JSON column, every full-text index, every spatial column. Count them. This inventory is your migration risk register. The surface area determines the tool choice and timeline.
Pick the strategy: dump-and-load vs CDC vs dual-write
Dump-and-load is simplest but requires downtime. Change-data-capture (CDC via pgloader, AWS DMS, Striim) allows near-zero-downtime. Dual-write (application writes to both DBs) is the safest but doubles complexity. Downtime tolerance and data volume decide.
Translate the schema
Key translation gotchas: AUTO_INCREMENT becomes SERIAL or GENERATED ALWAYS AS IDENTITY. ENUM behaves slightly differently in Postgres. JSON columns need testing against JSONB semantics. TINYINT/MEDIUMINT map to SMALLINT/INTEGER. TEXT collation defaults differ.
Translate queries and ORM bindings
ON DUPLICATE KEY UPDATE becomes ON CONFLICT ... DO UPDATE. MySQL has no RETURNING clause (use SELECT after INSERT or ORM equivalent). Isolation level defaults differ: MySQL defaults REPEATABLE READ, Postgres defaults READ COMMITTED. Your query behaviour may change.
Build dual-read / dual-write infrastructure
For zero-downtime migrations, write to both DBs during cutover window. Route reads to Postgres for a subset of traffic (shadow mode). Compare results. Only cut over fully when parity is confirmed.
Validate with shadow traffic
Compare result sets, performance, lock patterns, and EXPLAIN outputs between MySQL and Postgres on the same queries. The Postgres query planner will make different choices. Some queries will be faster; some will be slower until you add the right indexes.
Cut over
Saturday-night cutover is still the most common pattern for medium-scale migrations. Put MySQL into read-only mode, verify the Postgres replica is caught up, flip the application connection string, validate, remove MySQL read-only. Have a rollback plan.
Stabilise and decommission MySQL
Keep MySQL alive in read-only mode for 2-4 weeks after cutover. Monitor for issues. When stable, archive the MySQL dataset and decommission. Do not rush decommission; the read-only archive is cheap insurance.
DDL translation: common gotchas
| MySQL | Postgres | Note |
|---|---|---|
| AUTO_INCREMENT | SERIAL / BIGSERIAL | Or GENERATED ALWAYS AS IDENTITY (preferred in PG 17) |
| ON DUPLICATE KEY UPDATE | ON CONFLICT DO UPDATE | ON CONFLICT is more flexible; targets specific constraints |
| LAST_INSERT_ID() | RETURNING id | RETURNING is available in INSERT / UPDATE / DELETE |
| JSON column | JSONB column | JSONB stores binary; GIN indexes possible. Test JSON_TABLE vs jsonb_path_query |
| ENUM('a','b') | CREATE TYPE AS ENUM or VARCHAR with CHECK | Postgres ENUM alters are more restrictive; VARCHAR+CHECK is more flexible |
| TINYINT(1) | BOOLEAN | MySQL uses TINYINT for boolean; Postgres has native BOOLEAN |
| UNSIGNED INT | INTEGER (or BIGINT for range) | Postgres has no UNSIGNED. Adjust range assumptions. |
See side-by-side SQL at /code-samples.
Common failure modes
Lost data from collation differences
MySQL defaults to case-insensitive collation for VARCHAR. Postgres is case-sensitive by default. Application code that relies on case-insensitive comparison will behave differently.
Broken stored procedures
PL/pgSQL is not MySQL stored procedure SQL. All stored procedures and triggers need manual rewrite. There is no automated translation. Budget 1-2 days per complex procedure.
Replication lag during cutover
CDC-based migrations accumulate replication lag during the migration window. Monitor closely. A lag spike during the cutover window can extend downtime.
Query plan regressions post-migration
The Postgres query planner makes different choices than MySQL's. Some queries that were fast in MySQL will be slow in Postgres until you add the right indexes or rewrite the query.