A Database Migration Broke Production: Recover, Then Prevent It

When a migration breaks production, stop the damage first: freeze deploys, find the migration and any lock it holds, and snapshot the database before you touch anything. Then decide. If no data was lost, forward-fix with a new migration or redeploy compatible code. If data was lost or corrupted, restore. Afterwards, prevent repeats with expand-contract migrations and CI checks that replay every migration.

This guide is for the moment right after a deploy, when pages are timing out or returning errors like column "x" does not exist. The examples use PostgreSQL, and the order of work applies to most relational databases. If your app was built with Lovable and Supabase and nobody has migration files at all, start with stopping hand-edits to the production database instead. If production is down and you want help now, the fix one issue page lists what to send.

What should you do in the first 15 minutes after a migration breaks production?

Contain it, then look. Every change you make in panic is one more thing to untangle later, so the first steps are about stopping further damage and preserving evidence.

1. Freeze deploys. Pause the pipeline so an automatic retry or a teammate's merge cannot run the migration again or stack a second change on top.
2. Name the migration. Your migration tool records what ran: _prisma_migrations for Prisma, schema_migrations for Rails, flyway_schema_history for Flyway. Note which migration ran last and whether it is marked as finished or failed.
3. Check for locks. A migration waiting on a lock can block every query behind it (see the query below).
4. Snapshot the database before you change anything, even if you think the data is fine. It costs minutes and gives you a known point to compare against.
5. Stop harmful writes. If the app is writing wrong data, put it into maintenance mode or turn off the one feature doing the writing. Wrong rows keep piling up while you investigate.
6. Tell one person the plan. Name who is making changes. Two people running fixes at once is how a one-hour incident becomes a day.

This query shows what is running, for how long, and which sessions are blocking it:

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       now() - query_start AS running_for,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

pg_blocking_pids returns the sessions blocking a process, and counts a "soft block" too: a session that is only waiting for a conflicting lock, but is ahead in the queue (PostgreSQL system information functions). That is the classic migration outage. An ALTER TABLE waits for a long-running query, and every normal read and write then queues behind the ALTER. Cancelling the migration's session with pg_cancel_backend(pid) usually clears the queue at once.

Is the problem a lock, the schema, or the data?

Work out which of four failures you have before you choose a fix. They look alike from the outside (errors and timeouts) but need very different responses.

Failure	What you see	Typical cause	First move
Lock pile-up	Timeouts across many pages; database CPU often low	A table rewrite, or an index built without CONCURRENTLY, waiting on or holding a lock	Cancel the migration session; rerun later in a safe form
Schema and code out of step	500 errors naming a missing or renamed column	A column was dropped or renamed while old code was still serving traffic	Redeploy code that matches the schema, or restore the column
Half-applied migration	The migration tool marks it failed; later deploys refuse to run	A step that cannot run in a transaction failed partway through	Inspect what exists, finish or undo it by hand, then mark it resolved
Data damage	No errors, but wrong values, or rows missing	A backfill UPDATE with a wrong condition, or a dropped column that held data	Stop the writes; compare against the snapshot; plan a restore or repair

PostgreSQL runs most schema changes inside a transaction, so a failed migration usually rolls back cleanly. The exceptions cause the half-applied case. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and if it fails it leaves behind an "invalid" index that queries ignore but writes still maintain. The documented fix is to drop the index and run the command again (PostgreSQL CREATE INDEX).

Should you roll back, forward-fix or restore from backup?

Forward-fix whenever the data is intact, because it keeps every write your customers made. Restore only when data is actually lost or corrupted, and accept that a restore throws away the writes made since the restore point.

Option	Use it when	What you lose	Main risk
Roll back the code only	The new schema is a superset of the old one, and old code still works against it	The new feature, for now	Low, if the schema really is backward compatible
Run the down migration	The migration only added things, and nothing important has been written to them	Anything written to the new columns or tables	Down migrations are rarely tested, and dropping a column deletes its data
Forward-fix with a new migration	The data is intact and you understand the cause	Nothing	Rushing a second untested change onto a broken system
Restore or point-in-time recovery	Rows were deleted or overwritten and cannot be rebuilt	All writes after the restore point, unless you replay them	Restore time; reconciling the writes in between

