Data Migration Runbook Template with Rollback Steps

Overview

Data migrations are among the riskiest operations in software engineering. Unlike a bad deployment, a failed migration can’t be undone with kubectl rollout undo. The writes that landed during the attempt are already mixed into the dataset. A botched migration can corrupt production data, violate retention or compliance requirements, and stretch an outage well past the planned window.

This runbook template structures a migration into five verifiable phases (preparation, dry run, execution, validation, and rollback) with an explicit go/no-go gate between the rehearsal and production. Copy it into your repo, fill in the blanks, and rehearse it before the real run. For schema-only changes, pair it with the schema evolution guide; for the post-cutover checklist, use the post-deployment checklist template.

When to Use

Use this runbook when:

• Moving data between database versions or engines (MySQL 5.7 to 8.0, self-hosted PostgreSQL to Aurora)
• Splitting a monolith’s database into per-service stores
• Consolidating several data sources into a warehouse or a new primary
• Executing schema changes large enough to require rewriting existing rows
• Migrating between cloud providers (AWS RDS to GCP Cloud SQL)

You don’t need this runbook for:

• Routine schema migrations on small, low-traffic tables: your ORM migration tool or Flyway/Liquibase already covers that path
• Managed-service upgrades where the provider runs validation and rollback for you (for example, an RDS blue/green deployment)
• Read-only backfills that can simply be re-run if they fail; the runbook’s value is coordinating writes, verification, and rollback under pressure

For strategy-level guidance on zero-downtime approaches before you reach the runbook stage, see Data Migration: Zero-Downtime Strategies That Work.

Prerequisites

Before starting:

• Full backup of source and target systems completed and verified
• Migration script tested on a dataset with production-like volume
• Downtime window approved by stakeholders (if applicable)
• Rollback plan documented and tested
• Monitoring and alerting configured for both source and target

Solution

# Data Migration Runbook: `<Migration Name>`

## 1. Pre-Migration Checklist

### Source System
```bash
pg_dump -h source.db.internal -U admin mydb | gzip > /backups/pre-migration.sql.gz
gunzip -t /backups/pre-migration.sql.gz

## Record baseline metrics
psql -h source.db.internal -c "SELECT pg_size_pretty(pg_database_size('mydb'));"
psql -h source.db.internal -c "SELECT COUNT(*) FROM orders;"
psql -h source.db.internal -c "SELECT MAX(updated_at) FROM orders;"
```

| Metric | Value | Notes |
|--------|-------|-------|
| Database size | ______ | |
| Table row counts | ______ | |
| Latest update timestamp | ______ | |
| Active connections | ______ | |
| Replication lag | ______ | |

### Target System
- [ ] Target schema created and matches source structure
- [ ] Target indexes built and validated
- [ ] Target storage capacity > 2x expected data size
- [ ] Network connectivity verified between source and target
- [ ] Target performance baseline established

### Application
- [ ] Feature flags configured for dual-write or read-after-write
- [ ] Application code deployed that supports both old and new systems
- [ ] Monitoring dashboards updated with target system metrics

## 2. Migration Strategy Selection

| Strategy | Downtime | Complexity | Use Case |
|----------|----------|------------|----------|
| Big Bang | Minutes to hours | Low | Small datasets (< 100GB), simple schema |
| Incremental / Batch | Near-zero | Medium | Large datasets, can tolerate eventual consistency |
| Dual Write | Zero | High | Live systems requiring 100% availability |
| CDC (Change Data Capture) | Near-zero | High | Continuous replication, minimal downtime |

### Decision Record
**Selected strategy:** ______

**Justification:** ______

## 3. Dry Run Execution

```bash
## Run migration on a copy of production data
## Do NOT connect to production systems

cp /backups/pre-migration.sql.gz /tmp/dry-run.sql.gz
gunzip /tmp/dry-run.sql.gz

## Execute migration script
psql -h target-staging.db.internal -f /tmp/dry-run.sql

## Validate dry run
./scripts/validate-migration.sh \
  --source source-staging.db.internal \
  --target target-staging.db.internal
```

| Dry Run Result | Status |
|----------------|--------|
| Duration | ______ |
| Rows migrated | ______ |
| Errors encountered | ______ |
| Validation passed | [ ] |

**Decision Gate:** Only proceed to production if dry run completed without errors and validation passed.

## 4. Production Migration Execution

### Step 4a: Final Backup
```bash
## Create point-in-time backup immediately before migration
aws rds create-db-snapshot \
  --db-instance-identifier source-db \
  --db-snapshot-identifier pre-migration-$(date +%Y%m%d-%H%M%S)
```

