PostgreSQL on Kubernetes 面试题

graph TD
    subgraph CloudNativePG
        A1["纯 Kubernetes 原生设计"]
        A2["无外部依赖（无 Patroni/etcd）"]
        A3["Instance Manager 直接管理 PG"]
        A4["PVC 直接挂载，无 Operator 层"]
    end
    subgraph Zalando
        B1["Spilo 镜像封装 Patroni"]
        B2["依赖 etcd 作为 DCS"]
        B3["成熟的 HA 方案（Patroni）"]
        B4["需要 Operator + ConfigMap"]
    end

graph TD
    K8sAPI["Kubernetes API Server"]
    K8sAPI -->|"Watch"| Controller["CloudNativePG Controller Manager"]
    Controller --> ClusterCtl["Cluster Controller"]
    Controller --> BackupCtl["Backup Controller"]
    Controller --> ScheduledCtl["ScheduledBackup Controller"]
    Controller -->|"管理"| PGPod["PostgreSQL Pod"]
    PGPod --> IM["Instance Manager"]
    IM --> Lifecycle["Lifecycle Manager"]
    IM --> WAL["WAL Archiver"]
    IM --> BackupMgr["Backup Manager"]
    PGPod --> PGEngine["PostgreSQL 16/17（Primary/Standby）"]
    PGPod --> PVC["~~~ PVC（PGDATA + WAL）~~~"]

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-production
spec:
  # 实例数量 (Primary + Standby)
  instances: 3

  # PostgreSQL 镜像与版本
  imageName: ghcr.io/cloudnative-pg/postgresql:16.4

  # Primary Pod 调度策略
  primaryUpdateStrategy: unsupervised

  # 存储配置
  storage:
    size: 100Gi
    storageClass: premium-rwo

  # WAL 独立存储
  walStorage:
    size: 20Gi
    storageClass: premium-rwo

  # PostgreSQL 参数
  postgresql:
    parameters:
      shared_buffers: "4GB"
      max_connections: "500"
      pg_stat_statements.max: "10000"
      log_min_duration_statement: "1s"

  # 用于初始化数据库的引用
  bootstrap:
    initdb:
      database: appdb
      owner: appuser
      secret:
        name: pg-credentials

  # 监控
  monitoring:
    enablePodMonitor: true

  # 备份
  backup:
    barmanObjectStore:
      destinationPath: s3://pg-backups/production
      endpointURL: https://s3.cn-north-1.amazonaws.com.cn
      s3Credentials:
        accessKeyId:
          name: s3-credentials
          key: access_key_id
        secretAccessKey:
          name: s3-credentials
          key: secret_access_key
      wal:
        compression: gzip
        encryption: AES256
      retentionPolicy: "30d"

sequenceDiagram
    participant PG as PostgreSQL Process
    participant IM as Instance Manager
    participant OS as Object Storage

    PG->>PG: 生成 WAL 段（16MB default）
    PG->>IM: archive_command
    IM->>IM: 压缩 + 加密
    IM->>OS: 上传 WAL 段
    OS-->>IM: 确认写入
    IM-->>PG: 归档成功
    Note over PG,OS: .partial WAL（每 5min 或达到 10MB 时）
    PG->>IM: partial upload
    IM->>OS: 上传 .partial

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-restored
spec:
  instances: 3
  storage:
    size: 100Gi

  bootstrap:
    recovery:
      source: pg-production-backup-20250801
      # 恢复到指定时间点
      recoveryTarget:
        targetTime: "2025-08-01T14:30:00+08:00"
        # 或在恢复后自动提升为 Primary
        exclusive: false

      # 也可以恢复到指定事务
      # recoveryTarget:
      #   targetXID: "12345678"

  externalClusters:
    - name: pg-production-backup-20250801
      barmanObjectStore:
        destinationPath: s3://pg-backups/production
        endpointURL: https://s3.cn-north-1.amazonaws.com.cn
        s3Credentials:
          accessKeyId:
            name: s3-credentials
            key: access_key_id
          secretAccessKey:
            name: s3-credentials
            key: secret_access_key

apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: pg-manual-backup-20250801
spec:
  cluster:
    name: pg-production
  # 备份方法：barmanObjectStore 或 volumeSnapshot
  method: barmanObjectStore
  # barmanObjectStore 配置继承自 Cluster CRD

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: pg-daily-backup
spec:
  # Cron 表达式
  schedule: "0 2 * * *"
  # 是否立即执行一次
  immediate: true
  # 挂起调度
  suspend: false
  cluster:
    name: pg-production
  # 备份保留策略（覆盖 Cluster 级别设置）
  backupOwnerReference: self

# S3 兼容存储
s3Credentials:
  accessKeyId:
    name: s3-credentials
    key: access_key_id
  secretAccessKey:
    name: s3-credentials
    key: secret_access_key
  region:
    name: s3-credentials
    key: region
# Google Cloud Storage
googleCredentials:
  applicationCredentials:
    name: gcs-credentials
    key: credentials.json
# Azure Blob Storage
azureCredentials:
  connectionString:
    name: azure-credentials
    key: connection_string
  storageAccount:
    name: azure-credentials
    key: storage_account

时间线：T+0s ~ T+50s

Operator (Controller)              Instance Manager (Primary Pod)     Standby Pods
       │                                       │                          │
  T+0  │  Liveness Probe 探测 Primary           │                          │
       │───────────────────────────────────────►│                          │
       │◄─────────── 无响应 ────────────────────│                          │
       │                                       │                          │
  T+5  │  确认 Primary 不健康                    │                          │
       │                                       │                          │
  T+10 │  查询 Standby 复制状态                  │                          │
       │────────────────────────────────────────────────────────────────►│
       │◄─── LSN 位置 + 复制延迟 ─────────────────────────────────────────│
       │                                       │                          │
  T+15 │  选择 LSN 最超前的 Standby              │                          │
       │                                       │                          │
  T+20 │  发起 pg_ctl promote ───────────────────────────────────────────►│
       │                                       │              Promote     │
  T+25 │  更新 Pod Label                         │                          │
       │  role: primary → standby                │                          │
       │  role: standby → primary                │              role:      │
       │                                       │              primary     │
  T+30 │  更新 Service Endpoint                  │                          │
       │  -rw → 新 Primary                       │                          │
       │                                       │                          │
  T+35 │  其他 Standby 重新连接新 Primary          │                          │
       │────────────────────────────────────────────────────────────────►│
       │                                       │◄── streaming repl ────────│
  T+40 │  旧 Primary 重启为 Standby               │                          │
       │                                       │                          │
  T+50 │  集群恢复完整状态                         │                          │

