5 Steps, No Surprises on D-Day

The Continuous Migration Method

“A goal without a plan is just a wish” — set up the target architecture, fork CI onto it, migrate nightly, then cut over once CI stays green.

The 5-step method

Split a migration project into chunks, not one big-bang cutover

A migration project may take a little more than a weekend, so treat it like a real project. This method — Continuous Migration — is how to run it without betting everything on a single cutover night. Follow it and it's all going to be fine, I promise:

  1. 01

    Target architecture

    Stand up your target PostgreSQL architecture — HA included — before migrating a single row.

  2. 02

    Fork CI onto it

    Fork a Continuous Integration environment that runs your full test suite against PostgreSQL.

  3. 03

    Migrate nightly

    Re-run the full data migration from production every night, for the length of the project.

  4. 04

    Go green

    Schedule D-Day only once CI has run clean on PostgreSQL for long enough to trust it.

  5. 05

    Cut over

    Migrate on D-Day — by now it’s a formality, not a leap of faith.

PostgreSQL architecture

High availability comes first, not last

That needs to be done first. Setup your High Availability before doing anything else. The goal is for both the service and the data to be highly available, so start with a fully automated backup and restore solution.

For backup/restore, use either pgbarman or pgbackrest, or WAL-G if you want S3-compatible object storage.

For automated failover, use pg_auto_failover — a monitor-node-based failover extension and service: one monitor, N Postgres nodes, automated promotion on failure, no external consensus cluster to run and babysit separately.

Don't roll either of these yourself. It's really easy to do it wrong, and what you want is an all-automated recovery solution. Go for that.

Primary server and replicas

The shape every later module builds on

Set up High Availability with a Primary server and a set of Secondary servers, also named Standby or Replica. Read the whole PostgreSQL documentation about High Availability, Load Balancing, and Replication, and then about Logical Replication.

Coming from MySQL: MySQL's row-based replication lets a replica keep accepting local writes to other tables even while replicating — a real use case for some architectures, but the moment a replica's data diverges from the primary, it's no longer providing High Availability, just a copy that drifted. PostgreSQL's model doesn't allow that ambiguity: a physical Secondary is read-only, full stop — every write goes through the Primary, so there's never a question of which copy is correct. If your MySQL setup relied on writable replicas, that's architecture to redesign here, not just syntax to port.
pg_auto_failover architecture: Application, Primary, Secondary, and Monitor
pg_auto_failover's architecture: a monitor node watches Primary and Secondary over Postgres streaming replication, and drives promotion on failure.

If you're not sure what to do now, don't hand-roll the replication config — use pg_auto_failover instead. Two commands get you there:

  1. Create the monitor node: pg_autoctl create monitor.
  2. Register your Primary and Secondary against it: pg_autoctl create postgres, run on each node.

That's it — the monitor handles the replication setup, health checks, and promotion on failure for you, instead of you assembling postgresql.conf, pg_hba.conf, and pg_basebackup by hand.

CI/CD for your migration

Treat the migration itself as a pipeline, not a one-shot script

You already run CI/CD for application code — validate, promote, approve, record. Apply the same discipline to the migration itself: the whole point of nightly re-migration (next section) is to put the migration on the same automated, continuously-validated footing as any other change, so nothing about D-Day is a surprise.

Nightly data migration

Run the whole thing, every night, for the length of the project

Chances are that once your data migration script is tweaked for all the data you've seen, some new data will show up in production that defeats your script.

To avoid data-related surprises on D-Day, run the whole data migration script against production data every night, for the whole duration of the project. You'll build such a track record handling new data that you'll fear no surprises. In a migration project, surprises are seldom the good kind.

“If it wasn't for bad luck, I wouldn't have no luck at all.”

Albert King, Born Under a Bad Sign

Porting the code from MySQL to PostgreSQL

What actually changes in your queries

Now that you have a CI/CD environment fresh with yesterday's production data every morning, it's time to rewrite those MySQL queries for PostgreSQL. A few things to know:

  • PostgreSQL likes JOINs — yes, really, you can have very fast queries using JOINs in PostgreSQL.
  • Quoting is different — PostgreSQL quotes SQL identifiers using "double quotes" and literal values using 'single quotes'.
  • on duplicate key update is written on conflict do update — and it might even be do nothing.
  • See the FAQ for more conversion hints.

Most differences you'll see are PostgreSQL simply following the SQL standard more closely.

Ready to leave MySQL behind for good?

This is one piece of a bigger method — pgloader, pg_auto_failover, and the stored-procedure porting no tool automates for you — built for developers making exactly this move.

Get the MySQL → PostgreSQL Course
© 2026 MySQL to PostgreSQL. Part of The Art of PostgreSQL.