### Step 4b: Stop Writes (if using Big Bang)
```bash
## Set application to read-only
curl -X POST http://app.internal/admin/maintenance-mode

## Verify no active writes
psql -h source.db.internal -c "SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active';"
```

### Step 4c: Execute Migration
```bash
## Log migration start time
MIGRATION_START=$(date -u +%Y-%m-%dT%H:%M:%SZ)
echo "Migration started: $MIGRATION_START"

## Execute migration
psql -h target.db.internal -f migration-script.sql 2>&1 | tee migration.log

## Log migration end time
MIGRATION_END=$(date -u +%Y-%m-%dT%H:%M:%SZ)
echo "Migration ended: $MIGRATION_END"
```

### Step 4d: Resume Writes (if applicable)
```bash
## Verify target is healthy before switching writes
curl -X POST http://app.internal/admin/target-health-check

## Switch application to target
curl -X POST http://app.internal/admin/switch-datastore \
  -H "Content-Type: application/json" \
  -d '{"target": "new-database"}'

## Resume normal operations
curl -X POST http://app.internal/admin/normal-mode
```

## 5. Post-Migration Validation

### Row Count Verification
```sql
-- Compare row counts for all major tables
SELECT 'source_orders' as table_name, COUNT(*) as row_count FROM source.orders
UNION ALL
SELECT 'target_orders', COUNT(*) FROM target.orders
UNION ALL
SELECT 'source_users', COUNT(*) FROM source.users
UNION ALL
SELECT 'target_users', COUNT(*) FROM target.users;
```

### Data Integrity Checks
```sql
-- Checksum comparison for critical tables
SELECT 'source', SUM(CHECKSUM(id, amount, created_at)) FROM source.payments
UNION ALL
SELECT 'target', SUM(CHECKSUM(id, amount, created_at)) FROM target.payments;

-- Verify no NULL values in required columns
SELECT COUNT(*) FROM target.orders WHERE customer_id IS NULL;
SELECT COUNT(*) FROM target.orders WHERE created_at IS NULL;
```

### Application Smoke Tests
```bash
## Critical user flows
./scripts/smoke-test.sh --environment=production

## Performance baseline comparison
./scripts/performance-test.sh --target=new-db --baseline=old-db
```

| Validation Check | Source | Target | Match | Time |
|------------------|--------|--------|-------|------|
| Total row count | ______ | ______ | [ ] | ______ |
| Table-level counts | ______ | ______ | [ ] | ______ |
| Checksum for payments | ______ | ______ | [ ] | ______ |
| NULL constraint checks | N/A | ______ | [ ] | ______ |
| Smoke tests pass | N/A | ______ | [ ] | ______ |
| Performance within 10% | ______ | ______ | [ ] | ______ |

## 6. Rollback Procedure

### Trigger Conditions
Rollback if ANY of the following occur:
- Error rate > 1% after migration
- Data integrity check fails
- Performance degradation > 50%
- Customer-facing feature broken

### Rollback Steps
```bash
## 1. Stop writes to target immediately
curl -X POST http://app.internal/admin/maintenance-mode

## 2. Switch application back to source
curl -X POST http://app.internal/admin/switch-datastore \
  -d '{"target": "source-database"}'

## 3. Resume operations on source
curl -X POST http://app.internal/admin/normal-mode

## 4. DO NOT DELETE target data until root cause is resolved
## 5. Document all findings for postmortem
```

| Rollback Step | Status | Time |
|---------------|--------|------|
| Maintenance mode activated | [ ] | ______ |
| Source restored as primary | [ ] | ______ |
| Application switched | [ ] | ______ |
| Smoke tests passed on source | [ ] | ______ |
| Target data preserved | [ ] | ______ |

## 7. Post-Migration Actions

- [ ] Monitor target system for 24 hours minimum
- [ ] Compare error rates between pre and post migration
- [ ] Validate backup of target system
- [ ] Update runbook with actual duration and issues encountered
- [ ] Schedule cleanup of source data (after 30-day retention)
- [ ] Document lessons learned
- [ ] Close incident channel when stable

Explanation

The runbook enforces separation between three concerns that teams routinely blur under pressure: preparation (backups, baselines, a rehearsal), execution (the migration itself), and validation (proving the data moved correctly). The most important line in the template is the decision gate after the dry run: if the rehearsal didn’t finish cleanly on production-scale data, the production run doesn’t start.

Three details are worth understanding before you adapt it:

• The dry run must use production-scale data. A 5 GB staging subset won’t reveal the lock contention, replication lag, or memory pressure you hit at 500 GB. If you can’t clone production, at least replay a representative slice.
• Row counts alone aren’t validation. They catch dropped tables, not silent corruption: a truncated varchar, a timezone shift, charset mojibake. That’s why the template pairs counts with checksums on a critical table and NULL checks on required columns.
• The rollback plan assumes the source still works. Rollback here means “switch the app back”, not “restore from backup”. Restoring a 500 GB dump mid-incident takes hours; flipping a connection string takes seconds. Keep the source intact until the new system has survived real traffic.

