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
}]