A step-by-step MySQL to PostgreSQL migration plan: schema inventory and type mapping (unsigned ints, zero dates, TINYINT(1), ENUM), data copy with pgloader or AWS DMS, the application SQL that breaks (collation, GROUP BY, upsert, LIMIT), and a cutover you can roll back.

MySQL to PostgreSQL Migration: A Practical Guide for Engineers

The data copy in a MySQL to PostgreSQL migration is a weekend. The schema inventory, the application SQL, and the cutover rehearsal are the two to three weeks around it, and they are where migrations actually go wrong. This guide is written in the order we run these projects: inventory the schema, copy the data, fix the SQL, rehearse, cut over.

A word on whether to migrate at all. Both engines are mature MVCC databases; InnoDB is a multi-version storage engine just as PostgreSQL is, so concurrency alone is a weak reason to move. The good reasons are concrete: you want JSONB with GIN indexes over whole documents, partial indexes, native arrays, or an extension such as PostGIS, pgvector or pg_trgm. If your application is a plain OLTP store that MySQL serves fine, stay put.

Step 1: Inventory the schema before copying anything

Run SHOW CREATE TABLE on every table and read it against the mapping below. The point of the exercise is to find the columns that will convert without an error and still be wrong.

MySQL PostgreSQL What to watch
INT AUTO_INCREMENT INT GENERATED ALWAYS AS IDENTITY SERIAL works too; IDENTITY is the standard form (CREATE TABLE)
INT UNSIGNED BIGINT MySQL's range is 0 to 4,294,967,295 (integer types), which overflows PG INTEGER. pgloader widens it by default
BIGINT UNSIGNED NUMERIC (pgloader default) or BIGINT if values fit NUMERIC breaks foreign-key type matching and ORM models; pick BIGINT when your max value is below 2^63
TINYINT(1) BOOLEAN MySQL has no boolean type; check for columns holding 2 or -1 before casting
DATETIME TIMESTAMP Neither stores a zone. Use TIMESTAMPTZ only if you know the wall-clock values were UTC
TIMESTAMP TIMESTAMPTZ MySQL converts to UTC on storage; behaviour matches TIMESTAMPTZ
ENUM('a','b') CREATE TYPE or TEXT + CHECK PostgreSQL enums are standalone types; a CHECK constraint is easier to alter later
TEXT / MEDIUMTEXT / LONGTEXT TEXT Unlimited length; the size variants collapse
BLOB / LONGBLOB BYTEA pgloader casts with byte-vector-to-bytea
JSON JSONB MySQL indexes JSON through functional and multi-valued indexes on specific paths; JSONB adds GIN over the whole document

Two rows in that table deserve a second look. Unsigned integers have no PostgreSQL equivalent, and the widening that pgloader's default cast rules apply (integer when unsigned to bigint, bigint when unsigned to numeric) is safe for the data but changes the type of every column that references it. If users.id becomes BIGINT while orders.user_id stays a signed INT, the FK still creates, but the ORM model now disagrees with the database. Decide on a width per column and put it in a CAST rule.

Zero dates are the other one. Modern MySQL rejects 0000-00-00 because NO_ZERO_DATE and NO_ZERO_IN_DATE are in the default sql_mode, but databases that started life on 5.6 or run with a relaxed mode still carry those rows, and PostgreSQL will not accept them. pgloader's default rule for such columns is using zero-dates-to-null, which also drops the NOT NULL. Count them first:

SELECT COUNT(*) FROM orders WHERE shipped_at = '0000-00-00 00:00:00';

If the answer is not zero, decide whether NULL is the right meaning before the load.

Generated columns need one version check: PostgreSQL only gained virtual generated columns in PostgreSQL 18 (September 2025), where they are now the default. On PG 17 or older, a MySQL VIRTUAL column becomes STORED.

Step 2: Copy the data

For a one-shot migration with a maintenance window, pgloader does schema creation, type casting and the data load in one pass over the native COPY protocol, and it keeps going on bad rows by writing them to a reject file instead of aborting the table. We cover the tool in depth in pgloader: Migrating Databases to PostgreSQL in a Single Command; the minimum you want for MySQL is a command file, not the one-liner:

