Key takeaways

• Speed is the wrong finish line. If moving the date by a week buys you a clean cutover, move it.
• Define success in numbers first: zero rows lost, a recovery time objective, a signed-off cutover window. Let the downtime budget pick your method, dump and restore or logical replication.
• Validate before, during, and after, using row counts plus checksums and business totals. Counts alone can be right while the contents are wrong.
• Rehearse against a production copy and time it. That number is your real cutover window, not the one in the planning doc.
• Run a runbook instead of improvising, and keep the old system untouched until sign-off so rollback stays a decision rather than a scramble.

If you migrated a PostgreSQL database and no one remembers it, you did a phenomenal job. That isn’t luck. It’s the result of a method you rehearse and run on purpose.

On a mission-critical PostgreSQL migration, speed is the wrong finish line. The pressure is always to go fast, hit the date, and get it behind you. But the only score that counts is whether the data landed intact and the system came up clean. If moving the date by a week buys you that certainty, move the date.

We recently ran a migration for a regulated financial company moving a mission-critical Postgres database to EDB Postgres. The single most important decision on the whole project was to push the go-live date back. Nothing was broken. We just decided a clean cutover mattered more than a fast one, and we spent the extra time rehearsing until the migration was boring.

Here’s the method underneath that:

Decide What Success Means Before You Pick a Date

Write the acceptance criteria down first, in numbers. Zero rows lost. A recovery time objective you can state out loud if something goes sideways. A cutover window the business has signed off on.

For a company that moves money, an hour of wrong balances costs far more than a week of waiting. That math should drive the plan. Once everyone agrees on what a successful migration looks like, every later decision gets easier, because you’re measuring against a target instead of arguing about vibes.

Validate the Data Three Times, Not Once

“We moved most of it” is a data-integrity incident waiting to be discovered by a customer. Avoid it by checking the data at three separate points and making them agree.

Before the move, baseline the source. Per-table row counts. Aggregate checksums: hash each table or sum the columns that must reconcile. And the business totals that must match to the penny, like the sum of every account balance, the count of open accounts, and the timestamp of the most recent transaction. Capture a set of reference queries and their exact results while you’re at it.

During the move, verify each phase as it lands instead of waiting for the end. If you cut over with logical replication, watch the replication lag and confirm the subscription is applying changes instead of getting stuck behind a lock.

After the move, run the exact same checks on the target and compare them line for line. Reconcile the business totals. Run amcheck on your indexes to confirm they’re consistent, and run ANALYZE so the planner starts life on the new system with fresh statistics. If all three checkpoints agree, you know you moved everything, not most things.

One opinion worth stating plainly: row counts alone will lie to you. A table can carry the right number of rows and the wrong contents. Pair row counts with checksums and business totals, and you catch the content problems a count alone would miss.

Rehearse Against a Copy of Production

You want to meet the surprises in a rehearsal, where a fix costs an afternoon, instead of at 2 a.m. on cutover night, where it costs a rollback and a hard conversation. Build the target to match production exactly.

 Same major version, same extensions, same instance size, and the same high-availability setup, Patroni and streaming replicas included. Then run the whole migration against it, end to end. Not just a read-through of the steps. The actual run, with the real data volume, timed with a stopwatch.

That stopwatch is important. The real recovery time for your cutover comes from a timed rehearsal, not a number someone typed hopefully into a planning doc. Run the rehearsal more than once, and fix the runbook after every pass. The financial migration mentioned earlier moved its date specifically to get more rehearsal reps in, and every rep made the real event less eventful. 

Write the Runbook, Then Just Run It

Cutover night is not the time to think. It’s the time to execute something you already proved works.

So build one document. Every step, every command, every owner, every rollback point, with a rough duration next to each line and a go or no-go gate right before the point of no return. Pre-checks sit at the top: a fresh backup taken and verified, replication healthy, disk headroom confirmed. The cutover sequence sits in the middle: stop writes, let the final changes drain, promote the target, repoint the application, run smoke tests.

On the day, nobody improvises. The team runs the document. A migration should be the least creative thing your team does all month, and that’s a compliment to the plan.