Companion files: the data-migration-runbook-template companion repo has the runbook as a standalone file plus a validate-migration.sh script that compares row counts between source and target.

Choosing a Migration Strategy

The strategy table inside the template is a decision aid, not decoration. Pick based on three constraints.

How much downtime can you tolerate? If the answer is “none”, Big Bang is off the table and the choice is between dual-write and CDC-based replication. Dual-write pushes the burden into application code (every write path must hit both stores and survive partial failure), while CDC tools like Debezium or AWS DMS read the source’s transaction log and leave the app untouched.

How big is the data? Under ~100 GB with a maintenance window, a dump-and-restore Big Bang has the fewest moving parts. Past a few hundred GB, restore time usually exceeds any sane window, so you need incremental replication plus a short cutover.

How consistent must the cutover be? Eventual consistency during catch-up is fine for analytics; it isn’t for order processing. If reads-after-write must be exact, plan a brief write freeze at cutover: seconds, not the whole migration duration.

Whatever you choose, the runbook’s shape stays the same: rehearse, gate, execute, validate, be ready to go back.

Variants

Context	Approach	Notes
Database version upgrade	pg_upgrade / pg_dumpall	Test on identical OS and PostgreSQL versions
Cloud provider migration	AWS DMS / GCP Database Migration Service	Built-in validation, but monitor replication lag
Microservices extraction	Dual-write pattern	Complex, but zero downtime; requires application changes
Data warehouse ETL	Batch loads with Airflow	Schedule during low-traffic windows
NoSQL to SQL	Custom change scripts	Schema design is the hardest part; test queries thoroughly

What Works

1. Always run a dry run on production-scale data in an isolated environment
2. Never modify the source during migration; read-only access prevents accidental corruption
3. Validate incrementally: check row counts per table, not just totals
4. Preserve both systems until validation is complete and stable
5. Document actual vs. estimated duration: improves future planning

Common Mistakes

1. Not testing with production data volume: small datasets hide performance issues
2. Modifying source data during migration: creates inconsistency that can’t be reconciled
3. Skipping rollback rehearsal: discovers rollback doesn’t work when it’s needed most
4. Deleting source data too early: validation may reveal issues hours after migration
5. Not monitoring application behavior: database migration success != application success

Troubleshooting

• CDC replication lag never catches up: the source generates changes faster than the target applies them. Check the tool’s apply-rate metrics and the target’s IOPS before cutover; if lag grows steadily, pause non-essential writes or scale the replication instance.
• Checksum or row-count mismatch: first rule out a moving target: tables still receiving writes never match. Re-check after the write freeze; if the gap persists, diff primary keys to locate the missing range.
• Migration locks on hot tables: DDL or bulk updates on a live table queue behind long transactions. Inspect pg_stat_activity for blockers before starting, and kill or wait out idle-in-transaction sessions.
• Target disk fills mid-migration: indexes, WAL, and temp space can push the target to 2-3x the source’s data size. The 2x capacity check in the template exists for this; don’t skip it.
• App connects but misbehaves after cutover: search_path, collation, or timezone differences between source and target. Compare SHOW outputs for the settings your app depends on during the dry run.

Production Notes

• Rehearse the rollback in staging at least once; teams that skip the rehearsal discover missing permissions or stale connection strings during the incident.
• Keep the source intact and read-only until the target has served real traffic for the full retention window listed in the runbook.
• Watch application-level metrics, not only database metrics: a migration can look clean while checkout error rates quietly climb.
• Record the actual duration of each phase in the runbook; those numbers become the baseline for the next migration’s estimate.

Key Takeaways

• The decision gate after the dry run is the most valuable line in the runbook: never start a production migration on an untested script.
• Rollback means switching the app back to a preserved source, not restoring a backup mid-incident.
• Validate per-table, not in aggregate: a matching total row count can hide single-table corruption.
• Treat the filled-in runbook as an asset. Actual vs. planned durations are what make the next estimate trustworthy.

Adoption Pitfalls

• Storing the runbook in a wiki nobody opens during incidents: keep it next to the code or in the on-call repo.
• Copying the template without deleting sections that don’t apply: an irrelevant rollback section erodes trust in the document mid-incident.
• Leaving ______ placeholders unfilled: a runbook without real hostnames, thresholds, and owners is decoration.
• Never updating the document after the migration: the actual timings and gotchas are the most valuable part for next time.
• Running it without a named owner: when everyone is responsible for the runbook, nobody keeps it current.