MySQL to PostgreSQL Migration: The Technical Guide

Oracle's end-of-life notice says MySQL 8.0 has been covered under Sustaining Support since 21 April 2026, and recommends MySQL 8.4 LTS or 9.7 LTS. LTS releases follow Oracle Lifetime Support: five years of premier support plus three of extended support. If a supported version is the only goal, upgrading to 8.4 is a far smaller project than changing engines.

Migrate when there is a reason beyond preference: you need extensions such as PostGIS or pgvector, richer SQL for reporting on transactional data, a permissive licence, or a platform standard. If the deadline is the problem rather than the engine, patched builds of MySQL 8.0 from OSSeva buy time while you plan, and the end-of-life options are laid out in the MySQL end-of-life article. The rest assumes you have decided to move.

Start by counting what is actually in the schema. A column-type census tells you how many of the hard cases you have:

SELECT data_type, column_type, COUNT(*) AS n
FROM information_schema.columns
WHERE table_schema = 'appdb'
GROUP BY data_type, column_type
ORDER BY n DESC;

Then list what a schema-only tool will not move: stored procedures, functions, triggers, events and views. pgloader's documentation states that triggers are not migrated. Collect the application SQL from the ORM and the slow query log, and record the server's sql_mode, character set and collation. Measure the largest tables and the sustained write rate, because they decide whether a bulk-load window is acceptable or you need change data capture.

The table covers the differences that most often break a migration. Tools apply defaults; the point is to know where you are overriding them.

This is the failure that reaches production. MySQL 8's default collation, utf8mb4_0900_ai_ci, compares 'a' and 'A' as equal, and treats accented letters as equal to unaccented ones. PostgreSQL's default comparison is case-sensitive. A login lookup on an email column, a unique constraint on a username and a WHERE name = 'smith' all change meaning.

You have three options. The citext type compares case-insensitively and makes primary keys and unique constraints case-insensitive too, but its folding depends on the database's LC_CTYPE and it is slower than text. A nondeterministic ICU collation handles more Unicode cases, but PostgreSQL's documentation lists trade-offs: slower comparisons, no B-tree deduplication, and restrictions on pattern matching that depend on the version. An expression index on lower(column) works where you control the queries. Whichever you choose, search the existing data for values that differ only by case: loading them into a case-sensitive unique index succeeds, and the application then creates duplicates it used to reject.

Three tools cover most migrations. They differ mainly in whether they can keep the target in sync while MySQL still takes writes.

pgloader reads a MySQL database, converts the schema, builds indexes and foreign keys, loads the data in parallel and resets sequences. Its default casting rules turn tinyint(1) into boolean, widen unsigned integers, map datetime and timestamp to timestamptz and convert zero dates to NULL. A load file makes the choices explicit and repeatable:

LOAD DATABASE
     FROM mysql://migrator@mysql-host/appdb
     INTO postgresql://migrator@pg-host/appdb
 WITH include drop, create tables, create indexes, foreign keys, reset sequences;

Rehearse on a copy of production and time each phase. Where a write freeze of a few hours is acceptable this may be the whole data move; otherwise let CDC carry the changes that arrive during the load.

Both DMS and Debezium read the MySQL binary log, so the source needs row-based logging and enough retention to outlast the load. For DMS, AWS lists binlog_format = ROW, binlog_row_image = FULL, a retention of at least a day, and an account with REPLICATION CLIENT, REPLICATION SLAVE and SELECT:

[mysqld]
server_id                  = 101
log_bin                    = mysql-bin
binlog_format              = ROW
binlog_row_image           = FULL
binlog_expire_logs_seconds = 604800

DMS. Create a task in "Migrate existing data and replicate ongoing changes" mode. Do not use a lagging read replica as the source: AWS warns of possible data loss when replica lag means a transaction commits on the primary before the task starts but reaches the replica after. Because DMS does not create secondary indexes or defaults, build the schema first with DMS Schema Conversion or pgloader.

Debezium. The MySQL connector takes a consistent snapshot, then streams the binary log. If the saved position is purged while the connector is down, it fails and asks for a new snapshot; snapshot.mode=when_needed lets it re-snapshot automatically. On the PostgreSQL side, the Debezium JDBC sink consumes the change events directly. Use insert.mode=upsert so replays are idempotent, and delete.enabled=true with primary.key.mode=record_key so deletes apply.

Run validation continuously while the target is syncing, not once at the end:

• Row counts per table, and aggregates or hashes over key ranges, because equal counts hide type coercion and timestamp shifts.
• Sequences: every identity or serial value must be above the table's maximum. After a load with explicit IDs, advance each one:

SELECT setval(pg_get_serial_sequence('orders', 'id'),
              COALESCE(MAX(id), 1), MAX(id) IS NOT NULL)
FROM orders;

• Case-insensitive collisions: values that differ only by case, in columns that were unique under MySQL.
• Real queries: replay the top statements from the slow log against both databases, compare results, then run EXPLAIN ANALYZE on the PostgreSQL side. Plans differ, and indexes that suited the MySQL optimizer may not suit PostgreSQL.
• Application tests against PostgreSQL, including text with emoji and accents, timestamps near daylight-saving changes and NULL ordering: MySQL sorts NULLs first in ascending order and PostgreSQL sorts them last.

1. Days before: freeze schema changes, lower DNS time to live on the database hostname, and rehearse the full runbook at least once on a copy.
2. Confirm lag: replication lag is near zero and validation is clean.
3. Stop writes: set MySQL read-only (SET GLOBAL super_read_only = ON) and stop the application's writers.
4. Drain: wait until the pipeline reports zero lag and the last binary log position has been applied.
5. Final checks: counts and key-range hashes, then advance every sequence.
6. Switch: repoint the application at PostgreSQL through a connection pooler, and run the smoke tests.
7. Open writes and watch error rates, slow queries and connection counts.
8. Hold the old database read-only for a fixed period before decommissioning.

Decide rollback in advance. Once writes land on PostgreSQL, going back means either losing them or running replication the other way, for example Debezium's PostgreSQL source connector with the JDBC sink writing to MySQL. Many teams set a short rollback decision window instead.

• Case-insensitive assumptions in login, search and uniqueness logic.
• Zero dates and invalid dates in legacy rows. MySQL 8.4 enables NO_ZERO_DATE by default, but old data and relaxed modes still carry them.
• Stored procedures and triggers nobody inventoried.
• Binary log retention shorter than the initial load, which forces a restart.
• Connection handling: PostgreSQL runs a backend process per connection, so applications that open thousands of connections need a pooler.
• Operations: autovacuum, replication slots and WAL growth are new jobs for a team that only ran MySQL.

AceMQ supports both engines under one contract, before and after the move: MySQL support for the source while it runs, PostgreSQL support for the target, and PostgreSQL consulting for schema review, cutover planning and tuning. If the source is Oracle or SQL Server instead, that is a different migration, and OSSeva handles it.

Sources: Oracle's MySQL end-of-life notice and the MySQL manual on releases, default collation and SQL modes; pgloader MySQL reference; AWS DMS on MySQL as a source, Schema Conversion and missing secondary objects; Debezium's MySQL connector and JDBC sink; PostgreSQL on numeric types, enums, identity columns, collations and citext, plus MySQL and PostgreSQL on NULL ordering and PostgreSQL's process-per-connection model.