Recovering Data After an Irreversible Migration

A migration ran, a column is gone, and the down migration your tool generated cannot bring the data back — because the data was never structure, it was values, and DROP COLUMN deleted them the instant it committed. The down re-creates an empty column; the reporting job that alerted you is still returning nulls. This is the scenario that lands an engineer here: not “how do I reverse the schema” but “the schema reversal ran and the data is still gone.” Running the down again does nothing, because a down migration only restores shape, and the information that lived in that shape is now recoverable only from a copy that predates the migration — a point-in-time recovery target, a physical backup, or an audit table that captured the old values before they were destroyed. This page is the recovery runbook for exactly that moment, and then the design change that makes the next irreversible migration recoverable by construction.

Symptom / Error Signatures

The defining symptom is silence. A destructive migration produces no error — DROP COLUMN, TRUNCATE, and a lossy UPDATE are all perfectly legal SQL, so the pipeline reports exit code 0 and the down migration reports success. The loss surfaces downstream: a reconciliation job flags NULL where values used to be, a dashboard drops to zero, or support tickets arrive about vanished records. The tool’s history shows the rollback “worked.”

When you try to re-run the down to fix it, the second signature appears — the reversal restores structure but not contents, and any code that tries to read the old values fails or returns empty. On PostgreSQL a query against the re-added column returns only nulls; on MySQL 8.0 the same. If the down itself is not idempotent, a retry throws the classic guards-missing errors:

-- PostgreSQL · symptom seen when a non-idempotent down is retried
-- Context: read-only reproduction; the first down already dropped the column.
-- ERROR:  column "region_code" does not exist
SELECT region_code FROM orders LIMIT 1;

-- MySQL 8.0 · equivalent retry failure signature
-- Context: the down ran once; the second attempt cannot drop what is gone.
-- ERROR 1091 (42000): Can't DROP 'region_code'; check that column/key exists
ALTER TABLE orders DROP COLUMN region_code;

The third signature is the expand-and-contract trap: an automated reversal of a contract step re-adds the dropped legacy column as empty, “succeeds,” and permanently discards the data that column held — the reversal restored the container and threw away the contents.

Root Cause Analysis

The root cause is a category error baked into every migration tool: the generated down assumes rollback means “make the schema identical to what it was.” That is true for structure and false for data. Applying a change forward and reversing it are not mirror operations, because forward motion can destroy information that reverse motion has no way to reconstruct. A down migration is a DDL script; it has no memory of the rows it deleted. Once DROP COLUMN commits, the only place the old values exist is in a copy taken before the migration ran.

That splits recovery into three sources, and which one you have determines whether recovery is minutes or hours — and whether it is possible at all. The mechanics differ enough between engines to matter:

The lesson the table encodes: PITR and backups recover the database as it was, so you extract the lost column from a restored copy and backfill it into live production. An audit table recovers just the changed values, in place, without standing up a second database — which is why the durable fix is to capture old values before destroying them, not to hope a backup exists. The same asymmetry drives writing safe down migrations for automated rollback: a down that would destroy data must be a no-op, and recovery becomes a data operation, not a schema one.

Immediate Mitigation

Treat a destructive migration as a data-loss incident. Speed matters because WAL/binlog retention and backup windows are finite — the recovery source you need may age out.

1. Stop the pipeline immediately so no further migration steps run and compound the loss, and so ongoing writes do not overwrite the recovery window.

# Context: run from the deploy host; halts the migration runner before the next step.
# Freezes state so a PITR target or backup still predates the damage.
./bin/pipeline pause --reason "destructive migration — data recovery in progress"

2. Identify the recovery point — the exact timestamp or transaction id just before the destructive migration committed. Read it from the migration tool’s history, not from memory.

-- PostgreSQL · read-only · find when the destructive migration committed
-- Context: safe anywhere; the recovery target is one moment before this timestamp.
SELECT version, description, installed_on
FROM flyway_schema_history
WHERE success AND description ILIKE '%region_code%'
ORDER BY installed_on DESC LIMIT 1;

3. Restore a parallel copy to the recovery point — never restore over production. Bring up a scratch instance with PITR or a backup, so you can extract just the lost data.

# Context: run as the DB operator on a NEW host; must NOT target the live cluster.
# PITR replays WAL to the instant before the DROP; the live database is untouched.
pg_basebackup -D /restore/scratch -h backup-source -X stream
# recovery.signal + recovery_target_time set to one second before the migration commit
echo "recovery_target_time = '2026-07-24 14:32:09'" >> /restore/scratch/postgresql.conf

4. Extract the lost column from the parallel copy and backfill live, non-destructively — re-add the column with IF NOT EXISTS, then copy values in, matching on the primary key.

-- PostgreSQL · run as the migration role against LIVE, reading from the restored copy
-- Context: re-expand first (non-destructive), then backfill only the missing values.
ALTER TABLE orders ADD COLUMN IF NOT EXISTS region_code VARCHAR(8);
UPDATE orders o
SET region_code = r.region_code
FROM dblink('dbname=scratch', 'SELECT id, region_code FROM orders')
     AS r(id BIGINT, region_code VARCHAR(8))
WHERE r.id = o.id AND o.region_code IS NULL;

