MySQL to PostgreSQL migration: how to convert MySQL to Postgres without downtime

How do I migrate a MySQL database to PostgreSQL?

You migrate a MySQL database to PostgreSQL in four steps: convert the schema to Postgres types and constraints, backfill the existing rows table by table, replicate ongoing changes until both databases match, then validate and cut the application over during a short read-only window. The migration is a period of parallel running, not a single event.

Start with the schema, because everything downstream depends on getting the target column types right. Dump the MySQL structure, translate each column, and create the Postgres tables with primary keys and NOT NULL constraints in place but foreign keys and secondary indexes deferred. Loading into a table with a dozen indexes is dramatically slower than loading first and indexing after, and on a large table that difference is hours.

Then backfill. Chunk large tables by primary key range so each chunk is a bounded, retriable unit of work rather than one enormous transaction that can fail at 90 percent and roll back everything. Once the backfill lands, switch to incremental mode: read rows where updated_at is greater than the last watermark, and upsert them into Postgres. Set the initial watermark to the time the backfill started, not when it finished, so rows that changed mid-backfill get picked up on the first incremental pass.

The managed path is the MySQL to Postgres migration connector, which owns the credentials, the type casting, the watermark, and the idempotent writes, so nobody maintains a migration script that only one person understands. If you are also standing up new Postgres infrastructure for the target, the servers and zero-downtime deployment side of the cutover is worth automating at the same time, because a manual deploy during a migration window is where avoidable mistakes happen.

How do MySQL data types map to PostgreSQL?

Most MySQL types have a direct Postgres equivalent, but a handful do not and those are the ones that corrupt data quietly. TINYINT(1) is a boolean by convention in MySQL, unsigned integers can exceed the signed Postgres range, DATETIME carries no timezone, and MySQL accepts invalid dates like 0000-00-00 that Postgres rejects outright.

The AUTO_INCREMENT row deserves a note of its own. After a backfill, the Postgres identity sequence still sits at 1 while your table holds ten million rows, so the very first application insert fails on a duplicate key. Advance every sequence to the current maximum before the cutover and add it to your checklist, because this failure appears seconds after go-live and looks far scarier than it is.

Can I migrate MySQL to PostgreSQL without downtime?

You can get close to zero downtime, but not to literally none. The realistic target is a short read-only window, usually a few minutes, while the last changes drain and you point the application at Postgres. Everything before that window happens with both databases live, and everything after is monitoring plus a rollback plan.

The sequence that works: backfill while MySQL keeps serving traffic, run incremental syncs until the lag is seconds rather than hours, then put the application into read-only mode, wait for the final sync to drain, advance the sequences, run the validation queries, and flip the connection string. If the numbers do not match during validation, you take the application out of read-only mode still pointed at MySQL and nothing was lost except the window. That reversibility is the whole reason to run in parallel.

Keep MySQL running and writable for a few days after the flip, even though nothing writes to it. A rollback target you already trust is worth far more than the instance cost, and the pressure to "just fix forward" at 2am is exactly when a migration turns into an incident.

What are the biggest differences between MySQL and PostgreSQL?

The differences that break applications are case sensitivity, string comparison, and SQL dialect. MySQL compares strings case-insensitively under its default collation, so WHERE email = '[email protected]' matches a stored [email protected]. Postgres compares byte by byte and returns nothing. That single behavior change breaks logins, lookups, and dedupe logic.

Identifier casing is the second trap. MySQL on Linux typically stores table names lowercase and treats unquoted identifiers loosely; Postgres folds unquoted identifiers to lowercase but preserves the case of anything you quoted. If your migration tool created "Orders" with quotes, then SELECT * FROM orders fails. Create everything lowercase and unquoted, and the problem disappears.

None of these are hard to fix individually. The problem is discovering them one at a time in production. Grep your codebase for ON DUPLICATE KEY, two-argument LIMIT, and backtick-quoted identifiers before the cutover, and run your test suite against Postgres for at least a full sprint before you schedule the window.

How do I sync MySQL and PostgreSQL during the migration?

Sync incrementally on a watermark column. Read rows where updated_at > last_run_watermark, write them into Postgres with INSERT ... ON CONFLICT (id) DO UPDATE, and advance the watermark only after the batch commits. Overlap the window by a minute or two so rows written during the previous read are not skipped.

The watermark approach needs one thing from your schema: a column that reliably changes on every update. Many MySQL schemas have updated_at with ON UPDATE CURRENT_TIMESTAMP, which is ideal. Tables that lack it need either a trigger added before the migration or a full reload each pass, which is fine for small reference tables and painful for anything large.

The upsert is what makes retries safe. Because the primary key is stable, replaying an overlapping window updates rows in place instead of duplicating them, so a failed batch can simply be run again. Deletes are the gap a watermark cannot see: a hard-deleted MySQL row leaves no trace to sync. Either switch to soft deletes for the migration period, or run a periodic reconciliation that compares the id set on both sides and removes orphans in Postgres.

How do I verify the migration copied everything correctly?

Verify with three checks per table run against both databases at the same moment: row count, a sum or checksum over the columns that matter, and min plus max on the primary key and the main timestamp. If all three agree across every table, the copy is sound. A visual spot check of a few rows proves almost nothing.

Money columns deserve their own pass. Sum every amount column on both sides and compare to the cent, because a DECIMAL that silently became a float is the single most expensive migration bug and it never shows up in a row count. Do the same for counts grouped by status, which catches ENUM values that failed to map.

Then check the things a count cannot see: that every foreign key you deferred is created and valid, that every index exists, that sequences are advanced past max(id), and that NOT NULL and CHECK constraints made it across. Run ANALYZE on the Postgres side before you take real traffic, because a freshly loaded database with no statistics will choose bad plans and the first hour will look like a performance problem that is really just a missing ANALYZE.

How long does a MySQL to PostgreSQL migration take?

For a schema under a few hundred tables, plan on two to six weeks of calendar time and only minutes of downtime. Most of that time is application work, not data movement: finding the dialect differences, fixing case-sensitivity assumptions, and running your test suite against Postgres until it is green. The data copy itself is usually the shortest phase.

Size changes the backfill, not the plan. A 50GB database backfills in hours with chunked loads and deferred indexes; a multi-terabyte one takes longer and benefits from loading the largest tables first so they have the most time to catch up incrementally. What does not change is the shape: convert, backfill, sync, validate, cut over, keep a rollback.

The part teams underestimate is the tail of small integrations. Reporting jobs, cron scripts, and BI connections all point at MySQL, and each needs its own move. If some of those consumers only need analytical data, sending it to a warehouse rather than repointing at Postgres is often the better answer: see replicating MySQL to Snowflake for that path, or the MySQL to BigQuery connector if your analytics already live in Google Cloud. Either way, the fewer things pointing at the operational database on cutover day, the smaller the window.

If you would rather not own the sync machinery for the parallel-run period, the MySQL to Postgres connector handles the watermark, the type casting, and the idempotent upserts at a flat monthly price rather than a per-row meter, which matters when a migration means running the same tables through repeatedly. You can map the pair in the browser first, or book a walkthrough against your own schema.