Guide: Setting up a rollback plan before you cutover

A common failure pattern in database migration postmortems is the conflation of "readiness to cutover" with "having a functional rollback procedure." Teams often validate that the new system passes acceptance tests under load, but treat rollback as a simple reversion of configuration flags. This is a critical oversight. A true rollback plan is not an assumption; it is a separate, equally complex operational procedure that must be designed, documented, and tested *before* the cutover event. This guide details the architectural and procedural components required to establish a rollback capability that is both swift and data-safe.

The core principle is that rollback is a data migration problem, not merely a network routing one. Your primary concern is data divergence during the period the new system is live. A simple DNS switch-back is catastrophic if the legacy database has missed writes. Therefore, the plan must be constructed around **data synchronization states**.

**Phase 1: Pre-Cutover Rollback Architecture**
You must decide on and implement one of two synchronization patterns *during* the dual-write or live migration phase:

1. **Bidirectional Synchronization (Active-Active):** Both old (System A) and new (System B) databases are kept transactionally consistent throughout the migration window using change data capture (CDC) or application-level dual writes. This makes rollback trivial but imposes significant performance overhead and complexity.
2. **Unidirectional Sync with Logging (Active-Passive):** All writes are sent to System B, but a complete audit log of all changes (preferably in the order they were applied) is streamed to a durable queue. System A is kept as a read-only replica or static copy. This is more common.

For Pattern 2, a practical implementation using a CDC tool like Debezium for a PostgreSQL migration might look like this pre-cutover setup:

```sql
-- On the legacy PostgreSQL (Source)
CREATE PUBLICATION migration_pub FOR ALL TABLES;

-- On the new system (Target), and also on a staging instance of the old system for rollback testing
CREATE SUBSCRIPTION migration_sub
CONNECTION 'host=source_db port=5432 dbname=prod user=replicator'
PUBLICATION migration_pub;
```

The critical addition is a **verified rollback script** that consumes the audit log from the point of cutover and replays transformations *back* to the legacy schema. This script is not "reverse the ETL." It must handle:
* Primary key collisions from newly created records in System B.
* Data type transformations (e.g., a JSON column in B to a relational set in A).
* Deletion of records in B that never existed in A.

**Phase 2: The Rollback Runbook & Triggers**
The procedure must be a concrete, step-by-step runbook, not a conceptual outline. It should include:

* **Clear, measurable rollback triggers:** Defined not as "something is slow," but as "P95 latency > 500ms for 15 minutes" or "> 0.1% of writes result in constraint violations within the first hour."
* **Sequential, timed steps:**
1. Immediate cessation of all write traffic to System B (via feature flag, load balancer config).
2. Capture the exact last committed transaction ID or timestamp from the audit log.
3. Execute the pre-tested rollback SQL/application script to synchronize System A from the log.
4. Validate data integrity between the re-synced System A and the audit log for a sample of key entities.
5. Re-point all read and write traffic back to System A.
6. Document the failure mode and freeze changes to System B for analysis.
* **Designated decision authority:** A single person authorized to call the rollback, with a backup.

**Phase 3: Mandatory Dry-Run**
The only way to trust the runbook is to execute it against production-sized data in a staging environment. This dry-run must:
* Use a recent production backup for System A.
* Simulate cutover, run synthetic write traffic against System B for a representative period (e.g., 2 hours).
* Execute the full rollback runbook, including traffic re-routing.
* Measure two key outcomes: **Recovery Point Objective (RPO)** – how much data was lost? (Should be zero.) and **Recovery Time Objective (RTO)** – how long did the rollback and traffic restoration take?

In our last migration from a monolithic PostgreSQL to a distributed NewSQL platform, the cutover itself took 90 seconds. The fully tested rollback procedure, however, was budgeted for 47 minutes based on three dry-runs. This discrepancy is normal and acceptable if acknowledged. We did not need to rollback, but knowing the 47-minute RTO allowed the business to accept the risk profile. The rollback plan is not an admission of likely failure; it is the primary enabler of a confident cutover.