Skip to content
Gun.io
November 21, 2026 ยท 11 min read

Database Migration: Planning a Move That Doesn’t Lose Data or Downtime Budget

A ski resort group moving its guest and pass data from SQL Server to PostgreSQL can copy every row correctly and still break the ticket windows. On the old database, a search for “okafor” finds the guest registered as “Okafor” because the database’s collation ignores case. PostgreSQL compares text case-sensitively by default, so the same search returns nothing, and ticket window staff start creating duplicate guest records on the first morning after cutover.

A database migration moves data, schema and the behavior applications depend on from one database to another. A safe plan agrees two budgets before any data moves: how much downtime the business will accept (often minutes, sometimes none) and how much data loss it will accept (for records like these, none). Everything else follows from those two numbers: whether you stop writes for a window or keep both databases in sync while traffic moves; how you backfill history; how you prove the new database is right; and the point after which rolling back gets harder.

What Is a Database Migration?

The term covers two different jobs. A schema migration changes the structure of a database you keep, such as adding a column or splitting a table, and is part of normal application development. A database migration in the larger sense moves the data to a different database: a new engine, a new version, a new host or a managed service. The second kind, done while the applications that use the database keep running, is the one that needs a plan.

Moves between the same engine (an older PostgreSQL to a newer one) are called homogeneous, and moves between different engines (SQL Server to PostgreSQL) are heterogeneous. Homogeneous moves can often use the engine’s own replication. Heterogeneous moves need translation of types, functions and behavior, and that is where most surprises live.

What Should You Decide Before Moving Any Data?

Decide the budgets, the source of truth at each stage and who can stop the migration. These are business decisions as much as technical ones, so settle them with the people who run the operation as well as the engineers.

DecisionQuestion to answerExample for the resort group
Downtime budgetHow long can writes be unavailable, and when?Under ten minutes, after the lifts close
Data loss budgetCan any committed write be lost?No, pass sales, lift scans and invoices must all arrive
Source of truthWhich database is authoritative at each stage?SQL Server until cutover, PostgreSQL after
Point of no returnWhen does rollback require moving new data back?The first write accepted by PostgreSQL
Stop authorityWho can halt the cutover, and on what evidence?The engineering lead, if validation checks fail

The downtime budget often decides the method. If the business can close for a few hours on a Sunday night, a planned window with a final copy and validation is the simplest and safest route. When the group’s lodging desks take bookings around the clock, writes cannot stop for long, and you need a method that keeps both databases current while traffic moves.

How Do You Plan the Schema for the New Database?

Plan the target schema by mapping every type, constraint and behavior the applications rely on, beyond the table definitions. Types translate imperfectly between engines. Timestamps stored without a time zone in each resort’s local time need a decision about whether to convert them to a time zone aware type. Money stored as floating point is a chance to move to an exact numeric type, as long as every consumer is updated.

Behavior matters as much as structure. Case-insensitive search, default sort order for names with accents, how empty strings and nulls are treated, and what stored procedures and triggers do on insert all need explicit handling. The resort group’s case-insensitive guest search can be preserved with a case-insensitive collation, a normalized search column or functional indexes, but someone has to choose one before cutover.

The migration is also when you discover what the old database never enforced. Invoice lines whose invoice was deleted years ago, season passes pointing at a resort the group sold, and duplicate guest records will all fail the new database’s constraints if you add them. Find them early with queries on the source, decide with the business what to do about each category, and fix them at the source where you can.

Resist redesigning the whole model during the move. Changing engine and data model at the same time doubles the places a discrepancy can come from. Where the model does need to change, use an expand and contract approach: add the new structure alongside the old, migrate, switch readers, then remove the old structure in a later release.

Should You Use a Maintenance Window, Change Data Capture or Dual Writes?

Choose the simplest method your downtime budget allows. Each option trades operational simplicity for availability.

MethodHow it worksMain risk
Maintenance windowStop writes, copy and validate, switch, resumeWindow overruns if the copy or checks take longer than planned
Change data captureStream changes from the source’s log to the target while the application runsLag, ordering issues and schema changes during the stream
Dual writesApplication writes to both databases during transitionThe two writes are not atomic and can diverge

Dual writes deserve a warning, because they look simpler than they are. Writing to SQL Server and PostgreSQL from the same request does not make the two writes a single transaction. If the second write fails or times out, the databases disagree, and the application may not know. Dual writes can work as a validation tool or a transition step, but only with a reconciliation job that finds and repairs divergence, and with one database clearly designated as the source of truth.

Change data capture avoids that problem by reading the source’s own record of committed changes, so the target can receive every change in commit order without the application writing twice. It brings its own work: monitoring lag, handling schema changes that happen while the stream is running and deciding what happens if the stream stops.

How Do You Backfill Historical Data Safely?

Backfill in batches that can be stopped, resumed and rerun without creating duplicates. A resort group with fifteen years of lift scan history should not move it in one long transaction that locks tables and fails at hour three.

Copy by a stable key range, record the last completed batch, and make each batch idempotent so rerunning it produces the same result. Throttle the backfill so it does not compete with production traffic on the source. Set a watermark at the start: everything before it comes from the backfill, everything after it arrives through the sync method you chose, and the two overlap slightly so nothing falls in a gap.

