Apply Database Changes to Production by Re-running the Migration, Not Copying Rows

Date:

Your schema change works perfectly on staging. The new table looks exactly right. So you dump that table, restore it onto production, and move on – and a week later production has quietly lost a day of real writes, or a foreign key points at a row that only ever existed in your test data. The change itself was correct. The way you shipped it was not.

The short answer: to apply database changes to production, re-run the same migration – the code – against production, so the new state is regenerated from production’s own live data. Do not copy the rows you produced on staging. Those rows carry test-specific ids, stale values, and a snapshot of a moment that passed the instant you took it; promoting them overwrites whatever production did while you were testing. A migration is deterministic and replayable, so running it on production applies the same transformation to the real data, with a clean, auditable, repeatable trail. Promote the recipe, not the meal.

The shortcut that looks safe

You have a change to make. A new column backfilled from two old ones. A lookup table rebuilt from scratch. A status value normalized across a million rows. You write the script, point it at a staging copy of the database, run it, and inspect the result. Every row looks right.

The obvious next move is to take the thing you just verified – the finished table – and put it on production. Dump the table on staging, restore it on production, done:

# staging: export the table you just fixed
pg_dump --table=catalog_normalized staging_db > catalog_normalized.sql

# production: load the known-good rows straight in
psql production_db < catalog_normalized.sqlCode language: PHP (php)

You shipped exactly what you tested, byte for byte. What could be safer than copying the known-good answer over? This is the trap, and it is seductive precisely because the rows are correct – correct for a database that no longer exists.

Why copying the rows corrupts production

The copy is wrong for reasons that are all quiet – nothing errors, nothing crashes, and the damage surfaces days later as a mystery.

  • Production kept moving. While you were testing, real users were writing to production: new orders, edited profiles, fresh rows. The table you dumped from staging knows nothing about any of it. Restoring it on production overwrites those live writes with your test-time snapshot. You did not apply a change; you rolled production back to whenever you took the copy.
  • Test rows carry test-time values. Auto-increment ids, foreign keys, created-at timestamps, and any synthetic or partial content you generated for the test are baked into those rows. On production they point at the wrong parents, collide with real ids, or claim a row was created at a time it was not.
  • A snapshot is stale the instant you take it. Even if production were frozen, a dump is a photograph. Everything downstream – caches, replicas, audit logs – expects changes, not a wholesale swap of a table out from under them.
  • You lose the trail. A bulk row-load is an opaque event: nobody can see what transformation produced those values or reproduce it. A re-runnable script is a record of exactly what happened and can be run again.

Copy the data and you promote a snapshot that went stale the moment you took it.

Promote the code, not the data

The fix is a shift in what “apply to production” even means. It is not an instruction to move rows between machines. It is an instruction to run a piece of code – your migration – against a second database.

A migration is a deterministic function: given a database in a known shape, it produces the same change every time. Run it on staging and it transforms staging’s rows. Run the identical script on production and it transforms production’s rows – the real, current ones, including every write that landed while you were testing. The output is regenerated from production’s own state, not imported from somewhere else. Nothing about staging travels across; only the recipe does.

This is the same discipline that keeps a build pipeline honest. A rendered image is build output you regenerate from its vector master rather than commit and copy around – the same reasoning that says you regenerate a raster from its SVG master on export instead of shipping a stored copy that drifts. Your production table is output too: regenerate it by re-running the migration, do not hand-carry a copy. Keeping environments close enough that the same code runs cleanly in each is exactly what the Twelve-Factor App calls dev/prod parity, and treating every schema change as a versioned, replayable migration rather than a one-off edit is the heart of Martin Fowler’s evolutionary database design.

A migration is a recipe; your test rows are last night’s dinner. You ship the recipe.

Promote the code, not the data – how a database change reaches productionThe migration travels between environments; the rows never do
1. Mirror and validate

Copy production down to staging and run the migration there. Check the result against data shaped like the real thing.

2. Back up, re-run on production

Back up production, then run the same migration against it. The change is regenerated from prod's own current rows – not imported.

3. Verify, don't copy

Compare structure and counts, not rows. Production now matches what the migration produces, with a reproducible audit trail.

The staging rows never travel to production; only the migration does. Data is a snapshot; code is repeatable.

How to apply database changes to production safely

The whole discipline fits in a short loop, and every step exists to catch a specific way the copy-the-rows approach fails.

  1. Mirror. Copy production down into a staging database so you are testing against data shaped like the real thing – real distributions, real edge cases – not a handful of tidy fixtures.
  2. Validate. Run the migration on that mirror and check the result: row counts, spot checks, the queries that will read the new shape. Fix the script, re-mirror, run again, until it is clean.
  3. Back up production. Before you touch it, take a backup you have actually restored at least once, so a bad migration is a rollback and not an incident. (A backup you can afford to keep and actually restore is the one that saves you here.)
  4. Promote the same artifact. Run the identical migration script against production – no edits, no “production variant.” The only thing that changes is the target.
  5. Verify. Confirm production matches what the migration produces – structurally, by counts and checks, not by diffing against the staging rows you were careful never to copy.

