Recovering a Failed Liquibase Migration with Rollback and Manual Reconciliation

Problem: a migration fails mid-deploy and leaves the database in an unknown state

A failed liquibase update can leave a changeset marked as failed, a database lock held, and schema objects partially created. The useful takeaway is to treat recovery as two separate concerns: reversing the schema to a known good point, and then making DATABASECHANGELOG match reality. Automatic rollback is only safe when the changeset defines a block or Liquibase can generate a safe inverse for the change type used.

Desired outcome

Return the database to the last known good migration state, release any Liquibase lock, and leave DATABASECHANGELOG consistent with the actual schema so the next update runs without checksum errors.

Prerequisites

• Read access to DATABASECHANGELOG and DATABASECHANGELOGLOCK on the target database.
• DDL and DML permissions for the rollback or manual fix, run from a DBA workstation or CI runner with the same Liquibase configuration used for deployment.
• The master changelog file and all included changelogs accessible at the path used by the failing run.
• Knowledge of the database platform. DDL auto-commit behavior varies by engine and can prevent transactional rollback of multi-statement changesets.

Identify the failure point from changelog history

DATABASECHANGELOG is Liquibase's audit table. Each executed changeset is recorded with ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED and EXECUTIONSTATUS.

liquibase --url=<jdbc-url> --username=<db-user> --password=<db-pass> --changelog-file=<changelog.xml> history

Run this where you can reach the database. The command lists executed changesets in order. Identify the first changeset with EXECUTIONSTATUS = FAILED or the last successfully executed changeset. Note its ID, author and filename. Also note the most recent tag, count or date you want to roll back to.

Choose a rollback target and preview the SQL

Liquibase rollback supports targeting by tag, count or date. The command requires that each changeset between now and the target either has an explicit block or is of a type Liquibase can auto-generate.

liquibase --url=<jdbc-url> --username=<db-user> --password=<db-pass> --changelog-file=<changelog.xml> rollbackSQL --rollback-count=1

Replace the target with --rollback-tag=<tag-name> or --rollback-date=<yyyy-MM-ddTHH:mm:ss> as appropriate. Review the generated SQL before executing. For common operations like CREATE TABLE, ADD COLUMN and CREATE INDEX, Liquibase can often generate an inverse. Complex refactorings, data migrations, or changesets without a block may produce incomplete or unsafe SQL.

Risk: running rollback without preview can drop data or leave objects orphaned, especially on platforms where DDL auto-commits.

Apply rollback or manual reversal

When automatic rollback is available

If the preview matches the intended recovery state, run the rollback:

liquibase --url=<jdbc-url> --username=<db-user> --password=<db-pass> --changelog-file=<changelog.xml> rollback --rollback-count=1

This updates DATABASECHANGELOG to remove the rolled back changesets.

When automatic rollback is unavailable or unsafe

For changesets without a block, or for change types Liquibase cannot safely reverse, perform manual reversal:

1. Execute the inverse DDL or DML manually using the database client with appropriate permissions.
2. Reconcile DATABASECHANGELOG. Do not delete rows arbitrarily. Use changelogSync to mark the changelog as in sync with the database after the manual fix, or remove only the failed changeset entry if it was never marked ran.

liquibase --url=<jdbc-url> --username=<db-user> --password=<db-pass> --changelog-file=<changelog.xml> changelogSync

changelogSync updates DATABASECHANGELOG to reflect the current changelog without executing changes. Use it only after the schema has been manually brought to the expected state.

If a lock remains after a failed run:

liquibase --url=<jdbc-url> --username=<db-user> --password=<db-pass> --changelog-file=<changelog.xml> releaseLocks

Expected checks after recovery

• Run liquibase validate to verify checksums in DATABASECHANGELOG align with the changelog files.
• Run liquibase history again to confirm the failed changeset is no longer the latest or has been removed, and the lock is cleared.
• Run liquibase updateSQL in a dry-run to confirm the next forward migration produces the expected SQL and does not attempt to reapply the reversed changes.

Limitations and practical notes

Automatic rollback generation is not supported for all change types and may be limited in older Liquibase versions or certain editions. Verify support for your specific changeset before relying on it.

DDL rollback behavior varies by database platform; some engines auto-commit DDL, preventing full transactional rollback of multi-statement changesets.

Manually purging rows from DATABASECHANGELOG without reconciling the actual schema can cause checksum validation failures on subsequent update runs.

For production, run liquibase updateSQL before any update to preview forward SQL, and generate rollbackSQL for the planned target before deployment to reduce risk of irreversible data loss.