Split View: Kubernetes DB 심화 가이드 2025: Operator 패턴, HA 구성, 백업/복구, 성능 튜닝 실전

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: database
spec:
  serviceName: postgres-headless
  replicas: 3
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:16
          ports:
            - containerPort: 5432
          volumeMounts:
            - name: data
              mountPath: /var/lib/postgresql/data
          env:
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: password
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: ["ReadWriteOnce"]
        storageClassName: gp3-encrypted
        resources:
          requests:
            storage: 100Gi

postgres-0  →  postgres-0.postgres-headless.database.svc.cluster.local
postgres-1  →  postgres-1.postgres-headless.database.svc.cluster.local
postgres-2  →  postgres-2.postgres-headless.database.svc.cluster.local

spec:
  podManagementPolicy: OrderedReady  # 기본값
  # OrderedReady: 0→1→2 순서대로 생성, 2→1→0 순서대로 삭제
  # Parallel: 모든 Pod 동시 생성/삭제 (DB에서는 주의 필요)

volumeClaimTemplates:
  - metadata:
      name: data
      labels:
        type: database-storage
    spec:
      accessModes: ["ReadWriteOnce"]
      storageClassName: gp3-encrypted
      resources:
        requests:
          storage: 100Gi

data-postgres-0  →  100Gi PV (gp3-encrypted)
data-postgres-1  →  100Gi PV (gp3-encrypted)
data-postgres-2  →  100Gi PV (gp3-encrypted)

spec:
  updateStrategy:
    type: RollingUpdate       # 또는 OnDelete
    rollingUpdate:
      partition: 0            # partition 이상의 인덱스만 업데이트
      maxUnavailable: 1       # K8s 1.24+

apiVersion: v1
kind: Service
metadata:
  name: postgres-headless
  namespace: database
spec:
  type: ClusterIP
  clusterIP: None  # Headless Service
  selector:
    app: postgres
  ports:
    - port: 5432
      targetPort: 5432

# 모든 Pod IP 조회
nslookup postgres-headless.database.svc.cluster.local

# 특정 Pod 직접 접근
psql -h postgres-0.postgres-headless.database.svc.cluster.local -U postgres

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres
  namespace: database
spec:
  instances: 3

  postgresql:
    parameters:
      shared_buffers: "2GB"
      effective_cache_size: "6GB"
      work_mem: "64MB"
      maintenance_work_mem: "512MB"
      max_connections: "200"
      max_wal_size: "2GB"
      min_wal_size: "1GB"
      wal_buffers: "64MB"
      random_page_cost: "1.1"
      effective_io_concurrency: "200"
      max_worker_processes: "8"
      max_parallel_workers_per_gather: "4"
      max_parallel_workers: "8"

  storage:
    size: 100Gi
    storageClass: gp3-encrypted
    pvcTemplate:
      accessModes:
        - ReadWriteOnce

  walStorage:
    size: 20Gi
    storageClass: gp3-encrypted

  resources:
    requests:
      cpu: "2"
      memory: "8Gi"
    limits:
      cpu: "4"
      memory: "8Gi"  # limits = requests (QoS Guaranteed)

  monitoring:
    enablePodMonitor: true
    customQueriesConfigMap:
      - name: custom-queries
        key: queries

  bootstrap:
    initdb:
      database: myapp
      owner: myapp
      secret:
        name: myapp-db-credentials

정상 상태:
  my-postgres-1 (Primary, RW)
  my-postgres-2 (Replica, RO) - streaming replication
  my-postgres-3 (Replica, RO) - streaming replication

Primary 장애 발생:
  1. CloudNativePG가 Primary 장애 감지 (health check 실패)
  2. 가장 최신 WAL 위치를 가진 Replica 선택
  3. pg_promote() 실행 → 새 Primary 승격
  4. 나머지 Replica가 새 Primary를 팔로우
  5. pg_rewind로 이전 Primary를 Replica로 재합류