LOAD DATABASE
    FROM mysql://user:pass@mysql-host/mydb
    INTO postgresql://user:pass@pg-host/mydb
  
  WITH include drop, create tables, create indexes, reset sequences
  
  CAST type tinyint to boolean using tinyint-to-boolean,
       type integer when unsigned to bigint drop typemod
  
  ALTER SCHEMA 'mydb' RENAME TO 'public';

One thing that stops teams in the first five minutes: pgloader's last tagged release is 3.6.9 from 2021, and its MySQL client library does not speak caching_sha2_password, which has been the default authentication plugin since MySQL 8.0 (the issue is years old). Create a dedicated migration user with IDENTIFIED WITH mysql_native_password, and note that the plugin is deprecated in 8.4, so check it is still enabled on your server.

If you cannot afford a write freeze for the duration of the load, use AWS DMS or an equivalent CDC tool: full load first, then replay the binlog until you cut over. DMS needs the MySQL side prepared:

  1. binlog_format = ROW and binlog_row_image = FULL.
  2. Binlog retention long enough to cover the full load (binlog_expire_logs_seconds, or mysql.rds_set_configuration('binlog retention hours', 24) on RDS).
  3. A user with REPLICATION CLIENT and REPLICATION SLAVE.

DMS does not carry AUTO_INCREMENT across and does not replicate cascaded deletes from InnoDB FKs, so identity columns and constraints are yours to create on the PostgreSQL side. Either way, keep the command file or task definition in version control; you will run it at least three times before production.

Step 3: Fix the application SQL

Grep the codebase for raw SQL before you trust the ORM. Django, Rails and SQLAlchemy handle the dialect for generated queries, but every raw() and execute() call is yours. Roughly in order of how often it bites:

Case-insensitive comparisons. MySQL's default collation is utf8mb4_0900_ai_ci, accent- and case-insensitive. PostgreSQL compares bytes. WHERE email = 'John@Example.com' matched in MySQL and returns nothing after the move. Three fixes: wrap both sides in LOWER() and add an expression index, use the citext extension for the column, or create a nondeterministic ICU collation (CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false)). The collation is the cleanest and the slowest; the docs are explicit about the performance penalty.

Identifier quoting. Backticks become double quotes, and double quotes make identifiers case-sensitive. If pgloader created "CustomerID" from a mixed-case MySQL column, every unquoted reference to customerid now fails.

GROUP BY. MySQL has enforced ONLY_FULL_GROUP_BY by default since 5.7, but a large share of legacy applications set a relaxed sql_mode in the connection string. If yours does, expect column must appear in the GROUP BY clause errors. Both databases let you omit columns functionally dependent on a primary key in the GROUP BY, so most queries need only the non-key columns added.

String aggregation and upsert. GROUP_CONCAT(name SEPARATOR ', ') becomes string_agg(name, ', '). ON DUPLICATE KEY UPDATE becomes ON CONFLICT, which requires naming the conflict target:

-- MySQL (the VALUES() form is deprecated; the row alias is current)
  INSERT INTO users (id, email, name) VALUES (1, 'a@b.com', 'Alice') AS new
  ON DUPLICATE KEY UPDATE name = new.name;
  
  -- PostgreSQL
  INSERT INTO users (id, email, name) VALUES (1, 'a@b.com', 'Alice')
  ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

MySQL's version fires on any unique key; PostgreSQL's fires only on the one you name, so a table with two unique constraints needs a decision per statement (INSERT docs).

LIMIT on writes. DELETE FROM queue ORDER BY id LIMIT 1000 is a MySQL idiom for batching. PostgreSQL's UPDATE and DELETE have no LIMIT; rewrite as a CTE that selects ctid with LIMIT and joins back. SELECT ... LIMIT n OFFSET m is identical in both.

Last inserted id. LAST_INSERT_ID() becomes INSERT ... RETURNING id.

