Migrating MySQL to PostgreSQL Without Taking the App Down

The simple plan — put the app in maintenance mode, dump MySQL, load PostgreSQL, switch the connection string — works for a small internal tool. For a live product it tends to fail in predictable ways:

• The window is never long enough. Dump and restore time grows with the data, and a failed load halfway through means starting again with users waiting.
• Problems show up after the switch. Type mismatches, case-sensitive comparisons and broken sequences surface as production errors, when going back means losing new writes.
• Background work doesn't pause. Workers, webhooks and scheduled jobs keep writing, and anything that writes during the cut-over is lost or duplicated.

At Shivohini TechAI I migrated client databases from MySQL to Supabase/PostgreSQL mid-project while the AI agents kept running. The plan below is the general shape of that kind of migration, not any one project's runbook.

Generate the PostgreSQL schema, then review it table by table. Tools get most of it right; the differences that matter are the ones that change behaviour:

• AUTO_INCREMENT → identity. Use GENERATED BY DEFAULT AS IDENTITY so the backfill can insert existing IDs, then reset each sequence after the copy.
• TINYINT(1) → boolean. MySQL has no real boolean, so check the column only holds 0 and 1 before converting; some "flags" turn out to hold other values.
• DATETIME → timestamptz. MySQL DATETIME has no time zone. Decide what zone the stored values were written in, usually UTC, and convert explicitly rather than letting the session zone guess.
• Collation and case sensitivity. MySQL's default collations compare case-insensitively; PostgreSQL's don't. A login lookup by email or a unique index on usernames can change meaning. Use lower() indexes, citext or a nondeterministic collation where the app relies on it — and check for rows that collide once case matters.
• ENUMs. PostgreSQL enums are separate types and awkward to change later. A text column with a CHECK constraint is often easier to live with.
• Zero dates. Values like 0000-00-00 aren't valid in PostgreSQL; map them to NULL on the way in.

CREATE TABLE users (
    id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- was AUTO_INCREMENT
    email       text NOT NULL,
    is_active   boolean NOT NULL DEFAULT true,                       -- was TINYINT(1)
    plan        text NOT NULL CHECK (plan IN ('free', 'pro', 'team')),  -- was ENUM
    created_at  timestamptz NOT NULL,                                -- was DATETIME, stored as UTC
    updated_at  timestamptz NOT NULL
);
CREATE UNIQUE INDEX users_email_lower ON users (lower(email));      -- keeps MySQL's case-insensitive uniqueness

-- After the backfill, before PostgreSQL takes any writes:
SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 1)) FROM users;

The app keeps reading and writing MySQL as normal. Copy existing rows into PostgreSQL in batches, keyed on the primary key, so the copy can stop and resume without starting over. Load parent tables before children, or add foreign keys after the load.

BATCH = 5_000

def backfill(table: str, columns: list[str], transform):
    last_id = progress.get(table, 0)                 # resume point survives restarts
    while True:
        rows = mysql.fetch(
            f"SELECT {', '.join(columns)} FROM {table} WHERE id > %s ORDER BY id LIMIT %s",
            (last_id, BATCH),
        )
        if not rows:
            break
        pg.upsert(table, [transform(r) for r in rows], conflict="id")  # re-running a batch is safe
        last_id = rows[-1]["id"]
        progress.set(table, last_id)

Batching on the primary key keeps each query cheap on the live database, and the upsert makes retries harmless. Run it against a replica if you have one, and throttle it if the primary starts to feel it.

The backfill copies a moving target. Rows change while it runs, so you need a way to carry new writes across until cut-over. There are two common approaches, and neither is free:

• Change data capture (CDC). A tool reads MySQL's binlog (row-based format) and applies each change to PostgreSQL. It sees every write, including ones from scripts and admin tools, and the app doesn't change. The cost is another system to run and monitor, and schema changes during the migration need care.
• Dual-writes. The application writes to MySQL, then to PostgreSQL. No new infrastructure, but every write path has to be covered — ORM saves, raw SQL, bulk updates, background workers — and a crash between the two writes leaves them out of step.

The honest trade-off: CDC is more complete, dual-writes are simpler to start. Whichever you choose, assume something will be missed and plan a reconciliation pass rather than trusting the sync blindly. Long-running writers — agents, workers, scheduled jobs — are the easiest to forget, so list them before you start.

• Row counts per table. Cheap and catches gross failures, but equal counts can hide wrong data.
• Per-table checksums. Hash rows in primary-key ranges on both sides, normalising types the same way first — booleans, timestamps in UTC, trimmed text — then compare range by range to find drift.
• Sampled reads. Pick random IDs and real queries the app runs, and compare the results. This is where collation and time-zone differences show up.

def range_checksum(db, table: str, lo: int, hi: int) -> str:
    rows = db.fetch(f"SELECT * FROM {table} WHERE id >= %s AND id < %s ORDER BY id", (lo, hi))
    h = hashlib.sha256()
    for r in rows:
        h.update(normalise(r).encode())     # same type rules on both sides
    return h.hexdigest()

for lo in range(0, max_id + 1, 10_000):
    if range_checksum(mysql, "orders", lo, lo + 10_000) != range_checksum(pg, "orders", lo, lo + 10_000):
        report_drift("orders", lo, lo + 10_000)

1. Stop or pause writers briefly, or put writes behind a short read-only window.
2. Let the sync drain until PostgreSQL has applied every change, and run a final verification.
3. Reset sequences, then switch the app's connection to PostgreSQL behind a config flag.
4. Resume writers and watch error rates, slow queries and the paths the sampled reads covered.

The rollback path is what makes this safe. Keep MySQL intact, and if possible keep a reverse sync from PostgreSQL to MySQL running for a while, so switching back doesn't lose the writes made since cut-over. Retire MySQL only once you've stopped needing that option.

• It's still PostgreSQL. Schema mapping, backfill and verification work the same way on Supabase or any managed PostgreSQL.
• Mind connection pooling. Use the pooled connection for the application, and a direct connection for bulk loads, schema changes and long transactions, which transaction-mode poolers handle poorly.
• Check what's allowed. Managed instances limit extensions, superuser access and some settings, so confirm the ones your schema needs before cut-over day.

It covers the common path for an application database: tables, indexes, constraints and data. Stored procedures, triggers and views need rewriting by hand; very large tables may need partitioned or parallel loads; and specific CDC tools, their configuration and their failure modes aren't covered. The sketches are simplified, and the right plan depends on your write volume, downtime tolerance and how many systems write to the database.