Guide de déploiement PostgreSQL Master/Slave sur OpenShift

┌─────────────────────────┐
│  Service Master (RW)    │ ← Applications (lecture/écriture)
│  postgresql-master      │
└────────────┬────────────┘
             │
    ┌────────▼────────┐
    │  Pod Master     │
    │  postgresql-0   │
    └────────┬────────┘
             │ Streaming Replication
             │
    ┌────────▼────────┐
    │  Pod Slave      │
    │  postgresql-1   │
    └────────┬────────┘
             │
┌────────────▼────────────┐
│  Service Slave (RO)     │ ← Applications (lecture seule)
│  postgresql-slave       │
└─────────────────────────┘

oc project <nom-du-projet>
oc get all | grep postgres
oc get pvc | grep postgres
oc get configmap | grep postgres
oc get secret | grep postgres

POD_NAME=$(oc get pods -l name=postgresql -o jsonpath='{.items[0].metadata.name}')
oc exec $POD_NAME -- psql --version

# Sauvegarder les ressources OpenShift
oc get dc/postgresql -o yaml > backup-postgresql-dc.yaml
oc get svc/postgresql -o yaml > backup-postgresql-svc.yaml
oc get pvc/postgresql -o yaml > backup-postgresql-pvc.yaml
oc get configmap -o yaml > backup-configmaps.yaml
oc get secret -o yaml > backup-secrets.yaml

# Backup de la base de données
oc exec $POD_NAME -- pg_dumpall -U postgres > backup-database.sql

apiVersion: v1
kind: Secret
metadata:
  name: postgresql-replication
type: Opaque
stringData:
  replication-user: replicator
  replication-password: <GÉNÉRER_MOT_DE_PASSE_FORT>

oc apply -f postgresql-replication-secret.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-master-config
data:
  master.conf: |
    # Configuration Master PostgreSQL
    listen_addresses = '*'
    max_connections = 100
    shared_buffers = 256MB

    # ===== Configuration WAL pour Réplication =====
    wal_level =                     
                         
                   
                          
                             

    
      
      

    
      
      
      
      
      
      
      
      

   
    
                                             

    
                              
                               

oc apply -f postgresql-master-config.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-master-init
data:
  init-master.sh: |
    #!/bin/bash
    set -e

    echo "Initialisation du master pour la réplication..."

    # Vérifier si déjà initialisé
    if psql -U postgres -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='replicator'" | grep -q 1; then
        echo "Utilisateur de réplication déjà existant"
    else
        # Créer l'utilisateur de réplication
           
                   
             
        
         
    

oc apply -f postgresql-master-init.yaml

oc get dc/postgresql -o yaml > postgresql-master-updated.yaml

spec:
  template:
    spec:
      volumes:
        # ... volumes existants ...
        - name: master-config
          configMap:
            name: postgresql-master-config
        - name: master-init
          configMap:
            name: postgresql-master-init
            defaultMode: 0755
        - name: archive
          emptyDir: {}

containers:
  - name: postgresql
    volumeMounts:
      # ... volumeMounts existants ...
      - name: master-config
        mountPath: /etc/postgresql/master.conf
        subPath: master.conf
      - name: master-config
        mountPath: /etc/postgresql/pg_hba.conf
        subPath: pg_hba.conf
      - name: master-init
        mountPath: /docker-entrypoint-initdb.d/init-master.sh
        subPath: init-master.sh
      - name: archive
        mountPath: /var/lib/postgresql/archive

env:
  # ... env existantes ...
  - name: REPLICATION_PASSWORD
    valueFrom:
      secretKeyRef:
        name: postgresql-replication
        key: replication-password
  - name: POSTGRESQL_ADMIN_PASSWORD
    valueFrom:
      secretKeyRef:
        name: postgresql # Adapter au nom du secret existant
        key: database-password

args:
  - "-c"
  - "config_file=/etc/postgresql/master.conf"
  - "-c"
  - "hba_file=/etc/postgresql/pg_hba.conf"

# Validation syntaxe
oc apply -f postgresql-master-updated.yaml --dry-run=client

# Application (⚠️ redémarrage du pod)
oc apply -f postgresql-master-updated.yaml

# Surveillance
oc get pods -w