Failover 후:
  my-postgres-2 (Primary, RW) ← 자동 승격
  my-postgres-3 (Replica, RO) - 새 Primary 팔로우
  my-postgres-1 (Replica, RO) - pg_rewind 후 재합류

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres
spec:
  instances: 3

  backup:
    barmanObjectStore:
      destinationPath: s3://my-backup-bucket/postgres/
      endpointURL: https://s3.ap-northeast-2.amazonaws.com
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: ACCESS_SECRET_KEY
      wal:
        compression: gzip
        maxParallel: 4
      data:
        compression: gzip
        immediateCheckpoint: true
    retentionPolicy: "30d"

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: daily-backup
  namespace: database
spec:
  schedule: "0 3 * * *"  # 매일 오전 3시
  backupOwnerReference: self
  cluster:
    name: my-postgres
  immediate: false
  suspend: false

apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: manual-backup-20240901
  namespace: database
spec:
  method: barmanObjectStore
  cluster:
    name: my-postgres

# WAL 아카이빙은 backup.barmanObjectStore.wal 설정으로 자동 활성화
spec:
  backup:
    barmanObjectStore:
      wal:
        compression: gzip
        maxParallel: 4
        # WAL이 지속적으로 S3에 아카이브됨

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres-restored
spec:
  instances: 3

  bootstrap:
    recovery:
      source: my-postgres
      recoveryTarget:
        targetTime: "2024-09-01 14:30:00.00000+09"
        # 또는 특정 트랜잭션 ID
        # targetXID: "12345"
        # 또는 특정 LSN
        # targetLSN: "0/1234567"

  externalClusters:
    - name: my-postgres
      barmanObjectStore:
        destinationPath: s3://my-backup-bucket/postgres/
        endpointURL: https://s3.ap-northeast-2.amazonaws.com
        s3Credentials:
          accessKeyId:
            name: aws-creds
            key: ACCESS_KEY_ID
          secretAccessKey:
            name: aws-creds
            key: ACCESS_SECRET_KEY

apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: my-postgres-pooler-rw
  namespace: database
spec:
  cluster:
    name: my-postgres
  instances: 2
  type: rw  # rw 또는 ro
  pgbouncer:
    poolMode: transaction
    parameters:
      max_client_conn: "1000"
      default_pool_size: "25"
      min_pool_size: "5"
      reserve_pool_size: "5"
      reserve_pool_timeout: "5"
      server_idle_timeout: "300"
      log_connections: "1"
      log_disconnections: "1"

# 읽기 전용 풀러 (Replica 대상)
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: my-postgres-pooler-ro
spec:
  cluster:
    name: my-postgres
  instances: 2
  type: ro
  pgbouncer:
    poolMode: transaction
    parameters:
      max_client_conn: "2000"
      default_pool_size: "50"

# 읽기/쓰기 (Primary)
my-postgres-pooler-rw.database.svc.cluster.local:5432

# 읽기 전용 (Replica)
my-postgres-pooler-ro.database.svc.cluster.local:5432

# CloudNativePG에서 PodMonitor 자동 생성
spec:
  monitoring:
    enablePodMonitor: true
    customQueriesConfigMap:
      - name: pg-custom-queries
        key: queries

apiVersion: v1
kind: ConfigMap
metadata:
  name: pg-custom-queries
  namespace: database
data:
  queries: |
    pg_database_size:
      query: "SELECT datname, pg_database_size(datname) as size_bytes FROM pg_database WHERE datname NOT IN ('template0', 'template1')"
      master: true
      metrics:
        - datname:
            usage: "LABEL"
            description: "Database name"
        - size_bytes:
            usage: "GAUGE"
            description: "Database size in bytes"

    pg_stat_activity_count:
      query: "SELECT state, count(*) as count FROM pg_stat_activity GROUP BY state"
      master: true
      metrics:
        - state:
            usage: "LABEL"
            description: "Connection state"
        - count:
            usage: "GAUGE"
            description: "Number of connections"

    pg_replication_lag:
      query: "SELECT CASE WHEN pg_is_in_recovery() THEN EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int ELSE 0 END as lag_seconds"
      master: false
      metrics:
        - lag_seconds:
            usage: "GAUGE"
            description: "Replication lag in seconds"

