Migrating from MySQL to PostgreSQL

MySQL 8.0 reached end of life on April 30, 2026. Teams that never moved to 8.4 LTS or the 9.x innovation track are now choosing between a MySQL upgrade and a database switch, which makes this the most common migration question we see. This guide covers the actual work: schema conversion, data transfer with pgloader, the SQL your application has to change, and how to verify you didn't lose anything.

Worth saying up front: MySQL 8.4 LTS is supported until 2032 and is a perfectly good database. If your only reason to migrate is vague "Postgres is better" energy, upgrade MySQL instead — it's a weekend, not a quarter. Migrate when you have a concrete reason: you need transactional DDL, richer types (arrays, ranges, jsonb indexing), extensions like PostGIS or pgvector, or you're consolidating onto one engine.

The Three Approaches

For most databases under a few hundred GB, pgloader plus a maintenance window is the right call. pg_chameleon (2.0.21 as of January 2025) reads the MySQL binlog and replays changes into PostgreSQL, so you can sync continuously and cut over in minutes — at the cost of more moving parts.

Data Type Mapping

The core mappings, including the ones that bite:

Two of these cause real production incidents:

The tinyint(1)-to-boolean trap. pgloader's default cast rules convert TINYINT(1) specifically to boolean (plain TINYINT becomes smallint), because TINYINT(1) is MySQL's de facto boolean — BOOL is literally an alias for it. If any of your TINYINT(1) columns store actual small numbers (ratings, retry counts, status codes), the boolean conversion destroys everything above 1. Override per column in your load file:

CAST column products.rating to smallint drop typemod

or, if none of your tinyint(1) columns are real booleans, kill the rule wholesale with CAST type tinyint to smallint drop typemod.

Unsigned integers. MySQL's unsigned types have no PostgreSQL equivalent. An INT UNSIGNED primary key that has crossed 2.1 billion rows must become bigint, and every foreign key pointing at it must change too. Audit before migrating: SELECT MAX(id) on every unsigned key column tells you how urgent this is.

Zero dates. MySQL historically allowed 0000-00-00 and 0000-00-00 00:00:00. PostgreSQL rejects them — there is no year zero. pgloader converts them to NULL by default, which is almost always what you want, but check whether application code treats zero-dates as a sentinel value.

Moving the Data with pgloader

pgloader v4 (as of mid-2026) is a full rewrite distributed as a single JAR requiring Java 21+, a drop-in replacement for the old Common Lisp v3. It takes the same .load files and adds JDBC connection strings. The v3.6.9 native binary from 2022 still works and is what apt-get install pgloader gives you on Debian/Ubuntu, but v4 is where development happens:

curl -L -o pgloader.jar \
  https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar
java -jar pgloader.jar --version

The minimal migration is genuinely one line:

createdb appdb
java -jar pgloader.jar mysql://user:pass@mysql-host/appdb postgresql:///appdb

That copies schema (tables, indexes, foreign keys, comments), converts types with the default cast rules, and loads data in parallel. For anything real, use a load file so your decisions are versioned:

LOAD DATABASE
     FROM mysql://app:secret@10.0.0.5/appdb
     INTO postgresql://app@10.0.0.9/appdb

WITH include drop, create tables, create indexes,
     foreign keys, reset sequences,
     workers = 8, concurrency = 2

CAST type tinyint to smallint drop typemod,
     column orders.is_paid to boolean drop typemod using tinyint-to-boolean

ALTER SCHEMA 'appdb' RENAME TO 'public';

The ALTER SCHEMA line matters: pgloader creates a schema named after the MySQL database, and you almost certainly want your tables in public.

pgloader also downcases identifiers by default. MySQL identifier case sensitivity depends on the filesystem (lower_case_table_names); PostgreSQL folds unquoted identifiers to lowercase. Downcasing everything and never quoting identifiers is the sane end state — "CamelCase" table names in PostgreSQL mean quoting them forever.

SQL Your Application Has to Change

The schema converts mechanically. The queries don't. A checklist of the rewrites that show up in every migration:

• INSERT ... ON DUPLICATE KEY UPDATE → INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col. PostgreSQL makes you name the conflict target; that's a feature.
• REPLACE INTO → no equivalent. It's a delete-plus-insert (fires delete triggers, breaks foreign keys with ON DELETE CASCADE). Rewrite as ON CONFLICT DO UPDATE.
• LAST_INSERT_ID() → INSERT ... RETURNING id. One round trip instead of two, and no connection-state dependency.
• Backtick quoting → double quotes, or better, unquoted lowercase identifiers.
• Backslash escapes in strings. MySQL treats 'it\'s' as escaped by default; PostgreSQL with standard_conforming_strings (on since 9.1) treats backslash as a literal. Use doubled quotes: 'it''s'.
• GROUP_CONCAT(...) → string_agg(col, ',' ORDER BY col).
• IFNULL → coalesce. DATE_FORMAT → to_char. NOW() works in both, but check CURDATE() → CURRENT_DATE.
• Implicit type coercion. MySQL happily compares '123' to an integer column. PostgreSQL raises errors for most cross-type comparisons. These surface at runtime, which is why you test with real queries, not just schema checks.

Case-insensitive string comparison is the sleeper. MySQL's default collations are case-insensitive: WHERE email = 'Bob@Example.com' matches bob@example.com. PostgreSQL compares case-sensitively. Logins break, dedupe logic breaks, and no error is raised anywhere. The standard fixes: the citext extension for columns that should always compare case-insensitively (emails, usernames), or lower() on both sides with a matching expression index. ICU nondeterministic collations can also emulate CI comparison but come with operator restrictions — citext is the boring, proven answer.

Cutover and Validation

For a maintenance-window migration: stop writes, run pgloader, validate, switch the application's connection string, watch error rates. Keep MySQL running read-only for a week as the rollback path.

Validation is not optional and not just row counts:

-- On both sides, per table:
SELECT COUNT(*) FROM orders;
SELECT SUM(total_cents), MIN(created_at), MAX(created_at) FROM orders;
-- Spot-check text columns for encoding damage:
SELECT id, customer_name FROM orders ORDER BY random() LIMIT 20;

Aggregate checksums (sums, min/max of dates, counts per status) catch truncation and type-conversion damage that row counts miss. This side-by-side check is one place a multi-database client earns its keep — Mako connects to both MySQL and PostgreSQL simultaneously, so you can run the same validation query against both and diff the results without juggling two CLIs.

Common Mistakes

For the reverse decision — whether you should be on PostgreSQL at all — see our PostgreSQL vs MySQL comparison. If you're re-pointing application code afterwards, the connect-from guides for PostgreSQL cover drivers and pooling per language.

Mako connects to MySQL and PostgreSQL side-by-side with AI-powered autocomplete — useful for validating a migration query-by-query. Try it free at mako.ai.