For a small database, migrating from MySQL to PostgreSQL is a maintenance window: stop writes, run pgloader, verify, repoint the application. Past a certain size, or past a certain tolerance for downtime, that window stops being negotiable with the business. The alternative is dual-running: keep MySQL as the primary, continuously replicate changes into PostgreSQL, and cut over when the copy is provably current and provably correct.
The machinery for this is change data capture. A CDC pipeline, most commonly Debezium, reads the MySQL binlog and applies each row change to PostgreSQL, keeping the two engines converged while the application still writes to MySQL. Done well, the cutover itself shrinks to seconds of write-freeze rather than hours of load time. Done casually, you get a migration that looks live but has silently diverged for weeks.
I have run this pattern and reviewed other people's versions of it. Here is the shape of the ones that worked.
The architecture in one paragraph
MySQL must run with binlog_format=ROW, and you want binlog_row_image=FULL so every change carries complete row state. GTID mode makes positions survivable across source failovers and is worth enabling if it is not already. A Debezium MySQL connector performs an initial consistent snapshot of the tables, then transitions to streaming the binlog from the position captured at snapshot time. Downstream, a sink, typically a JDBC sink connector in upsert mode, applies the events to PostgreSQL keyed on primary keys, so replays and retries are idempotent. Between snapshot and streaming there is no gap: the snapshot position is recorded, and streaming resumes from exactly there.
Schema is the one thing this pipeline does not solve. Create the PostgreSQL schema first, deliberately, with the type mappings you actually want, tinyint(1) versus boolean, DATETIME versus timestamptz, unsigned widths, rather than letting a sink connector auto-create tables with its own guesses. Auto-created schemas are how you end up with every column nullable and every string a text with no constraints.
Getting the snapshot right
The initial snapshot is the longest and most fragile phase. Three notes from experience. First, snapshot from a quiet replica if you can, or at least during a quiet period; a multi-hour snapshot competing with peak traffic slows both. Second, understand your locking: Debezium's default snapshot holds a global read lock only briefly to capture a consistent position, then reads table data within a consistent view, but the exact behavior depends on privileges and settings, so rehearse it and watch the source. Third, binlog retention must comfortably exceed snapshot duration plus a safety margin. If the binlog the connector needs has been purged by the time the snapshot finishes, you start over. I set retention to several days for the migration period and treat that disk spend as cheap insurance.
Row volume also surfaces data-quality landmines during snapshot: zero dates, invalid charset bytes, values that violate the stricter PostgreSQL types you chose. Every one of these is better discovered in a rehearsal snapshot than in the production one. Run the whole pipeline end to end against a staging copy first, and treat rejected events as schema-mapping work, not noise.
Catch-up: measure lag with a heartbeat you own
Once streaming begins, the question becomes: how far behind is PostgreSQL? Connector metrics report milliseconds behind source, and you should graph them, but I always add an end-to-end heartbeat table as well, because it measures the whole pipeline including the sink, not just the connector's reading position.
-- On MySQL, a job updates this once per second:
-- REPLACE INTO cdc_heartbeat (id, ts) VALUES (1, NOW(6));
-- On PostgreSQL, lag is then simply:
SELECT now() - ts AS end_to_end_lag
FROM cdc_heartbeat
WHERE id = 1;Watch lag through at least one full weekly cycle before scheduling cutover. Batch jobs, bulk updates, and schema migrations on the source all produce lag spikes, and you want to know their shape. While the pipeline runs, also validate continuously: row counts and chunked checksums comparing both sides, because a CDC pipeline can be current and still wrong if an event type is mishandled. Convergence and correctness are separate properties; prove both.
The cutover checklist
Cutover is a sequence, and the sequence matters. Mine looks like this:
- Freeze writes on MySQL. Put the application in maintenance or read-only behavior, then set the database itself read-only (super_read_only) so stragglers cannot sneak writes in behind your back.
- Drain the pipeline. Watch the heartbeat until PostgreSQL has consumed everything, then verify final row counts and checksums on the hottest tables.
- Fix sequences. CDC inserts rows with their source ids; it does not advance PostgreSQL sequences. Before the first application write, set every sequence past its column maximum:
SELECT setval(
pg_get_serial_sequence('orders', 'id'),
(SELECT coalesce(max(id), 1) FROM orders)
);
-- repeat per table, or generate this from the catalog- Repoint the application at PostgreSQL and lift the freeze.
- Record the moment. Note the binlog position and timestamp of cutover. If anything needs forensic reconstruction later, that bookmark is gold.
- Watch the first hour like an incident. Error rates, p95 latency, lock waits, connection pool behavior. Have the owners of the top five query paths on the call.
The PG-side patterns for making the application tolerate this kind of switch, connection draining, retry behavior, dual-write hygiene if you use it, are the same ones that apply to any zero-downtime PostgreSQL migration, and that post covers them in more depth.
The rollback window
The most under-planned part of every CDC migration is the way back. After cutover, new writes exist only in PostgreSQL. If you discover a blocking problem on day two, pointing the application back at MySQL means abandoning those writes, unless you planned a reverse pipeline.
The serious version: at cutover, start a second CDC pipeline in the opposite direction, PostgreSQL logical decoding feeding a sink into MySQL, so MySQL stays current while you gain confidence. This is the same logical replication machinery PostgreSQL uses natively, and the logical replication guide covers the PG-side concepts: slots, publications, and the operational care slots require, chiefly that an abandoned slot retains WAL forever, so the reverse pipeline must be actively monitored and deliberately decommissioned.
The pragmatic version, for teams that cannot justify a reverse pipeline: define a rollback window in business terms. For 48 hours, MySQL remains untouched and rollback means accepting the loss or manual replay of writes since cutover; after 48 hours of clean metrics, rollback is declared closed and MySQL is retired. Either version is fine. Having neither, which is the default outcome of not deciding, is not.
Failure modes I have actually seen
- Sequences left behind. The classic. First insert after cutover throws a duplicate key error. It is on the checklist above because I have watched it happen.
- Tables without primary keys. Upsert sinks need keys. Keyless tables silently degrade to append behavior or fail. Find them before the pipeline does.
- Source schema changes mid-flight. An ALTER TABLE on MySQL during the dual-run must be coordinated with the target schema and the pipeline. Freeze DDL during the migration period or route it through the migration owner.
- TRUNCATE and other non-row events. Depending on configuration, some operations do not replicate as row events. Know which maintenance jobs on the source do this.
- Slot and offset hygiene. Both directions of the pipeline hold positions, Kafka offsets, replication slots. Losing them mid-migration means re-snapshotting.
After cutover: baseline PostgreSQL with MonPG from hour one
The dual-run gets you onto PostgreSQL safely. What it cannot tell you is whether the workload is healthy on the new engine, because there is no history yet to compare against. That baseline problem is worth solving deliberately rather than waiting for it to solve itself.
MonPG monitors PostgreSQL only, and I would stand it up against the target before cutover, while CDC is still applying writes. That way query-family history, wait events, lock behavior, autovacuum activity on the freshly loaded tables, and connection patterns are already accumulating when production traffic arrives, and the first regression shows up as a visible change against a real baseline instead of a mystery. The PostgreSQL monitoring guide describes the day-one signal set: slow query regression, idle-in-transaction sessions, replication and vacuum health, the things that decide whether week one on PostgreSQL is quiet.