1️⃣ cluster.yaml

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgresql
  namespace: default
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:17.0

  # Nombre d'instances (1 primaire + 2 réplicas pour HA)
  instances: 3

  # Stockage persistant
  storage:
    size: 20Gi
    storageClass: nfs-client  # adapte selon ton environnement

  # Initialisation du cluster PostgreSQL
  bootstrap:
    initdb:
      database: appdb
      owner: appuser
      secret:
        name: postgresql-appuser-secret

  # Configuration PostgreSQL
  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: "256MB"
      work_mem: "16MB"
      maintenance_work_mem: "64MB"
      wal_level: "replica"
      archive_mode: "on"
      archive_timeout: "60s"

  # Stratégie de mise à jour
  primaryUpdateStrategy: unsupervised

  # Sauvegarde automatique vers S3/MinIO
  backup:
    barmanObjectStore:
      destinationPath: s3://postgresql-backup/
      s3Credentials:
        accessKeyId:
          name: s3-credentials
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: s3-credentials
          key: ACCESS_SECRET_KEY
      wal:
        compression: gzip
    retentionPolicy: "14d"

  # Activation du monitoring Prometheus
  monitoring:
    enablePodMonitor: true

  # Ressources
  resources:
    requests:
      cpu: "250m"
      memory: "512Mi"
    limits:
      cpu: "1"
      memory: "1Gi"

2️⃣ secret-appuser.yaml

---
apiVersion: v1
kind: Secret
metadata:
  name: postgresql-appuser-secret
  namespace: default
type: kubernetes.io/basic-auth
stringData:
  username: appuser
  password: motdepassefort

3️⃣ secret-s3.yaml

---
apiVersion: v1
kind: Secret
metadata:
  name: s3-credentials
  namespace: default
type: Opaque
stringData:
  ACCESS_KEY_ID: ton-access-key
  ACCESS_SECRET_KEY: ta-secret-key

4️⃣ backup.yaml

---
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: postgresql-manual-backup
  namespace: default
spec:
  cluster:
    name: postgresql

5️⃣ scheduled-backup.yaml

---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgresql-restore
  namespace: default
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:17.0
  instances: 3
  storage:
    size: 20Gi
    storageClass: nfs-client
  bootstrap:
    recovery:
      source: postgresql

6️⃣ restore-cluster.yaml

---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgresql-restore
  namespace: default
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:17.0
  instances: 3
  storage:
    size: 20Gi
    storageClass: nfs-client
  bootstrap:
    recovery:
      source: postgresql

2️⃣Provoquer un failover manuel*

Option 1 : Switchover planifié (manuel propre)

kubectl patch cluster postgresql -n default \
  --type merge \
  -p '{"spec":{"manualSwitchover":{"target":"postgresql-1","scheduledAt":"2025-10-28T14:00:00Z"}}}'

Option 2 : Failover forcé (rapide, test de panne)****

kubectl delete pod postgresql-0 -n default

Vérifie la connexion PostgreSQL :

kubectl exec -it postgresql-1 -n default -- psql -U appuser -d appdb -c "SELECT pg_is_in_recovery();"
kubectl describe cluster postgresql -n default | grep -A3 "Last Transition"
kubectl get events -n default --sort-by=.metadata.creationTimestamp | grep postgresql
kubectl patch cluster postgresql -n default \
--type merge  -p '{"spec":{"manualSwitchover":{"target":"postgresql-0","scheduledAt":"2025-10-28T14:10:00Z"}}}'
kubectl get pods -n default -l cnpg.io/cluster=postgresql -L role

Pour minio:*

kubectl patch cluster postgresql-lab -n test --type merge -p '{
  "spec": {
    "backup": {
      "barmanObjectStore": {
        "destinationPath": "s3://postgresnstest/",
        "endpointURL": "http://10.200.2.30:9000",
        "s3Credentials": {
          "accessKeyId": {"name": "minio-secret", "key": "MINIO_ACCESS_KEY"},
          "secretAccessKey": {"name": "minio-secret", "key": "MINIO_SECRET_KEY"}
        },
        "wal": {"compression": "gzip"}
      },
      "retentionPolicy": "1d"
    }
  }
}'

*** Test Postgres SQL

[root@prd-k8s-m01 lab_cluste_postgress]# kubectl exec -it svc/postgresql-lab-rw -n test -- bash

Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
postgres@postgresql-lab-1:/$ psql -U postgres
psql (17.0 (Debian 17.0-1.pgdg110+1))
Type "help" for help.

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
 appdb     | appuser  | UTF8     | libc            | C       | C     |        |           |
 postgres  | postgres | UTF8     | libc            | C       | C     |        |           |
 template0 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
(4 rows)

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
 appdb     | appuser  | UTF8     | libc            | C       | C     |        |           |
 postgres  | postgres | UTF8     | libc            | C       | C     |        |           |
 template0 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
(4 rows)

postgres=# \c labdb
connection to server on socket "/controller/run/.s.PGSQL.5432" failed: FATAL:  database "labdb" does not exist
Previous connection kept
postgres=# \c appdb
You are now connected to database "appdb" as user "postgres".
appdb=# CREATE TABLE sauvegarde_test (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    date_creation TIMESTAMP DEFAULT NOW()
);

INSERT INTO sauvegarde_test (nom) VALUES ('Sauvegarde test 1'), ('Sauvegarde test 2');

SELECT * FROM sauvegarde_test;
CREATE TABLE
INSERT 0 2
 id |        nom        |       date_creation
----+-------------------+----------------------------
  1 | Sauvegarde test 1 | 2025-10-29 15:26:47.567904
  2 | Sauvegarde test 2 | 2025-10-29 15:26:47.567904
(2 rows)

appdb=# INSERT INTO sauvegarde_test (nom) VALUES ('Sauvegarde test 4'), ('Sauvegarde test 3');
INSERT 0 2
appdb=# CREATE TABLE sauvegarde_test (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    date_creation TIMESTAMP DEFAULT NOW()
);

INSERT INTO sauvegarde_test (nom) VALUES ('Sauvegarde test 1'), ('Sauvegarde test 2');

SELECT * FROM sauvegarde_test;^C
appdb=# SELECT * FROM sauvegarde_test;
 id |        nom        |       date_creation
----+-------------------+----------------------------
  1 | Sauvegarde test 1 | 2025-10-29 15:26:47.567904
  2 | Sauvegarde test 2 | 2025-10-29 15:26:47.567904
  3 | Sauvegarde test 4 | 2025-10-29 15:27:06.816419
  4 | Sauvegarde test 3 | 2025-10-29 15:27:06.816419
(4 rows)

appdb=# ex