Blue-Green Deployment Migration - Zero Downtime

advanced

Blue-green deployment is the gold standard for zero-downtime migrations. Setup new database (green), sync data continuously, switch traffic instantly with load balancer, keep old database (blue) for instant rollback. Stack Overflow uses this for SQL Server upgrades with zero downtime. Critical for high-availability systems where even 1 minute of downtime costs thousands of dollars.

-- Phase 1: Setup Green (New) Database

-- Create new PostgreSQL database

CREATE DATABASE myapp_green;

-- Convert schema from MySQL to PostgreSQL

-- Use pgloader or manual conversion

-- MySQL schema (Blue - Old)

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL,

email VARCHAR(100) NOT NULL,

created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);

-- PostgreSQL schema (Green - New)

CREATE TABLE users (

user_id SERIAL PRIMARY KEY,

username VARCHAR(50) NOT NULL,

email VARCHAR(100) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- Phase 2: Initial Data Sync (Full Dump)

-- Export from MySQL (Blue)

mysqldump --single-transaction --quick myapp_blue > dump.sql

-- Transform and import to PostgreSQL (Green)

pgloader mysql://user:pass@localhost/myapp_blue postgresql://user:pass@localhost/myapp_green

-- Verify row counts match

-- MySQL: SELECT COUNT(*) FROM users; -- 1000000

-- PostgreSQL: SELECT COUNT(*) FROM users; -- 1000000

-- Phase 3: Continuous Sync (CDC with Debezium)

-- Setup Debezium to capture MySQL changes and apply to PostgreSQL

-- Monitors MySQL binlog, applies INSERT/UPDATE/DELETE to PostgreSQL

-- Lag: 1-5 seconds

-- Monitor replication lag

-- Check last synced transaction timestamp

SELECT MAX(created_at) FROM users; -- Should be within 5 seconds

-- Phase 4: Validation (Before Cutover)

-- Row count validation

SELECT 'MySQL' as db, COUNT(*) as count FROM users; -- 1000000