spec:
  postgresql:
    parameters:
      # Liveness: 检查 PG 进程是否存活
      # Readiness: 检查 PG 是否接受连接
      # 生产环境建议在 init 阶段放宽探针
      pg_ctl_timeout: "3600"

graph TD
    subgraph K8s["Kubernetes Service"]
        RW["pg-cluster-rw<br/>(读写 Service)<br/>--> Primary Pod"]
        RO["pg-cluster-ro<br/>(只读 Service)<br/>--> Standby Pods"]
    end
    
    RW -->|"路由"| Primary["Primary<br/>(Read/Write)"]
    RO -->|"路由"| Standby["Standby-1 / Standby-2<br/>(Read-Only)"]
    
    Primary -->|"Streaming Replication"| Standby

# 从 3 实例扩展至 5 实例
kubectl patch cluster pg-production \
  --type merge -p '{"spec":{"instances":5}}'

# 查看实例状态
kubectl get cluster pg-production -o jsonpath='{.status.instancesStatus}'

# Cluster CRD 扩缩容配置
spec:
  instances: 5  # 从 3 修改为 5

  # 控制 Rolling Update 期间不可用实例上限
  maxParallelWalSender: 4

# 应用配置示例
spring:
  datasource:
    write:
      url: jdbc:postgresql://pg-production-rw:5432/appdb
    read:
      url: jdbc:postgresql://pg-production-ro:5432/appdb

graph LR
    subgraph S1["Step 1: 升级 Standby(C)"]
        PA1["Primary(A)<br/>v16.2"]
        SB1["Standby(B)<br/>v16.2"]
        SC1["Standby(C)<br/>v16.4 (升级)"]
    end
    
    subgraph S2["Step 2: 升级 Standby(B)"]
        PA2["Primary(A)<br/>v16.2"]
        SB2["Standby(B)<br/>v16.4 (升级)"]
        SC2["Standby(C)<br/>v16.4"]
    end
    
    subgraph S3["Step 3: Switchover"]
        SA3["Standby(A)<br/>v16.2 (降级)"]
        PB3["Primary(B)<br/>v16.4 (提升)"]
        SC3["Standby(C)<br/>v16.4"]
    end
    
    subgraph S4["Step 4: 升级旧 Primary(A)"]
        SA4["Standby(A)<br/>v16.4 (升级)"]
        PB4["Primary(B)<br/>v16.4"]
        SC4["Standby(C)<br/>v16.4"]
    end
    
    S1 --> S2 --> S3 --> S4

# 方式一：修改 imageName 触发 Rolling Update
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:16.4  # 从 16.2 升级

# 方式二：更新 PostgreSQL 参数
spec:
  postgresql:
    parameters:
      shared_buffers: "8GB"  # 参数变更重启时滚动更新

# 方式三：更新 Operator 版本支持新特性

graph LR
    subgraph Primary["Primary Pod"]
        PG1["PostgreSQL Primary"]
        Writer["WAL Writer"]
        Sender1["WAL Sender"]
        Sender2["WAL Sender"]
        PVC1["~~~ WAL PVC ~~~"]
    end
    
    subgraph Standby["Standby Pod"]
        PG2["PostgreSQL Standby"]
        Receiver["WAL Receiver"]
        Applier["WAL Apply"]
        Startup["Startup Process"]
        PVC2["~~~ WAL PVC ~~~"]
    end
    
    Writer -->|"Write"| Sender1
    Writer -->|"Write"| Sender2
    Sender1 -->|"TCP 5432"| Receiver
    Sender2 -->|"TCP 5432"| Applier
    Receiver --> Applier
    Applier --> Startup

spec:
  instances: 3
  postgresql:
    parameters:
      # 同步复制：至少 1 个 Standby 确认 WAL 写入
      synchronous_standby_names: "ANY 1 (*)"
    # 同步模式
    synchronous:
      # 同步实例数量
      numberOfInstances: 1
      # 是否启用同步复制
      enabled: true
      # 当同步 Standby 不可用时，降低同步约束
      # 防止写入阻塞导致整个集群不可用

-- 查询复制延迟（字节）
SELECT
  application_name,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
  pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS total_lag_bytes
FROM pg_stat_replication;

graph TD
    subgraph Pod["PostgreSQL Pod"]
        PGDATA["PGDATA<br/>/var/lib/postgresql/data"]
        PGWAL["PG_WAL<br/>/var/lib/postgresql/wal"]
    end
    
    PGDATA -->|"目录"| Base["base/ (数据文件)"]
    PGDATA -->|"符号链接"| WalLink["pg_wal/"]
    PGDATA -->|"目录"| Stat["pg_stat/ (统计信息)"]
    
    PGDATA --> PVC1["PVC: pgdata-cluster-n<br/>StorageClass: premium-rwo"]
    
    PGWAL -->|"目录"| Archive["archive_status/"]
    PGWAL -->|"文件"| WALFiles["*.wal"]
    
    PGWAL --> PVC2["PVC: wal-cluster-n<br/>StorageClass: premium-rwo"]

spec:
  storage:
    size: 200Gi  # 从 100Gi 扩容至 200Gi
    storageClass: premium-rwo
    # 扩容策略
    resizeInUseVolumes: true  # 允许在线扩容（需 CSI 驱动支持）

  walStorage:
    size: 50Gi   # WAL 独立扩容
    storageClass: premium-rwo

1. 修改 Cluster CRD storage.size
2. Operator 更新 StatefulSet volumeClaimTemplate
3. 对于已存在的 PVC：
   - Controller Expand：CSI 驱动增加卷容量
   - Node Expand：kubelet 调整文件系统大小
   - Operator 发送 SIGHUP 给 PG 进程（如果配置了 online resize）
