✍️ 필사 모드: K8s에서 DB 운영하기 — CNPG, Percona, Vitess, Helm 차트 완전 가이드

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  serviceName: postgres
  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
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: ['ReadWriteOnce']
        storageClassName: gp3
        resources:
          requests:
            storage: 50Gi

# Helm 리포지토리 추가
helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update

# Operator 설치
helm upgrade --install cnpg \
  --namespace cnpg-system \
  --create-namespace \
  cnpg/cloudnative-pg

kubectl apply --server-side -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.29/releases/cnpg-1.29.0.yaml

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: prod-pg
spec:
  instances: 3
  storage:
    size: 100Gi
    storageClass: gp3
  backup:
    barmanObjectStore:
      destinationPath: s3://my-backup-bucket/prod-pg/
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: SECRET_ACCESS_KEY
      wal:
        compression: gzip
    retentionPolicy: '30d'

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: prod-pg-daily
spec:
  schedule: '0 2 * * *'
  cluster:
    name: prod-pg
  backupOwnerReference: self

apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBCluster
metadata:
  name: prod-mysql
spec:
  crVersion: '1.15.0'
  pxc:
    size: 3
    image: percona/percona-xtradb-cluster:8.0
    resources:
      requests:
        memory: 2Gi
        cpu: '1'
    volumeSpec:
      persistentVolumeClaim:
        storageClassName: gp3
        resources:
          requests:
            storage: 100Gi
  haproxy:
    enabled: true
    size: 2
  pmm:
    enabled: true
    serverHost: monitoring-service
  backup:
    schedule:
      - name: daily-backup
        schedule: '0 3 * * *'
        keep: 7
        storageName: s3-backup
    storages:
      s3-backup:
        type: s3
        s3:
          bucket: my-backup-bucket
          credentialsSecret: aws-creds
          region: ap-northeast-2

# Vitess Operator 설치
kubectl apply -f https://github.com/planetscale/vitess-operator/releases/latest/download/operator.yaml

apiVersion: planetscale.com/v2
kind: VitessCluster
metadata:
  name: prod-vitess
spec:
  images:
    vtgate: vitess/lite:v19
    vttablet: vitess/lite:v19
    vtbackup: vitess/lite:v19
    vtctld: vitess/lite:v19
    vtorc: vitess/lite:v19
  cells:
    - name: zone1
      gateway:
        replicas: 2
        resources:
          requests:
            cpu: '1'
            memory: 1Gi
  keyspaces:
    - name: commerce
      turndownPolicy: Immediate
      partitionings:
        - equal:
            parts: 2
            shardTemplate:
              databaseInitScriptSecret:
                name: commerce-schema
                key: init.sql
              tabletPools:
                - cell: zone1
                  type: replica
                  replicas: 3
                  dataVolumeClaimTemplate:
                    storageClassName: gp3
                    resources:
                      requests:
                        storage: 50Gi

helm install prod-pg bitnami/postgresql-ha \
  --set postgresql.replicaCount=3 \
  --set postgresql.resources.requests.memory=2Gi \
  --set postgresql.resources.requests.cpu=1 \
  --set persistence.size=100Gi \
  --set persistence.storageClass=gp3 \
  --set pgpool.replicaCount=2 \
  --set metrics.enabled=true

# 권장 StorageClass 예시 (AWS EBS gp3)
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: gp3-db
provisioner: ebs.csi.aws.com
parameters:
  type: gp3
  iops: '5000'
  throughput: '250'
  encrypted: 'true'
volumeBindingMode: WaitForFirstConsumer
allowVolumeExpansion: true
reclaimPolicy: Retain

# Pod에 CPU pinning 및 NUMA 인식 설정 예시
spec:
  containers:
    - name: postgres
      resources:
        requests:
          cpu: '4'
          memory: 8Gi
        limits:
          cpu: '4'
          memory: 8Gi
      # Guaranteed QoS 클래스 확보
      # requests == limits로 설정

spec:
  affinity:
    podAntiAffinity:
      requiredDuringSchedulingIgnoredDuringExecution:
        - labelSelector:
            matchExpressions:
              - key: app
                operator: In
                values:
                  - postgres
          topologyKey: kubernetes.io/hostname

