Bluegreen Db Migration FAQ & Answers

Database left in partial state with incomplete schema changes. Both blue (old) and green (new) environments share same database, both become broken. Example: Migration adds column 'age', removes 'birthday', fails at 60%. Result: 'age' exists but incomplete, 'birthday' may be partially removed. Blue code expects 'birthday', crashes. Green code expects complete migration, crashes. No automatic rollback because blue-green shares DB. Manual intervention required. This is THE critical failure mode for blue-green with shared database.

Phase 1 (Deploy 1): Add new column as nullable: ALTER TABLE users ADD COLUMN age INT NULL. Phase 2 (Deploy 2): Dual-write period - code writes to both 'age' and 'birthday', backfill data: UPDATE users SET age = calculate(birthday). Phase 3 (Deploy 3): Make new column NOT NULL: ALTER TABLE users ALTER COLUMN age SET NOT NULL. Phase 4 (Deploy 4): Remove old column: ALTER TABLE users DROP COLUMN birthday. Each phase is a separate deployment. Old code continues working until Phase 4. Rollback safe at any phase. This is the ONLY safe approach for blue-green.

Idempotent migrations can run multiple times safely, producing same result. Check state before modifying. Pattern: DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='age') THEN ALTER TABLE users ADD COLUMN age INT NULL; END IF; END $$;. Critical for: (1) Resuming after partial failure, (2) Running same migration in multiple environments, (3) Re-running after timeout. Without idempotency, retry causes 'column already exists' error. Use transactions where possible: BEGIN; ALTER TABLE...; COMMIT; for automatic rollback on failure.

No for most cases. Shared database is standard for blue-green deployments. Separate databases add complexity: logical replication overhead, 2x costs for large databases, replication lag on high-write workloads, synchronization challenges. Shared DB approach: implement backward-compatible migrations (4-phase pattern), decouple schema changes from code deployments, simpler operational model. When separate DBs make sense: (1) Database size is small (copying fast, affordable), (2) Low write volume (replication can keep up), (3) Major version upgrades, (4) Complete application rewrites, (5) Testing major schema refactors. AWS RDS Blue/Green example (2025): uses logical replication between separate DBs, copies complete topology to green environment, replicates all writes from blue to green. Limitations: replication lag on high-volume writes (can cause failures), database copy time for large DBs, double infrastructure costs during deployment. Alternative: AWS DMS for continuous sync on large/busy databases. Best practice recommendation: use shared database with backward-compatible migrations for 90% of cases - simpler, proven, lower cost. Reserve separate databases for exceptional scenarios where benefits outweigh complexity. Test replication lag tolerance before adopting separate DB strategy.

Split migrations into two phases: Pre-migration (run BEFORE switchover): Additive changes only - ADD COLUMN, CREATE INDEX CONCURRENTLY, INSERT default data. Safe because old code ignores new columns. Post-migration (run AFTER switchover confirmed): Destructive changes - DROP COLUMN, ALTER COLUMN NOT NULL, remove old data. Pattern: Liquibase changesets with contexts: ADD COLUMN, DROP COLUMN. Execute: liquibase update --contexts=pre before deploy, --contexts=post after 24-hour monitoring period. Enables safe rollback before destructive changes.

After switchover to green, setup replication from green (new production) back to blue (old production) for rollback capability. PostgreSQL: On green: CREATE PUBLICATION rollback_pub FOR ALL TABLES;. On blue: CREATE SUBSCRIPTION rollback_sub CONNECTION 'host=green dbname=mydb' PUBLICATION rollback_pub;. Changes flow green→blue. If rollback needed within 1-2 hours: Stop writes to green, let replication catch up (minutes), switch traffic back to blue. Blue is now current. Use for: critical launches, major changes, compliance requirements. Trade-off: Replication overhead (~5-10%), limited time window (hours not days).

Don't force-remove during incident. Steps: (1) Check what was applied: SELECT * FROM schema_migrations ORDER BY installed_rank DESC LIMIT 5;. (2) Assess damage: Check if partial changes affect both blue and green. (3) Complete or rollback: If column added, complete it: ALTER TABLE IF NOT EXISTS. If removal started, keep both columns temporarily. (4) Fix in next deployment, not during incident. (5) Document in runbook. Never attempt: manual DROP COLUMN of partially added columns, forcing migrations to 'complete' status, restarting migration tools mid-run. Rule: Make system stable first, fix properly in next deploy.

Old code must continue working with new schema. Backward compatible: ADD COLUMN (nullable), CREATE INDEX CONCURRENTLY, INSERT new rows, ADD constraint (not enforced yet). NOT backward compatible: DROP COLUMN (old code crashes), RENAME COLUMN (old code can't find it), ALTER COLUMN NOT NULL (old code may write NULL), ALTER COLUMN type (may truncate data). Safe pattern: Additive changes in deploy N, remove old structures in deploy N+2 (skip one deploy to ensure rollback possible). Test: Can N-1 code run against N schema? If yes, backward compatible.

Use transactions where possible but understand limitations. Transactional migrations: BEGIN; ALTER TABLE users ADD COLUMN age INT; UPDATE users SET age = 25; COMMIT;. On failure, automatic rollback. Limitations: (1) Some DDL statements don't support transactions (CREATE INDEX CONCURRENTLY in PostgreSQL), (2) Large migrations may hold locks too long, (3) Some databases don't support transactional DDL (MySQL with some storage engines). Best practice: Use transactions for small, fast migrations (<5 seconds). For long migrations: Break into smaller idempotent pieces, each in own transaction. Always test rollback behavior in staging.