oc wait --for=condition=ready pod -l name=postgresql --timeout=300s
POD_NAME=$(oc get pods -l name=postgresql -o jsonpath='{.items[0].metadata.name}')

oc exec $POD_NAME -- psql -U postgres -c "SELECT rolname, rolreplication FROM pg_roles WHERE rolname='replicator';"

oc exec $POD_NAME -- psql -U postgres -c "SELECT * FROM pg_replication_slots;"

oc exec $POD_NAME -- psql -U postgres -c "SHOW wal_level; SHOW max_wal_senders; SHOW wal_keep_size;"

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgresql-slave
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 20Gi # Ajuster à la taille du master ou plus
  storageClassName: standard # Ajuster selon votre storage class

oc apply -f postgresql-slave-pvc.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-slave-config
data:
  slave.conf: |
    # Configuration Slave PostgreSQL
    listen_addresses = '*'
    max_connections = 100
    shared_buffers = 256MB

    # ===== Configuration Réplication =====
    wal_level  
                             
      
      

    
      
      
      
      
      
      
      

   
     

    
     
              
       
       
    

     

    
     [   ] 
         
         
    

    
     
      
          
          
          
            
          
         

    
     

    
      

     

oc apply -f postgresql-slave-config.yaml

oc apply -f postgresql-slave-deployment.yaml

apiVersion: v1
kind: Service
metadata:
  name: postgresql-slave
  labels:
    app: postgresql
    role: slave
spec:
  ports:
    - name: postgresql
      port: 5432
      targetPort: 5432
  selector:
    app: postgresql
    role: slave
  type: ClusterIP

oc apply -f postgresql-slave-service.yaml

# Surveiller les pods
oc get pods -l role=slave -w

# Logs de l'init container
SLAVE_POD=$(oc get pods -l role=slave -o jsonpath='{.items[0].metadata.name}')
oc logs $SLAVE_POD -c setup-slave -f

# Logs du conteneur principal
oc logs $SLAVE_POD -c postgresql -f

MASTER_POD=$(oc get pods -l name=postgresql -o jsonpath='{.items[0].metadata.name}')

oc exec $MASTER_POD -- psql -U postgres -c "
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn
FROM pg_stat_replication;
"

 pid | usename    | application_name | client_addr | state     | sync_state
-----+------------+------------------+-------------+-----------+------------
 123 | replicator | walreceiver      | 10.128.x.x  | streaming | async

oc exec $MASTER_POD -- psql -U postgres -c "
SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn
FROM pg_replication_slots;
"

      slot_name       | slot_type | active | restart_lsn
----------------------+-----------+--------+-------------
 replication_slot_slave | physical  | t      | 0/3000000

oc exec $MASTER_POD -- psql -U postgres -c "
SELECT
    client_addr,
    state,
    (pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024)::INT AS lag_mb,
    (EXTRACT(EPOCH FROM (now() - replay_time)))::INT AS lag_seconds
FROM pg_stat_replication;
"