# 이미지 업데이트
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:16.3

# CloudNativePG가 자동으로:
# 1. Replica부터 하나씩 재시작
# 2. 각 Replica가 Ready 상태 확인 후 다음 진행
# 3. 마지막에 Primary를 switchover (새 Primary 승격 후 이전 Primary 재시작)
# → 다운타임 없이 업데이트 완료

apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBCluster
metadata:
  name: my-mysql
  namespace: database
spec:
  crVersion: "1.14.0"
  secretsName: my-mysql-secrets

  pxc:
    size: 3
    image: percona/percona-xtradb-cluster:8.0.35
    resources:
      requests:
        cpu: "2"
        memory: "8Gi"
      limits:
        cpu: "4"
        memory: "8Gi"
    volumeSpec:
      persistentVolumeClaim:
        storageClassName: gp3-encrypted
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 100Gi
    configuration: |
      [mysqld]
      innodb_buffer_pool_size=4G
      innodb_log_file_size=1G
      innodb_flush_method=O_DIRECT
      max_connections=500
      wsrep_sync_wait=3
      wsrep_slave_threads=4

  haproxy:
    enabled: true
    size: 2
    image: percona/haproxy:2.8.5
    resources:
      requests:
        cpu: "500m"
        memory: "1Gi"

  proxysql:
    enabled: false

  backup:
    image: percona/percona-xtradb-cluster-operator:1.14.0-pxc8.0-backup-pxb8.0.35
    storages:
      s3-backup:
        type: s3
        s3:
          bucket: my-mysql-backups
          region: ap-northeast-2
          credentialsSecret: aws-s3-secret
    schedule:
      - name: daily-full
        schedule: "0 3 * * *"
        keep: 7
        storageName: s3-backup

spec:
  proxysql:
    enabled: true
    size: 2
    image: percona/proxysql2:2.5.5
    resources:
      requests:
        cpu: "500m"
        memory: "1Gi"
    configuration: |
      datadir="/var/lib/proxysql"
      admin_variables=
      {
        admin_credentials="admin:admin"
        mysql_ifaces="0.0.0.0:6032"
      }
      mysql_variables=
      {
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        poll_timeout=2000
        interfaces="0.0.0.0:3306;/tmp/proxysql.sock"
        default_schema="information_schema"
        stacksize=1048576
        connect_timeout_server=3000
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
      }

# 수동 백업 실행
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterBackup
metadata:
  name: manual-backup-20240901
  namespace: database
spec:
  pxcCluster: my-mysql
  storageName: s3-backup

# 백업 상태 확인
kubectl get pxc-backup -n database
# NAME                    CLUSTER    STORAGE     STATUS
# manual-backup-20240901  my-mysql   s3-backup   Succeeded

# 백업에서 복원
kubectl apply -f - <<EOF
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterRestore
metadata:
  name: restore-20240901
  namespace: database
spec:
  pxcCluster: my-mysql
  backupName: manual-backup-20240901
EOF

apiVersion: mongodbcommunity.mongodb.com/v1
kind: MongoDBCommunity
metadata:
  name: my-mongodb
  namespace: database
