RDS 連携とマイグレーション運用

Fargate Task (Private Subnet)
    │
    │ DATABASE_URL  (Secrets Manager から)
    ▼
sg-rds  ← 5432 ← sg-fargate
    │
    ▼
RDS Postgres Multi-AZ  (DB Subnet Group)
    Primary  (AZ-a)
    Standby  (AZ-c)   ← Failover 時に昇格

aws rds create-db-subnet-group \
  --db-subnet-group-name blog-db-subnets \
  --db-subnet-group-description "Blog API DB subnets" \
  --subnet-ids subnet-db-a subnet-db-c

sg-rds  inbound:
   port 5432  ← sg-fargate     ← Fargate task のみ
   port 5432  ← sg-bastion     ← (任意) 運用者のジャンプホスト

sg-rds  outbound:
   不要 (default から絞る)

aws ec2 create-security-group \
  --group-name sg-rds \
  --description "RDS allow from Fargate" \
  --vpc-id $VPC_ID

aws ec2 authorize-security-group-ingress \
  --group-id $SG_RDS \
  --protocol tcp --port 5432 \
  --source-group $SG_FARGATE

aws secretsmanager create-secret \
  --name blog-api/db \
  --secret-string '{
    "username": "blog_admin",
    "password": "S3cr3t-r4nd0m-32-bytes-...",
    "engine": "postgres",
    "host": "PLACEHOLDER",
    "port": 5432,
    "dbname": "blogdb"
  }'

aws rds create-db-instance \
  --db-instance-identifier blog-db \
  --db-instance-class db.t4g.small \
  --engine postgres --engine-version 16.4 \
  --allocated-storage 20 --storage-type gp3 \
  --master-username blog_admin \
  --master-user-password "S3cr3t-r4nd0m-32-bytes-..." \
  --db-name blogdb \
  --vpc-security-group-ids $SG_RDS \
  --db-subnet-group-name blog-db-subnets \
  --multi-az \
  --publicly-accessible false \
  --backup-retention-period 7 \
  --deletion-protection \
  --enable-performance-insights \
  --storage-encrypted \
  --auto-minor-version-upgrade

Primary (AZ-a)  ─ 同期レプリケーション ─▶  Standby (AZ-c)
    │                              │
    × 障害                         │
    ▼                              ▼
フェイルオーバー トリガー        Standby → Primary に昇格
                                  │
                                  ▼
DNS endpoint が新 Primary へ自動更新 (60~120s)

{
  "containerDefinitions": [
    {
      "name": "api",
      "image": "...",
      "secrets": [
        {
          "name": "DATABASE_URL",
          "valueFrom": "arn:aws:secretsmanager:ap-northeast-2:123456789012:secret:blog-api/db-AbCdEf:url::"
        }
      ],
      "environment": [
        { "name": "ENVIRONMENT", "value": "production" }
      ]
    }
  ]
}

{
  "url": "postgresql://blog_admin:PASSWORD@blog-db.xxxx.rds.amazonaws.com:5432/blogdb",
  "username": "blog_admin",
  "password": "PASSWORD",
  "host": "blog-db.xxxx.rds.amazonaws.com",
  "port": 5432,
  "dbname": "blogdb"
}

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": ["secretsmanager:GetSecretValue"],
    "Resource": "arn:aws:secretsmanager:ap-northeast-2:123456789012:secret:blog-api/db-*"
  }]
}

psql "postgresql://blog_admin@blog-db.xxxx.ap-northeast-2.rds.amazonaws.com:5432/blogdb"
# パスワード入力 → SELECT 1;

aws ecs update-service \
  --cluster blog-cluster --service blog-api \
  --enable-execute-command --force-new-deployment

# task の中へ入る
TASK=$(aws ecs list-tasks --cluster blog-cluster --service-name blog-api \
   --query 'taskArns[0]' --output text)
aws ecs execute-command --cluster blog-cluster --task $TASK \
   --container api --interactive --command "/bin/sh"

