How to test a database migration rollback safely

The symptoms

• •Post-rollback schema diff still contains columns, indexes, or constraints introduced by the forward migration, indicating the rollback did not actually revert the structure.
• •Row counts in one or more tables differ between the pre-migration snapshot and the post-rollback state, suggesting data loss or orphaned writes that survived the rollback.
• •Foreign key violations, check constraint failures, or view recompile errors appear after rollback even though the migration tool reported success.
• •The migration tool reports a 'dirty' state or an applied version that cannot be re-applied, blocking subsequent forward migrations.
• •Application smoke tests against the rolled-back database return different results than the same tests against the pre-migration baseline.

Likely causes

• •The forward migration contained destructive DDL such as DROP COLUMN, DROP TABLE, or TRUNCATE without preserving the original objects in a form the rollback script can restore.
• •The database engine does not wrap DDL in transactions for the affected statements, so a partial failure leaves the schema in an intermediate state with no automatic rollback path.
• •A down script is missing, untested, or was written after the up script, and never validated against a realistic data volume.
• •Data migrations overwrite or backfill columns without recording the original values, so a rollback can restore the schema but not the original cell contents.
• •Dependent objects such as views, materialized views, functions, or grants were invalidated by the forward change and were not recreated by the rollback.
• •Concurrent application traffic during the migration window created rows in the new schema that have no representation in the pre-migration baseline.

First ten minutes

1. 01Stop further writes if a rollback is in progress and capture the current schema and a row count snapshot before any further corrective action, so you have evidence to compare against.
2. 02Confirm whether the migration tool recorded the migration as applied or failed, and whether the engine supports transactional DDL for the statements that were run.
3. 03Verify that a recent logical or physical backup exists and that the backup's restore procedure has been tested recently enough to be trusted as a fallback.
4. 04Identify any non-database components that depend on the new schema, such as application code paths, scheduled jobs, and reports, and decide whether they must be rolled back alongside the database.
5. 05List the tables, columns, indexes, and constraints that the forward migration touched so the rollback test can target exactly those objects rather than the whole database.
6. 06Quarantine the environment from new traffic so the rollback test is not contaminated by concurrent writes that would invalidate the comparison.

Evidence to collect

• •A schema-only dump of the database captured immediately before the forward migration, suitable for textual diff against a post-rollback dump.
• •Row counts per affected table, taken before the forward migration and again after the rollback completes, with the same isolation level and the same counting query.
• •A data fingerprint for each affected table, for example a deterministic hash of a sorted projection of representative columns, so partial data loss can be detected even when counts match.
• •The migration tool's recorded version, applied timestamp, and dirty flag for the target migration, plus the exact forward and rollback scripts that were run.
• •The list of dependent objects that reference the affected tables, including foreign keys, views, functions, and grants, captured before the migration.

Where to look

• •The migration directory in version control, where the up and down scripts for the target version live, including any earlier revisions of the down script.
• •The migration tool's metadata table or state file, which records applied versions, checksums, and execution outcomes for each environment.
• •Database engine logs from the migration window, including DDL statements, lock waits, and any statements that returned errors or were retried.
• •Application logs and access logs from the same time window, to identify traffic that may have written to the new schema during or after the forward migration.
• •Backup storage and backup catalog metadata, to confirm that a recoverable backup exists for the exact pre-migration point in time.

Diagnostic steps

1. 01Confirm that a down script exists for the target migration, is committed to the same repository as the up script, and has been reviewed for parity with the up script's effects.
2. 02Reproduce the migration on a disposable copy of the production data at the same scale, capture a pre-migration schema dump, row counts, and a data fingerprint per affected table.
3. 03Run the forward migration in the disposable environment, verify the migration tool records the version as applied and clean, and record any warnings emitted by the engine.
4. 04Run the rollback in the same disposable environment without applying any compensating forward change, then re-capture schema dumps, row counts, and data fingerprints.
5. 05Diff the pre-migration and post-rollback schema dumps and require that the diff is empty for every object the forward migration touched; investigate any residual difference.
6. 06Run referential integrity and constraint checks against the post-rollback state to confirm that no foreign keys, check constraints, or triggers reference objects the rollback failed to restore.
7. 07Run a focused application smoke test against the rolled-back database, exercising reads and writes that exercise the same code paths as production traffic for the affected tables.