spec:
  members: 3
  type: ReplicaSet
  version: "7.0.12"
  security:
    authentication:
      modes: ["SCRAM"]
  users:
    - name: admin
      db: admin
      passwordSecretRef:
        name: mongodb-admin-password
      roles:
        - name: clusterAdmin
          db: admin
        - name: userAdminAnyDatabase
          db: admin
      scramCredentialsSecretName: admin-scram
    - name: myapp
      db: myapp
      passwordSecretRef:
        name: mongodb-myapp-password
      roles:
        - name: readWrite
          db: myapp
      scramCredentialsSecretName: myapp-scram

  statefulSet:
    spec:
      template:
        spec:
          containers:
            - name: mongod
              resources:
                requests:
                  cpu: "2"
                  memory: "4Gi"
                limits:
                  cpu: "4"
                  memory: "4Gi"
      volumeClaimTemplates:
        - metadata:
            name: data-volume
          spec:
            storageClassName: gp3-encrypted
            accessModes: ["ReadWriteOnce"]
            resources:
              requests:
                storage: 50Gi
        - metadata:
            name: logs-volume
          spec:
            storageClassName: gp3-encrypted
            accessModes: ["ReadWriteOnce"]
            resources:
              requests:
                storage: 10Gi

  additionalMongodConfig:
    storage.wiredTiger.engineConfig.cacheSizeGB: 2
    net.maxIncomingConnections: 500

# mongodump 기반 논리 백업 (CronJob)
apiVersion: batch/v1
kind: CronJob
metadata:
  name: mongodb-backup
  namespace: database
spec:
  schedule: "0 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
            - name: backup
              image: mongo:7.0
              command:
                - /bin/sh
                - -c
                - |
                  mongodump \
                    --uri="mongodb://myapp:password@my-mongodb-0.my-mongodb-svc.database.svc.cluster.local:27017/myapp?replicaSet=my-mongodb&authSource=myapp" \
                    --gzip \
                    --archive=/backup/myapp-$(date +%Y%m%d).gz
                  # S3에 업로드
                  aws s3 cp /backup/myapp-$(date +%Y%m%d).gz \
                    s3://my-backup-bucket/mongodb/
              volumeMounts:
                - name: backup-volume
                  mountPath: /backup
          volumes:
            - name: backup-volume
              emptyDir: {}
          restartPolicy: OnFailure

# AWS EBS gp3 StorageClass
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: gp3-encrypted
provisioner: ebs.csi.aws.com
parameters:
  type: gp3
  encrypted: "true"
  iops: "6000"           # gp3 기본 3000, 최대 16000
  throughput: "250"       # gp3 기본 125MB/s, 최대 1000MB/s
  fsType: ext4
reclaimPolicy: Retain     # DB 데이터는 반드시 Retain
volumeBindingMode: WaitForFirstConsumer  # 토폴로지 인식 바인딩
allowVolumeExpansion: true

# GCP PD SSD StorageClass
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: pd-ssd-encrypted
provisioner: pd.csi.storage.gke.io
parameters:
  type: pd-ssd
  disk-encryption-kms-key: projects/my-project/locations/global/keyRings/my-ring/cryptoKeys/my-key
reclaimPolicy: Retain
volumeBindingMode: WaitForFirstConsumer
allowVolumeExpansion: true

# AWS EBS CSI 드라이버 설치
helm repo add aws-ebs-csi-driver https://kubernetes-sigs.github.io/aws-ebs-csi-driver
helm install aws-ebs-csi-driver aws-ebs-csi-driver/aws-ebs-csi-driver \
  --namespace kube-system \
  --set controller.serviceAccount.annotations."eks\.amazonaws\.com/role-arn"="arn:aws:iam::ACCOUNT:role/ebs-csi-role"

# local-path-provisioner (개발/테스트 환경)
kubectl apply -f https://raw.githubusercontent.com/rancher/local-path-provisioner/master/deploy/local-path-storage.yaml

# fio 벤치마크 Pod
kubectl run fio-bench --image=nixery.dev/fio \
  --overrides='{
    "spec": {
      "containers": [{
        "name": "fio-bench",
        "image": "nixery.dev/fio",
        "command": ["sleep", "3600"],
        "volumeMounts": [{
          "name": "test-vol",
          "mountPath": "/data"
        }]
      }],
      "volumes": [{
        "name": "test-vol",
        "persistentVolumeClaim": {
          "claimName": "bench-pvc"
        }
      }]
    }
  }'