Two guardrails make this safe to run by hand under pressure:

  • Name the target explicitly; never default it. A migration that writes to whatever database happens to be configured is one tired evening away from running against production by accident. Require the target to be passed in, and require a second, louder confirmation for anything that is not a throwaway test database:
# a forgotten flag must never silently hit production
migrate.py --target=staging                 # runs
migrate.py --target=production               # refuses: destructive target needs confirmation
migrate.py --target=production --confirm=production   # runsCode language: PHP (php)
  • Make the migration idempotent. Running it twice should not double-apply. Guard new columns with “if not exists”, write backfills as upserts keyed on identity, and your promotion becomes safe to re-run after an interruption – which is the whole point of promoting code. Migration tools such as Alembic exist to version these scripts so each one runs once, in order, and is replayable across environments.

“Apply to production” is a verb about code, not a noun about rows.

When you really are moving data

Sometimes data genuinely has to move, and it is worth naming the exceptions so the rule stays sharp.

Reference and seed data – a country list, a set of category definitions, default settings – does get promoted. But promote it as code: a seed script, checked into version control, idempotent, re-run against each environment. It is still “run the script against production,” not “copy the rows.”

A genuine data transformation – changing a domain name throughout a site, re-encoding stored content, splitting a column – rewrites values in place. Even here you do not hand-copy; you run a deterministic, re-runnable transform against production’s own rows, exactly the way a WordPress domain migration uses a scripted search-and-replace rather than a raw find-and-replace that corrupts serialized data. The form differs; the principle does not. What you promote is the code that produces the data.

The short version

  • To apply database changes to production, re-run the same migration against production. Do not copy the rows you produced on staging.
  • Copying test rows overwrites concurrent production writes, carries test-time ids and timestamps, ships a stale snapshot, and leaves no audit trail.
  • A migration is deterministic and replayable, so running it on production regenerates the change from production’s own current data.
  • The loop: mirror production to staging, validate, back up production, promote the same script, verify by structure and counts – never by copying rows.
  • Name the target explicitly, require a confirmation for production, and make the migration idempotent so it is safe to re-run.
  • Reference data and genuine data transforms are promoted as code too – a re-runnable script – not as a row copy.

Frequently Asked Questions

Can’t I just dump the table from staging and restore it on production?

No – that is the exact move that corrupts production. A restore overwrites every row with your staging snapshot, discarding whatever real writes landed on production while you were testing, and it drags test-time ids and timestamps along with it. Run the same migration against production instead, so the change is applied to the live rows rather than replacing them.

Isn’t re-running the migration slower than copying the finished rows?

Usually the difference is negligible, and when it is not, correct-but-slower beats fast-but-wrong every time. A migration that runs for a few extra minutes is cheap; a row copy that silently overwrites a day of production writes is an incident with no clean undo. If a backfill is genuinely huge, batch it – but still as a re-runnable script against production, never as an import.

What about seed or reference data – isn’t that data I promote?

You promote it as code. Keep the reference data in a seed script under version control, make the script idempotent (inserts that skip or update existing rows rather than duplicating them), and run that script against each environment. You are still running code against production; you are just running code whose job is to establish known rows.

How do I make a migration safe to re-run?

Make each step conditional and identity-keyed: create columns and tables only “if not exists”, write backfills as upserts on a stable key, and avoid blind inserts that duplicate on a second run. Require the target database to be named explicitly, demand a confirmation for production, and back up first. Together those make a migration something you can run twice with no harm – which is exactly what lets you promote it with confidence.

Share post:

Subscribe

Popular

More like this
Related

Can AI CLI Conversation History Leak Answers Into a Blind Test?

You use an AI command-line tool to grade or answer hundreds of questions, one fresh run each. Then you find out it keeps every conversation on disk, answer keys included. Is run 200 quietly reading run 199?

The Code Snippets 403 That Comes From Your Host’s Firewall, Not WordPress

You change a few lines in a snippet, press Save, and get "Could not update snippet. Request failed with status code 403". Press Save on the unchanged snippet and it works. Your login is fine. Something between you and WordPress is reading the code itself.

Why a Flaky Test Passes Alone and Fails When the Machine Is Busy

Run the test by itself and it passes, every time. Run the whole suite in parallel and it fails, but only some days, and its failure looks exactly like a real bug. In our case the code was fine. The test was waiting on a clock.

Why Multisite Subsite Registration Sends Visitors to the Main Site

You put a "create an account" link on one site of a WordPress multisite. Visitors who click it land on the main site, which either says "Registration has been disabled" or signs them up there instead.