4. 对于新 Pod：直接创建新容量 PVC

# 从快照恢复集群
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-from-snapshot
spec:
  storage:
    size: 100Gi
  bootstrap:
    initdb:
      import:
        type: snapshot
        snapshot:
          name: pg-data-snapshot-20250801

spec:
  storage:
    size: 100Gi
  # 当存储使用率达到阈值告警
  # Operator 通过 Prometheus 指标暴露存储状态

graph TD
    subgraph Operator["StackGres Operator"]
        SGCluster["SGCluster Controller"]
        SGDistributed["SGDistributed Logs Ctl"]
        SGScript["SGScript Controller"]
    end
    
    Operator -->|"管理"| ClusterPod["StackGres Cluster Pod"]
    
    subgraph ClusterPod["StackGres Cluster Pod"]
        subgraph Patroni["Container: patroni"]
            PGCore["PostgreSQL Engine (custom build)"]
            PatroniHA["Patroni HA Manager"]
            Extensions["内建扩展 (150+ extensions)"]
        end
        
        subgraph Envoy["Container: envoy (Sidecar)"]
            Pool["连接池"]
            Traffic["流量管理"]
        end
        
        subgraph Util["Container: postgres-util"]
            Backup["pgBackRest / WAL-G 备份"]
            Exporter["postgres_exporter"]
            Fluent["fluent-bit (日志采集)"]
        end
        
        PVC["~~~ PVC ~~~"]
    end

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: pg-production
spec:
  instances: 3
  postgres:
    version: "16.2"
    flavor: "babelfish"  # 可选：babelfish / postgresql
  pods:
    persistentVolume:
      size: 100Gi
      storageClass: premium-rwo
  sgInstanceProfile: profile-production
  configurations:
    sgPostgresConfig: pgconfig-oltp
    sgPoolingConfig: poolconfig-default
    sgBackupConfig: backupconfig-daily
  distributedLogs:
    retention: 7d
    maxQueueSize: 100MB
  prometheusAutobind: true

graph LR
    subgraph App["Application Pod"]
        App2["Application<br/>jdbc:postgresql://cluster:5432"]
    end
    
    subgraph SGPod["StackGres Cluster Pod"]
        subgraph Envoy2["Container: envoy"]
            Filter["PostgreSQL Filter<br/>- 连接池<br/>- 查询路由<br/>- TLS 终止"]
        end
        
        subgraph Patroni2["Container: patroni"]
            PG2["PostgreSQL<br/>(localhost:5432)"]
        end
        
        Filter -->|"转发"| PG2
    end
    
    App2 -->|"TCP 5432"| Filter

apiVersion: stackgres.io/v1
kind: SGPoolingConfig
metadata:
  name: poolconfig-production
spec:
  pgBouncerPgbouncerIni:
    pgbouncer:
      pool_mode: transaction
      max_client_conn: 1000
      default_pool_size: 50
      reserve_pool_size: 10
      reserve_pool_timeout: 5.0
      max_db_connections: 200
      max_user_connections: 50
      server_idle_timeout: 600
      query_wait_timeout: 60
      client_idle_timeout: 0
      stats_period: 60

graph TD
    subgraph Image["StackGres Custom PostgreSQL Image"]
        subgraph Engine["PostgreSQL Engine"]
            Babelfish["Babelfish (SQL Server)"]
            Ext150["150+ Extensions (预装)"]
            Plugins["TimescaleDB / PostGIS / etc"]
        end
        
        subgraph Patroni3["Patroni"]
            LeaderElection["Leader 选举"]
            AutoFailover["自动 Failover"]
            DCS["DCS 集成"]
            RestAPI["REST API"]
        end
        
        subgraph PgBouncer["PgBouncer"]
            Pool2["连接池"]
            QueryRoute["查询路由"]
            RateLimit["限流"]
        end
    end

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: pg-babelfish
spec:
  postgres:
    version: "16.2"
    flavor: "babelfish"  # 启用 Babelfish
  instances: 3

graph TD
    subgraph Patroni4["Patroni"]
        DCS2["DCS: Kubernetes Endpoints/ConfigMap"]
        LeaderLock["Leader Lock: ConfigMap"]
        MemberRegistry["Member Registry: Endpoints"]
        Config2["Configuration: ConfigMap"]
        
        RestAPI2["REST API: http://localhost:8008"]
        GetLeader["GET /leader"]
        GetReplica["GET /replica"]
        PostSwitchover["POST /switchover"]
        PostRestart["POST /restart"]
    end
    
    DCS2 --> LeaderLock
    DCS2 --> MemberRegistry
    DCS2 --> Config2
    
    RestAPI2 --> GetLeader
    RestAPI2 --> GetReplica
    RestAPI2 --> PostSwitchover
    RestAPI2 --> PostRestart

apiVersion: stackgres.io/v1
kind: SGInstanceProfile
metadata:
  name: profile-production
spec:
  cpu: "8"
  memory: 32Gi

# 修改 SGInstanceProfile 后，Operator 按 Rolling Update 方式
# 逐个 Pod 重建，使用新资源配置
kubectl patch sginstanceprofile profile-production \
  --type merge -p '{"spec":{"cpu":"16","memory":"64Gi"}}'

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: pg-production
spec:
  instances: 5  # 从 3 增至 5

apiVersion: stackgres.io/v1
kind: SGDbOps
metadata:
  name: restart-instance-2
spec:
  op: restart
  sgCluster: pg-production
  maxRetries: 1
  timeout: 3600
  # 仅操作指定 Pod
  pods:
    - pg-production-2

spec:
  sgInstanceProfile: profile-production
  pods:
    scheduling:
      nodeSelector:
        node-type: database
      tolerations:
        - key: "database"
          operator: "Equal"
          value: "true"
          effect: "NoSchedule"
      nodeAffinity:
        preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 100
            preference:
              matchExpressions:
                - key: topology.kubernetes.io/zone
                  operator: In
                  values:
                    - zone-a
                    - zone-b