# 랜덤 읽기 벤치마크
kubectl exec fio-bench -- fio \
  --name=randread \
  --ioengine=libaio \
  --iodepth=32 \
  --rw=randread \
  --bs=4k \
  --direct=1 \
  --size=1G \
  --numjobs=4 \
  --runtime=60 \
  --directory=/data \
  --group_reporting

# pgbench (PostgreSQL 성능 테스트)
kubectl exec -it my-postgres-1 -- pgbench \
  -i -s 100 myapp  # 초기화 (100 scale factor)

kubectl exec -it my-postgres-1 -- pgbench \
  -c 50 -j 4 -T 300 -P 10 myapp  # 50 클라이언트, 300초

# CloudNativePG: 동기 복제 설정
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres-sync
spec:
  instances: 3

  postgresql:
    parameters:
      synchronous_standby_names: "ANY 1 (*)"
      # ANY 1: 최소 1개 Replica에 동기 복제 보장
      # FIRST 1: 첫 번째 Replica에 동기 복제

  # minSyncReplicas / maxSyncReplicas로 세밀 제어
  minSyncReplicas: 1
  maxSyncReplicas: 1

apiVersion: policy/v1
kind: PodDisruptionBudget
metadata:
  name: postgres-pdb
  namespace: database
spec:
  maxUnavailable: 1  # 동시에 최대 1개 Pod만 중단 허용
  selector:
    matchLabels:
      app: postgres

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres
spec:
  instances: 3

  topologySpreadConstraints:
    - maxSkew: 1
      topologyKey: topology.kubernetes.io/zone
      whenUnsatisfiable: DoNotSchedule
      labelSelector:
        matchLabels:
          cnpg.io/cluster: my-postgres
    - maxSkew: 1
      topologyKey: kubernetes.io/hostname
      whenUnsatisfiable: DoNotSchedule
      labelSelector:
        matchLabels:
          cnpg.io/cluster: my-postgres

spec:
  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname
    # 또는 topology.kubernetes.io/zone (AZ 분산)
    podAntiAffinityType: required
    # required: 반드시 분산 (Pod 스케줄 불가 시 Pending)
    # preferred: 가능하면 분산 (스케줄 불가 시 동일 노드 허용)

    nodeAffinity:
      requiredDuringSchedulingIgnoredDuringExecution:
        nodeSelectorTerms:
          - matchExpressions:
              - key: node-role
                operator: In
                values:
                  - database
    tolerations:
      - key: database-only
        operator: Equal
        value: "true"
        effect: NoSchedule

WAL 아카이빙 흐름:
PG Primary → WAL 세그먼트 생성 (16MB 단위) → S3/GCS 아카이브
                                                    ↓
                                            연속적 데이터 보호
                                                    ↓
                                        PITR: 임의 시점 복구 가능

# CloudNativePG WAL 아카이빙 설정 (자동)
spec:
  backup:
    barmanObjectStore:
      destinationPath: s3://my-backup-bucket/wal-archive/
      wal:
        compression: gzip
        maxParallel: 4
        # WAL 세그먼트가 완성될 때마다 자동 업로드

# 1. 현재 백업 목록 확인
kubectl get backup -n database

# 2. 복원 대상 시간 결정 (예: 실수로 DELETE 실행 직전)
# 시간: 2024-09-01 14:29:00 (삭제가 14:30에 실행됨)

# 3. 새 클러스터로 PITR 복원
kubectl apply -f pitr-restore.yaml
# (앞서 3.4절의 PITR YAML 참조)

# 4. 복원 진행 상황 모니터링
kubectl get cluster my-postgres-restored -n database -w

# 5. 복원 완료 후 데이터 검증
kubectl exec -it my-postgres-restored-1 -- psql -U myapp -d myapp \
  -c "SELECT count(*) FROM important_table;"