Stored procedures. Budget a rewrite. MySQL's procedural SQL and PL/pgSQL differ in declarations, cursors, error handling and result sets (PostgreSQL functions return sets or refcursors; procedures do not return result sets at all). The client-side DELIMITER // dance goes away because PL/pgSQL bodies are dollar-quoted strings. If a procedure is a wrapper around three statements, move the logic to the application.

Step 4: Rehearse, validate, cut over

The rehearsal is the load into staging with the production dataset, followed by the full application test suite against it. Do it twice: once to find problems, once to prove the command file fixes them without manual edits. Then the production run looks like this.

  1. Freeze writes on MySQL (read-only mode, or stop the writers) if using pgloader; if using CDC, stop writers only when replication lag reaches zero.
  2. Run the load, or let CDC drain.
  3. Validate: row counts per table on both sides, MAX(id) per table against SELECT last_value FROM <sequence> to confirm reset sequences did its job, and a checksum on a sample of wide tables (md5(string_agg(t::text, '' ORDER BY id)) on PostgreSQL against the equivalent GROUP_CONCAT on MySQL, for a bounded id range).
  4. Create anything the tool skipped: foreign keys on DMS, partial and expression indexes on both, triggers and views everywhere.
  5. Point the application at PostgreSQL with the new driver, run smoke tests, open traffic.
  6. Keep MySQL running read-only for a week. Rollback is a connection string change until you have written data you cannot afford to lose.

The sequence check matters more than it looks. A sequence that was not reset produces duplicate key errors on the first insert after cutover, which is the most common reason we have seen a migration rolled back an hour after it succeeded.

Plan the calendar around the SQL audit and the two rehearsals; the load itself is measured in hours. If you want a second pair of eyes on the schema inventory or the cutover plan, that is work we do.

Frequently Asked Questions

What is the easiest way to migrate MySQL to PostgreSQL?

For a one-shot migration with a maintenance window, pgloader creates the schema, casts types and loads data in one pass over the COPY protocol, driven by a command file with explicit CAST rules. If you cannot freeze writes, use AWS DMS or an equivalent CDC tool to do a full load and then replay the binlog until cutover.

How long does a MySQL to PostgreSQL migration take?

The data copy itself is measured in hours, typically a weekend. The schema inventory, application SQL fixes and two cutover rehearsals are the two to three weeks around it, and that is where migrations actually go wrong.

Does pgloader work with MySQL 8?

Only with a workaround. pgloader's last release, 3.6.9 from 2021, does not support caching_sha2_password, the default MySQL authentication plugin since 8.0. Create a dedicated migration user with IDENTIFIED WITH mysql_native_password, and check the plugin is still enabled since it is deprecated in MySQL 8.4.

What SQL breaks when moving from MySQL to PostgreSQL?

The most common breakages are case-insensitive comparisons (MySQL's default collation is case-insensitive, PostgreSQL compares bytes), backtick identifier quoting, relaxed GROUP BY, GROUP_CONCAT versus string_agg, ON DUPLICATE KEY UPDATE versus ON CONFLICT, LIMIT on UPDATE and DELETE, LAST_INSERT_ID versus RETURNING, and stored procedures, which need a rewrite in PL/pgSQL.

How do you map MySQL unsigned integers to PostgreSQL?

PostgreSQL has no unsigned types. INT UNSIGNED maps to BIGINT because MySQL's range up to 4,294,967,295 overflows PostgreSQL INTEGER, and BIGINT UNSIGNED maps to NUMERIC by default in pgloader, or BIGINT when your values fit below 2^63. Decide a width per column in a CAST rule so foreign-key and ORM types stay consistent.

Should I migrate from MySQL to PostgreSQL at all?

Only for concrete reasons. Both are mature MVCC databases, so concurrency alone is a weak motive. Good reasons are JSONB with GIN indexes, partial indexes, native arrays, or extensions such as PostGIS, pgvector or pg_trgm. If MySQL serves a plain OLTP workload fine, stay put.