graph TD
    K8sAPI["Kubernetes API Server"]
    
    K8sAPI -->|"Watch postgresql CRD"| Operator["Zalando Postgres Operator"]
    
    subgraph Operator["Zalando Postgres Operator"]
        Sync["Sync Controller"]
        Connection["Connection Pooler"]
        Rolling["Rolling Update Manager"]
    end
    
    Operator -->|"创建 StatefulSet / Service / Secret"| SpiloPod["Spilo Pod"]
    
    subgraph SpiloPod["Spilo Pod (PostgreSQL)"]
        subgraph Patroni5["Patroni"]
            HA_Manager["HA Manager"]
            RestAPI3["REST API"]
            CallbackScript["Callback Script"]
        end
        
        subgraph PG["PostgreSQL"]
            PrimaryStandby["Primary / Standby"]
            Basebackup["pg_basebackup"]
            WALArch["WAL-E / WAL-G 归档"]
        end
        
        subgraph Etcd["etcd Cluster (独立部署或通过 Operator 集成)"]
            LeaderLock2["Leader 锁"]
            ConfigStore["配置存储"]
            MemberDiscovery["成员发现"]
        end
    end
    
    Patroni5 -->|"DCS Connection"| Etcd

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: pg-production
spec:
  teamId: "sre"
  numberOfInstances: 3
  postgresql:
    version: "16"
    parameters:
      shared_buffers: "4GB"
      max_connections: "500"
  volume:
    size: 100Gi
    storageClass: premium-rwo
  patroni:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
  resources:
    requests:
      cpu: 4
      memory: 8Gi
    limits:
      cpu: 8
      memory: 16Gi

graph TD
    subgraph Spilo["Spilo Docker Image"]
        subgraph AppLayer["Application Layer"]
            Patroni6["Patroni (HA Manager)"]
            WALG2["WAL-G (Backup & WAL Archive)"]
            PgBackRest["pgBackRest (可选)"]
            PgExporter["postgres_exporter"]
            PgBouncer2["PgBouncer (可选)"]
            Configure["configure_spilo.py (启动脚本)"]
        end
        
        subgraph EngineLayer["PostgreSQL Engine"]
            PG16["PostgreSQL 12/13/14/15/16/17"]
            Contrib["贡献扩展 (Contrib Extensions)"]
            PL["PL/Python / PL/Perl / PL/Tcl"]
            Plugins2["TimescaleDB / PostGIS / pg_cron"]
        end
        
        subgraph BaseLayer["Base OS Layer"]
            OS["Ubuntu / Debian"]
            Python["Python 3.x"]
        end
    end
    
    AppLayer --> EngineLayer --> BaseLayer

configure_spilo.py (启动入口)
       │
       ├── 1. 检测角色（Primary / Standby）
       │      ├── 已有数据目录 → Standby
       │      └── 无数据目录 → 检查 DCS 是否有 Leader
       │             ├── 有 Leader → pg_basebackup 拉取数据
       │             └── 无 Leader → initdb 初始化
       │
       ├── 2. 配置 Patroni
       │      ├── 注入 postgresql 参数
       │      ├── 设定 DCS 连接（etcd / Kubernetes / ZooKeeper）
       │      └── 设定 REST API 监听
       │
       ├── 3. 启动 Patroni
       │      └── Patroni 管理 PostgreSQL 进程生命周期
       │
       └── 4. Patroni 守护循环
              ├── 每 loop_wait 秒检查 PostgreSQL 健康
              ├── 通过 DCS 维护 Leader 锁
              └── Leader 故障时触发 Failover

graph TD
    subgraph Etcd2["etcd Cluster"]
        Root["/service/scope/"]
        
        Root --> Leader2["leader - Leader 锁（TTL 续约）"]
        Root --> Members["members/ - 成员注册与状态"]
        Members --> Member0["member-0"]
        Members --> Member1["member-1"]
        
        Root --> Config3["config/ - 集群配置"]
        Config3 --> PGParams["postgresql.parameters"]
        Config3 --> LoopWait["patroni.loop_wait"]
        
        Root --> Init["initialize - 初始化标记"]
        Root --> History["history - 切换历史"]
        Root --> Optime["optime/ - 最近 Leader 墙钟时间"]
        Optime --> LeaderTime["leader"]
    end

时间线：Patroni 守护循环

T+0s   Standby: GET /service/scope/leader → 存在（Primary-0）
T+1s   Primary-0: PUT /service/scope/leader (renew TTL=30s)

--- Primary-0 故障 ---

T+30s  租约过期，Leader Key 自动删除
T+31s  Standby-1: GET /service/scope/leader → 不存在
T+32s  Standby-1: PUT /service/scope/leader (CAS, value=Standby-1)
        → 写入成功 → Standby-1 提升为 Primary
T+33s  Standby-2: PUT /service/scope/leader (CAS)
        → 写入失败（已被 Standby-1 持有）
T+34s  Standby-1: pg_ctl promote → 完成提升

graph TD
    Start["pg_backup_start() 标记开始"]
    
    Start --> FullBackup["全量备份<br/>- pg_basebackup<br/>- 或 tar 打包"]
    FullBackup --> Push["WAL-G push<br/>上传至对象存储"]
    
    Start --> Stop["pg_backup_stop() 标记结束"]
    Stop --> WalPush["WAL-G wal-push<br/>增量 WAL 上传"]
    
    Push --> Stop
    
    NoteRight["Note: archive_command 持续推送 WAL<br/>→ WalPush"]

# 全量备份 + WAL 归档
spec:
  backup:
    barmanObjectStore:
      destinationPath: s3://pg-backups/production
      endpointURL: https://s3.cn-north-1.amazonaws.com.cn
      s3Credentials:
        accessKeyId:
          name: s3-credentials
          key: access_key_id
        secretAccessKey:
          name: s3-credentials
          key: secret_access_key
      wal:
        compression: gzip
        encryption: AES256
      data:
        compression: gzip
        encryption: AES256
        # 立即执行一次 Checkpoint 保证一致性
        immediateCheckpoint: false
      retentionPolicy: "30d"