Backfill the data the applications need first. When the ticket windows only look up current passes and the last three seasons of purchases, those can move and be validated while older history follows.

How Do You Validate a Database Migration?

Validate at three levels: row counts, content and business meaning. Each catches problems the others miss.

Row counts per table, and per meaningful slice such as resort and month, show whether anything is missing. Content checks compare checksums or hashes of rows in batches to show whether what arrived matches what left. Business checks confirm that the data still means the same thing: each resort’s invoiced total per day matches across databases, every pass points at a guest that exists, and outstanding balances agree.

Before cutover, run real application queries against both databases and compare the results. Shadow reads like these catch behavioral differences, such as the case-sensitive search, that counts and checksums cannot. Gun.io’s brief on paying down a vibe-coded platform’s technical mortgage describes this kind of proof in an analytics move: a temporary dual-write backend was used to show that the new ClickHouse system matched the legacy results before the official cutover, with approved billing records kept separately in PostgreSQL so they stayed fixed.

How Do You Plan the Cutover and Rollback?

Plan the cutover as a rehearsed sequence with a named decision point. A typical sequence for the resort group looks like this:

  1. Confirm change data capture lag is near zero and validation checks are green.
  2. Stop writes to the source at the agreed time, after the lifts close and lodging bookings are paused.
  3. Let the final changes drain to PostgreSQL and run the fast validation set.
  4. Switch application connection settings to PostgreSQL.
  5. Resume writes and watch error rates, query latency and business checks.
  6. Keep the source read-only and available until the rollback window closes.

Rehearse the whole sequence on a copy of production data at least once, and time each step. The rehearsal tells you whether the downtime budget is realistic before the real night.

Rollback is easy until the new database accepts its first write. After that, going back means moving every new pass sale, booking and invoice back to the old database, so plan for it explicitly: either keep a reverse stream from PostgreSQL to SQL Server running for a defined period, or accept that after the decision point you will fix forward. Write down which choice you have made and who can trigger it.

Why Isn’t Replication a Backup?

Replication is not a backup because a replica copies mistakes as faithfully as it copies good data. If someone runs an update without a where clause, or a migration script drops the wrong table, every replica applies that change too. A replica protects you against losing a server. It does not let you return to the state before an error.

A backup that supports point-in-time recovery does. PostgreSQL’s documentation on continuous archiving and point-in-time recovery describes restoring a base backup and replaying the write-ahead log up to a chosen moment, such as just before a table was dropped. Set that up on the new database before cutover, and test a restore, because the first days after a migration are when an unexpected script is most likely to run.

How Gun.io Supports Database Migrations

Data and backend engineering is core Gun.io project work, and a migration is a good example of work that needs one experienced owner rather than a loose group of contributors. Where your team has the plan and a lead to run it, an augmented engineer can take on the backfill, validation tooling or change capture work under that lead. Where nobody internally has run a migration like this, a scoped engagement can cover the design, the rehearsals and the cutover itself. Our SQL and database engineers work across both models.

Migrations mean someone outside your company may touch production data, so access has to be deliberate. Gun.io sets out its approach to security and privacy, and your own access policy decides what an engineer can reach. Once the new database is live, the data engineer job description is a useful check on who should own the pipelines that feed it.

Frequently Asked Questions

How Long Does a Database Migration Take?

It depends on data volume, how many applications and reports use the database, how much translation the target engine needs and how quickly the business can agree on budgets and data fixes. The rehearsal is the best estimator: once you have timed a full run on production-sized data, you know far more than any upfront guess.

Can You Migrate a Database With Zero Downtime?

Sometimes, but zero downtime usually means near-zero write unavailability, not zero risk. Change data capture or a carefully reconciled dual write can keep the application running while traffic moves, at the cost of more moving parts. A business that can tolerate a short planned window is often better served by the simpler method.

What Should Happen to Stored Procedures and Triggers?

Inventory every one and decide whether to port it, move its logic into the application or retire it. Triggers are easy to miss because nothing calls them directly, yet they often enforce rules the business depends on, such as updating a balance when an invoice line changes. Each one needs a test that proves the behavior survived the move.

How Do You Handle Reports and Other Teams That Query the Database?

Find them before you start, because direct readers will break at cutover even if the main application works. Check database logs for connections from reporting tools, scheduled jobs and other services, then give each owner a date, a new connection and a way to test early.

Who Should Approve the Cutover?

The engineering lead running the migration should make the final call, based on validation results agreed in advance, with the business owner of the data confirming the timing. Deciding the pass criteria before the night removes the pressure to approve a cutover that is almost ready.

Should You Upgrade the Database Version at the Same Time?

Only if the upgrade is the point of the migration. Each change adds a source of discrepancy, so moving to a new host and a new major version at once makes it harder to tell why a query behaves differently. When possible, change one thing, validate, then change the next.

Agree on the Downtime Budget Before the First Row Moves

A database migration goes well when the business and the engineers agree in advance on what the move may cost in downtime and what it may never cost in data. If you are planning a move and want a second view of the method, the validation or the cutover, walk us through the database you’re moving, the applications that depend on it and the window you have to work with.

Gun.io

Sign up for our newsletter to keep in touch!

This field is for validation purposes and should be left unchanged.

+1 (615) 541-8095

team@gun.io

© 2026 Gun.io