✍️ 필사 모드: 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"

  monitoring:
    enablePodMonitor: true

  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として再合流

フェイルオーバー後：
  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-1.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: Cluster
metadata:
  name: my-postgres-restored
spec:
  instances: 3

  bootstrap:
    recovery:
      source: my-postgres
      recoveryTarget:
        targetTime: "2024-09-01 14:30:00.00000+09"

  externalClusters:
    - name: my-postgres
      barmanObjectStore:
        destinationPath: s3://my-backup-bucket/postgres/
        endpointURL: https://s3.ap-northeast-1.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
  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"

# 読み取り専用プーラー（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

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_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から1つずつ再起動
# 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

  haproxy:
    enabled: true
    size: 2
    image: percona/haproxy:2.8.5

  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-1
          credentialsSecret: aws-s3-secret
    schedule:
      - name: daily-full
        schedule: "0 3 * * *"
        keep: 7
        storageName: s3-backup

# 手動バックアップ
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterBackup
metadata:
  name: manual-backup-20240901
spec:
  pxcCluster: my-mysql
  storageName: s3-backup

# バックアップ状態確認
kubectl get pxc-backup -n database

# バックアップからの復元
kubectl apply -f - <<EOF
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterRestore
metadata:
  name: restore-20240901
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

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

# 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

# fioベンチマークPod
kubectl run fio-bench --image=nixery.dev/fio -- sleep 3600

# ランダム読取ベンチマーク
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スケールファクター）

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 (*)"

  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
    podAntiAffinityType: required

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

# 1. 現在のバックアップ一覧確認
kubectl get backup -n database

# 2. 復元対象時間の決定（例：誤ってDELETEを実行する直前）

# 3. 新クラスターでPITR復元
kubectl apply -f pitr-restore.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-1

# DBネームスペース全体バックアップ（PV含む）
velero backup create db-full-backup \
  --include-namespaces database \
  --include-resources '*' \
  --snapshot-volumes=true

# スケジュールバックアップ
velero schedule create daily-db-backup \
  --schedule="0 4 * * *" \
  --include-namespaces database \
  --snapshot-volumes=true \
  --ttl 720h  # 30日保持

# ソースリージョンのCluster
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リージョンの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

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）

    # 並列処理
    max_worker_processes: "8"
    max_parallel_workers_per_gather: "4"
    max_parallel_workers: "8"

    # 接続
    max_connections: "200"
    idle_in_transaction_session_timeout: "30000"

    # ロギング
    log_min_duration_statement: "1000"   # 1秒以上のクエリをログ
    log_checkpoints: "on"
    log_lock_waits: "on"

    # 自動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%）

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
      ports:
        - protocol: TCP
          port: 5432
    - from:
        - podSelector:
            matchLabels:
              cnpg.io/cluster: my-postgres
      ports:
        - protocol: TCP
          port: 5432
  egress:
    - to: []
      ports:
        - protocol: TCP
          port: 443
    - to:
        - podSelector:
            matchLabels:
              cnpg.io/cluster: my-postgres
      ports:
        - protocol: TCP
          port: 5432
    - 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

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-postgres
spec:
  certificates:
    serverTLSSecret: my-postgres-server-tls
    serverCASecret: my-postgres-ca

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

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: 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）
pg_dump -Fc -d myapp -h vm-db.internal -U postgres > myapp.dump

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を削除

マイグレーション前：
[ ] ソースDBサイズとテーブル数を把握
[ ] K8sクラスターリソース余裕を確認
[ ] StorageClass / PVサイズを算定（ソースの2倍の余裕）
[ ] ネットワーク接続確認（ソースDB → K8sクラスター）

マイグレーション中：
[ ] 初期データコピー完了を確認
[ ] レプリケーション遅延が0に収束することを確認
[ ] シーケンス値の同期を確認
[ ] アプリケーション読取トラフィックをK8sに切り替え（テスト）
[ ] 書込トラフィックの切り替え

マイグレーション後：
[ ] データ整合性検証（行数、チェックサム）
[ ] アプリケーション性能確認
[ ] ソースDBレプリケーション停止
[ ] バックアップポリシー適用確認
[ ] モニタリング/アラート動作確認