graph TD
    L1["异地灾备<br/>跨区域备份<br/>S3/GCS 跨区域复制<br/>每周 1 次 / 保留 3 个月"]
    L2["快照<br/>Volume Snapshot<br/>每天 1 次 / 保留 7 天"]
    L3["全量 + WAL<br/>barmanObjectStore / WAL-G<br/>PITR 恢复<br/>每天全量 + 持续 WAL / 保留 30 天"]
    
    L1 --> L2 --> L3

Primary 上的 WAL 保留行为：

有活跃 Replication Slot
  WAL 保留到所有 Slot 的 restart_lsn 之后
  → 风险：Standby 长时间宕机导致 Primary WAL 堆积磁盘满

无 Replication Slot
  WAL 保留受 wal_keep_size 限制
  → 风险：Standby 延迟超过 wal_keep_size 后无法追上，需重新 pg_basebackup

spec:
  replicationSlots:
    # 启用高可用复制槽（每个 Standby 自动创建）
    highAvailability:
      enabled: true
      # Slot 前缀
      slotPrefix: _cnpg_
    # 当 Standby 被删除时自动清理对应 Slot
    synchronizeReplicas: true
    # 更新间隔
    updateInterval: 30

-- 监控复制槽 WAL 延迟
SELECT
  slot_name,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retention_bytes,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retention_pretty
FROM pg_replication_slots
WHERE NOT temporary;

1. 旧 Primary 故障
2. 新 Primary 提升（Standby 上的 Slot 变为可用）
3. 旧 Primary 恢复为 Standby
4. 新 Primary 创建与新 Standby 对应的 Slot
5. 旧 Primary 上的旧 Slot 通过 synchronizeReplicas: true 自动清理

graph TD
    Update["UPDATE 操作"]
    
    Update --> OldTuple["旧版本元组 (Dead Tuple)<br/>标记为无效"]
    Update --> NewTuple["新版本元组 (Live Tuple)<br/>新插入"]
    
    OldTuple --> Vacuum["VACUUM: 回收 Dead Tuple 空间"]
    NewTuple --> Vacuum
    
    Vacuum --> FSM["更新 FSM (空闲空间映射)"]
    Vacuum --> VM["更新 VM (可见性映射)"]
    Vacuum --> Freeze["冻结事务 ID (防止回卷)"]

# postgresql.conf / Cluster CRD parameters
autovacuum = on

# 触发阈值
autovacuum_vacuum_threshold = 50               # 最小 Dead Tuple 数
autovacuum_vacuum_scale_factor = 0.1           # 表大小的 10% Dead Tuple
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

# 成本限制（I/O 节流）
autovacuum_vacuum_cost_delay = 2ms             # 每次 Cost 到达后暂停
autovacuum_vacuum_cost_limit = 200             # 每次暂停前的 Cost 上限

# Worker 并发
autovacuum_max_workers = 5                     # 最大同时运行的 Worker 数

# 大表优化
autovacuum_vacuum_insert_threshold = 1000      # Insert-Only 表触发阈值
autovacuum_vacuum_insert_scale_factor = 0.1    # Insert-Only 表触发比例

-- 查看表膨胀比例
SELECT
  schemaname || '.' || relname AS table_name,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

graph TD
    subgraph Apps["Application Pods (100+ instances)"]
        App1["App-1"]
        App2["App-2"]
        App3["App-N"]
    end
    
    Apps -->|"大量短连接"| PgBouncer3["PgBouncer Pod<br/>(Connection Pool)<br/>Sidecar 或独立 Pod<br/><br/>Client Pool: 1000<br/>Server Pool: 50"]
    
    PgBouncer3 -->|"少量长连接"| PG3["PostgreSQL Pod<br/>max_connections: 200"]

[databases]
* = host=localhost port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# 连接池模式
pool_mode = transaction

# 连接数控制
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 5.0
max_db_connections = 0
max_user_connections = 0

# 超时与生命周期
server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15
client_idle_timeout = 0
query_wait_timeout = 60
query_timeout = 0
idle_transaction_timeout = 0

# 管理
admin_users = admin
stats_users = stats
stats_period = 60

graph TD
    subgraph Stack["Prometheus Stack"]
        Prometheus["Prometheus<br/>(Scrape + TSDB)"]
        Grafana["Grafana<br/>(Dashboard + Alert)"]
        Prometheus --> Grafana
    end
    
    Prometheus -->|"Scrape /metrics"| Exporter2["postgres_exporter<br/>:9187/metrics<br/>Sidecar 容器"]
    
    Exporter2 -->|"SQL Query"| PG4["PostgreSQL"]
    
    subgraph PG4["PostgreSQL"]
        PgStatStatements["pg_stat_statements"]
        PgStatActivity["pg_stat_activity"]
        PgStatReplication["pg_stat_replication"]
    end

spec:
  monitoring:
    enablePodMonitor: true
    # 自定义 PodMonitor 标签
    podMonitor:
      labels:
        app: prometheus
      additionalLabels:
        team: sre

spec:
  prometheusAutobind: true

spec:
  postgresql:
    parameters:
      shared_preload_libraries: "pg_stat_statements"
      pg_stat_statements.track: "all"
      pg_stat_statements.max: "10000"
      pg_stat_statements.track_utility: "off"

# 连接数告警
- alert: HighConnections
  expr: |
    pg_stat_database_numbackends{datname="appdb"}
    / on(instance) group_left
    pg_settings_max_connections * 100 > 80    
  for: 5m

# 复制延迟告警
- alert: ReplicationLag
  expr: pg_stat_replication_replay_lag_bytes > 104857600  # 100MB
  for: 2m

# 长事务告警
- alert: LongRunningTransaction
  expr: pg_stat_activity_max_tx_duration_seconds > 300
  for: 1m

graph TD
    Query["查询执行"]
    
    Query --> PgAudit["pgAudit Extension"]
    
    subgraph PgAudit["pgAudit Extension"]
        EventFilter["Event Class Filter<br/>READ/WRITE/ROLE/DDL"]
        ObjectFilter["Object Filter<br/>table/role/schema..."]
        AuditWriter["Audit Log Writer<br/>(csvlog / syslog)"]
        
        EventFilter --> ObjectFilter --> AuditWriter
    end
    
    AuditWriter --> LogOutput["log_statement / log_destination"]

