Aurora Blue/Green 実践検証 — RDS Proxy 経由の Switchover でダウンタイムはどう変わるか

aws ec2 create-security-group \
  --description "SG for Blue/Green + RDS Proxy verification" \
  --group-name bg-proxy-verify-sg \
  --vpc-id $VPC_ID
 
# 自己参照ルール（Proxy ↔ Aurora ↔ EC2 間の PostgreSQL 通信）
aws ec2 authorize-security-group-ingress \
  --group-id $SG_ID \
  --port 5432 \
  --protocol tcp \
  --source-group $SG_ID

aws rds create-db-subnet-group \
  --db-subnet-group-name bg-proxy-verify-subnet \
  --db-subnet-group-description "Subnet group for BG Proxy verification" \
  --subnet-ids '["$SUBNET_1","$SUBNET_2","$SUBNET_3"]'

aws secretsmanager create-secret \
  --name bg-proxy-verify-secret \
  --secret-string '{"username":"postgres","password":"$PASSWORD"}'

aws rds create-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-proxy-verify-cpg \
  --db-parameter-group-family aurora-postgresql16 \
  --description "Cluster PG with logical replication"
 
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-proxy-verify-cpg \
  --parameters '[{"ParameterName":"rds.logical_replication","ParameterValue":"1","ApplyMethod":"pending-reboot"}]'

aws rds create-db-cluster \
  --db-cluster-identifier bg-proxy-verify \
  --db-cluster-parameter-group-name bg-proxy-verify-cpg \
  --db-subnet-group-name bg-proxy-verify-subnet \
  --engine aurora-postgresql \
  --engine-version 16.9 \
  --master-username postgres \
  --master-user-password $PASSWORD \
  --vpc-security-group-ids '["$SG_ID"]'
 
aws rds create-db-instance \
  --db-cluster-identifier bg-proxy-verify \
  --db-instance-class db.r6g.large \
  --db-instance-identifier bg-proxy-verify-w1 \
  --engine aurora-postgresql

aws rds reboot-db-instance --db-instance-identifier bg-proxy-verify-w1

aws iam create-role \
  --role-name bg-proxy-verify-role \
  --assume-role-policy-document '{
    "Version":"2012-10-17",
    "Statement":[{
      "Effect":"Allow",
      "Principal":{"Service":"rds.amazonaws.com"},
      "Action":"sts:AssumeRole"
    }]
  }'
 
aws iam put-role-policy \
  --role-name bg-proxy-verify-role \
  --policy-name SecretsManagerAccess \
  --policy-document '{
    "Version":"2012-10-17",
    "Statement":[{
      "Effect":"Allow",
      "Action":["secretsmanager:GetSecretValue","secretsmanager:DescribeSecret"],
      "Resource":"$SECRET_ARN"
    }]
  }'

aws rds create-db-proxy \
  --db-proxy-name bg-proxy-verify \
  --engine-family POSTGRESQL \
  --auth '[{"AuthScheme":"SECRETS","SecretArn":"$SECRET_ARN","IAMAuth":"DISABLED"}]' \
  --role-arn arn:aws:iam::$ACCOUNT_ID:role/bg-proxy-verify-role \
  --vpc-subnet-ids '["$SUBNET_1","$SUBNET_2","$SUBNET_3"]' \
  --vpc-security-group-ids '["$SG_ID"]'
 
# Proxy が available になったらターゲットを登録
aws rds register-db-proxy-targets \
  --db-proxy-name bg-proxy-verify \
  --db-cluster-identifiers '["bg-proxy-verify"]'

aws rds create-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-proxy-verify-cpg-17 \
  --db-parameter-group-family aurora-postgresql17 \
  --description "Cluster PG for PG17"
 
aws rds create-blue-green-deployment \
  --blue-green-deployment-name bg-proxy-verify-bgd \
  --source arn:aws:rds:ap-northeast-1:$ACCOUNT_ID:cluster:bg-proxy-verify \
  --target-engine-version 17.6 \
  --target-db-cluster-parameter-group-name bg-proxy-verify-cpg-17

# EC2 用 IAM ロールとインスタンスプロファイルの作成
aws iam create-role \
  --role-name bg-proxy-verify-ec2-role \
  --assume-role-policy-document '{
    "Version":"2012-10-17",
    "Statement":[{
      "Effect":"Allow",
      "Principal":{"Service":"ec2.amazonaws.com"},
      "Action":"sts:AssumeRole"
    }]
  }'
 
aws iam attach-role-policy \
  --role-name bg-proxy-verify-ec2-role \
  --policy-arn arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
 
aws iam create-instance-profile \
  --instance-profile-name bg-proxy-verify-ec2-profile
 