# 6. 애플리케이션 연결 전환
kubectl patch service myapp-db -n database \
  -p '{"spec":{"selector":{"cnpg.io/cluster":"my-postgres-restored"}}}'

# Velero 설치
velero install \
  --provider aws \
  --plugins velero/velero-plugin-for-aws:v1.9.0 \
  --bucket my-velero-bucket \
  --backup-location-config region=ap-northeast-2 \
  --snapshot-location-config region=ap-northeast-2

# DB 네임스페이스 전체 백업 (PV 포함)
velero backup create db-full-backup \
  --include-namespaces database \
  --include-resources '*' \
  --snapshot-volumes=true \
  --volume-snapshot-locations default

# 스케줄 백업
velero schedule create daily-db-backup \
  --schedule="0 4 * * *" \
  --include-namespaces database \
  --snapshot-volumes=true \
  --ttl 720h  # 30일 보관

# Source 리전 (ap-northeast-2)의 CloudNativePG 클러스터
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres-primary
spec:
  instances: 3
  backup:
    barmanObjectStore:
      destinationPath: s3://my-backup-bucket-primary/
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: ACCESS_SECRET_KEY

---
# DR 리전 (us-west-2)의 Replica 클러스터
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres-dr
spec:
  instances: 2

  replica:
    enabled: true
    source: my-postgres-primary

  externalClusters:
    - name: my-postgres-primary
      barmanObjectStore:
        destinationPath: s3://my-backup-bucket-primary/
        s3Credentials:
          accessKeyId:
            name: aws-creds-dr
            key: ACCESS_KEY_ID
          secretAccessKey:
            name: aws-creds-dr
            key: ACCESS_SECRET_KEY

# DB Pod는 반드시 QoS Guaranteed 클래스로 설정
resources:
  requests:
    cpu: "4"
    memory: "16Gi"
  limits:
    cpu: "4"        # requests == limits → Guaranteed
    memory: "16Gi"  # OOM Killer 방지

총 메모리 16Gi 기준:
- shared_buffers: 4GB (25%)
- effective_cache_size: 12GB (75%)
- work_mem: 64MB (세션당)
- maintenance_work_mem: 1GB
- OS/기타: 약 2GB

# 노드에 Huge Pages 설정 필요
# /etc/sysctl.conf: vm.nr_hugepages=2048

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres
spec:
  postgresql:
    parameters:
      shared_buffers: "4GB"
      huge_pages: "on"

  resources:
    requests:
      cpu: "4"
      memory: "16Gi"
      hugepages-2Mi: "4Gi"
    limits:
      cpu: "4"
      memory: "16Gi"
      hugepages-2Mi: "4Gi"

postgresql:
  parameters:
    # 메모리
    shared_buffers: "4GB"
    effective_cache_size: "12GB"
    work_mem: "64MB"
    maintenance_work_mem: "1GB"
    wal_buffers: "64MB"

    # WAL
    max_wal_size: "4GB"
    min_wal_size: "1GB"
    checkpoint_completion_target: "0.9"
    wal_compression: "zstd"

    # 쿼리 플래너
    random_page_cost: "1.1"       # SSD인 경우 (HDD는 4.0)
    effective_io_concurrency: "200"  # SSD인 경우 (HDD는 2)
    seq_page_cost: "1.0"

    # 병렬 처리
    max_worker_processes: "8"
    max_parallel_workers_per_gather: "4"
    max_parallel_workers: "8"
    max_parallel_maintenance_workers: "4"

    # 연결
    max_connections: "200"       # PgBouncer 사용 시 낮게
    idle_in_transaction_session_timeout: "30000"  # 30초

    # 로깅
    log_min_duration_statement: "1000"   # 1초 이상 쿼리 로깅
    log_checkpoints: "on"
    log_lock_waits: "on"
    log_temp_files: "0"

    # 자동 VACUUM
    autovacuum_max_workers: "4"
    autovacuum_naptime: "30"
    autovacuum_vacuum_cost_limit: "1000"

