RDS and Aurora blue/green deployments: a checklist for major version upgrades with a one-minute switchover

#Goal

You need a major version upgrade (for example PostgreSQL 13 to 16, or MySQL 8.0 to 8.4), or a big parameter or instance class change. An in-place upgrade means a long outage. A blue/green deployment builds a synchronised copy (green), you upgrade and test it, and a switchover moves the production endpoints to it. Downtime is usually under a minute.

This checklist covers what to verify before, during and after. Work through it top to bottom. Most failed blue/green upgrades come from skipping the pre-checks.

#How it works

• Blue is the current production instance or cluster. Green is a copy kept in sync by replication: binlog replication for MySQL/MariaDB, logical replication for PostgreSQL major version upgrades.
• At switchover, RDS blocks writes on both sides, waits for green to catch up and renames the resources. Green takes the blue names and endpoints, and the old blue gets an -old1 suffix.
• Applications keep the same endpoint names. They do have to reconnect.

#1. Pre-checks (a week before)

#Engine and target version

aws rds describe-db-engine-versions --engine postgres --engine-version 13.15 \
  --query 'DBEngineVersions[0].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].EngineVersion'

Use aurora-postgresql, mysql or aurora-mysql as needed. Version numbers vary by region and over time, so check your account's output.

#Parameter groups ready for the target version

Create a parameter group (and cluster parameter group for Aurora) for the target family and copy over your custom settings. A default group on green is a common cause of "it's slower after the upgrade".

aws rds create-db-parameter-group --db-parameter-group-name pg16-prod \
  --db-parameter-group-family postgres16 --description "PG16 prod"

#Replication prerequisites

Warning

Turning on rds.logical_replication needs a reboot of blue. Schedule it in its own window before blue/green day, not on the day.

#PostgreSQL logical replication traps

PostgreSQL major version upgrades use logical replication, and that has hard limits:

• Every table needs a primary key (or REPLICA IDENTITY FULL). Without one, UPDATE and DELETE on that table fail on blue once replication is running.
• DDL is not replicated. Any CREATE, ALTER or DROP on blue puts green into a replication degraded state, and the only fix is to delete green and start again. Freeze schema changes and migrations for the whole window.
• Large objects (lo_*, pg_largeobject) cause the same degraded state.
• Blue can't already be a logical replication publisher or subscriber.
• Apply on green is single-threaded. Write-heavy databases can build lag.

Find tables without a primary key:

SELECT n.nspname AS schema, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (SELECT 1 FROM pg_constraint p
                  WHERE p.conrelid = c.oid AND p.contype = 'p');

The MySQL equivalent:

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name
 AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND c.constraint_name IS NULL
  AND t.table_schema NOT IN ('mysql','sys','information_schema','performance_schema');

#Other dependencies

• RDS Proxy: blue must already be a proxy target before you create the deployment. You can't add it afterwards. Expect dropped connections at switchover.
• Self-managed replication, CDC or DMS tasks reading from blue: plan to stop them, switch over, then recreate them against the new instance.
• Cross-region read replicas, Aurora Global Database and other features: support varies by engine and has changed over time. Read the current limitations page for your engine before you start.

#2. Create green

aws rds create-blue-green-deployment \
  --blue-green-deployment-name prod-pg16-upgrade \
  --source arn:aws:rds:eu-west-1:111122223333:db:prod-pg \
  --target-engine-version 16.4 \
  --target-db-parameter-group-name pg16-prod

For Aurora, pass the cluster ARN as --source and add --target-db-cluster-parameter-group-name. Get notified on state changes:

aws rds create-event-subscription --subscription-name bg-events \
  --sns-topic-arn arn:aws:sns:eu-west-1:111122223333:dba-alerts \
  --source-type blue-green-deployment

Watch progress:

aws rds describe-blue-green-deployments \
  --blue-green-deployment-identifier bgd-abc123 \
  --query 'BlueGreenDeployments[0].{Status:Status,Tasks:Tasks,Members:SwitchoverDetails}'

Wait for AVAILABLE. If you see INVALID_CONFIGURATION, read StatusDetails. It usually names the missing prerequisite.

#3. Validate green (before switchover)

• Engine version and parameter groups on the green members are what you expect.
• PostgreSQL: run ANALYZE on green. A major version upgrade doesn't carry planner statistics, and without them the first queries after switchover can be very slow.
• Connect read-only to green's own endpoint and run your top queries. Compare plans with EXPLAIN against blue.
• Extensions: check SELECT extname, extversion FROM pg_extension; and update any extension the target version needs, following the engine's upgrade notes.
• Don't write to green. Writes there can break replication. On MySQL green is read_only by default, so leave it that way.
• Replication lag is near zero and stable during your normal peak. For MySQL check ReplicaLag on green. For PostgreSQL watch OldestReplicationSlotLag and TransactionLogsDiskUsage on blue.
• Application drivers support the new version (TLS, auth plugins such as MySQL 8.4 defaults, SCRAM for PostgreSQL).

#4. Switchover

Pick a low-traffic window. Stop batch jobs and migrations, and make sure no long transactions are open:

-- PostgreSQL
SELECT pid, now() - xact_start AS age, state, left(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '1 minute';

-- MySQL
SELECT * FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 1 MINUTE;

Then switch:

aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier bgd-abc123 \
  --switchover-timeout 300

Guardrails block the switchover if replication is unhealthy, lag is too high for the timeout, or long writes or DDL are running. If the timeout is exceeded, RDS rolls the switchover back and blue stays production. That is safe. Fix the cause and try again.

Caution

During switchover both environments refuse writes. Apps must retry on connection errors and read-only errors. Apps that cache DNS for a long time, or hold pools that never reconnect, can keep talking to the old instance. See the post on writes going to the old instance after an Aurora failover.

#5. After switchover

• describe-blue-green-deployments shows SWITCHOVER_COMPLETED.
• Apps are connected to the new version: SELECT version();
• Error rates, latency and CPU are back to baseline. Watch DatabaseConnections, ReadLatency and WriteLatency, and Performance Insights top SQL.
• Recreate CDC/DMS tasks and self-managed replication you stopped earlier.
• Re-point anything that used instance-level endpoints instead of cluster endpoints.

#Rollback

There is no reverse switchover. The old blue (prod-pg-old1) is kept, but it is stale and doesn't receive new writes. If you must go back, you have to rebuild from it or restore from backup and accept losing writes since the switchover. Decide your go/no-go criteria before switchover, and test thoroughly on green.

#Clean up

aws rds delete-blue-green-deployment --blue-green-deployment-identifier bgd-abc123

After a switchover this removes only the deployment object. Once you're confident, take a final snapshot of prod-pg-old1 and delete it. You pay for it until you do.

If you abandon the upgrade before switchover, add --delete-target to remove the green resources too.

#Prevent it next time

• Keep every table on a primary key. Enforce it in schema review so blue/green is always possible.
• Leave rds.logical_replication (PostgreSQL) and binary logging (MySQL) on in production parameter groups, so you don't need an extra reboot window.
• Rehearse the whole flow on a restored snapshot in non-production and time each step.
• Track engine end-of-support dates and plan upgrades a quarter ahead. See the RDS pending maintenance checklist.
• Make connection retry and DNS refresh part of every application's production readiness review.