Repairing failed Flyway migrations

The database migration scripts themselves should never generate an error, however there are external factors that can cause errors.

The steps to recover are dependent on the database vendor.

PostgreSQL

All migrations are run in transactions so PostgreSQL will automatically roll back if it encounters an error. So simply fix the error and rerun the migration.

Oracle

Oracle doesn't support transactional DDL so any changes are committed immediately.

In general there are 2 options for dealing with migration errors.

1. Manually rollback, fix the issue and run migrate again
2. Manually fix the issue, manually run the remaining migration statements and manually update the flyway status to mark the migration as successful.

Note: Depending on when the migration failed and the statements that it had already run, option 1 may not be possible.

Option 1

• Run flyway repair to remove any failed migrations from the Flyway history
• Undo any statements that were executed as part of the failed migration that executed before the failure
• Fix the issue that caused the migration error
• Run flyway migrate again to rerun the failed migration and the remaining migrations

Option 2

• Fix the issue that caused the error

• Manually run the remaining statements that are part of the failed Flyway migration

• Update the Flyway status for the failed migration in the database: (assuming the migration failed in 2.3.11)

  update "schema_version" set "schema_version"."success" = 1 where "schema_version"."version" = '2.3.11'

• Run flyway migrate to run the remaining migrations