PgBouncer 권장 설정:

pool_mode = transaction
- 트랜잭션 단위로 커넥션 할당/반환
- K8s 환경에서 수백 Pod 동시 연결 시 필수
- PREPARE 문 사용 제한 (session mode에서만 가능)

default_pool_size = 25
- 백엔드 DB 커넥션 수 (max_connections의 12.5%)
- 너무 높으면 DB 과부하, 너무 낮으면 대기 증가

max_client_conn = 1000
- 프론트엔드 최대 연결 수
- Pod 수 x Pod당 커넥션 수 기준으로 산정

reserve_pool_size = 5
- 피크 트래픽 대비 예비 커넥션

server_idle_timeout = 300
- 유휴 커넥션 5분 후 정리

apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: postgres-network-policy
  namespace: database
spec:
  podSelector:
    matchLabels:
      cnpg.io/cluster: my-postgres
  policyTypes:
    - Ingress
    - Egress
  ingress:
    # 애플리케이션 네임스페이스에서만 접근 허용
    - from:
        - namespaceSelector:
            matchLabels:
              app.kubernetes.io/part-of: myapp
        - podSelector:
            matchLabels:
              app: myapp
      ports:
        - protocol: TCP
          port: 5432
    # 같은 클러스터 내 복제 트래픽 허용
    - from:
        - podSelector:
            matchLabels:
              cnpg.io/cluster: my-postgres
      ports:
        - protocol: TCP
          port: 5432
  egress:
    # S3 백업을 위한 아웃바운드 (HTTPS)
    - to: []
      ports:
        - protocol: TCP
          port: 443
    # 클러스터 내 통신
    - to:
        - podSelector:
            matchLabels:
              cnpg.io/cluster: my-postgres
      ports:
        - protocol: TCP
          port: 5432
    # DNS
    - to: []
      ports:
        - protocol: UDP
          port: 53
        - protocol: TCP
          port: 53

apiVersion: external-secrets.io/v1beta1
kind: ExternalSecret
metadata:
  name: postgres-credentials
  namespace: database
spec:
  refreshInterval: 1h
  secretStoreRef:
    name: aws-secrets-manager
    kind: ClusterSecretStore
  target:
    name: myapp-db-credentials
    creationPolicy: Owner
  data:
    - secretKey: username
      remoteRef:
        key: production/database/postgres
        property: username
    - secretKey: password
      remoteRef:
        key: production/database/postgres
        property: password

# CloudNativePG는 기본적으로 TLS 활성화
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres
spec:
  certificates:
    serverTLSSecret: my-postgres-server-tls
    serverCASecret: my-postgres-ca
    clientCASecret: my-postgres-client-ca
    replicationTLSSecret: my-postgres-replication-tls

  postgresql:
    parameters:
      ssl: "on"
      ssl_min_protocol_version: "TLSv1.3"

# Prometheus 알림 규칙
apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: postgres-alerts
  namespace: monitoring
spec:
  groups:
    - name: postgres.rules
      rules:
        - alert: PostgresReplicationLagHigh
          expr: cnpg_pg_replication_lag > 10
          for: 5m
          labels:
            severity: warning
          annotations:
            summary: "PostgreSQL 복제 지연이 10초를 초과합니다"

        - alert: PostgresConnectionsNearLimit
          expr: >
            cnpg_pg_stat_activity_count /
            cnpg_pg_settings_setting{name="max_connections"} > 0.8
          for: 5m
          labels:
            severity: warning
          annotations:
            summary: "연결 수가 최대치의 80%를 초과했습니다"

        - alert: PostgresCacheHitRatioLow
          expr: >
            cnpg_pg_stat_database_blks_hit /
            (cnpg_pg_stat_database_blks_hit + cnpg_pg_stat_database_blks_read) < 0.95
          for: 15m
          labels:
            severity: warning
          annotations:
            summary: "캐시 히트율이 95% 미만입니다. shared_buffers 증가를 고려하세요"

        - alert: PostgresStorageNearFull
          expr: >
            kubelet_volume_stats_used_bytes{namespace="database"} /
            kubelet_volume_stats_capacity_bytes{namespace="database"} > 0.85
          for: 10m
          labels:
            severity: critical
          annotations:
            summary: "DB 스토리지 사용량이 85%를 초과했습니다"

        - alert: PostgresFailoverDetected
          expr: changes(cnpg_pg_replication_is_primary[5m]) > 0
          labels:
            severity: critical
          annotations:
            summary: "PostgreSQL Failover가 감지되었습니다"

