Zero Downtime Database Migration: Don't Break Production

Let me tell you about my first production migration disaster. It was 2019, we had a 2TB PostgreSQL 9.6 database that needed to move to AWS RDS. "How hard could it be?" - famous last words. Took the site down for 6 hours on a Tuesday morning.

The CEO called me at 3am asking why our biggest customer couldn't place orders. That's when I learned that "just a quick database migration" can cost you $50K in lost revenue and almost your job.

What Actually Breaks During Migrations

Here's what nobody tells you about database migrations - they fail in the stupidest ways:

Connection Pool Exhaustion

Your application has 100 database connections configured, but during migration you're running dual-writes to both databases. Suddenly you need 200 connections. PostgreSQL 12.8 defaults to 100 max_connections, so half your writes start failing with FATAL: remaining connection slots are reserved for non-replication superuser connections.

I learned this the hard way - PgBouncer saved my ass when I had to handle the connection math during our user table migration. The PostgreSQL documentation explains connection limits in detail, but they don't warn you about the dual-write connection doubling problem.

Replication Lag Hell

PostgreSQL logical replication looks great in the docs. In practice, it falls behind during high load. I've seen 10+ minute replication lag during peak hours, which means your new database is serving stale data while users are adding new orders.

During our 2TB migration, lag hit 15 minutes during our morning traffic spike. Had to throttle the bulk migration and add monitoring alerts to catch this shit before customers noticed. PostgreSQL logical replication documentation covers tuning parameters, and Uber's blog post shows how they handled similar lag issues at scale.

Timezone Fuckery

You think your timestamps are stored correctly? Think again. PostgreSQL TIMESTAMP WITHOUT TIME ZONE columns become TIMESTAMP WITH TIME ZONE on the target. All your stored times are suddenly off by your server's timezone offset. I spent 8 hours debugging why all our scheduled jobs were running at the wrong times. PostgreSQL's timezone documentation is comprehensive but this Stack Overflow thread explains the practical differences better. The Postgres Wiki on timezone handling has saved me multiple times.

Foreign Key Cascade Nightmares

That innocent foreign key constraint with ON DELETE CASCADE? During migration, it decided to delete 50K related records when I was just trying to clean up test data. No warning, no rollback - just gone.

Two hours of explaining to the VP of Engineering why half our user profiles disappeared. Pro tip: disable foreign key constraints during migration, re-enable after you're done shitting your pants. MySQL's constraint documentation covers constraint handling, and PostgreSQL's approach to deferrable constraints. This comprehensive migration guide covers foreign key handling strategies.

The Three Things That Actually Matter

After breaking production multiple times, here's what I learned:

1. Test Your Rollback First

Don't test your migration strategy - test your rollback strategy. When shit hits the fan at 2am, you need to be able to switch back to the old database in under 60 seconds.

I use pg_basebackup for point-in-time recovery that actually works. Practiced the rollback procedure 12 times before our production migration. Good thing, because I had to use it twice during the actual migration when replication lag spiked to 45 minutes. This comprehensive guide on PostgreSQL backup and recovery is essential reading, and Percona's backup guide covers large database strategies.

2. Monitor Everything, Trust Nothing

Set up alerts on replication lag, connection counts, and query performance. I use Prometheus with custom metrics to track dual-write success rates. If replication lag hits 30 seconds, the migration stops automatically. This Prometheus PostgreSQL exporter provides essential migration metrics, and Grafana's PostgreSQL dashboard visualizes them perfectly.

Learned this after our order processing started serving stale inventory data. Customers were buying products we didn't have in stock. That was a fun Monday morning. DataDog's database monitoring guide and New Relic's best practices helped me set up proper alerting.

3. Staging Environment Must Be Identical

Your staging database with 1GB of data will not reveal the same issues as production with 500GB. I learned this when a migration that took 10 minutes in staging took 4 hours in production because of checkpoint frequency differences.

