Data Migration Runbook: Checklist

Overview

Data migrations are among the riskiest operations in software engineering. Unlike code deployments, data migrations cannot be rolled back with a simple kubectl rollout undo. A failed migration can corrupt production data, violate compliance requirements, and cause extended outages. This runbook template structures the migration into verifiable phases: preparation, dry run, execution, validation, and rollback.

When to Use

• For alternatives, see Data Migration: Zero-Downtime Strategies That Work.

Use this resource when:

• Moving data between database versions or engines (MySQL 5.7 to 8.0, PostgreSQL to Aurora)
• Migrating from monolith to microservices (database per service)
• Consolidating multiple data sources into a data warehouse
• Executing large-scale schema changes that require data change
• Migrating between cloud providers (AWS RDS to GCP Cloud SQL)

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

## 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

## 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)

## 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

## 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)

## 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

-- 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

-- 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

## 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

## 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 separates **preparation** (backups, dry runs) from **execution** (the actual migration) and **validation** (data integrity checks). The critical insight is the **decision gate** after the dry run — never run an untested migration in production. The rollback procedure is designed to be fast (minutes, not hours) because data issues compound over time.

## Variants

| Context | Approach | Notes |
|---------|----------|-------|
| Database version upgrade | `pg_dumpall` / `pg_upgrade` | 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 cannot 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

- **Pipeline fails silently**: enable verbose logging and store pipeline artifacts between stages so you can inspect the exact state that failed.
- **Container crashes on startup**: check that environment variables, secrets, and config files are mounted correctly.  Read the first 50 lines of logs before scaling replicas.
- **Deployment rolls back repeatedly**: verify health checks, resource limits, and startup probes.  A failing readiness probe is a common cause of rolling restarts.
- **Slow CI builds**: cache dependencies and docker layers.  Split large test suites into parallel jobs to reduce wall-clock time.
- **Drift between environments**: use infrastructure-as-code and immutable artifacts.



## Production Notes

- **Deploy gradually** using canary or blue-green to catch regressions early.
- **Configure alerts** for error rate, p99 latency, and failure rate before enabling in production.
- **Document the rollback** in the runbook; test the procedure in staging at least once per quarter.
- **Review structured logs** with correlation IDs to trace requests end-to-end during incidents.

## Key Takeaways

- **Apply data migration runbook template** when you need a practical solution for your use case.
- **Monitor performance** after implementation; measure latency, errors, and resource usage before and after.
- **Check the Troubleshooting section** for common failures; most have documented root causes with fixes.
- **Keep dependencies updated** and run tests in CI to prevent production regressions.

## FAQ

### How do I handle very large migrations (TB+)?

Use an incremental approach: migrate historical data in batches during low-traffic periods, then use CDC for the final delta. Tools like AWS DMS, Debezium, or custom batch scripts work well. Plan for days or weeks, not hours.

### What if source and target schemas differ?

Document the change in the migration script and validate every changed field. Common issues: timezone conversions, character encodings, enum values, and nullable columns. Test edge cases in the dry run.

### How long should I keep source data after migration?

Minimum 30 days for most systems. For compliance-regulated data, follow your retention policy (often 90 days or longer). Keep until you're confident the migration is stable and all downstream consumers have verified their integrations.

## Common Production Pitfalls

- Leaving required fields blank or using vague one-word answers.
- Filling the document once and never updating it after scope or decisions change.
- Storing the document where the team does not look during incidents or reviews.
- Not assigning an owner, due date, or review cadence.
- Copying boilerplate without removing sections that do not apply.
- Skipping version control, which makes rollback and accountability impossible.
- Failing to link the document to related decisions or follow-up actions.
- Avoiding quarterly reviews that would retire stale or unused sections.