5. Neutralize the destructive migration so it cannot run again — replace its body with an explicit no-op, because rollback for a data-bearing change belongs to the application image, not to a schema reversal.

-- PostgreSQL/MySQL · the down for a backfilled column must NOT drop it
-- Context: rollback is handled by restoring the previous application image.
-- DOWN: intentional no-op — region_code holds recovered data; do not DROP.
SELECT 1;

6. Verify recovered counts against the restored copy before resuming any deploys — recovery is not done until the numbers match.

Permanent Fix / Long-Term Pattern

The durable fix is to design reversibility up front, so no migration is ever irreversible in a way that costs you data. This is a policy, not a heroic recovery: capture data before you destroy it, and forbid destructive steps from running where recovery is impossible. It is the same discipline the parent rollback automation contract enforces — automated reversal restores code and flips flags, but never destroys data.

The first safeguard is soft-delete over hard-drop. Instead of dropping a column or row in the same deploy that stops using it, rename or park it and remove it only in a later, deliberate contract step — the sequencing the expand and contract methodology formalizes. The window between “stop reading” and “actually drop” is your free recovery buffer.

-- PostgreSQL · park instead of dropping · reversible for the whole retention window
-- Context: run as migration role; the old data survives until a later contract step.
ALTER TABLE orders RENAME COLUMN region_code TO region_code_deprecated_20260724;
-- A separate, reviewed migration DROPs it weeks later, once recovery is no longer needed.

The second safeguard is an audit table that captures old values before a destructive change, so recovery never depends on standing up a parallel database. A trigger writes the pre-image; the destructive migration then has a guaranteed, in-place recovery source.

-- PostgreSQL · capture the pre-image before any destructive UPDATE/DELETE
-- Context: run as owner; the trigger must exist BEFORE the destructive migration.
CREATE TABLE orders_audit (id BIGINT, region_code VARCHAR(8), captured_at TIMESTAMPTZ DEFAULT now());
CREATE OR REPLACE FUNCTION capture_region_code() RETURNS trigger AS $$
BEGIN
  INSERT INTO orders_audit(id, region_code) VALUES (OLD.id, OLD.region_code);
  RETURN OLD;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_capture_region BEFORE DELETE OR UPDATE OF region_code ON orders
  FOR EACH ROW EXECUTE FUNCTION capture_region_code();

The third safeguard is a gate that blocks destructive DDL from shipping without a recovery plan, the same enforcement pattern migration pipeline gating applies to the rest of the pipeline. A required check scans for DROP COLUMN, DROP TABLE, TRUNCATE, and DELETE, and fails the build unless the change carries a reviewed waiver confirming an audit table or verified backup exists.

# scripts/require-recovery-plan.sh — block destructive DDL without a recovery source
# Context: pure text scan; no DB connection; runs as a required CI check.
set -euo pipefail
for f in migrations/*.sql; do
  if grep -E -i '\b(DROP\s+COLUMN|DROP\s+TABLE|TRUNCATE|DELETE)\b' "$f" \
     | grep -vq '-- recovery-plan:'; then
    echo "Destructive DDL without a recovery-plan waiver: $f" >&2; exit 1
  fi
done

Underpinning all three, keep every recovery-shaped migration idempotent with IF EXISTS / IF NOT EXISTS guards, following how to write idempotent SQL scripts for safe deploys, so a retrying pipeline can re-apply the re-expand and backfill without erroring. The rule that makes irreversibility survivable: never destroy data in the same migration that stops using it, and never ship a destructive step without a named, verified recovery source.

Verification Checklist

• The recovery point predates the destructive migration by reading the exact commit timestamp from the tool’s history, not from memory.
• The parallel copy was restored to a NEW instance — production was never restored over or written to during recovery.
• Recovered row and value counts match the restored copy before any deploy resumed.
• The destructive migration was neutralized to an explicit SELECT 1; no-op so it cannot re-run.
• A required gate blocks DROP COLUMN, DROP TABLE, TRUNCATE, and DELETE unless a reviewed recovery-plan waiver names an audit table or verified backup.
• Future destructive changes park or soft-delete data first, dropping it only in a later, deliberate contract step.

Frequently Asked Questions

Can a down migration ever restore dropped data? No. A down migration is a DDL script that restores structure, not the values that lived in it. When the up ran DROP COLUMN or TRUNCATE, the data was deleted at commit, and re-creating the column just gives you an empty one. Recovery must come from a copy that predates the migration — a point-in-time recovery target, a physical backup, or an audit table that captured the old values — never from re-running the down.

PITR or a backup — which should I reach for first? Reach for whichever predates the destructive migration with the least data loss and the finest granularity you can restore quickly. Point-in-time recovery replays WAL or binlogs to the exact moment before the migration committed, so it loses the least, but it requires standing up a parallel instance. A recent table-level backup is faster to extract from if it is fresh enough. Either way, restore to a separate instance and extract just the lost data — never restore over live production.

How do I make the next destructive migration recoverable by construction? Capture old values before destroying them and separate “stop using” from “actually drop.” Add an audit trigger that records the pre-image before any destructive change, park columns by renaming instead of dropping them in the same deploy, and gate the pipeline so destructive DDL cannot ship without a reviewed recovery plan. Then a mistaken drop is a low-cost in-place backfill from the audit table, not a full database restore.