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.
Table of Contents
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.
Copy production down to staging and run the migration there. Check the result against data shaped like the real thing.
Back up production, then run the same migration against it. The change is regenerated from prod's own current rows – not imported.
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.
- 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.
- 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.
- 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.)
- Promote the same artifact. Run the identical migration script against production – no edits, no “production variant.” The only thing that changes is the target.
- 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.