Database

Recover a database after a half-run migration

The danger here is not the broken schema — it is the second attempt. Take a byte-level snapshot before touching anything, then work out exactly which statements committed.

Stop doing this first

Do not re-run the same migration hoping it completes.
Do not run VACUUM, REINDEX or pg_resetwal before you have a snapshot.
Do not let the app auto-migrate on next start — disable that first.

The safe order of operations

1
Stop the application writing to the database.
2
Take a physical copy (file copy for SQLite, pg_dump or a base backup snapshot for Postgres) before any repair.
3
Read the migration log and the database's own schema to find which statements actually committed.
4
For SQLite corruption, use .recover rather than .dump — it salvages data from damaged pages.
5
For Postgres, prefer point-in-time recovery from a backup over hand-editing the catalog.

Commands that do the work

Snapshot SQLite

cp app.db app.db.snapshot && sqlite3 app.db 'PRAGMA integrity_check;'

Salvage a damaged SQLite file

sqlite3 app.db '.recover' > recovered.sql

Snapshot Postgres

pg_dump --format=custom --file=pre-repair.dump "$DATABASE_URL"

See what the schema really looks like

psql "$DATABASE_URL" -c '\d+ affected_table'

Realistic odds

Partially to likely recoverable, depending on whether a pre-migration backup exists.

Your case will differ in the details. Load this scenario into the recovery form and add what you already tried — you'll get a plan for your exact situation.

Get my recovery plan