MIGRATION_RUNBOOK.md

Database Migration Execution Runbook

Pre-Migration Phase

1. Preparation (T-24 hours)

• Review migration scripts
• Test in staging environment
• Create backup strategy
• Schedule maintenance window
• Prepare rollback plan

2. Pre-Flight Checks (T-1 hour)

# Execute pre-migration checklist
./pre_migration_check.sh

# Create backup
./backup_database.sh

# Verify backup
ls -lh backups/

Migration Execution Phase

3. Execute Migration

# Source configuration
source db_config.sh

# Run migration
./migrate.sh up 001_create_products.sql

# Monitor logs
tail -f logs/migration_*.log

4. Validation

# Run validation queries
psql -f migrations/validation/001_validate_products.sql

# Check migration history
psql -c "SELECT * FROM migration_history WHERE migration_name = '001_create_products.sql';"

Post-Migration Phase

5. Verification Steps

• Schema changes applied correctly
• Data integrity maintained
• Indexes created successfully
• Constraints functioning
• Application connectivity restored

6. Monitoring (First 30 minutes)

• Query performance metrics
• Error logs review
• Application health checks
• User acceptance testing

Troubleshooting Guide

Issue: Migration Fails Mid-Execution

Solution:

1. Check error logs
2. Verify transaction rollback occurred
3. Execute rollback migration if needed
4. Restore from backup if corruption detected

Issue: Performance Degradation

Solution:

1. Check query execution plans
2. Verify indexes created
3. Run ANALYZE on affected tables
4. Consider rollback if severe

Issue: Lock Timeout

Solution:

1. Identify blocking queries
2. Terminate long-running transactions
3. Retry migration
4. Adjust timeout settings if needed

Success Criteria

Migration is successful when:

• All SQL statements executed without errors
• Validation checks pass
• Application functions normally
• Performance within acceptable range
• No data loss or corruption