spec:
  postgresql:
    parameters:
      # 预加载 pgAudit
      shared_preload_libraries: "pg_stat_statements, pgaudit"

      # 审计配置
      pgaudit.log: "write, ddl, role"
      pgaudit.log_catalog: "off"
      pgaudit.log_level: "log"
      pgaudit.log_parameter: "on"
      pgaudit.log_relation: "on"
      pgaudit.log_rows: "off"
      pgaudit.log_statement_once: "off"

      # 角色级审计
      pgaudit.role: "auditor"

      # 日志输出
      log_destination: "csvlog"
      logging_collector: "on"
      log_directory: "pg_log"
      log_filename: "postgresql-%a.log"
      log_rotation_age: "1d"
      log_rotation_size: "0"

graph TD
    PA["pgAudit"]
    CSV["csvlog → /var/lib/postgresql/data/pg_log/"]
    PVC["PVC (PGDATA)"]
    Sidecar["Sidecar: fluent-bit / filebeat"]
    Tail["tail pg_log/*.csv"]
    Parse["解析 CSV → JSON"]
    Backend["Elasticsearch / Loki / Kafka"]
    PA --> CSV --> PVC
    CSV --> Sidecar --> Tail --> Parse --> Backend

# Promtail DaemonSet 或 Sidecar 采集 PostgreSQL 日志
scrape_configs:
  - job_name: postgresql-audit
    kubernetes_sd_configs:
      - role: pod
    relabel_configs:
      - source_labels: [__meta_kubernetes_pod_label_cnpg_io_cluster]
        action: keep
        regex: pg-production
    pipeline_stages:
      - regex:
          expression: '^(?P<audit_type>AUDIT):.*STATEMENT:(?P<statement>.*)'
      - labels:
          audit_type:

graph LR
    subgraph Publisher[Publisher 源端]
        A[WAL Decoder<br/>pgoutput]
        B[Publication<br/>- orders<br/>- payments]
        A --> B
        Slot1[Logical Replication Slot<br/>orders_slot] -.-> A
    end
    subgraph Subscriber[Subscriber 目标端]
        C[Logical Replay Worker]
        D[Subscription<br/>- orders_sub<br/>CONNECTION ...]
        C --> D
        Slot2[Logical Replication Slot<br/>目标端内部] -.-> C
    end
    A -->|TCP 5432| C

-- 源端：创建发布
CREATE PUBLICATION orders_pub FOR TABLE orders, payments
  WITH (publish = 'insert,update,delete');

-- 目标端：创建订阅
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=pg-source-rw dbname=appdb user=replicator password=xxx'
  PUBLICATION orders_pub
  WITH (
    copy_data = true,
    create_slot = true,
    enabled = true,
    synchronous_commit = 'off'
  );

# 源集群（Publisher）
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-source
spec:
  instances: 3
  postgresql:
    parameters:
      wal_level: "logical"
      max_replication_slots: "10"
      max_wal_senders: "10"

# 目标集群（Subscriber）
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-target
spec:
  instances: 3
  postgresql:
    parameters:
      wal_level: "logical"
      max_replication_slots: "10"
      max_logical_replication_workers: "8"
      max_worker_processes: "16"

graph TD
    subgraph L0[Level 0: Schema 隔离]
        PG0[PostgreSQL Instance]
        PG0 --> TA[tenant_a]
        PG0 --> TB[tenant_b]
    end
    subgraph L1[Level 1: Database 隔离]
        PG1[PostgreSQL Instance]
        PG1 --> DA[db_tenant_a]
        PG1 --> DB[db_tenant_b]
    end
    subgraph L2[Level 2: Cluster 隔离]
        CA[Cluster A<br/>namespace-A]
        CB[Cluster B<br/>namespace-B]
    end
    subgraph L3[Level 3: Operator / Node 隔离]
        OA[Operator-A<br/>NodeGroup-A]
        OB[Operator-B<br/>NodeGroup-B]
    end
    L0 --> L1 --> L2 --> L3

# 生产环境：每租户一个 Cluster（Namespace 隔离）
apiVersion: v1
kind: Namespace
metadata:
  name: tenant-acme-db
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-acme
  namespace: tenant-acme-db
spec:
  instances: 3
  storage:
    size: 50Gi
    storageClass: premium-rwo
  postgresql:
    parameters:
      shared_buffers: "2GB"
      max_connections: "200"
  resources:
    requests:
      cpu: "2"
      memory: 4Gi
    limits:
      cpu: "4"
      memory: 8Gi

# Namespace 级别 ResourceQuota
apiVersion: v1
kind: ResourceQuota
metadata:
  name: tenant-acme-quota
  namespace: tenant-acme-db
spec:
  hard:
    requests.cpu: "8"
    requests.memory: 32Gi
    requests.storage: 500Gi
    persistentvolumeclaims: "5"

graph TD
    subgraph Region[Region: cn-north-1]
        subgraph AZ_A[Availability Zone A]
            N1[Node-1]
            P[Primary<br/>同步复制]
            N1 --> P
        end
        subgraph AZ_B[Availability Zone B]
            N3[Node-3]
            S1[Standby-1<br/>同步确认]
            N3 --> S1
        end
        subgraph AZ_C[Availability Zone C]
            N5[Node-5]
            S2[Standby-2<br/>异步复制]
            N5 --> S2
            OS[S3/GCS 对象存储<br/>WAL Archive<br/>+ BaseBackup]
        end
        P -->|WAL| S1
        S1 -->|WAL| S2
        S2 -->|WAL| OS
    end

spec:
  instances: 3

  # 拓扑分布约束：Pod 强制分布到不同 Zone
  topologySpreadConstraints:
    - maxSkew: 1
      topologyKey: topology.kubernetes.io/zone
      whenUnsatisfiable: DoNotSchedule
      labelSelector:
        matchLabels:
          cnpg.io/cluster: pg-production

  # 节点亲和性：仅调度到数据库专用节点
  affinity:
    nodeAffinity:
      requiredDuringSchedulingIgnoredDuringExecution:
        nodeSelectorTerms:
          - matchExpressions:
              - key: node-role.kubernetes.io/database
                operator: Exists
    podAntiAffinity:
      requiredDuringSchedulingIgnoredDuringExecution:
        - labelSelector:
            matchLabels:
              cnpg.io/cluster: pg-production
          topologyKey: kubernetes.io/hostname

  # 同步复制：至少 1 个 Standby 确认
  postgresql:
    synchronous:
      numberOfInstances: 1