A) コンテナ起動時に自動 (CMD before uvicorn/gunicorn)
   ─ 簡単。小さなチーム。
   ─ 危険: replica N 個が同時実行 → ロック / race
   ─ 危険: 失敗時にコンテナ自身が立ち上がらない → ロールバックが厄介

B) 別の ECS RunTask (deploy 段階)
   ─ 1 回実行。ロック/race なし。
   ─ 結果を分離して追跡。
   ─ 推奨。

C) CodeDeploy lifecycle hook
   ─ blue/green デプロイの一部として
   ─ pre-traffic-shift または before-allow-traffic

aws ecs run-task \
  --cluster blog-cluster \
  --task-definition blog-api-migrate:5 \
  --launch-type FARGATE \
  --network-configuration "awsvpcConfiguration={
       subnets=[subnet-aaa],
       securityGroups=[sg-fargate],
       assignPublicIp=ENABLED
     }" \
  --count 1 \
  --started-by "deploy-$(git rev-parse --short HEAD)"

{
  "family": "blog-api-migrate",
  "containerDefinitions": [{
    "name": "migrate",
    "image": "...:v123",
    "command": ["alembic", "upgrade", "head"],
    "essential": true
  }]
}

ALTER TABLE posts ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'draft';

-- 1) カラムだけ追加 (NULL 許可)
ALTER TABLE posts ADD COLUMN status VARCHAR(20);

-- 2) バックフィル (バッチで)
UPDATE posts SET status = 'draft' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
-- ... 繰り返し

-- 3) NOT NULL + default
ALTER TABLE posts ALTER COLUMN status SET DEFAULT 'draft';
ALTER TABLE posts ALTER COLUMN status SET NOT NULL;

aws rds create-db-snapshot \
  --db-snapshot-identifier blog-db-pre-deploy-$(date +%Y%m%d-%H%M) \
  --db-instance-identifier blog-db

Fargate (10 task × pool 20) ──▶ RDS Proxy ──▶ RDS
        合計 200 接続                ▼
                              実際の RDS 接続: 20~50

resource "aws_db_instance" "blog" {
  # ...
  deletion_protection      = true
  skip_final_snapshot      = false
  final_snapshot_identifier = "blog-db-final-${formatdate("YYYYMMDD-hhmm", timestamp())}"

  lifecycle {
    prevent_destroy = true
  }
}

SET lock_timeout = '5s';
ALTER TABLE posts ADD COLUMN status TEXT;
-- 5s 以内にロックを取れなければエラーで抜ける

resource "aws_db_subnet_group" "main" {
  name       = "blog-db"
  subnet_ids = var.db_subnet_ids        # 2 AZ の DB サブネット
}

resource "aws_security_group" "rds" {
  name_prefix = "blog-rds-"
  vpc_id      = var.vpc_id
}

# Fargate SG から来た 5432 のみ許可
resource "aws_security_group_rule" "rds_from_fargate" {
  type                     = "ingress"
  security_group_id        = aws_security_group.rds.id
  source_security_group_id = var.fargate_sg_id
  from_port = 5432, to_port = 5432, protocol = "tcp"
}

resource "aws_db_instance" "main" {
  identifier              = "blog-db"
  engine                  = "postgres"
  engine_version          = "17.2"
  instance_class          = "db.t4g.micro"
  allocated_storage       = 20
  storage_encrypted       = true          # 事故防止: 保存時の暗号化
  multi_az                = true           # 事故防止: AZ 障害のフェイルオーバー
  deletion_protection     = true           # 事故防止: 誤削除の遮断
  backup_retention_period = 14             # PITR 14日 (第30章)
  db_subnet_group_name    = aws_db_subnet_group.main.name
  vpc_security_group_ids  = [aws_security_group.rds.id]

  db_name                     = "blog"
  username                    = "blog"
  manage_master_user_password = true       # → Secrets Manager 自動生成・ローテーション
}

secrets = [{
  name      = "DB_SECRET"
  valueFrom = aws_db_instance.main.master_user_secret[0].secret_arn
}]