Website Database Migration

Website Database Migration

Database migration is technically the riskiest part of any infrastructure work. Data loss or site downtime during migration has direct financial consequences. Approach depends on database size, acceptable downtime and data consistency requirements.

Downtime Strategies

Maintenance window (simplest): site in maintenance mode → dump → transfer → start. Downtime = dump + transfer time. Acceptable for databases up to 10 GB at night.

Online migration: replication from source to target, minimal downtime only for switch. For MySQL — Percona XtraBackup or binlog replication. For PostgreSQL — pglogical or pg_basebackup + WAL shipping.

Blue-Green: parallel database, application writes to both, then switch. More complex, but zero downtime.

MySQL: Safe Dump and Restore

# Dump with locking for consistency
mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  -u root -p mysite_db \
  | gzip > /backup/mysite_$(date +%Y%m%d_%H%M%S).sql.gz

# Size before compression (for time estimate)
mysql -u root -p -e "
  SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
  FROM information_schema.tables
  WHERE table_schema = 'mysite_db'
  GROUP BY table_schema;
"

# Restore
gunzip < /backup/mysite_20241201_030000.sql.gz | mysql -u root -p new_db

PostgreSQL: pg_dump and pg_restore

# Custom format (faster, compressed, parallel restore)
pg_dump \
  -U postgres \
  -d mysite_db \
  -F custom \
  -f /backup/mysite_$(date +%Y%m%d).dump \
  --verbose

# Parallel restore
pg_restore \
  -U postgres \
  -d new_db \
  -j 4 \              # 4 parallel threads
  --verbose \
  /backup/mysite_20241201.dump

# Integrity check
psql -U postgres -d new_db -c "SELECT COUNT(*) FROM users;"
psql -U postgres -d new_db -c "SELECT COUNT(*) FROM posts;"

PostgreSQL Online Migration with Minimal Downtime

# 1. Set up replication via pglogical
# On source
psql -c "CREATE EXTENSION pglogical;"
psql -c "SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=source dbname=mysite user=replication');"
psql -c "SELECT pglogical.create_replication_set('all_tables');"
psql -c "SELECT pglogical.replication_set_add_all_tables('all_tables', ARRAY['public']);"

# On target
psql -c "CREATE EXTENSION pglogical;"
psql -c "SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=target dbname=mysite_new user=replication');"
psql -c "SELECT pglogical.create_subscription(
  subscription_name := 'sub_mysite',
  provider_dsn := 'host=source dbname=mysite user=replication password=secret'
);"

# 2. Wait for sync
psql -c "SELECT * FROM pglogical.show_subscription_status();"

# 3. Downtime: stop writes, switch app to new DB
# Downtime = seconds, not hours

Post-Migration Validation

# Compare row counts in critical tables
for table in users posts orders products; do
  src=$(mysql -h source -u root -p -se "SELECT COUNT(*) FROM mysite.$table")
  dst=$(mysql -h target -u root -p -se "SELECT COUNT(*) FROM mysite.$table")
  if [ "$src" != "$dst" ]; then
    echo "MISMATCH: $table: $src vs $dst"
  else
    echo "OK: $table: $src rows"
  fi
done

Database Sizes and Approximate Time

Full migration with planning, test run and rollback plan — 1–5 days depending on complexity.