oc exec $MASTER_POD -- psql -U postgres <<EOF
CREATE TABLE IF NOT EXISTS test_replication (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO test_replication (message)
VALUES ('Test 1'), ('Test 2'), ('Test 3');
EOF

SLAVE_POD=$(oc get pods -l role=slave -o jsonpath='{.items[0].metadata.name}')
oc exec $SLAVE_POD -- psql -U postgres -c "SELECT * FROM test_replication;"

oc exec $SLAVE_POD -- psql -U postgres -c "
INSERT INTO test_replication (message) VALUES ('Should fail');
"

oc exec $MASTER_POD -- psql -U postgres <<EOF
INSERT INTO test_replication (message)
SELECT 'Test ' || generate_series(1, 10000);
EOF

oc exec $SLAVE_POD -- psql -U postgres -c "SELECT COUNT(*) FROM test_replication;"

oc run postgresql-test --image=postgres:15 --rm -it --restart=Never -- bash

# Connexion master (RW)
psql -h postgresql -U postgres -d postgres

# Connexion slave (RO)
psql -h postgresql-slave -U postgres -d postgres

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-monitoring-queries
data:
  replication-status.sql: |
    SELECT
        client_addr,
        state,
        sync_state,
        (pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024)::INT AS lag_mb,
        (EXTRACT(EPOCH FROM (now() - replay_time)))::INT AS lag_seconds
    FROM pg_stat_replication;

  database-size.sql: |
    SELECT
        pg_database.datname,
        pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database
    ORDER BY pg_database_size(pg_database.datname) DESC;

  connection-count.sql: |
    SELECT
        count(*) as total_connections,
        sum(case when state = 'active' then 1 else 0 end) as active_connections,
        sum(case when state = 'idle' then 1 else 0 end) as idle_connections
    FROM pg_stat_activity;

  replication-slots.sql: |
    SELECT
        slot_name,
        slot_type,
        active,
        pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
    FROM pg_replication_slots;

oc exec $MASTER_POD -- psql -U postgres -f /path/to/replication-status.sql

apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: postgresql-replication-alerts
spec:
  groups:
    - name: postgresql-replication
      interval: 30s
      rules:
        - alert: PostgreSQLReplicationLagHigh
          expr: pg_replication_lag_seconds > 30
          for: 5m
          labels:
             
          
             
             

          
             
           
          
             
          
             
             

          
             
           
          
             
          
             
             

apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgresql-replication-check
spec:
  schedule: "*/15 * * * *" # Toutes les 15 minutes
  jobTemplate:
    spec:
      template:
        spec:
          serviceAccountName: postgresql-monitor
          containers:
            - name: check
              image: postgres:15
              
                 
                 
                 
                   
                           
           

# Supprimer les ressources
oc delete deployment postgresql-slave
oc delete service postgresql-slave
oc delete pvc postgresql-slave

# Nettoyer le slot de réplication sur le master
MASTER_POD=$(oc get pods -l name=postgresql -o jsonpath='{.items[0].metadata.name}')
oc exec $MASTER_POD -- psql -U postgres -c "
SELECT pg_drop_replication_slot('replication_slot_slave');
"

# Restaurer la configuration d'origine
oc apply -f backup-postgresql-dc.yaml

# Surveiller le rollout
oc rollout status dc/postgresql

# Vérifier l'état
oc get pods -l name=postgresql

oc logs $SLAVE_POD -c setup-slave
oc logs $SLAVE_POD -c postgresql
oc describe pod $SLAVE_POD

oc exec $SLAVE_POD -- ping postgresql

oc get secret postgresql-replication -o yaml

oc exec $MASTER_POD -- psql -U postgres -c "SELECT * FROM pg_replication_slots;"

oc delete pod $SLAVE_POD
oc delete pvc postgresql-slave
oc apply -f postgresql-slave-pvc.yaml

# Ressources du slave
oc describe pod $SLAVE_POD
oc top pod $SLAVE_POD

# Charge du master
oc top pod $MASTER_POD

oc edit deployment postgresql-slave
# Augmenter memory et cpu

oc exec $MASTER_POD -- ping $SLAVE_POD_IP

oc exec $SLAVE_POD -- iostat -x 1

oc exec $MASTER_POD -- psql -U postgres -c "
SELECT
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
"

# Supprimer le slot
oc exec $MASTER_POD -- psql -U postgres -c "
SELECT pg_drop_replication_slot('replication_slot_slave');
"

# Recréer le slave depuis zéro

# Pour une application déployée
oc set env deployment/mon-application \
  DATABASE_URL_WRITE="postgresql://user:pass@postgresql:5432/dbname" \
  DATABASE_URL_READ="postgresql://user:pass@postgresql-slave:5432/dbname"

{
  type: 'postgres',
  replication: {
    master: {
      host: 'postgresql',
      port: 5432,
      username: 'user',
      password: 'pass',
      database: 'dbname'
    },
    slaves: [{
      host: 'postgresql-slave',
      port: 5432,
      username: 'user',
      password: 'pass',
      database: 'dbname'
    }]
  }
}

# Logs
oc logs <pod-name>
oc logs <pod-name> -c <container-name>

# Events
oc get events --sort-by='.lastTimestamp' | grep postgres

# Describe
oc describe pod <pod-name>
oc describe pvc <pvc-name>

# État des ressources
oc get all -l app=postgresql
oc top pod -l app=postgresql