A middle path often works for data damage. Restore the backup into a separate database, then copy only the damaged rows or columns back into production. That keeps the new orders or sign-ups made since the incident, and repairs only what the migration broke. Check with your hosting provider how far back point-in-time recovery reaches on your plan. That window varies by provider and tier, and it is worth knowing before you need it.

How do you forward-fix a broken migration safely?

Treat the fix as a normal change, only smaller and faster. Write it as a new migration file in the repository, never as a hand-edit in a database console, so staging, production and every developer's machine end up with the same schema.

1. Reproduce on a copy. Restore the snapshot, or replay all migrations on an empty database, and apply the broken migration there. Now you can try fixes without risk.
2. Write the smallest fix that returns the app to a working state, such as adding back the column the old code still reads.
3. Set a lock timeout so the fix cannot cause a second pile-up. PostgreSQL's lock_timeout aborts a statement that waits too long for a lock. It is disabled by default, and the limit applies to each lock attempt separately (PostgreSQL client connection defaults).
4. Apply, then verify with the journeys that were failing, not only with the migration tool's "success" message.
5. Repair the migration history if the tool marked a migration as failed. Most tools have a resolve command for this. Use it only after the database matches what the history claims.

Here is a forward-fix for the most common case: a migration renamed customers.phone to phone_number while the running code still reads phone.

-- migrations/20261204_restore_phone_column.sql
SET lock_timeout = '5s';

-- Put the old column back so running code works again.
ALTER TABLE customers ADD COLUMN IF NOT EXISTS phone text;
UPDATE customers SET phone = phone_number WHERE phone IS NULL;

-- Keep both columns until every deployed version uses phone_number.

On a large table, run that UPDATE in batches of a few thousand rows so one long transaction does not hold locks. The fix leaves you with two columns on purpose. That is the first half of expand-contract, which is how the change should have been shipped in the first place.

How do expand-contract migrations prevent this?

By never shipping a schema change that the running code cannot handle. Expand and contract, also called parallel change, breaks a backward-incompatible change into three phases: expand, migrate and contract (Danilo Sato, Parallel Change, martinfowler.com). At every point, old and new code both work against the database.

Release	Database	Application
1. Expand	Add phone_number, nullable	Writes both columns, reads phone
2. Migrate	Backfill phone_number in batches	Reads phone_number, still writes both
3. Contract	Drop phone	Uses only phone_number

The same thinking applies to the statements themselves. PostgreSQL gives you safe forms of several risky operations:

• Adding a column with a constant default does not rewrite the table. A volatile default such as clock_timestamp() rewrites the whole table and its indexes (PostgreSQL ALTER TABLE).
• Adding a constraint with NOT VALID skips the table scan and commits immediately. A later VALIDATE CONSTRAINT checks existing rows, holding only a SHARE UPDATE EXCLUSIVE lock, which lets normal reads and writes carry on (same page).
• Making a column NOT NULL normally scans the whole table. If a valid CHECK constraint already proves there are no nulls, the scan is skipped (same page).
• Creating an index with CONCURRENTLY avoids the lock that blocks inserts, updates and deletes (PostgreSQL CREATE INDEX).

-- Safe NOT NULL on a large table, in two migrations.
-- Migration A:
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_not_null
  CHECK (customer_id IS NOT NULL) NOT VALID;

-- Migration B (a later deploy):
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_not_null;

Which migration checks should run in CI?

Four checks catch most migration outages before they reach production. Each one runs in seconds to a few minutes on a pull request.

1. Replay every migration on an empty database. If the files cannot rebuild the schema, someone changed production by hand, or a migration depends on data only one machine has.
2. Run the test suite against the migrated schema, not against a schema generated from your ORM models. The two can drift apart.
3. Keep applied migrations append-only. Fail the build if a file that already exists on the main branch was edited. Editing an applied migration means production and a fresh database quietly disagree.
4. Flag dangerous statements for review. A small script can fail the build on patterns that need a second look.

// scripts/check-migrations.ts: run in CI on new migration files.
import { readFileSync } from 'node:fs'

