Flyway versioned undo scripts vs. repeatable migrations for zero-downtime rollback

Answer to the exact question

Yes – you can keep repeatable migrations limited to additive, idempotent changes (indexes, views) and use versioned migrations with matching undo scripts for destructive DDL. Flyway’s undo command only works on versioned migrations; it has no built‑in logic for repeatable ones, so the rollback must be orchestrated by an external wrapper script that first runs flyway undo and then re‑applies repeatable migrations if their checksum changed.

Confirmed facts

• Repeatable migrations (R__*) are re‑executed only when their checksum changes; Flyway provides no automatic undo for them.
• Versioned migrations (V__*) with corresponding undo scripts (U__*) are processed by flyway undo and give explicit rollback for destructive changes.
• Flyway 10.x does not expose a flag to conditionally skip undo for repeatable migrations; any such logic must be handled outside Flyway.
• Flyway supports both migration types in the same project; execution order is defined by directory or configuration.

Recommended pattern for a 30‑second, zero‑downtime rollback

1. Design principle: Restrict R__* to additive, idempotent changes; place any destructive DDL (DROP, ALTER, data‑loss) in V__* with a matching U__*.
2. Health‑check hook: When a deployment health check fails, trigger a rollback script (e.g., via CI/CD or a lightweight service). The script should run against the same database used by the deployment.
3. Rollback script steps:

   # 1. Identify the last applied versioned migration (optional, flyway undo defaults to latest)
   flyway info | grep Applied | tail -1 | awk '{print $1}'
   # 2. Undo that migration (and any subsequent versioned migrations if multiple failed)
   flyway undo
   # 3. Re‑apply repeatable migrations if their checksum changed during the rollback window
   flyway migrate
   # 4. Validate the schema state
   flyway validate
   

4. Verification:
   • After flyway undo, run flyway info to confirm the last successful versioned migration is marked Success and no destructive changes remain.
   • Run flyway validate to ensure the current schema matches the expected baseline.

Missing diagnostic detail

To fine‑tune the rollback script, could you confirm whether your health‑check mechanism can identify the exact migration that caused the failure (e.g., via a database flag or CI/CD log)? This would allow the script to target only that migration instead of a blanket flyway undo.