Spent all night with the CEO texting me "status updates" every 15 minutes. Now staging has the same hardware specs, PostgreSQL config, and data volume as production. Painful lesson but worth it. This production parity guide explains why dev/staging/prod consistency matters, and PostgreSQL's configuration tuning guide helps match performance characteristics.

The truth is, every database migration is different, and most of them go wrong in ways you didn't expect. But if you test the rollback procedure, monitor the shit out of everything, and have staging that actually matches production, you might not break your site. This comprehensive migration checklist and GitHub's migration best practices provide additional safety nets that have saved my ass multiple times.

Look, I'm going to save you 40 hours of debugging by telling you exactly what breaks and how to fix it. Skip the theory - here's what happens in production.

Phase 1: Don't Skip the Boring Shit (Assessment)

Everyone wants to jump straight to the migration. Don't. I spent 3 weeks cleaning up a migration because I skipped this step.

Find Your Biggest Tables (They'll Fuck You Over)

-- PostgreSQL: Find tables that will ruin your day
SELECT schemaname,tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as \"Size\"
FROM pg_tables 
WHERE schemaname NOT IN ('information_schema','pg_catalog')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Anything over 50GB needs special handling. My user_events table was 800GB and took 14 hours to migrate. Should have chunked it instead of trying to dump the whole thing at once.

Check Your Connection Limits (Before They Check You)

Your production app is probably using connection pooling with 100-200 connections. During migration, you'll need double that. Check this:

-- See current connections
SELECT count(*) FROM pg_stat_activity;
-- See your limit
SHOW max_connections;

If you're close to the limit, increase max_connections or your migration will randomly fail with FATAL: sorry, too many clients already. PostgreSQL's connection tuning documentation covers the settings, and this connection pooling guide explains the architecture.

Found this out the hard way when our checkout flow started throwing 500 errors during Black Friday prep. Perfect timing. Heroku's connection pool guide and Amazon RDS documentation helped me understand the limits better.

Phase 2: Dual-Write Hell (It Will Break)

Dual-write sounds simple: write to both databases. It's not. Here's what actually happens:

Network Timeouts Will Destroy You

## This code WILL fail in production
def dual_write_user(user_data):
    old_db.execute(\"INSERT INTO users (...) VALUES (...)\")  # Works
    new_db.execute(\"INSERT INTO users (...) VALUES (...)\")  # Random timeout

The first write succeeds, the second fails. Now you have inconsistent data. Here's the fix:

import uuid
from contextlib import contextmanager

@contextmanager
def dual_write_transaction():
    tx_id = str(uuid.uuid4())
    old_tx = old_db.begin()
    new_tx = new_db.begin()
    try:
        yield tx_id
        old_tx.commit()
        new_tx.commit()
    except Exception as e:
        old_tx.rollback()
        new_tx.rollback()
        # Log for manual recovery
        log_failed_dual_write(tx_id, e)
        raise

Connection Pool Exhaustion (The Silent Killer)

Your app has 100 connections to the old database. Now you need 100 to the new one too. Except your PgBouncer config only allows 100 total. Half your writes start failing with no warning. This PgBouncer configuration guide is essential reading, and 2ndQuadrant's tuning article covers production settings.

I learned this at 2am when our payment system stopped working. Double your connection limits before starting. Nothing worse than explaining to the CTO why credit card processing is down because you forgot basic math. Zalando's connection pooling post and Spotify's database scaling story show how the pros handle this.

Race Conditions in Foreign Keys

## This will randomly fail with foreign key violations
def create_order_with_items(order_data, items):
    # Write order first
    order_id = dual_write_order(order_data)
    # Write items - but new DB might not have the order yet
    for item in items:
        item['order_id'] = order_id
        dual_write_item(item)  # FOREIGN KEY VIOLATION

Solution: Use deferred constraints or batch the writes. Took me 3 hours to figure out why orders kept failing validation - turned out the race condition only happened under high load. PostgreSQL's deferred constraints documentation explains the syntax, and this race condition debugging guide helped me understand the timing issues.

Phase 3: Historical Data Migration (The Long Haul)

This is where migrations die. 500GB of data doesn't migrate in 30 minutes like your test did with 1GB.

Chunking That Actually Works

#!/bin/bash
## Don't use pg_dump for huge tables - it locks them
## Use COPY instead

table_name=\"user_events\"
chunk_size=1000000
max_id=$(psql -t -c \"SELECT max(id) FROM $table_name\" source_db)

for ((start_id=1; start_id<=max_id; start_id+=chunk_size)); do
    end_id=$((start_id + chunk_size - 1))
    
    echo \"Processing chunk: $start_id to $end_id\"
    
    # Use COPY - it's faster than INSERT
    psql source_db -c \"\\COPY (SELECT * FROM $table_name WHERE id BETWEEN $start_id AND $end_id) TO STDOUT\" | \
    psql target_db -c \"\\COPY $table_name FROM STDIN\"
    
    # Check if chunk worked
    source_count=$(psql -t -c \"SELECT count(*) FROM $table_name WHERE id BETWEEN $start_id AND $end_id\" source_db)
    target_count=$(psql -t -c \"SELECT count(*) FROM $table_name WHERE id BETWEEN $start_id AND $end_id\" target_db)
    
    if [ \"$source_count\" != \"$target_count\" ]; then
        echo \"CHUNK FAILED: $start_id to $end_id\"
        exit 1
    fi
    
    # Don't overwhelm the database
    sleep 5
done

Monitor Everything (Or Die Debugging)

Set up Prometheus metrics for:

• Replication lag
• Connection counts
• Disk space (migrations eat disk like crazy)
• Query performance

I use this query to track replication lag:

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag_seconds;

If lag hits 60 seconds, stop the migration. Your new database is too far behind. I spent a whole weekend debugging inconsistent data because I ignored lag warnings. This Grafana dashboard saved my sanity during the next migration. PostgreSQL's replication monitoring docs explain the lag queries, and this comprehensive monitoring guide covers alerting strategies.

Phase 4: Shadow Reads (Find Your Bugs Before Users Do)

OK, this is where it gets interesting. You think your migration is working fine, then users start complaining that their search results are wrong or their timezone settings are fucked.

Run identical queries on both databases and compare results. This catches the weird shit that breaks in production. Honestly, I wish I'd known about shadow reads before my first disaster - would have saved me from looking like an idiot in front of the entire engineering team.

Real Bugs I Found With Shadow Reads

1. Timezone issues: TIMESTAMP vs TIMESTAMPTZ - all times were off by 8 hours
2. Case sensitivity: New database had different collation - searches stopped working
3. NULL handling: PostgreSQL and MySQL handle NULL differently in ORDER BY
4. JSON differences: PostgreSQL validates JSON, MySQL doesn't - 500 errors everywhere

import json
import logging

def shadow_read(query, params):
    # Always return old DB results to users
    old_result = old_db.execute(query, params).fetchall()
    
    try:
        new_result = new_db.execute(query, params).fetchall()
        
        # Compare row counts first
        if len(old_result) != len(new_result):
            log_shadow_mismatch('row_count', query, len(old_result), len(new_result))
            return old_result
        
        # Compare actual data
        for i, (old_row, new_row) in enumerate(zip(old_result, new_result)):
            if old_row != new_row:
                log_shadow_mismatch('data_difference', f\"{query} row {i}\", old_row, new_row)
                break
                
    except Exception as e:
        # New DB is broken, log it but don't break users
        logging.error(f\"Shadow read failed: {e}\")
    
    return old_result

Run shadow reads for 2 weeks minimum. I caught 47 different issues this way that would have broken production. Netflix's blog posts on canary deployments explain their validation strategies, and GitHub's shadow mode documentation shows how they validate changes.

The migration that took me 6 hours of downtime the first time? With these techniques, the second attempt took 12 minutes of read-only mode and zero customer impact. Stripe's dual-write pattern and Facebook's migration best practices provided the patterns that finally worked for our scale.

Database-Specific Migration Strategies

PostgreSQL: What Actually Works (And What Doesn't)

I'm mostly a PostgreSQL guy, so this is where I have the most battle scars. Logical replication is great until it isn't. I've used it successfully for 500GB+ databases, but here's what they don't tell you:

Things That Will Bite You:

• Logical replication breaks with large transactions - I had a data cleanup job that updated 50M rows in one transaction. Replication lag went to 4 hours.
• CREATE INDEX CONCURRENTLY fails on high-write tables - it times out after maintenance_work_mem is exhausted
• pg_upgrade is NOT zero downtime - it's low downtime (5-30 minutes), but your app is still down
• Sequence numbers don't replicate with logical replication - your auto-increment IDs will be wrong

Critical PostgreSQL Migration Commands:

-- Enable logical replication
ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM SET max_replication_slots = 4;
ALTER SYSTEM SET max_wal_senders = 4;

-- Create publication for specific tables
CREATE PUBLICATION migration_pub FOR TABLE orders, payments, users;

-- On target server, create subscription
CREATE SUBSCRIPTION migration_sub 
CONNECTION 'host=source-db port=5432 dbname=mydb user=replica_user' 
PUBLICATION migration_pub;

MySQL: gh-ost Will Save Your Ass (Usually)

I don't do much MySQL anymore, but when I did, gh-ost was the only migration tool that didn't make me want to cry. pt-online-schema-change works too, but it uses triggers which can slow down your production writes by 20-30%.

We tried pt-online-schema-change first - big mistake. Took our order processing from 1200 TPS down to 800 TPS during peak hours. Never again. Percona's toolkit documentation explains the differences between tools, and MySQL's performance impact documentation covers the resource costs.

gh-ost Migration Example:

## Migrate large table without downtime
gh-ost \
    --user=\"migration_user\" \
    --password=\"secure_password\" \
    --host=\"mysql-host.example.com\" \
    --database=\"production_db\" \
    --table=\"orders\" \
    --alter=\"ADD COLUMN order_priority ENUM('low','medium','high') DEFAULT 'medium'\" \
    --cut-over=default \
    --exact-rowcount \
    --concurrent-rowcount \
    --default-retries=120 \
    --panic-flag-file=\"/tmp/ghost.panic.flag\" \
    --execute

MongoDB: Haven't Used This Much But...

MongoDB people will hate me for this, but I've only done a few MongoDB migrations. The flexible document model does make some things easier - when you don't have fixed schemas, migration is more about data transformation than structural changes.

Rolling upgrades through replica sets work well for version upgrades. At least that's what the docs say - I've never had to do one under real load.

MongoDB Migration Patterns:

• Replica Set Rolling Upgrade: Upgrade secondary nodes first, then primary
• Sharded Cluster Migration: Migrate one shard at a time
• Schema Evolution: Gradual document structure changes using application logic
• Cross-Cluster Sync: Use MongoDB Atlas Live Migration or custom sync scripts

Cloud Migration Services: Marketing vs Reality

AWS DMS: Good When It Works, Nightmare When It Doesn't

AWS DMS promised to migrate our 800GB MySQL database in "a few hours." It took 3 days and cost $2,400 in compute charges. The support engineer kept telling me it was "still within normal parameters" while our staging environment was completely broken.

What Actually Happens:

1. SCT finds 500+ incompatibilities - mostly stuff that doesn't matter but you have to review each one
2. Initial load is slow as hell - 100GB/day is good performance
3. Replication randomly stops - usually with cryptic errors like "ERROR: invalid memory alloc request size 1073741824"
4. Validation doesn't catch data type issues - JSON fields became TEXT, dates lost timezone info

AWS DMS troubleshooting guide covers the common errors, and this deep-dive blog post explains performance tuning. AWS re:Invent talks on DMS frequently cover real-world migration experiences.

DMS Reality Check:

• Large datasets: Budget 3x longer than AWS estimates
• Cross-engine migrations: Expect 2-4 weeks of testing to catch data type issues
• Cost: A 500GB migration will cost $1,500-$3,000 in compute charges
• Success rate: About 70% work on first try, 30% need debugging

Azure DMS: Better Than AWS, Still Not Great

Azure Database Migration Service is more reliable than AWS DMS, but that's not saying much. The UI is confusing as hell and the error messages are useless.

Haven't used this as much as AWS, but the few times I did, it failed less spectacularly. Still took twice as long as promised and still cost way more than expected. Azure's migration documentation is clearer than AWS, and this comparison guide helps choose between cloud providers.

Google Cloud DMS: The New Kid

Google's DMS works well for PostgreSQL migrations, but it's newer so fewer edge cases are handled. I had good luck with a 200GB PostgreSQL migration, but haven't tried anything larger.

The docs are cleaner than AWS and the error messages actually make sense. Still, I wouldn't bet my production migration on it without extensive testing. Google Cloud DMS documentation is excellent, and this migration guide covers PostgreSQL specifics. Cloud SQL documentation provides comprehensive migration guidance.

Monitoring: The Shit You Actually Need to Watch

You don't need 47 different metrics. Focus on the ones that tell you when you're fucked.

Metrics That Actually Matter

Database Layer:

• Replication lag (if it hits 30 seconds, you're in trouble)
• Connection count (watch for exhaustion)
• Disk space (migrations eat disk like crazy)
• Query latency (P95, not averages - averages lie)

Application Layer:

• Error rate by endpoint (look for 500s from database timeouts)
• Dual-write success rate (anything under 99.9% is bad)
• Queue depths (for retry mechanisms)

Business Layer:

• Revenue per minute (the only metric CEOs care about)
• Critical transaction success (payments, signups, orders)

Monitoring Stack Example

## Prometheus configuration for migration monitoring
- job_name: 'postgresql-migration'
  static_configs:
    - targets: ['old-db:9187', 'new-db:9187']
  scrape_interval: 15s
  metrics_path: /metrics

## Alert rules for migration issues  
groups:
  - name: migration_alerts
    rules:
    - alert: ReplicationLagHigh
      expr: pg_stat_replication_lag_seconds > 10
      for: 2m
      labels:
        severity: critical
      annotations:
        summary: \"Replication lag exceeding 10 seconds\"
        
    - alert: MigrationErrorRateHigh
      expr: rate(migration_errors_total[5m]) > 0.01
      for: 1m
      labels:
        severity: warning

Rollback and Disaster Recovery

Every migration requires a comprehensive rollback strategy. The approach varies based on migration method and progress stage.

Rollback Strategies by Migration Phase

Pre-Cutover (Dual Write Active):

• Immediate: Simply stop directing reads to new database
• Data Loss Risk: Minimal (writes continue to old database)
• Recovery Time: Under 5 minutes

Post-Cutover (First 24 Hours):

• Moderate Complexity: Reverse traffic direction, may require data reconciliation
• Data Loss Risk: Potential for recent transactions
• Recovery Time: 15-60 minutes depending on data volume

Post-Migration (After Old Database Decommission):

• High Complexity: Requires backup restoration and transaction log replay
• Data Loss Risk: All changes since backup
• Recovery Time: Hours to complete restoration

Automated Rollback Implementation

class MigrationRollback:
    def __init__(self, migration_id, rollback_trigger):
        self.migration_id = migration_id
        self.rollback_trigger = rollback_trigger
        
    def execute_rollback(self):
        # Stop new writes to target database
        self.stop_dual_writes()
        
        # Redirect all traffic to source
        self.redirect_traffic_to_source()
        
        # Verify data consistency
        consistency_check = self.verify_data_consistency()
        
        if not consistency_check.passed:
            # Emergency procedure: restore from backup
            self.emergency_restore_from_backup()
            
        # Update monitoring dashboards
        self.update_rollback_status()
        
        return rollback_success

The key to successful rollback lies in preparation and testing. Practice rollback procedures in staging environments and maintain detailed runbooks for various failure scenarios.