For the mechanics of promoting and switching traffic without an outage, our guide to rolling OS patching on a Patroni cluster covers the standby-first pattern in detail.

Keep a Way Back the Entire Time

A rollback plan turns go-live from a leap into a decision you can reverse. That’s why it matters.

Leave the old system running and untouched until you verify and sign off the new one. Decide in advance what triggers a rollback, and where the point of no return sits, meaning the moment after which writes on the new system can no longer be reconciled back cleanly. Time-box the cutover window so a stall doesn’t turn into an all-nighter. And know exactly how to fail back, down to the connection string or DNS change, then rehearse that path too.

Knowing you can walk it back is often what gives you the nerve to go forward. If you haven’t confirmed your recovery path lately, test your restore and your real recovery time before you schedule anything.

A rollback turns go-live from a leap into a decision you can reverse. That’s why it matters.

Leave the old system running and untouched until you verify and sign off the new one. Decide in advance what triggers a rollback, and where the point of no return sits, meaning the moment after which writes on the new system can no longer be reconciled back cleanly. Time-box the cutover window so a stall doesn’t turn into an all-nighter. And know exactly how to fail back, down to the connection string or DNS change, then rehearse that path too.

The People Who Run It Should Touch It First

A migration lands twice. Once when the data arrives, and again when the team that operates it is ready to own what it inherited.

So give that team time on the new environment before cutover, not after. Let them run queries, break the rehearsal copy, and ask the sharp questions while it’s still safe to get an answer wrong. Set up monitoring and alerting on the new system before go-live. A migration that lands the data cleanly but leaves the operators guessing has only half succeeded.

Pick the Cutover Method That Fits Your Downtime Budget

This is where you decide the downtime number, so pick on purpose.

A dump and restore is simple and dependable, and it needs a maintenance window sized to your data volume. That’s fine for many systems and genuinely painful for large ones. Logical replication gives you a near-zero-downtime cutover by streaming changes to the target while the old system keeps serving, then switching over once the two are in sync. It takes more setup and more care, since sequences, large objects, and schema changes all need attention, but for a system that can’t afford a long window, it usually earns its keep.

This choice deserves its own deep dive, and it’s the next layer to plan once the method above is in place.

Boring Is the Goal

Some things in life are meant to be boring. You never want to be on an exciting flight, for example!

Database migration is similar – you don’t want any excitement as you migrate. No drama, no data loss, no surprises at 2 a.m. That outcome comes from discipline.

For a business that runs on its database, that discipline is what protects revenue, customer trust, and your standing with a regulator. A migration is one of the few operations where doing it steadily but reliably is the mark of doing it well.

Stormatics runs mission-critical PostgreSQL migrations for regulated teams, with the rehearsal, the runbook, and the rollback handled end to end.
Talk to us about your migration.

FAQ

How do you migrate PostgreSQL with zero downtime?

Use logical replication to stream changes to the new system while the old one keeps serving, then switch over once both are in sync. That keeps the cutover down to seconds or minutes. If a short maintenance window is acceptable, smaller databases can use dump and restore instead. The only reliable downtime number comes from a timed rehearsal.

How do you make sure no data is lost during a migration?

Validate at three points. Before the move, baseline row counts, table checksums, and the business totals that have to reconcile. During, verify each phase as it lands. After, run the exact same checks on the target and compare them line for line. Row counts alone aren’t enough, since a table can have the right count and the wrong contents, so lean on checksums and business totals.

What is a database migration runbook?

A single document that lists every step, command, owner, rollback point, and expected duration for the cutover, with go or no-go gates before the point of no return. On the day, the team runs the runbook instead of improvising. You prove it works in rehearsal, then follow it exactly.

How long does a PostgreSQL migration take?

The project around it usually takes weeks of preparation. The cutover itself can range from minutes to hours depending on the method and the data volume. The dependable way to know your real cutover time is to run the full migration against a copy of production and time it.

How do you roll back a failed migration?

Keep the source system running and untouched until you verify and sign off the new one. Define the point of no return in advance, and rehearse the fail-back path, including the connection string or DNS change, so reverting is a rehearsed routine, not a scramble.