跨 AZ 同步复制延迟模型：

Primary (AZ-A) ──WAL──► Standby-1 (AZ-B, 同步)
  │                        │
  │  RTT: ~2ms             │  确认返回: 2ms
  │                        │
  │◄──── 同步确认 ──────────┘
  │
  │  事务提交延迟 = 本地写入 + 2ms RTT

Primary (AZ-A) ──WAL──► Standby-2 (AZ-C, 异步)
  │                        RTT: ~2ms
  │                        不等待确认

# Region-B 灾备集群，通过 WAL 归档恢复
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-dr-region-b
spec:
  instances: 3
  bootstrap:
    recovery:
      source: pg-production-wal-archive
  externalClusters:
    - name: pg-production-wal-archive
      barmanObjectStore:
        destinationPath: s3://pg-dr-backups/production
        endpointURL: https://s3.cn-south-1.amazonaws.com.cn
        s3Credentials:
          accessKeyId:
            name: s3-dr-credentials
            key: access_key_id
          secretAccessKey:
            name: s3-dr-credentials
            key: secret_access_key

┌─────────────────────┬──────────────┬──────────────┬─────────────┬──────────────┐
│        维度          │ CloudNativePG│  StackGres   │   Zalando   │  PGO (Crunchy)│
├─────────────────────┼──────────────┼──────────────┼─────────────┼──────────────┤
│ 维护方              │ EDB          │ OnGres       │ Zalando     │ Crunchy Data │
│ 许可证              │ Apache 2.0   │ AGPL v3      │ MIT         │ Apache 2.0   │
│ 首次发布            │ 2021         │ 2020         │ 2017        │ 2022 (v5)    │
├─────────────────────┼──────────────┼──────────────┼─────────────┼──────────────┤
│ 外部依赖            │ 无           │ 无           │ etcd        │ 无           │
│ HA 方案             │ 自研 Manager │ Patroni      │ Patroni     │ Patroni      │
│ DCS                 │ K8s API      │ K8s API      │ etcd        │ K8s API      │
│ 连接池              │ 需自行部署   │ Envoy Sidecar│ PgBouncer CR│ PgBouncer CR │
│ 备份方案            │ Barman Cloud │ pgBackRest   │ WAL-G       │ pgBackRest   │
│ 备份目标            │ S3/GCS/Azure │ S3/GCS/Azure │ S3/GCS      │ S3/GCS/Azure │
├─────────────────────┼──────────────┼──────────────┼─────────────┼──────────────┤
│ PITR                │ ✓            │ ✓            │ ✓           │ ✓            │
│ Volume Snapshot     │ ✓            │ ✓            │ ✗           │ ✓            │
│ 自动 Failover       │ ✓            │ ✓            │ ✓           │ ✓            │
│ 计划内 Switchover   │ ✓            │ ✓            │ ✓           │ ✓            │
│ Synchronous Repl    │ ✓            │ ✓            │ ✓           │ ✓            │
│ Read Replicas       │ ✓            │ ✓            │ ✓           │ ✓            │
│ WAL 独立 PVC        │ ✓            │ ✗            │ ✗           │ ✓            │
│ TLS 证书自动管理    │ ✓            │ ✓            │ ✗(手动)     │ ✓            │
│ PgBouncer 集成      │ ✗            │ ✓(Envoy)     │ ✓(CRD)      │ ✓(CRD)      │
│ Babelfish           │ ✗            │ ✓            │ ✗           │ ✗            │
│ 150+ Extensions     │ ✗            │ ✓            │ ✗           │ ✗            │
│ 大版本升级          │ pg_upgrade   │ pg_upgrade   │ pg_upgrade  │ pg_upgrade   │
├─────────────────────┼──────────────┼──────────────┼─────────────┼──────────────┤
│ Prometheus 集成     │ PodMonitor   │ PodMonitor   │ 手动        │ PodMonitor   │
│ Grafana Dashboard   │ 社区提供     │ 预置         │ 社区提供    │ 预置         │
│ pgAudit             │ ✓            │ ✓            │ ✓           │ ✓            │
├─────────────────────┼──────────────┼──────────────┼─────────────┼──────────────┤
│ 运维复杂度          │ 低           │ 中           │ 高          │ 低           │
│ 学习曲线            │ 平缓         │ 中等         │ 陡峭        │ 平缓         │
│ 社区活跃度          │ 非常高       │ 中等         │ 中等        │ 中等         │
│ K8s 原生程度        │ 极高         │ 高           │ 中          │ 高           │
│ PostgreSQL 版本支持  │ 12-17        │ 12-17        │ 12-17       │ 12-17        │
│ 扩展点支持          │ 中           │ 高(150+)     │ 中          │ 中           │
└─────────────────────┴──────────────┴──────────────┴─────────────┴──────────────┘

是否已有 etcd 基础设施？
  ├── 是 → 考虑 Zalando Operator
  └── 否 →
        是否需要 Babelfish / 150+ Extensions / 一体化平台？
          ├── 是 → 考虑 StackGres
          └── 否 →
                是否使用 Red Hat OpenShift？
                  ├── 是 → 考虑 Crunchy Data PGO
                  └── 否 → 考虑 CloudNativePG（K8s 原生，最小依赖）

