Rolling back a failed Alembic migration in production

Run alembic current first — with a single-transaction upgrade the database is exactly where it started and there is nothing to roll back, and with transaction_per_migration it is at a known intermediate revision. In both cases the fix is almost always forward, because downgrade() is the least-tested code in the repository. This is the failure path under managing multiple heads and database branching.

Quick Answer

First, establish the state — before changing anything:

$ alembic current --verbose
Current revision(s) for postgresql://…/orders:
Rev: 8f2c14a9b7d3
Parent: c3a1029ef455
Path: alembic/versions/8f2c14a9b7d3_add_order_status.py

If the whole run rolled back (the default single-transaction behaviour), the database is at the revision it started from. Fix the revision and redeploy — there is nothing to undo.

If part of the run committed (transaction_per_migration=True), fix forward with a new revision:

"""Repair the partially applied status column.

The failing revision 9c14ab02f7de added the column and then failed adding the
constraint, because existing rows were NULL. This revision completes the work
in the order that actually succeeds.
"""
revision = "b7d2e1f09a44"
down_revision = "9c14ab02f7de"


def upgrade() -> None:
    op.execute("UPDATE orders SET status = 'unknown' WHERE status IS NULL")
    op.alter_column("orders", "status", nullable=False)


def downgrade() -> None:
    op.alter_column("orders", "status", nullable=True)

Downgrading is the last resort:

$ alembic downgrade -1     # only if you have read the downgrade() and trust it

Read the downgrade() function before running it. It is the least-executed code in the repository, and a dropped column it recreates comes back empty.

Execution Context & Async Workflow Integration

Because the migration ran in a job rather than in the application, the failure is visible as a non-zero exit and the rollout has stopped. That is the state you want: the old replicas are still serving against the old schema, and nothing is degraded except the deploy.

The first thing to establish is the revision in every environment involved, which is worth having as a command rather than a manual sequence:

"""ops/revisions.py — report the current revision across environments."""
from __future__ import annotations

import asyncio
import os

from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine

ENVIRONMENTS = ("staging", "production-eu", "production-us")


async def current_revision(url: str) -> list[str]:
    engine = create_async_engine(url, poolclass=None)
    try:
        async with engine.connect() as connection:
            rows = await connection.execute(text("SELECT version_num FROM alembic_version"))
            return [row[0] for row in rows]
    finally:
        await engine.dispose()


async def main() -> None:
    for name in ENVIRONMENTS:
        url = os.environ[f"DATABASE_URL_{name.upper().replace('-', '_')}"]
        revisions = await current_revision(url)
        marker = "  ← MULTIPLE" if len(revisions) > 1 else ""
        print(f"{name:16} {', '.join(revisions)}{marker}")


if __name__ == "__main__":
    asyncio.run(main())

Reading alembic_version directly rather than shelling out to alembic current means this works against any environment you have a URL for, without needing that environment's configuration — which is what you want at two in the morning.

More than one row for an environment means it is at multiple heads, which is a separate problem covered in [resolving Alembic multiple head revisions errors](' + P + 'resolving-alembic-multiple-head-revisions-errors/) and must be resolved before anything else.

Resolving Warnings, Errors & Common Mistakes

Advanced: Why Fixing Forward Is Almost Always Right

The instinct to reverse a failed change is strong and, for schema migrations, usually wrong. Four reasons compound.

Downgrades are the least-tested code you have. Upgrades run in every environment, on every developer machine, on every CI build. Downgrades run approximately never. Executing an untested code path against a production database during an incident is a poor way to reduce risk.

Most downgrades are lossy. The reverse of drop_column recreates the column empty. The reverse of a type change from text to integer cannot restore values that failed to convert. The reverse of a data migration is generally not expressible at all. A downgrade that runs successfully can still have destroyed information.

The application has already moved on. During a rollout, replicas running the new code may already have written data that only the new schema can hold. Downgrading discards it, which is a data-loss incident caused by the recovery rather than by the original failure.

Forward is testable. A repair revision goes through the same pipeline as any other migration: applied to a fresh database, checked against the models, reviewed. A downgrade run by hand during an incident has none of that.

"""A repair revision reads like an explanation, which is part of its value."""
revision = "b7d2e1f09a44"
down_revision = "9c14ab02f7de"


def upgrade() -> None:
    # 9c14ab02f7de added orders.status and then failed setting NOT NULL, because
    # 41 rows predating the feature had no value. Backfill, then constrain.
    op.execute("UPDATE orders SET status = 'unknown' WHERE status IS NULL")
    op.alter_column("orders", "status", nullable=False)


def downgrade() -> None:
    op.alter_column("orders", "status", nullable=True)

The narrow case where downgrading genuinely is right: a purely additive change, applied minutes ago, that nothing has written to yet, where reverting the application version requires the old schema. Even then, downgrade() should be read before it is run.

Making a Failed Migration a Non-Event

The best response to a failed migration is one that was already planned for. Four practices between them turn it from an incident into a paused deploy.

Keep migrations additive during a rollout. Nothing is dropped or made stricter until the code depending on the old shape is gone. That is exactly the expand-and-contract ladder from zero-downtime schema migration strategies, and its side effect is that almost every migration becomes trivially reversible by simply not using the new column.

Split DDL from data. A revision that adds a column and backfills it has two ways to fail and one ambiguous state. Two revisions have one each and a clear answer from alembic current.

Run migrations before the new code and gate on their success. A failed migration then stops the rollout, and the previous version keeps serving. This is the difference between a paused deploy and a crash loop.

Set lock_timeout. A migration that waits behind an open transaction turns into a deploy that hangs. Failing after ten seconds with a message naming the lock is a diagnosis; hanging is not.

# In env.py, on the migration connection
connection.execute(text("SET lock_timeout = '10s'"))
connection.execute(text("SET statement_timeout = '5min'"))

statement_timeout deserves care: it must be long enough for the largest legitimate DDL in the revision. Setting it too low turns a slow-but-working index build into a failure. Setting it at all is still worth doing, because the alternative is a migration that runs for an unbounded time and nobody can safely interrupt.

Together these change the question after a failure from "how do we undo this" to "what should the next revision say" — which is a much better question to be answering under pressure.

Frequently Asked Questions

Related

• Managing Multiple Heads and Database Branching — The parent guide: revision structure, and the state a failure leaves the graph in.
• Running Alembic migrations in CI/CD pipelines — The job whose non-zero exit is what stopped the rollout.
• Writing data migrations safely in Alembic — Splitting DDL from data so a failure has one clear cause.
• Zero-downtime schema migration strategies — Keeping migrations additive so almost nothing needs reversing.