How to Migrate from MySQL to PostgreSQL

The short answer

A reliable migration has five stages:

1. Audit: schema, types, application queries, stored procedures.
2. Rehearsal on a copy: move data with pgloader, fix errors.
3. Application changes: driver, ORM, raw SQL queries.
4. Validation: row counts, totals, sampled records.
5. Cutover in a maintenance window with a rollback plan ready.

Moving the data is usually the easy part. Most of the time goes into queries that MySQL tolerated and PostgreSQL does not.

Type differences

Syntax and behavior differences

• Backtick quoting `name` → double quotes "name", or better, unquoted lowercase names.
• Case: PostgreSQL folds unquoted identifiers to lowercase. A table created as "Users" needs quotes everywhere.
• LIMIT 10, 20 → LIMIT 20 OFFSET 10.
• IFNULL() → COALESCE(), GROUP_CONCAT() → string_agg().
• INSERT ... ON DUPLICATE KEY UPDATE → INSERT ... ON CONFLICT (...) DO UPDATE.
• String comparison: often case-insensitive in MySQL because of collations, case-sensitive in PostgreSQL. Use ILIKE, lower() with an expression index, or the citext type.
• GROUP BY: PostgreSQL requires every non-aggregated column to be in GROUP BY or functionally dependent on the key.
• Type strictness: MySQL silently converts types, while PostgreSQL raises an error when you compare a numeric column with a parameter sent as text.

Run the application's full test suite against PostgreSQL — the fastest way to find these queries.

Moving data with pgloader

pgloader reads the MySQL schema, creates tables, indexes and foreign keys in PostgreSQL, copies the data and resets sequences. A one-command quick start:

pgloader mysql://user:pass@mysql-host/shop postgresql://user:pass@pg-host/shop

For repeatable runs, a command file is more convenient:

LOAD DATABASE
  FROM mysql://app:secret@mysql-host/shop
  INTO postgresql://app:secret@pg-host/shop

WITH include drop, create tables, create indexes,
     reset sequences, foreign keys

ALTER SCHEMA 'shop' RENAME TO 'public';

pgloader migrate.load

Worth knowing:

• By default pgloader puts tables into a schema named after the MySQL database — hence the ALTER SCHEMA.
• pgloader has default type casting rules; override them in a CAST clause.
• Read the summary report: rows with errors are skipped and listed separately.
• Triggers, stored procedures and views are not migrated automatically — rewrite them by hand.

Validating the data

Do not trust "completed without errors". Check:

• Exact row counts for every table with COUNT(*) in both databases. MySQL's information_schema row counts for InnoDB are estimates.
• Control aggregates: sums of money columns, MIN/MAX of keys and dates.
• Sampled records: the same 100 random ids in both databases, compared row by row.
• Encoding: Cyrillic, special apostrophes, emoji in user-generated data.
• Sequences: the next id must be greater than the current maximum.

Run ANALYZE after loading so the planner has statistics.

Cutover with minimal downtime

The most practical option for most projects:

1. Rehearse the migration in advance and time the data transfer.
2. In a maintenance window, put the application in read-only mode or stop writes.
3. Run the final transfer and validation.
4. Switch the connection string and restart the application.
5. Watch errors and slow queries; keep MySQL untouched for rollback.

If even a short pause is unacceptable, use change data capture (CDC) — for example Debezium or a cloud migration service. It is more complex and needs its own testing, so it is justified only for truly critical systems.