Database Migrations I Can Roll Back at 2am

说明与用途

作者阐述了如何通过提供可逆步骤、使用扩展-收缩模式以及正确排序增删改操作，避免数据库迁移导致的数据丢失。

问题

A migration with no down step that dropped a column at 2am caused data loss and a three‑hour recovery effort.

结果

The author now avoids 2am data‑loss incidents by ensuring reversible migrations, using expand‑and‑contract patterns, and ordering migrations correctly.

工作流

• Write the up step and a corresponding down step before shipping.
• Test the migration by running up, then down, then up again locally.
• Split risky changes into expand‑and‑contract stages across multiple deploys.
• Deploy additive migrations before code changes; deploy destructive migrations after code changes.
• Ensure rollback works against the live schema without running a down step.
• Make backfills idempotent and set statement timeouts for flaky migrations.
• Use structured logging to monitor the deploy window.
• Maintain a playbook with logging, retries, and migration rules.

证据

The worst database mistake I ever made was a migration with no down step, run at 2am, that dropped a column before I had a backup.

来源

I test the down step before I deploy. I run up, then immediately run down, then up again.

来源

The expand and contract way takes four steps across two or more deploys.

来源

The single rule that prevents most 2am incidents: additive migrations run before the deploy, destructive migrations run after.

来源