핵심 모니터링 패널:
1. 복제 지연 (초) - cnpg_pg_replication_lag
2. 활성 연결 수 - cnpg_pg_stat_activity_count
3. 캐시 히트율 - blks_hit / (blks_hit + blks_read)
4. TPS (트랜잭션/초) - rate(cnpg_pg_stat_database_xact_commit[5m])
5. 쿼리 레이턴시 P99
6. 디스크 IOPS / 처리량
7. WAL 생성 속도 - rate(cnpg_pg_stat_archiver_archived_count[5m])
8. 스토리지 사용량 - kubelet_volume_stats_used_bytes
9. CPU / 메모리 사용률
10. Vacuum / Analyze 실행 상태

# 방법 1: pg_dump/pg_restore (소규모 DB)
# 소스 VM에서:
pg_dump -Fc -d myapp -h vm-db.internal -U postgres > myapp.dump

# K8s Pod에서 복원:
kubectl cp myapp.dump database/my-postgres-1:/tmp/myapp.dump
kubectl exec -it my-postgres-1 -n database -- \
  pg_restore -d myapp -U postgres /tmp/myapp.dump

# 방법 2: 논리 복제 (대규모 DB, 무중단)
# 1. K8s에서 빈 클러스터 생성
# 2. VM DB에서 publication 생성
#    CREATE PUBLICATION my_pub FOR ALL TABLES;
# 3. K8s DB에서 subscription 생성
#    CREATE SUBSCRIPTION my_sub
#      CONNECTION 'host=vm-db.internal dbname=myapp user=replicator'
#      PUBLICATION my_pub;
# 4. 초기 데이터 동기화 완료 대기
# 5. 애플리케이션 연결을 K8s DB로 전환
# 6. subscription 삭제

# AWS DMS (Database Migration Service) 활용
# 1. DMS 복제 인스턴스 생성
# 2. 소스 엔드포인트: RDS PostgreSQL
# 3. 대상 엔드포인트: K8s PostgreSQL (NLB/Port-forward 통해 접근)
# 4. Full Load + CDC (Change Data Capture) 모드로 실행
# 5. 동기화 완료 후 애플리케이션 전환

마이그레이션 전:
[ ] 원본 DB 크기 및 테이블 수 파악
[ ] K8s 클러스터 리소스 여유 확인
[ ] StorageClass / PV 크기 산정 (원본의 2배 여유)
[ ] 네트워크 연결 확인 (원본 DB → K8s 클러스터)
[ ] 논리 복제 사용자 생성 및 권한 부여

마이그레이션 중:
[ ] 초기 데이터 복사 완료 확인
[ ] 복제 지연이 0에 수렴하는지 확인
[ ] 시퀀스 값 동기화 확인
[ ] 애플리케이션 읽기 트래픽 K8s로 전환 (테스트)
[ ] 쓰기 트래픽 전환 (짧은 다운타임 또는 이중 쓰기)

마이그레이션 후:
[ ] 데이터 정합성 검증 (행 수, 체크섬)
[ ] 애플리케이션 성능 확인
[ ] 원본 DB 복제 중단
[ ] 백업 정책 적용 확인
[ ] 모니터링/알림 동작 확인