MySQL to PostgreSQL migration without a long outage

Is migrating worth it?

Good reasons: you need Postgres features like transactional DDL, partial indexes, JSONB with GIN indexes, PostGIS, or extensions such as pgvector; you want to consolidate on one engine; or your hosting or compliance requirements point there.

Weak reasons: “MySQL is slow”. A slow MySQL database is usually slow for the same reasons a Postgres one would be – queries and indexes. Fix those first with database performance tuning; you may not need to move at all.

Schema and behavior differences that bite

• Case sensitivity. MySQL’s default collations compare strings case-insensitively; Postgres doesn’t. Unique emails and login lookups change behavior. Use citext or lower() expression indexes.
• Dates. Zero dates like 0000-00-00 are invalid in Postgres and need cleaning. Decide deliberately between timestamp and timestamptz, and how existing DATETIME values map to UTC.
• Types. TINYINT(1) becomes boolean, unsigned integers need a wider type or a check constraint, ENUMs become Postgres enum types or text with a check, and AUTO_INCREMENT becomes identity columns whose sequences must be reset with setval after the copy.
• Text data. Postgres rejects NUL bytes in text columns, and older MySQL utf8 (three-byte) columns can hide encoding problems.
• SQL dialect. ON DUPLICATE KEY UPDATE becomes ON CONFLICT, GROUP_CONCAT becomes string_agg, IFNULL becomes COALESCE, backtick quoting goes, and loose GROUP BY queries that MySQL accepted will fail.
• Implicit casts. MySQL silently coerces comparisons between strings and numbers; Postgres throws an error. Your test suite will find many of these – if you have one.

The migration plan

1. Run the app against Postgres in CI. Translate the schema, load a sanitized copy of production, and run the full test suite. Fix query incompatibilities in the application while it still runs on MySQL in production – most ORMs make this manageable.
2. Bulk copy with pgloader (or AWS DMS for full load), which handles most type mapping. Review its choices table by table rather than trusting the defaults.
3. Keep it in sync with CDC. MySQL’s binlog in ROW format feeds Debezium or DMS change data capture into Postgres, so the target trails production by seconds.
4. Verify continuously. Row counts and per-table checksums on both sides, plus shadow reads: run selected production queries against both and compare results.
5. Rehearse the cutover on a staging copy until the runbook is boring.
6. Cut over. Stop writes briefly, wait for CDC lag to reach zero, reset sequences, switch the connection string, smoke-test, reopen writes. Keep MySQL intact as a fallback until you’re confident.

Zero downtime migration: how close can you get?

With CDC, the write pause at cutover is typically seconds to a few minutes, depending on lag and how quickly you can redeploy configuration. Most teams accept that, scheduled at their quietest hour.

True zero downtime needs dual writes from the application to both databases, with reconciliation for the cases where one write succeeds and the other fails. It’s possible, but it adds weeks and new failure modes. I recommend it only when even a brief write pause has real cost – payments mid-flight, for example. For the general pattern of changing data under a live system, see zero-downtime deployment and the strangler fig pattern.

Related

• PostgreSQL performance tuning when the database is the bottleneck
• Cloud migration consultant for near-zero-downtime moves to AWS and GCP
• Zero downtime deployment, including the database
• The strangler fig pattern, explained with a SaaS migration
• Legacy modernization services that keep the business running