Aurora Blue/Green 実践検証 — メンテナンス時に実際どれだけ止まるのかを実測する

# サブネットグループ作成（デフォルト VPC の 3 AZ）
aws rds create-db-subnet-group \
  --db-subnet-group-name bg-test-subnet-group \
  --db-subnet-group-description "Subnet group for Blue/Green deployment test" \
  --subnet-ids '["subnet-xxxxx","subnet-yyyyy","subnet-zzzzz"]' \
  --region ap-northeast-1
 
# Aurora PostgreSQL 16.9 クラスター作成
aws rds create-db-cluster \
  --db-cluster-identifier bg-test-apg \
  --engine aurora-postgresql \
  --engine-version 16.9 \
  --master-username postgres \
  --master-user-password '<your-password>' \
  --db-subnet-group-name bg-test-subnet-group \
  --storage-encrypted \
  --no-deletion-protection \
  --region ap-northeast-1
 
# Writer インスタンス
aws rds create-db-instance \
  --db-instance-identifier bg-test-apg-writer \
  --db-cluster-identifier bg-test-apg \
  --db-instance-class db.r6g.large \
  --engine aurora-postgresql \
  --no-auto-minor-version-upgrade \
  --region ap-northeast-1
 
# Reader インスタンス
aws rds create-db-instance \
  --db-instance-identifier bg-test-apg-reader \
  --db-cluster-identifier bg-test-apg \
  --db-instance-class db.r6g.large \
  --engine aurora-postgresql \
  --no-auto-minor-version-upgrade \
  --region ap-northeast-1

# クラスターが使用しているセキュリティグループ ID を確認
SG_ID=$(aws rds describe-db-clusters \
  --db-cluster-identifier bg-test-apg \
  --query 'DBClusters[0].VpcSecurityGroups[0].VpcSecurityGroupId' \
  --output text --region ap-northeast-1)
 