const RULES: Array<[RegExp, string]> = [
  [/\bDROP\s+COLUMN\b/i, 'Drop a column only in a contract release'],
  [/\bRENAME\s+(COLUMN|TO)\b/i, 'Rename with expand-contract, not in one step'],
  [/\bCREATE\s+(UNIQUE\s+)?INDEX\b(?!\s+CONCURRENTLY)/i, 'Use CREATE INDEX CONCURRENTLY'],
  [/\bALTER\s+COLUMN\s+\w+\s+(SET\s+DATA\s+)?TYPE\b/i, 'A type change can rewrite the table'],
]

const files = process.argv.slice(2)
let failed = false
for (const file of files) {
  const sql = readFileSync(file, 'utf8')
  if (sql.includes('-- migration-check: reviewed')) continue
  for (const [pattern, message] of RULES) {
    if (pattern.test(sql)) {
      console.error(file + ': ' + message)
      failed = true
    }
  }
}
process.exit(failed ? 1 : 0)

The escape comment matters. Some risky statements are correct, such as the contract step of a rename. The script's job is to make someone look, not to forbid them. Where these checks sit among the other gates is covered in how to set up QA for an early-stage SaaS.

On Sundor Skin, a B2B wholesale platform RAITHub built with 146 PostgreSQL tables, CI replays all 76 migrations on an empty database on every change. It also fails the build if any buyer-scoped table lacks a row-level-security policy, among 530+ automated tests. A missing policy on a new table becomes a failed build, not a production incident. The policy pattern itself is explained in the PostgreSQL row-level security guide.

What should the write-up cover after the incident?

Keep it short and blameless: what happened, when, how it was found, what fixed it, and which check would have caught it. The last item is the one that matters. Every migration incident should end with a new CI rule, a new test or a change to the release order, so the same class of failure cannot ship again. If the honest answer is "nothing would have caught it", you have found the gap to close first.

Why RAITHub for this

RAITHub treats migrations as code with gates: replayed on an empty database in CI, reviewed for locking and backward compatibility, and shipped with expand-contract when a change is not backward compatible. The Sundor Skin pipeline above is that practice running on a 146-table schema. For an incident, RAITHub scopes a fixed job after a free 15-minute technical audit: root cause, the forward-fix or repair plan, and the CI checks that stop a repeat, with the IP assigned to you. For a wider mess of drifted schemas and missing tests, the code rescue service is the better fit.

When you don't need us

• The fix is a code rollback and the schema is already backward compatible. Roll back, then add the CI checks above yourself.
• You need someone in the database within the hour. RAITHub starts with an audit call and a written quote. Use your host's support or an on-call engineer you trust for the outage, then bring RAITHub in for the root cause and the prevention.
• Your provider manages the schema for you, as with some hosted back ends. Their support team knows its restore tooling better than any outside team.
• You need a certified vendor for your procurement. RAITHub is not SOC 2 or ISO 27001 certified.

To get a broken migration diagnosed and fenced off for good, book the free 15-minute technical audit.

Documentation checked on 29 September 2026.

Frequently asked questions

Should I roll back a failed database migration?

Only if nothing important has been written to what the migration added. A down migration that drops a column deletes its data. If the data is intact, a forward-fix, meaning a new migration or a redeploy of compatible code, is usually safer.

How do I know if a migration is blocking my database?

Query pg_stat_activity with pg_blocking_pids(pid). If an ALTER TABLE is waiting and many queries are blocked behind it, cancel that session. The queue usually clears at once.

When should I restore from a backup instead of fixing forward?

When rows were deleted or overwritten and cannot be rebuilt from what remains. Consider restoring into a separate database and copying back only the damaged rows, so you keep the writes made since the incident.

What is an expand-contract migration?

A way to make an incompatible schema change in three releases: add the new structure, move code and data over, then remove the old structure. Old and new code both work at every step, so a deploy never breaks the running app.

What migration checks should run in CI?

Replay every migration on an empty database, run tests against the migrated schema, fail on edits to already-applied migration files, and flag risky statements such as column drops, renames and non-concurrent index builds for review.

Can a failed migration leave the database half-changed?

Yes, for steps that cannot run in a transaction. In PostgreSQL, a failed CREATE INDEX CONCURRENTLY leaves an invalid index behind. Drop it and run the command again, then mark the migration resolved in your tool.