spec:
  postgresql:
    parameters:
      # shared_buffers：数据库缓存，推荐物理内存的 25%
      # K8s 容器环境中，以 resource.limits.memory 为基准
      shared_buffers: "4GB"            # 假设 limit=16Gi → 25%

      # effective_cache_size：查询规划器估计的系统缓存
      effective_cache_size: "12GB"     # 75% of 16Gi

      # work_mem：单次排序/哈希操作内存
      work_mem: "64MB"                 # (total - shared_buffers) / (max_connections * 2)

      # maintenance_work_mem：VACUUM/CREATE INDEX 内存
      maintenance_work_mem: "1GB"      # limit 的 10%，OOM Kill 风险边界

      # WAL 配置
      max_wal_size: "4GB"             # 自动 Checkpoint 触发阈值
      min_wal_size: "1GB"             # 保留的最小 WAL 空间
      wal_buffers: "64MB"             # WAL 写入缓冲区

      # 检查点配置
      checkpoint_timeout: "15min"
      checkpoint_completion_target: "0.9"  # 摊平 I/O 峰值，90% 时间内完成

      # 随机页访问开销（容器环境使用 SSD 时降低）
      random_page_cost: "1.1"         # SSD 设为 1.0-1.5，HDD 保持 4.0
      effective_io_concurrency: "200"  # NVMe SSD

      # Autovacuum 调优（参见 Q20）
      autovacuum_max_workers: "5"
      autovacuum_vacuum_cost_limit: "2000"  # SSD 可适当提高

# Node 级别启用 Huge Pages（需要 kubelet 支持）
# /etc/default/kubelet: --feature-gates=HugePageStorageMediumSize=true

spec:
  postgresql:
    parameters:
      huge_pages: "on"

  resources:
    limits:
      hugepages-2Mi: "2Gi"  # shared_buffers 映射到 Huge Pages

spec:
  # 分离 WAL 存储，避免与数据文件的 I/O 竞争
  walStorage:
    size: 20Gi
    storageClass: premium-rwo  # 与 PGDATA 使用不同 StorageClass 更佳

  postgresql:
    parameters:
      wal_level: "replica"
      wal_compression: "on"         # WAL 页压缩，减少写入量
      wal_log_hints: "on"           # 支持 pg_rewind
      full_page_writes: "on"        # 保证 WAL 页完整性

      # Checkpoint I/O 节流
      max_wal_size: "4GB"
      min_wal_size: "2GB"
      checkpoint_timeout: "15min"
      checkpoint_completion_target: "0.9"

-- 验证 shared_buffers 命中率
SELECT
  (blks_hit * 100.0 / NULLIF(blks_hit + blks_read, 0)) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();
-- 目标：> 99%

-- 验证 Checkpoint 频率
SELECT
  checkpoints_timed,
  checkpoints_req,
  (checkpoints_req * 100.0 / NULLIF(checkpoints_timed + checkpoints_req, 0)) AS req_ratio
FROM pg_stat_bgwriter;
-- 目标：req_ratio < 10%（大部分为定时 Checkpoint 而非 WAL 触发）

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-v16
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:16.4

  bootstrap:
    pg_upgrade:
      # 引用原集群的 PVC，或备份恢复位置
      sourceCluster:
        name: pg-v15
      # pg_upgrade 模式
      import:
        type: snapshot  # 或 type: backup

1. 创建新版本的 Pod（init 容器执行 pg_upgrade --check）
2. pg_upgrade --check 验证兼容性（扩展、数据类型、编码）
3. 如果验证通过：
   a. 停止旧集群（或从快照创建克隆）
   b. 运行 pg_upgrade --link（硬链接模式，快速升级）
   c. 启动新版本 PostgreSQL
   d. VACUUM ANALYZE 更新统计信息
4. 如果验证失败：回滚到旧集群

graph TD
    P1[Phase 1: 搭建目标集群]
    SRC[源 15.x<br/>Production]
    TGT[目标 16.x<br/>New]
    P1 --> SRC
    P1 --> TGT

    P2[Phase 2: 全量数据同步<br/>CREATE SUBSCRIPTION<br/>copy_data = true]
    SRC -->|全量复制| TGT

    P3[Phase 3: 增量追赶<br/>持续同步至延迟接近 0]
    TGT -->|变更数据| P3
    SRC -->|变更数据| P3

    P4[Phase 4: 切换]
    P3 --> P4
    P4 --> S1[停止应用写入源集群]
    S1 --> S2[确认复制延迟为 0]
    S2 --> S3[切换应用连接到目标集群]
    S3 --> S4[DROP SUBSCRIPTION 目标端]
    S4 --> S5[清理源集群]

-- 升级后更新优化器统计信息
VACUUM ANALYZE;

-- 重建因升级可能失效的索引
REINDEX DATABASE appdb;

spec:
  instances: 3  # 生产最小 3 实例（1 Primary + 2 Standby）

  # 同步复制：至少 1 个 Standby 确认
  postgresql:
    synchronous:
      numberOfInstances: 1

  # Pod 反亲和：强制分布到不同节点
  affinity:
    podAntiAffinity:
      requiredDuringSchedulingIgnoredDuringExecution:
        - labelSelector:
            matchLabels:
              cnpg.io/cluster: pg-production
          topologyKey: kubernetes.io/hostname

  # 拓扑分布：强制分布到不同 AZ
  topologySpreadConstraints:
    - maxSkew: 1
      topologyKey: topology.kubernetes.io/zone
      whenUnsatisfiable: DoNotSchedule

spec:
  # 禁用 superuser 访问
  enableSuperuserAccess: false

  # TLS 证书自动管理
  certificates:
    serverTLSSecret: pg-tls-cert
    clientCASecret: pg-client-ca

  postgresql:
    parameters:
      # 强制加密连接
      ssl: "on"
      ssl_min_protocol_version: "TLSv1.3"

      # 密码加密
      password_encryption: "scram-sha-256"

      # 审计日志（参见 Q23）
      shared_preload_libraries: "pg_stat_statements, pgaudit"
      pgaudit.log: "write, ddl, role"

      # 连接安全
      log_connections: "on"
      log_disconnections: "on"

      # 事务安全
      idle_in_transaction_session_timeout: "300s"
      statement_timeout: "30s"     # 应用自定义，此为兜底
      lock_timeout: "10s"

集群实例数：
  生产：>= 3（至少覆盖 3 AZ）
  预发布：>= 2
  开发：1

存储容量：
  PVC Size = 预估 12 个月数据量 × 1.5（增长余量）

CPU：
  requests ≥ 实际平均使用量 × 1.2
  limits ≥ 实际峰值 × 1.3

内存：
  requests = limits（Guaranteed QoS）
  总量 = shared_buffers + (max_connections × work_mem) + maintenance_work_mem + OS reserve(512MB)