Common mistakes

• •Assuming that the forward migration is safe because it succeeded in staging, without checking that the staging data volume and write patterns exercise the rollback path the same way production does.
• •Writing or applying the down script only after the up script has already run in production, then declaring rollback 'tested' based on a synthetic empty database.
• •Rolling back the database but leaving the application deployed against the new schema, or rolling back the application while the database remains on the new schema.
• •Treating a 'successful' rollback status from the migration tool as proof, without independently diffing schema dumps or comparing row counts.
• •Dropping columns or tables before verifying that no views, reports, or background jobs still reference them, so the rollback restores the schema but leaves broken dependents.

Safe fixes

• •Use an expand-and-contract pattern: add new columns or tables in one release, backfill and dual-write in a second, then remove the old structure in a third, so each step has a reversible counterpart.
• •Wrap the entire forward migration in a transaction where the engine supports transactional DDL, and abort cleanly on the first error so the engine itself performs the rollback.
• •Preserve original values during data migrations by writing them to a sidecar column or sidecar table rather than overwriting in place, so a later rollback can restore the original cell contents.
• •Keep a verified logical or physical backup from immediately before the migration window, and rehearse the restore on a separate host so the backup is known to be recoverable, not just known to exist.
• •Quarantine the test environment from new writes during the rollback test, so the post-rollback snapshot is comparable to the pre-migration snapshot rather than contaminated by concurrent traffic.

Prove the fix

1. 01A textual diff between the pre-migration schema-only dump and the post-rollback schema-only dump returns no differences for any object the forward migration touched.
2. 02Row counts for every affected table match the pre-migration snapshot to the row, and the deterministic data fingerprint per table matches the pre-migration fingerprint.
3. 03Referential integrity checks report zero violations, and all dependent objects such as views, functions, and grants either were not affected or were successfully restored by the rollback.
4. 04The migration tool's recorded state for the target version reverts from applied to not-applied, and a subsequent forward run of the same migration succeeds against the rolled-back database.
5. 05The application smoke test suite that exercises the affected tables passes against the rolled-back database with results equivalent to the pre-migration baseline.

Prevention and next steps

• •Require an up and a down script for every migration in code review, and require that the down script be diffed against the up script before the change can be merged.
• •Run every migration through a CI job that applies and rolls back the change against a disposable copy of production data, and fail the build on any residual schema or row count difference.
• •Practice the full restore from the most recent backup on a recurring cadence, so the backup is known to be recoverable on the day it is needed, not just known to exist.
• •Adopt expand-and-contract as the default pattern for any schema change that is not strictly additive, and document the rollback strategy explicitly in the migration's accompanying notes.

Safe commands and checks

pg_dump --schema-only --no-owner --file=<pre_migration_schema>.sql <dbname>
pg_dump --schema-only --no-owner --file=<post_rollback_schema>.sql <dbname>
diff -u <pre_migration_schema>.sql <post_rollback_schema>.sql
SELECT count(*) FROM <table_name>;
SELECT md5(string_agg(<id_column>::text, ',' ORDER BY <id_column>)) FROM <table_name>;
SELECT version, description, success FROM <schema_history_table> ORDER BY installed_rank;
SELECT conname, conrelid::regclass, confrelid::regclass FROM pg_constraint WHERE contype IN ('f','c') AND conrelid::regclass::text IN ('<table_a>','<table_b>');
SELECT relation::regclass, mode, granted, pid FROM pg_locks WHERE relation = '<table_name>'::regclass;