阅读中文版本

Roll Forward a Failed DB Migration

Prefer a corrective forward migration over a rollback when a schema change has already run, to avoid data-loss surprises.

migrate status
psql -c "SELECT * FROM schema_migrations ORDER BY version DESC LIMIT 5;"

ALTER TABLE orders ADD COLUMN IF NOT EXISTS note text;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_note ON orders(note);

What this recipe does

Decide and implement a forward corrective migration when a schema change fails after partial application.

A migration adds a column then fails on a constraint, leaving the schema half-applied and deployments blocked.

Prerequisites

• A migration tool with version tracking
• A staging copy to rehearse
• A backup of the schema

Procedure

1. Inspect the migration state and applied steps.

migrate status
psql -c "SELECT * FROM schema_migrations ORDER BY version DESC LIMIT 5;"

Expected result: The partially applied version and the exact failing step are visible.

2. Write a forward migration that completes the change safely.

ALTER TABLE orders ADD COLUMN IF NOT EXISTS note text;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_note ON orders(note);

Expected result: A new forward migration reconciles the schema without destructive rollback.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the forward migration applies cleanly, the schema matches expectations, and deployments unblock.

Troubleshooting

Duplicate column on retry — Use IF NOT EXISTS guards so a rerun of a partially applied migration is safe.

Rollback drops a column with data — Rollbacks that drop data are risky; a forward fix preserving data is usually safer.

Limitations

• Forward-only means no schema history rewrite.
• Requires careful idempotency in each migration.
• Does not recover data already lost.