# CNPG에서 PodMonitor 활성화
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: prod-pg
spec:
  instances: 3
  monitoring:
    enablePodMonitor: true
    customQueriesConfigMap:
      - name: pg-custom-queries
        key: queries
  storage:
    size: 100Gi

# CNPG 복구 클러스터 예시
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: recovery-cluster
spec:
  instances: 2
  storage:
    size: 100Gi
  bootstrap:
    recovery:
      source: prod-pg
      recoveryTarget:
        targetTime: '2026-04-10T08:00:00Z'
  externalClusters:
    - name: prod-pg
      barmanObjectStore:
        destinationPath: s3://my-backup-bucket/prod-pg/
        s3Credentials:
          accessKeyId:
            name: aws-creds
            key: ACCESS_KEY_ID
          secretAccessKey:
            name: aws-creds
            key: SECRET_ACCESS_KEY

kubectl create namespace database

kubectl create secret generic pg-superuser \
  --namespace database \
  --from-literal=username=postgres \
  --from-literal=password=CHANGE_ME_TO_STRONG_PASSWORD

kubectl create secret generic aws-creds \
  --namespace database \
  --from-literal=ACCESS_KEY_ID=your-access-key \
  --from-literal=SECRET_ACCESS_KEY=your-secret-key

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: prod-pg
  namespace: database
spec:
  description: 'Production PostgreSQL HA Cluster'
  imageName: ghcr.io/cloudnative-pg/postgresql:16.4

  instances: 3

  startDelay: 30
  stopDelay: 30
  primaryUpdateStrategy: unsupervised

  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'
      log_statement: 'ddl'
      log_min_duration_statement: '1000'
    pg_hba:
      - host all all 10.0.0.0/8 scram-sha-256

  bootstrap:
    initdb:
      database: appdb
      owner: appuser
      secret:
        name: pg-superuser

  storage:
    size: 100Gi
    storageClass: gp3-db

  walStorage:
    size: 30Gi
    storageClass: gp3-db

  resources:
    requests:
      memory: 8Gi
      cpu: '4'
    limits:
      memory: 8Gi
      cpu: '4'

  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname

  monitoring:
    enablePodMonitor: true

  backup:
    barmanObjectStore:
      destinationPath: s3://my-backup-bucket/prod-pg/
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: SECRET_ACCESS_KEY
      wal:
        compression: gzip
        maxParallel: 4
      data:
        compression: gzip
        jobs: 4
    retentionPolicy: '30d'

  nodeMaintenanceWindow:
    inProgress: false
    reusePVC: true

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: prod-pg-daily
  namespace: database
spec:
  schedule: '0 2 * * *'
  backupOwnerReference: self
  cluster:
    name: prod-pg
  target: prefer-standby

apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: prod-pg-pooler-rw
  namespace: database
spec:
  cluster:
    name: prod-pg
  instances: 2
  type: rw
  pgbouncer:
    poolMode: transaction
    parameters:
      max_client_conn: '1000'
      default_pool_size: '50'

kubectl apply -f cluster.yaml
kubectl apply -f scheduled-backup.yaml
kubectl apply -f pooler.yaml

# 클러스터 상태 확인
kubectl get cluster -n database

# Pod 상태 확인
kubectl get pods -n database

# 클러스터 상세 정보
kubectl describe cluster prod-pg -n database

# Primary에 접속 테스트
kubectl exec -it prod-pg-1 -n database -- psql -U postgres -d appdb

# 올바른 Anti-Affinity 설정
affinity:
  podAntiAffinity:
    requiredDuringSchedulingIgnoredDuringExecution:
      - labelSelector:
          matchExpressions:
            - key: cnpg.io/cluster
              operator: In
              values:
                - prod-pg
        topologyKey: topology.kubernetes.io/zone

# External Secrets 예시
apiVersion: external-secrets.io/v1beta1
kind: ExternalSecret
metadata:
  name: pg-credentials
spec:
  refreshInterval: 1h
  secretStoreRef:
    name: aws-secretsmanager
    kind: SecretStore
  target:
    name: pg-superuser
  data:
    - secretKey: username
      remoteRef:
        key: prod/database/credentials
        property: username
    - secretKey: password
      remoteRef:
        key: prod/database/credentials
        property: password