k8s cloudnative-pg 使用minio作为存储备份,恢复到任意时间

apiVersion: v1
kind: Secret
metadata:
  name: minio-creds
type: Opaque
data:
  ACCESS_KEY_ID: bWluaW9yb290cm9vdA==      # 默认: miniorootroot
  ACCESS_SECRET_KEY: bWluaW9yb290cm9vdA==  # 默认: miniorootroot
---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: minio-pv
spec:
  capacity:
    storage: 250Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /data/pabackup   # n04 节点的本地目录
    type: DirectoryOrCreate
  persistentVolumeReclaimPolicy: Retain
  nodeAffinity:         # 强制绑定 n04 节点
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - n04
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: minio-pvc
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 250Gi
  storageClassName: ""  # 关键：禁用自动存储类，手动绑定
  volumeName: minio-pv
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: minio
spec:
  replicas: 1
  selector:
    matchLabels:
      app: minio
  template:
    metadata:
      labels:
        app: minio
    spec:
      nodeName: n04
      containers:
      - name: minio
        image: minio/minio:RELEASE.2025-09-07T16-13-09Z
        args:
        - server
        - /data
        - --console-address
        - ":9001"
        env:
        - name: MINIO_ROOT_USER
          valueFrom:
            secretKeyRef:
              name: minio-creds
              key: ACCESS_KEY_ID
        - name: MINIO_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: minio-creds
              key: ACCESS_SECRET_KEY
        ports:
        - containerPort: 9000
          name: s3
        - containerPort: 9001
          name: console
        volumeMounts:
        - name: data
          mountPath: /data
      volumes:
      - name: data
        persistentVolumeClaim:
          claimName: minio-pvc
---
apiVersion: v1
kind: Service
metadata:
  name: minio
spec:
  ports:
  - port: 9000
    targetPort: 9000
    name: s3
  - port: 9001
    targetPort: 9001
    name: console
  selector:
    app: minio

kubectl exec -it minio-6d5b5cbf5d-l7dkq -- mc alias set local http://localhost:9000 miniorootroot miniorootroot
kubectl exec -it minio-6d5b5cbf5d-l7dkq -- mc mb local/cnpg-backups

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres
spec:
  instances: 2

  postgresql:
    parameters:
      shared_buffers: "256MB"
      max_connections: "20000"
      log_min_duration_statement: "5000"
  storage:
    size: 2000Gi
  imageName: ghcr.io/cloudnative-pg/postgresql:17.9
  imagePullPolicy: IfNotPresent
  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname
    podAntiAffinityType: required
    nodeSelector:
      node-role.kubernetes.io/postgres: run
    tolerations:
    - key: node-role.kubernetes.io/postgres
      operator: Equal
      value: run
      effect: NoSchedule
  backup:
    barmanObjectStore:
      destinationPath: "s3://cnpg-backups/"
      endpointURL: "http://minio:9000"
      s3Credentials:
        accessKeyId:
          name: minio-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: minio-creds
          key: ACCESS_SECRET_KEY
      wal:
        compression: gzip
        maxParallel: 2
      data:
        compression: gzip
    retentionPolicy: "7d"


kubectl apply -f postgresql-CloudNativePG.yaml

kubectl get cluster postgres
kubectl get pods -l cnpg.io/cluster=postgres

kind: Backup
metadata:
  name: postgres-backup-20260521
  namespace: default
spec:
  method: barmanObjectStore
  cluster:
    name: postgres

kubectl get backup

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: postgres-scheduled-backup
  namespace: default
spec:
  schedule: "0 0 2 */3 * *"     # 每3天凌晨2点（每月1,4,7,10,13,16,19,22,25,28,31号）
  backupOwnerReference: self
  immediate: true                # 创建后立即执行一次
  cluster:
    name: postgres
  method: barmanObjectStore

# recovery-cluster.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-new
spec:
  instances: 2

  storage:
    size: 200Gi
    storageClass: local-path
  postgresql:
    parameters:
      shared_buffers: "256MB"
      max_connections: "20000"
      log_min_duration_statement: "5000"
  imageName: ghcr.io/cloudnative-pg/postgresql:17.9
  imagePullPolicy: IfNotPresent
  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname
    podAntiAffinityType: required
    nodeSelector:
      node-role.kubernetes.io/postgres: run
    tolerations:
    - key: node-role.kubernetes.io/postgres
      operator: Equal
      value: run
      effect: NoSchedule
  bootstrap:
    recovery:
      source: postgres-source
      # ====== 指定恢复目标时间点 ======
      recoveryTarget:
        targetTime: "2026-05-20 10:30:00.000000+00"   # UTC时间，恢复到此时间点

  externalClusters:
  - name: postgres-source
    barmanObjectStore:
      destinationPath: "s3://cnpg-backups/"
      endpointURL: "http://minio:9000"
      s3Credentials:
        accessKeyId:
          name: minio-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: minio-creds
          key: ACCESS_SECRET_KEY
      wal:
        maxParallel: 2

# 查看集群状态
kubectl get cluster postgres-new -w

# 查看恢复日志
kubectl logs postgres-new-1 -c postgres -f

# 等待出现类似信息:
# "recovery has completed"
# "database system is ready to accept connections"

# 连接恢复后的集群验证数据
kubectl exec -it pg-cluster-recovery-1 -- psql -U postgres -c "SELECT now();"
kubectl exec -it pg-cluster-recovery-1 -- psql -U postgres -c "SELECT count(*) FROM your_table;"

recoveryTarget:
  # 方式1: 恢复到指定时间
  targetTime: "2026-05-20 10:30:00.000000+00"

  # 方式2: 恢复到指定事务ID
  # targetXID: "1234567"

  # 方式3: 恢复到指定LSN
  # targetLSN: "0/1000000"

  # 方式4: 恢复到指定命名还原点
  # targetName: "my_savepoint"