# 自分のパブリック IP を取得
MY_IP=$(curl -s https://checkip.amazonaws.com)
 
# PostgreSQL ポートを自分の IP からのみ許可
aws ec2 authorize-security-group-ingress \
  --group-id "${SG_ID}" \
  --protocol tcp --port 5432 \
  --cidr "${MY_IP}/32" \
  --region ap-northeast-1
 
# 両インスタンスをパブリックアクセス可能にする
aws rds modify-db-instance \
  --db-instance-identifier bg-test-apg-writer \
  --publicly-accessible --apply-immediately \
  --region ap-northeast-1
 
aws rds modify-db-instance \
  --db-instance-identifier bg-test-apg-reader \
  --publicly-accessible --apply-immediately \
  --region ap-northeast-1

#!/usr/bin/env bash
set -euo pipefail
 
ENDPOINT="${1:?Usage: $0 <cluster-endpoint> <password> [interval_ms]}"
PASSWORD="${2:?Password required}"
INTERVAL_MS="${3:-500}"
INTERVAL_SEC=$(echo "scale=3; ${INTERVAL_MS}/1000" | bc)
 
TOTAL=0; SUCCESS=0; FAIL=0
FIRST_FAIL_TS=""; LAST_FAIL_TS=""
OUTFILE="/tmp/bg-test/connectivity-$(date '+%Y%m%d-%H%M%S').csv"
 
echo "timestamp,query_num,status,latency_ms,server_ip,error" | tee "${OUTFILE}"
 
cleanup() {
  echo ""
  echo "=== Summary ==="
  echo "Total: ${TOTAL} | Success: ${SUCCESS} | Failed: ${FAIL}"
  if [ -n "${FIRST_FAIL_TS}" ]; then
    echo "First failure: ${FIRST_FAIL_TS}"
    echo "Last failure: ${LAST_FAIL_TS}"
  fi
}
trap cleanup EXIT
 
export PGCONNECT_TIMEOUT=3
 
while true; do
  TOTAL=$((TOTAL + 1))
  TS=$(date '+%Y-%m-%dT%H:%M:%S.%3N')
  START_NS=$(date '+%s%N')
 
  RESULT=$(PGPASSWORD="${PASSWORD}" psql -h "${ENDPOINT}" -p 5432 -U postgres -d postgres \
    -t -A -c "SELECT inet_server_addr()::text" 2>&1) && STATUS="OK" || STATUS="FAIL"
 
  END_NS=$(date '+%s%N')
  LATENCY_MS=$(( (END_NS - START_NS) / 1000000 ))
 
  if [ "${STATUS}" = "OK" ]; then
    SUCCESS=$((SUCCESS + 1))
    echo "${TS},${TOTAL},OK,${LATENCY_MS},${RESULT}," | tee -a "${OUTFILE}"
  else
    FAIL=$((FAIL + 1))
    ERROR=$(echo "${RESULT}" | tr '\n' ' ' | cut -c1-100)
    [ -z "${FIRST_FAIL_TS}" ] && FIRST_FAIL_TS="${TS}"
    LAST_FAIL_TS="${TS}"
    echo "${TS},${TOTAL},FAIL,${LATENCY_MS},,${ERROR}" | tee -a "${OUTFILE}"
  fi
 
  sleep "${INTERVAL_SEC}"
done

# Blue 環境用カスタムパラメータグループ作成
aws rds create-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-apg16-params \
  --db-parameter-group-family aurora-postgresql16 \
  --description "Custom params for Blue/Green deployment test"
 
# 論理レプリケーション有効化
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-apg16-params \
  --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot"
 
# クラスターに適用
aws rds modify-db-cluster \
  --db-cluster-identifier bg-test-apg \
  --db-cluster-parameter-group-name bg-test-apg16-params \
  --apply-immediately
 
# 全インスタンスを再起動（pending-reboot → in-sync にするため）
aws rds reboot-db-instance --db-instance-identifier bg-test-apg-writer
aws rds reboot-db-instance --db-instance-identifier bg-test-apg-reader

aws rds describe-db-clusters \
  --db-cluster-identifier bg-test-apg \
  --query 'DBClusters[0].DBClusterMembers[].{Id:DBInstanceIdentifier,ParamStatus:DBClusterParameterGroupStatus}' \
  --region ap-northeast-1

aws rds create-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-apg17-params \
  --db-parameter-group-family aurora-postgresql17 \
  --description "Custom params for PG17 green environment"
 
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-apg17-params \
  --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot"

# Blue/Green デプロイメントの削除
aws rds delete-blue-green-deployment \
  --blue-green-deployment-identifier bgd-xxxxx \
  --delete-target \
  --region ap-northeast-1
 
# 旧 Blue 環境（-old1 サフィックス）のインスタンス削除
aws rds delete-db-instance \
  --db-instance-identifier bg-test-apg-reader-old1 \
  --skip-final-snapshot \
  --region ap-northeast-1
aws rds delete-db-instance \
  --db-instance-identifier bg-test-apg-writer-old1 \
  --skip-final-snapshot \
  --region ap-northeast-1
 
# 旧 Blue クラスター削除
aws rds delete-db-cluster \
  --db-cluster-identifier bg-test-apg-old1 \
  --skip-final-snapshot \
  --region ap-northeast-1
 
# 新クラスター（Green が昇格したもの）のインスタンス削除
aws rds delete-db-instance \
  --db-instance-identifier bg-test-apg-reader \
  --skip-final-snapshot \
  --region ap-northeast-1
aws rds delete-db-instance \
  --db-instance-identifier bg-test-apg-writer \
  --skip-final-snapshot \
  --region ap-northeast-1
 
# 新クラスター削除
aws rds delete-db-cluster \
  --db-cluster-identifier bg-test-apg \
  --skip-final-snapshot \
  --region ap-northeast-1
 
# パラメータグループ削除（クラスター削除後）
aws rds delete-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-apg16-params
aws rds delete-db-cluster-parameter-group \
  --db-cluster-parameter-group-name bg-test-apg17-params
 
# サブネットグループ削除
aws rds delete-db-subnet-group \
  --db-subnet-group-name bg-test-subnet-group
 
# セキュリティグループのインバウンドルール削除
aws ec2 revoke-security-group-ingress \
  --group-id sg-xxxxx \
  --protocol tcp --port 5432 \
  --cidr <your-ip>/32