aws iam add-role-to-instance-profile \
  --instance-profile-name bg-proxy-verify-ec2-profile \
  --role-name bg-proxy-verify-ec2-role
 
# IAM の伝播を待つ
sleep 10
 
aws ec2 run-instances \
  --image-id $AL2023_AMI_ID \
  --instance-type t3.medium \
  --security-group-ids '["$SG_ID"]' \
  --subnet-id $SUBNET_ID \
  --iam-instance-profile '{"Name":"bg-proxy-verify-ec2-profile"}'
 
# SSM 経由で psql をインストール
dnf install -y postgresql16

PGPASSWORD=$PASSWORD psql \
  -h $PROXY_ENDPOINT.ap-northeast-1.rds.amazonaws.com \
  -p 5432 -U postgres -d postgres \
  -c "CREATE TABLE IF NOT EXISTS switchover_test (
    id SERIAL PRIMARY KEY,
    ts TIMESTAMPTZ DEFAULT now(),
    msg TEXT
  );"

#!/bin/bash
ENDPOINT="$1"
PASSWORD="$2"
COUNT=${3:-200}
LOGFILE="/home/ec2-user/switchover_$(date +%Y%m%d_%H%M%S).csv"
 
echo "seq,timestamp,status,latency_ms,server_ip,message" > "$LOGFILE"
 
for i in $(seq 1 $COUNT); do
  START_MS=$(date +%s%3N)
  RESULT=$(PGPASSWORD="${PASSWORD}" psql -h "${ENDPOINT}" -p 5432 -U postgres -d postgres \
    -t -A -c "INSERT INTO switchover_test (msg) VALUES ('seq=$i') RETURNING host(inet_server_addr());" 2>&1)
  END_MS=$(date +%s%3N)
  LATENCY=$((END_MS - START_MS))
  TS=$(date -u +%Y-%m-%dT%H:%M:%S.%3NZ)
 
  if echo "$RESULT" | grep -qE '^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$'; then
    echo "$i,$TS,OK,$LATENCY,$RESULT," >> "$LOGFILE"
  else
    MSG=$(echo "$RESULT" | tr '\n' ' ' | cut -c1-120)
    echo "$i,$TS,FAIL,$LATENCY,,$MSG" >> "$LOGFILE"
  fi
  sleep 1
done

# Blue/Green Deployment の削除
aws rds delete-blue-green-deployment \
  --blue-green-deployment-identifier bgd-wmrsads60kvsah4c \
  --delete-target \
  --region ap-northeast-1
 
# Switchover 後の旧 Blue 環境（-old1 にリネーム済み）の削除
aws rds delete-db-instance \
  --db-instance-identifier bg-proxy-verify-w1-old1 \
  --skip-final-snapshot
aws rds delete-db-cluster \
  --db-cluster-identifier bg-proxy-verify-old1 \
  --skip-final-snapshot
 
# RDS Proxy の削除
aws rds deregister-db-proxy-targets \
  --db-proxy-name bg-proxy-verify \
  --db-cluster-identifiers '["bg-proxy-verify"]'
aws rds delete-db-proxy --db-proxy-name bg-proxy-verify
 
# Aurora クラスターの削除
aws rds delete-db-instance \
  --db-instance-identifier bg-proxy-verify-w1 \
  --skip-final-snapshot
aws rds delete-db-cluster \
  --db-cluster-identifier bg-proxy-verify \
  --skip-final-snapshot
 
# EC2 インスタンスの削除
aws ec2 terminate-instances --instance-ids i-0478189b7d8e3aea9
 
# IAM ロール・ポリシーの削除
aws iam delete-role-policy --role-name bg-proxy-verify-role --policy-name SecretsManagerAccess
aws iam delete-role --role-name bg-proxy-verify-role
aws iam remove-role-from-instance-profile \
  --instance-profile-name bg-proxy-verify-ec2-profile \
  --role-name bg-proxy-verify-ec2-role
aws iam detach-role-policy \
  --role-name bg-proxy-verify-ec2-role \
  --policy-arn arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
aws iam delete-role --role-name bg-proxy-verify-ec2-role
aws iam delete-instance-profile --instance-profile-name bg-proxy-verify-ec2-profile
 
# Secrets Manager シークレットの削除
aws secretsmanager delete-secret \
  --secret-id bg-proxy-verify-secret \
  --force-delete-without-recovery
 
# パラメータグループの削除
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name bg-proxy-verify-cpg
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name bg-proxy-verify-cpg-17
 
# サブネットグループの削除
aws rds delete-db-subnet-group --db-subnet-group-name bg-proxy-verify-subnet
 
# セキュリティグループの削除
aws ec2 delete-security-group --group-id $SG_ID