🔝 Retour au Sommaire

19.6.4. Backup et DR (Disaster Recovery)

Introduction

"The question is not IF you will need backups, but WHEN."

Les sauvegardes (backups) sont votre assurance vie en production. Sans elles, une erreur humaine, une corruption de disque, ou une attaque ransomware peut détruire des années de données en quelques secondes.

Le Disaster Recovery (DR) va au-delà des simples sauvegardes : c'est l'ensemble des processus, politiques et procédures qui garantissent la continuité d'activité en cas de catastrophe (incendie, panne datacenter, cyberattaque, etc.).

Cette section couvre :

Les différents types de sauvegardes PostgreSQL
Les stratégies de sauvegarde efficaces
La restauration et le Point-In-Time Recovery (PITR)
L'automatisation et la validation
La planification de la reprise après sinistre

Règle d'or : Une sauvegarde qui n'a jamais été testée n'est pas une sauvegarde. C'est une espérance.

1. Concepts Fondamentaux

1.1. RTO et RPO : Définir vos Objectifs

Avant toute stratégie de backup, définissez :

RPO (Recovery Point Objective) : Combien de données pouvez-vous vous permettre de perdre ?

RPO = 24h → Backup quotidien acceptable
RPO = 1h → Nécessite archivage WAL continu
RPO = 0 → Réplication synchrone

RTO (Recovery Time Objective) : Combien de temps pour rétablir le service ?

RTO = 4h → Restauration standard acceptable
RTO = 15 min → Nécessite réplication + failover automatique
RTO = 0 → Haute disponibilité avec failover instantané

Exemple :

Application	RPO	RTO	Stratégie
Blog personnel	24h	4h	pg_dump quotidien
E-commerce	15 min	30 min	PITR + réplication asynchone
Banque en ligne	0	1 min	Réplication synchrone + failover auto

1.2. Types de Sauvegardes

PostgreSQL offre deux approches principales :

1. Sauvegardes Logiques (pg_dump)

Export SQL ou format personnalisé
Lecture humaine possible (SQL)
Restauration sélective (tables spécifiques)
Indépendant de la version PostgreSQL (migration facile)
Inconvénient : Lent sur grosses bases

2. Sauvegardes Physiques (pg_basebackup)

Copie binaire du répertoire de données
Rapide, même pour gros volumes
Permet le Point-In-Time Recovery (PITR)
Inconvénient : Doit être même version/architecture

Recommandation : Les deux ! Logique pour flexibilité, physique pour vitesse.

2. Sauvegardes Logiques avec pg_dump

2.1. pg_dump : Sauvegarder une Base

Commande de base :

pg_dump -U postgres -d myapp -f myapp_backup.sql

Formats disponibles :

# 1. Format SQL (texte, lisible)
pg_dump -U postgres -d myapp -f myapp.sql

# 2. Format personnalisé (custom) - RECOMMANDÉ
pg_dump -U postgres -d myapp -Fc -f myapp.dump

# 3. Format directory (parallélisation)
pg_dump -U postgres -d myapp -Fd -j 4 -f myapp_dir/

# 4. Format tar
pg_dump -U postgres -d myapp -Ft -f myapp.tar

Explications :

-Fc : Format custom, compressé, permet restauration sélective
-Fd : Format directory, permet parallélisation avec -j
-j 4 : Utilise 4 workers en parallèle (plus rapide)

2.2. Options Importantes

# Backup complet avec toutes les options
pg_dump \
  -U postgres \
  -d myapp \
  -Fc \                           # Format custom
  -Z 9 \                          # Compression max (0-9)
  -f /backups/myapp_$(date +%Y%m%d_%H%M%S).dump \
  --verbose \                     # Afficher progression
  --quote-all-identifiers \       # Quote tous les identifiants
  --no-owner \                    # Ne pas inclure les propriétaires
  --no-privileges                 # Ne pas inclure les permissions

Options utiles :

Option	Description
`--schema=SCHEMA`	Backup d'un schéma uniquement
`--table=TABLE`	Backup d'une table uniquement
`--exclude-table=PATTERN`	Exclure certaines tables
`--data-only`	Données uniquement (sans DDL)
`--schema-only`	Structure uniquement (sans données)
`--inserts`	Utiliser INSERT au lieu de COPY (compatible)
`--column-inserts`	INSERT avec noms de colonnes
`--clean`	Ajouter DROP avant CREATE
`--if-exists`	Utiliser IF EXISTS dans DROP

2.3. Exemples Pratiques

Backup d'une table spécifique :

pg_dump -U postgres -d myapp -t users -Fc -f users_backup.dump

Backup de plusieurs tables :

pg_dump -U postgres -d myapp -t users -t orders -t products -Fc -f tables_backup.dump

Backup sans tables de logs :

pg_dump -U postgres -d myapp \
  --exclude-table='logs_*' \
  --exclude-table='audit_*' \
  -Fc -f myapp_no_logs.dump

Backup structure uniquement :

pg_dump -U postgres -d myapp --schema-only -f schema.sql

2.4. pg_dumpall : Sauvegarder Tout le Cluster

Cas d'usage : Backup de toutes les bases + rôles + configurations globales.

# Backup complet du cluster
pg_dumpall -U postgres -f cluster_full_backup.sql

# Rôles et tablespaces uniquement
pg_dumpall -U postgres --globals-only -f globals.sql

Limitations :

Format SQL uniquement (pas de compression custom)
Ne parallélise pas (lent sur gros volumes)

Recommandation : Utilisez pg_dump individuellement par base + pg_dumpall --globals-only.

2.5. Restauration avec pg_restore

Format custom ou directory :

# Restauration complète
pg_restore -U postgres -d myapp myapp_backup.dump

# Restauration avec DROP des objets existants
pg_restore -U postgres -d myapp --clean --if-exists myapp_backup.dump

# Restauration en parallèle (format directory)
pg_restore -U postgres -d myapp -j 4 myapp_dir/

# Restauration d'une table spécifique
pg_restore -U postgres -d myapp -t users myapp_backup.dump

# Liste des objets dans le backup
pg_restore --list myapp_backup.dump

Options importantes :

Option	Description
`--clean`	DROP objets avant restauration
`--if-exists`	Utiliser IF EXISTS (pas d'erreur si absent)
`--create`	Créer la base avant restauration
`-j N`	Paralléliser sur N workers
`--verbose`	Mode verbeux
`--exit-on-error`	Arrêter à la première erreur
`--no-owner`	Ne pas restaurer les propriétaires
`--no-privileges`	Ne pas restaurer les permissions

Format SQL :

# Restauration depuis SQL
psql -U postgres -d myapp -f myapp_backup.sql

3. Sauvegardes Physiques avec pg_basebackup

3.1. Principe

pg_basebackup crée une copie binaire du répertoire de données PostgreSQL. C'est la base du Point-In-Time Recovery (PITR).

Avantages :

Très rapide (copie binaire)
Permet PITR avec archivage WAL
Base de la réplication
Pas de verrouillage de tables

Prérequis :

Réplication configurée (wal_level = replica)
Slot de réplication (recommandé)

3.2. Configuration Préalable

Fichier : postgresql.conf

# Activer l'archivage WAL
wal_level = replica  
archive_mode = on  
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'  
# Ou via script plus robuste
# archive_command = '/usr/local/bin/archive_wal.sh %p %f'

# Créer le répertoire d'archives
# mkdir -p /mnt/wal_archive
# chown postgres:postgres /mnt/wal_archive

Fichier : pg_hba.conf

# Autoriser la réplication
host    replication     replicator      127.0.0.1/32            scram-sha-256  
host    replication     replicator      10.0.1.0/24             scram-sha-256

Créer un utilisateur de réplication :

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Restart :

sudo systemctl restart postgresql-18

3.3. Créer un Backup Physique

Commande de base :

pg_basebackup \
  -U replicator \
  -h localhost \
  -D /backups/base_$(date +%Y%m%d_%H%M%S) \
  -Fp \                             # Format plain (répertoire)
  -Xs \                             # Stream WAL (inclus dans backup)
  -P \                              # Afficher progression
  -v                                # Verbose

Formats disponibles :

# Format plain (répertoire)
pg_basebackup -D /backups/base -Fp

# Format tar (archive)
pg_basebackup -D /backups/base.tar -Ft

# Format tar avec compression
pg_basebackup -D /backups/base.tar.gz -Ft -z -Z 9

Options importantes :

Option	Description
`-D DIR`	Répertoire de destination
`-Fp`	Format plain (répertoire)
`-Ft`	Format tar
`-z`	Compresser (avec tar)
`-Z 0-9`	Niveau de compression
`-Xs`	Stream WAL (inclus dans backup)
`-Xf`	Fetch WAL à la fin
`-P`	Progression
`-c fast`	Checkpoint rapide
`-R`	Créer fichiers de config pour standby (recovery.signal, etc.)
`-S slot`	Utiliser un slot de réplication

3.4. Backup avec Slot de Réplication (Recommandé)

Avantage : Garantit que les WAL nécessaires ne sont pas supprimés pendant le backup.

-- Créer un slot de réplication
SELECT pg_create_physical_replication_slot('backup_slot');

# Backup avec slot
pg_basebackup \
  -U replicator \
  -h localhost \
  -D /backups/base_$(date +%Y%m%d_%H%M%S) \
  -Fp \
  -Xs \
  -S backup_slot \
  -P -v

-- Supprimer le slot après backup
SELECT pg_drop_replication_slot('backup_slot');

3.5. Script d'Automatisation

#!/bin/bash
# /usr/local/bin/backup_postgres.sh

set -e

BACKUP_DIR="/backups/postgres"  
DATE=$(date +%Y%m%d_%H%M%S)  
BACKUP_PATH="${BACKUP_DIR}/base_${DATE}"  
RETENTION_DAYS=7  

echo "=== PostgreSQL Physical Backup ==="  
echo "Date: $(date)"  
echo "Destination: ${BACKUP_PATH}"  

# Créer le répertoire
mkdir -p "${BACKUP_DIR}"

# Créer slot de réplication
echo "Creating replication slot..."  
psql -U postgres -c "SELECT pg_create_physical_replication_slot('backup_slot_${DATE}');" || true  

# Backup
echo "Starting backup..."  
pg_basebackup \  
  -U replicator \
  -h localhost \
  -D "${BACKUP_PATH}" \
  -Fp \
  -Xs \
  -S "backup_slot_${DATE}" \
  -P -v

# Supprimer le slot
echo "Dropping replication slot..."  
psql -U postgres -c "SELECT pg_drop_replication_slot('backup_slot_${DATE}');"  

# Créer un manifeste
echo "Creating manifest..."  
echo "Backup Date: $(date)" > "${BACKUP_PATH}/backup_manifest.txt"  
echo "PostgreSQL Version: $(psql -U postgres -t -c 'SELECT version();')" >> "${BACKUP_PATH}/backup_manifest.txt"  
du -sh "${BACKUP_PATH}" >> "${BACKUP_PATH}/backup_manifest.txt"  

# Compression (optionnel)
echo "Compressing backup..."  
tar -czf "${BACKUP_PATH}.tar.gz" -C "${BACKUP_DIR}" "base_${DATE}"  
rm -rf "${BACKUP_PATH}"  

# Nettoyage des anciens backups
echo "Cleaning old backups (> ${RETENTION_DAYS} days)..."  
find "${BACKUP_DIR}" -name "base_*.tar.gz" -mtime +${RETENTION_DAYS} -delete  

echo "Backup completed: ${BACKUP_PATH}.tar.gz"  
echo "Size: $(du -sh ${BACKUP_PATH}.tar.gz | cut -f1)"

Rendre exécutable :

chmod +x /usr/local/bin/backup_postgres.sh

Planifier via cron :

# Backup quotidien à 2h du matin
0 2 * * * /usr/local/bin/backup_postgres.sh >> /var/log/postgres_backup.log 2>&1

4. Point-In-Time Recovery (PITR)

4.1. Concept

Le PITR permet de restaurer la base à n'importe quel moment entre :

Le backup physique (pg_basebackup)
La date actuelle (ou un moment spécifique)

Exemple :

Backup effectué lundi 00h00
Erreur humaine mercredi 14h30 (DELETE sans WHERE)
PITR permet de restaurer à mercredi 14h29 (juste avant l'erreur)

4.2. Configuration de l'Archivage WAL

Fichier : postgresql.conf

# Activer l'archivage
wal_level = replica  
archive_mode = on  
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'  

# Optionnel : augmenter max_wal_senders si réplication
max_wal_senders = 10  
wal_keep_size = 1GB

Script d'archivage robuste :

#!/bin/bash
# /usr/local/bin/archive_wal.sh

WAL_FILE=$1  
WAL_NAME=$2  
ARCHIVE_DIR="/mnt/wal_archive"  

# Créer le répertoire si nécessaire
mkdir -p "${ARCHIVE_DIR}"

# Copier avec vérification
if cp "${WAL_FILE}" "${ARCHIVE_DIR}/${WAL_NAME}"; then
  # Vérifier l'intégrité (optionnel)
  if diff "${WAL_FILE}" "${ARCHIVE_DIR}/${WAL_NAME}" > /dev/null 2>&1; then
    exit 0
  else
    echo "ERROR: WAL copy verification failed" >&2
    rm -f "${ARCHIVE_DIR}/${WAL_NAME}"
    exit 1
  fi
else
  echo "ERROR: Failed to copy WAL file" >&2
  exit 1
fi

Permissions :

chmod +x /usr/local/bin/archive_wal.sh  
chown postgres:postgres /usr/local/bin/archive_wal.sh

Mettre à jour postgresql.conf :

archive_command = '/usr/local/bin/archive_wal.sh %p %f'

4.3. Restauration PITR : Étape par Étape

Scénario : Base corrompue à 14h30, dernier backup à 00h00, restaurer à 14h29.

Étape 1 : Arrêter PostgreSQL

sudo systemctl stop postgresql-18

Étape 2 : Sauvegarder les Données Corrompues (au cas où)

mv /var/lib/pgsql/18/data /var/lib/pgsql/18/data_corrupt_$(date +%Y%m%d)

Étape 3 : Restaurer le Backup Physique

# Extraire le backup
tar -xzf /backups/base_20251123_000000.tar.gz -C /var/lib/pgsql/18/data

# Ou copier si format plain
# cp -r /backups/base_20251123_000000 /var/lib/pgsql/18/data

Étape 4 : Configurer la Récupération

Créer postgresql.auto.conf ou modifier postgresql.conf :

# Configuration de récupération
restore_command = 'cp /mnt/wal_archive/%f %p'  
recovery_target_time = '2025-11-23 14:29:00'  
recovery_target_action = 'promote'

Créer le signal de récupération :

touch /var/lib/pgsql/18/data/recovery.signal

Étape 5 : Démarrer PostgreSQL

sudo systemctl start postgresql-18

Étape 6 : Surveiller les Logs

tail -f /var/lib/pgsql/18/data/log/postgresql-*.log

Résultat attendu :

LOG:  starting point-in-time recovery to 2025-11-23 14:29:00+00  
LOG:  restored log file "000000010000000000000001" from archive  
LOG:  redo starts at 0/1000028  
LOG:  consistent recovery state reached at 0/2000000  
LOG:  restored log file "000000010000000000000002" from archive  
...
LOG:  recovery stopping before commit of transaction 12345, time 2025-11-23 14:30:15  
LOG:  recovery has paused  
LOG:  recovery complete, database system is ready for connections

Étape 7 : Vérifier

-- Vérifier que les données sont bien là
SELECT count(*) FROM users;

-- Vérifier le timestamp de récupération
SELECT pg_last_xact_replay_timestamp();

4.4. Options de recovery_target

# Par temps
recovery_target_time = '2025-11-23 14:29:00'

# Par transaction ID
recovery_target_xid = '12344'

# Par nom (avec pg_create_restore_point)
recovery_target_name = 'before_migration'

# Par LSN (Log Sequence Number)
recovery_target_lsn = '0/3000000'

# Immediate (dès que consistent)
recovery_target = 'immediate'

Actions après récupération :

# Promouvoir en primary
recovery_target_action = 'promote'

# Arrêter
recovery_target_action = 'shutdown'

# Pause (pour inspection)
recovery_target_action = 'pause'

5. Stratégies de Sauvegarde

5.1. La Règle 3-2-1

3 copies de vos données
2 types de média différents
1 copie hors site (off-site)

Exemple :

Copie production : Base de données active
Backup local : Disque SSD sur serveur (pg_basebackup quotidien)
Backup distant : Cloud S3/Azure Blob (copie du backup local)

Médias différents :

SSD local (rapide, restauration rapide)
HDD externe (grande capacité)
Cloud storage (durabilité, géo-réplication)

5.2. Fréquences Recommandées

Type	Fréquence	Rétention	Objectif
pg_dump	Quotidien	7 jours	Restauration rapide tables
pg_basebackup	Quotidien	7 jours	PITR récent
Hebdomadaire	Dimanche	4 semaines	Récupération moyen terme
Mensuel	1er du mois	12 mois	Conformité, audit
Annuel	1er janvier	7 ans	Archivage légal

5.3. Stratégies par Type d'Application

Petite Application (< 10 GB) :

# Backup quotidien logique (simple et suffisant)
0 2 * * * pg_dump -U postgres -d myapp -Fc -f /backups/myapp_$(date +\%Y\%m\%d).dump

# Rétention : 7 jours
0 3 * * * find /backups -name "myapp_*.dump" -mtime +7 -delete

Application Moyenne (10-100 GB) :

# Backup physique quotidien
0 2 * * * /usr/local/bin/backup_postgres.sh

# Backup logique hebdomadaire (dimanche)
0 3 * * 0 pg_dump -U postgres -d myapp -Fc -f /backups/weekly/myapp_$(date +\%Y\%m\%d).dump

# Archivage WAL continu (dans postgresql.conf)
archive_mode = on  
archive_command = 'cp %p /mnt/wal_archive/%f'

Grosse Application (> 100 GB) :

# Backup physique quotidien + streaming WAL
archive_mode = on  
archive_command = 'aws s3 cp %p s3://my-bucket/wal/%f'  

# Backup hebdomadaire complet + incrémental quotidien
# Utiliser pgBackRest ou Barman

5.4. Tableau de Décision

Taille DB	RPO	RTO	Solution recommandée
< 10 GB	24h	4h	pg_dump quotidien
10-50 GB	1h	2h	pg_basebackup + WAL archiving
50-500 GB	15 min	30 min	pgBackRest + réplication
> 500 GB	5 min	10 min	Barman + réplication synchrone
Mission	0	1 min	Réplication synchrone multi-zone + failover auto

6. Outils Avancés de Backup

6.1. pgBackRest

pgBackRest est l'outil de backup le plus avancé pour PostgreSQL.

Fonctionnalités :

Backup complet, différentiel, incrémental
Compression et chiffrement
Parallélisation
Backup vers S3/Azure/GCS
Restauration PITR simplifiée
Vérification d'intégrité

Installation :

# RHEL/CentOS
sudo dnf install pgbackrest

# Debian/Ubuntu
sudo apt install pgbackrest

Configuration (/etc/pgbackrest.conf) :

[global]
repo1-path=/var/lib/pgbackrest  
repo1-retention-full=2  
repo1-retention-diff=4  
start-fast=y  
log-level-console=info  
log-level-file=debug  

[myapp]
pg1-path=/var/lib/pgsql/18/data  
pg1-port=5432  
pg1-user=postgres

Commandes :

# Backup complet
pgbackrest --stanza=myapp backup --type=full

# Backup incrémental
pgbackrest --stanza=myapp backup --type=incr

# Backup différentiel
pgbackrest --stanza=myapp backup --type=diff

# Restauration
pgbackrest --stanza=myapp restore

# PITR
pgbackrest --stanza=myapp restore \
  --type=time \
  --target="2025-11-23 14:29:00"

# Info sur les backups
pgbackrest --stanza=myapp info

6.2. Barman

Barman (Backup and Recovery Manager) par 2ndQuadrant.

Fonctionnalités :

Backup physique avec compression
Archivage WAL
PITR
Géo-réplication des backups
Interface web

Installation :

# RHEL/CentOS
sudo dnf install barman

# Debian/Ubuntu
sudo apt install barman

Configuration (/etc/barman.conf) :

[barman]
barman_user = barman  
configuration_files_directory = /etc/barman.d  
barman_home = /var/lib/barman  
log_file = /var/log/barman/barman.log  
compression = gzip  

[myapp]
description = "Production database"  
ssh_command = ssh postgres@dbserver  
conninfo = host=dbserver user=barman dbname=postgres  
backup_method = postgres  
backup_options = concurrent_backup  
archiver = on

Commandes :

# Backup
barman backup myapp

# Liste des backups
barman list-backup myapp

# Restauration
barman recover myapp latest /var/lib/pgsql/18/data

# PITR
barman recover myapp latest /var/lib/pgsql/18/data \
  --target-time "2025-11-23 14:29:00"

# Check
barman check myapp

6.3. WAL-G

WAL-G : Outil moderne, cloud-first.

Avantages :

Conçu pour le cloud (S3, GCS, Azure)
Compression avancée (zstd, lz4)
Chiffrement natif
Backup incrémental efficace

Installation :

# Download latest release
wget https://github.com/wal-g/wal-g/releases/download/v2.0.1/wal-g-pg-ubuntu-20.04-amd64.tar.gz  
tar -xzf wal-g-pg-ubuntu-20.04-amd64.tar.gz  
sudo mv wal-g-pg-ubuntu-20.04-amd64 /usr/local/bin/wal-g

Configuration (~/.walg.json) :

{
  "WALG_S3_PREFIX": "s3://my-backup-bucket/postgres",
  "AWS_REGION": "us-east-1",
  "WALG_COMPRESSION_METHOD": "zstd",
  "WALG_DELTA_MAX_STEPS": "5",
  "PGDATA": "/var/lib/pgsql/18/data"
}

Commandes :

# Backup
wal-g backup-push

# Restauration
wal-g backup-fetch /var/lib/pgsql/18/data LATEST

# Liste des backups
wal-g backup-list

7. Backup vers le Cloud

7.1. AWS S3

pg_dump vers S3 :

#!/bin/bash
# Backup vers S3

BACKUP_FILE="myapp_$(date +%Y%m%d_%H%M%S).dump"  
BACKUP_PATH="/tmp/${BACKUP_FILE}"  
S3_BUCKET="s3://my-postgres-backups"  

# Créer le backup
pg_dump -U postgres -d myapp -Fc -f "${BACKUP_PATH}"

# Uploader vers S3
aws s3 cp "${BACKUP_PATH}" "${S3_BUCKET}/${BACKUP_FILE}"

# Nettoyage local
rm -f "${BACKUP_PATH}"

# Lifecycle policy S3 (via AWS CLI ou Console)
# - Transition vers Glacier après 30 jours
# - Suppression après 365 jours

Archive WAL vers S3 :

# postgresql.conf
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f'

Restauration depuis S3 :

# postgresql.conf (récupération)
restore_command = 'aws s3 cp s3://my-wal-archive/%f %p'

7.2. Azure Blob Storage

#!/bin/bash
# Backup vers Azure Blob

BACKUP_FILE="myapp_$(date +%Y%m%d_%H%M%S).dump"  
BACKUP_PATH="/tmp/${BACKUP_FILE}"  
CONTAINER="postgres-backups"  

# Backup
pg_dump -U postgres -d myapp -Fc -f "${BACKUP_PATH}"

# Upload vers Azure
az storage blob upload \
  --account-name mystorageaccount \
  --container-name "${CONTAINER}" \
  --name "${BACKUP_FILE}" \
  --file "${BACKUP_PATH}"

# Nettoyage
rm -f "${BACKUP_PATH}"

7.3. Google Cloud Storage

#!/bin/bash
# Backup vers GCS

BACKUP_FILE="myapp_$(date +%Y%m%d_%H%M%S).dump"  
BACKUP_PATH="/tmp/${BACKUP_FILE}"  
GCS_BUCKET="gs://my-postgres-backups"  

# Backup
pg_dump -U postgres -d myapp -Fc -f "${BACKUP_PATH}"

# Upload vers GCS
gsutil cp "${BACKUP_PATH}" "${GCS_BUCKET}/${BACKUP_FILE}"

# Nettoyage
rm -f "${BACKUP_PATH}"

8. Tests de Restauration

8.1. Pourquoi Tester ?

Statistiques terrifiantes :

30% des backups ne peuvent pas être restaurés
60% des entreprises découvrent que leur backup est corrompu au moment critique
La plupart des entreprises ne testent JAMAIS leurs backups

Conséquence : Un backup non testé = pas de backup.

8.2. Plan de Test Mensuel

Checklist :

#!/bin/bash
# /usr/local/bin/test_backup_restore.sh

set -e

TEST_DIR="/tmp/restore_test_$(date +%Y%m%d_%H%M%S)"  
BACKUP_FILE="/backups/latest/myapp.dump"  
TEST_PORT=5433  

echo "=== PostgreSQL Backup Restore Test ==="  
echo "Date: $(date)"  

# 1. Créer un cluster de test
echo "Creating test cluster..."  
initdb -D "${TEST_DIR}/data"  

# 2. Configurer le port (éviter conflit)
echo "port = ${TEST_PORT}" >> "${TEST_DIR}/data/postgresql.conf"

# 3. Démarrer le cluster de test
echo "Starting test cluster..."  
pg_ctl -D "${TEST_DIR}/data" -l "${TEST_DIR}/logfile" start  

# 4. Créer la base
echo "Creating database..."  
psql -p ${TEST_PORT} -U postgres -c "CREATE DATABASE myapp_test;"  

# 5. Restaurer le backup
echo "Restoring backup..."  
pg_restore -p ${TEST_PORT} -U postgres -d myapp_test "${BACKUP_FILE}"  

# 6. Vérifier l'intégrité
echo "Verifying restore..."  
TABLE_COUNT=$(psql -p ${TEST_PORT} -U postgres -d myapp_test -t -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';")  
echo "Tables restored: ${TABLE_COUNT}"  

if [ "${TABLE_COUNT}" -gt 0 ]; then
  echo "✅ RESTORE TEST PASSED"
  EXIT_CODE=0
else
  echo "❌ RESTORE TEST FAILED"
  EXIT_CODE=1
fi

# 7. Nettoyage
echo "Cleaning up..."  
pg_ctl -D "${TEST_DIR}/data" stop  
rm -rf "${TEST_DIR}"  

exit ${EXIT_CODE}

Planifier via cron :

# Test mensuel le 1er à 4h
0 4 1 * * /usr/local/bin/test_backup_restore.sh >> /var/log/backup_test.log 2>&1

8.3. Tests PITR

Scénario de test complet :

#!/bin/bash
# Test PITR complet

# 1. Noter l'heure actuelle
CURRENT_TIME=$(date +"%Y-%m-%d %H:%M:%S")  
echo "Current time: ${CURRENT_TIME}"  

# 2. Insérer des données de test
psql -U postgres -d myapp -c "CREATE TABLE pitr_test (id serial, created_at timestamp default now());"  
psql -U postgres -d myapp -c "INSERT INTO pitr_test (id) SELECT generate_series(1, 1000);"  

# 3. Backup physique
pg_basebackup -U replicator -D /backups/pitr_test -Fp -Xs -P

# 4. Attendre 1 minute
sleep 60

# 5. "Détruire" les données (simuler erreur)
DELETE_TIME=$(date +"%Y-%m-%d %H:%M:%S")  
psql -U postgres -d myapp -c "DROP TABLE pitr_test;"  

# 6. Restaurer avec PITR (avant le DROP)
# ... (procédure PITR détaillée section 4.3)

# 7. Vérifier
# Les 1000 lignes doivent être là

9. Disaster Recovery : Plan d'Action

9.1. Scénarios de Sinistre

Scénario	Gravité	Solution
Erreur humaine (DELETE)	Modéré	PITR vers timestamp avant erreur
Corruption disque	Élevé	Restauration depuis backup + WAL
Panne datacenter	Critique	Failover vers standby géo-répliqué
Ransomware	Critique	Restauration depuis backup off-site immutable
Catastrophe naturelle	Extrême	DR site dans région différente

9.2. Runbook : Récupération Après Sinistre

Document à avoir imprimé et disponible 24/7.

# RUNBOOK: PostgreSQL Disaster Recovery

## Contact d'urgence
- DBA Principal: +33 6 XX XX XX XX
- DBA Secondaire: +33 6 YY YY YY YY
- Support Cloud: support@provider.com

## Étape 1 : Évaluation (5 min)
- [ ] Confirmer l'incident (vérifier monitoring)
- [ ] Identifier la cause (erreur humaine, hardware, attaque)
- [ ] Déterminer l'étendue (une base, tout le cluster)
- [ ] Notifier les parties prenantes

## Étape 2 : Arrêt d'Urgence (si nécessaire)
```bash
sudo systemctl stop postgresql-18
# Ou kill -QUIT $(head -1 /var/lib/pgsql/18/data/postmaster.pid)

Étape 3 : Choix de la Stratégie

PITR si erreur récente
Restauration complète si corruption
Failover si standby disponible

Étape 4 : Restauration PITR (si applicable)

# Voir section 4.3 pour procédure détaillée

Étape 5 : Restauration Complète

# 1. Backup données corrompues
mv /var/lib/pgsql/18/data /var/lib/pgsql/18/data_corrupt

# 2. Extraire backup
tar -xzf /backups/latest.tar.gz -C /var/lib/pgsql/18/data

# 3. Configurer récupération WAL
echo "restore_command = 'cp /mnt/wal_archive/%f %p'" >> postgresql.conf

# 4. Démarrer
sudo systemctl start postgresql-18

Étape 6 : Validation

Service démarré
Connexions applicatives OK
Vérifier intégrité données
Vérifier réplication (si applicable)

Étape 7 : Post-Mortem

Documenter l'incident
Identifier cause racine
Améliorer procédures
Mettre à jour runbook


### 9.3. Tests de DR

**Fréquence recommandée** : Trimestriel

**Exercice complet** :
1. Simuler une panne totale (arrêt volontaire)  
2. Équipe DR restore depuis backup sans aide  
3. Chronométrer le RTO effectif  
4. Valider l'intégrité des données  
5. Documenter les écarts avec le RTO cible

---

## 10. Checklist de Production

### ✅ Configuration Backup

- [ ] `wal_level = replica` configuré  
- [ ] `archive_mode = on` activé  
- [ ] `archive_command` configuré et testé  
- [ ] Répertoire d'archives avec permissions correctes  
- [ ] Utilisateur de réplication créé  
- [ ] pg_hba.conf configure pour réplication

### ✅ Sauvegardes Quotidiennes

- [ ] pg_basebackup quotidien automatisé  
- [ ] pg_dump hebdomadaire automatisé  
- [ ] Archivage WAL continu fonctionnel  
- [ ] Logs de backup centralisés  
- [ ] Alertes en cas d'échec de backup

### ✅ Stockage et Rétention

- [ ] Backups locaux (restauration rapide)  
- [ ] Backups cloud (durabilité)  
- [ ] Règle 3-2-1 respectée  
- [ ] Rétention définie (7j, 4 semaines, 12 mois)  
- [ ] Nettoyage automatique anciens backups

### ✅ Sécurité des Backups

- [ ] Backups chiffrés (au repos)  
- [ ] Transferts chiffrés (TLS/SSL)  
- [ ] Accès restreint aux backups  
- [ ] Immutabilité activée (cloud)  
- [ ] Backups géo-répliqués

### ✅ Tests et Validation

- [ ] Test de restauration mensuel  
- [ ] Test PITR trimestriel  
- [ ] Test de DR complet semestriel  
- [ ] Documentation des procédures  
- [ ] Runbook accessible 24/7

### ✅ Monitoring

- [ ] Alerte si backup échoue  
- [ ] Alerte si archivage WAL bloqué  
- [ ] Monitoring espace disque archives  
- [ ] Dashboard taille backups  
- [ ] Alerte si backup trop ancien (> 25h)

### ✅ Documentation

- [ ] Procédure de backup documentée  
- [ ] Procédure de restauration documentée  
- [ ] Runbook DR accessible  
- [ ] Contacts d'urgence à jour  
- [ ] Localisation des backups documentée

---

## 11. Bonnes Pratiques

### 11.1. Dos

✅ **Testez vos backups régulièrement**
- Restauration mensuelle obligatoire
- Tests DR trimestriels

✅ **Automatisez tout**
- Backups automatisés via cron/systemd
- Nettoyage automatique des anciens backups
- Alertes automatiques en cas d'échec

✅ **Documentez tout**
- Procédures claires et accessibles
- Runbooks imprimés disponibles
- Historique des tests conservé

✅ **Chiffrez vos backups**
- Chiffrement au repos (cloud, disque)
- Chiffrement en transit (TLS)

✅ **Surveillez l'espace disque**
- Archives WAL peuvent remplir rapidement
- Alertes avant saturation

✅ **Gardez plusieurs générations**
- Ne gardez pas qu'un seul backup
- Rétention progressive (7j, 4w, 12m)

### 11.2. Don'ts

❌ **Ne faites pas confiance aveuglément**
- Backup non testé = pas de backup
- Validez toujours l'intégrité

❌ **Ne stockez pas que localement**
- Incendie, vol, panne = perte totale
- Toujours avoir une copie off-site

❌ **Ne négligez pas les WAL**
- Sans WAL, pas de PITR
- Surveiller l'archivage continu

❌ **N'oubliez pas les globals**
- Rôles, tablespaces, configs
- pg_dumpall --globals-only

❌ **Ne saturez pas la production**
- Backups pendant heures creuses
- Limiter l'impact I/O (nice, ionice)

---

## 12. Outils de Monitoring Backup

### 12.1. check_postgres

```bash
# Installation
sudo apt install check-postgres

# Vérifier l'âge du dernier backup
check_postgres --action=last_vacuum --warning=25h --critical=26h

12.2. Script de Vérification

#!/bin/bash
# /usr/local/bin/check_backup_freshness.sh

BACKUP_DIR="/backups"  
MAX_AGE_HOURS=25  

LATEST_BACKUP=$(find "${BACKUP_DIR}" -name "*.dump" -o -name "*.tar.gz" | sort | tail -1)

if [ -z "${LATEST_BACKUP}" ]; then
  echo "CRITICAL: No backup found"
  exit 2
fi

AGE_SECONDS=$(( $(date +%s) - $(stat -c %Y "${LATEST_BACKUP}") ))  
AGE_HOURS=$(( AGE_SECONDS / 3600 ))  

if [ ${AGE_HOURS} -gt ${MAX_AGE_HOURS} ]; then
  echo "CRITICAL: Latest backup is ${AGE_HOURS}h old (> ${MAX_AGE_HOURS}h)"
  exit 2
elif [ ${AGE_HOURS} -gt 24 ]; then
  echo "WARNING: Latest backup is ${AGE_HOURS}h old"
  exit 1
else
  echo "OK: Latest backup is ${AGE_HOURS}h old"
  exit 0
fi

13. Ressources et Documentation

Documentation Officielle

Outils

pgBackRest : https://pgbackrest.org/
Barman : https://www.pgbarman.org/
WAL-G : https://github.com/wal-g/wal-g

Guides et Blogs

2ndQuadrant Backup Guide : https://www.2ndquadrant.com/en/blog/
Percona PostgreSQL Backup : https://www.percona.com/blog/
AWS RDS Backup : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html

Conclusion

Les backups et le Disaster Recovery ne sont pas optionnels en production. Ce sont les filets de sécurité qui vous permettent de dormir la nuit.

Récapitulatif des priorités :

Jour 1 : Automatiser pg_dump quotidien + test manuel
Semaine 1 : Configurer pg_basebackup + archivage WAL
Semaine 2 : Backups cloud + règle 3-2-1
Mois 1 : Tests automatisés mensuels
Mois 2 : Runbook DR complet + tests trimestriels

Les trois piliers :

Backups automatisés : Quotidiens, fiables, diversifiés
Tests réguliers : Mensuels, documentés, chronométrés
Plan DR : Documenté, accessible, pratiqué

"Hope is not a strategy. Backup is."

Avec une stratégie de backup solide, vous ne serez jamais à la merci d'un accident. Vos données sont protégées, votre entreprise est résiliente, et vous pouvez vous concentrer sur l'innovation plutôt que sur la peur de perdre tout.

Section suivante : 19.6.5. Documentation runbooks

⏭️ Documentation runbooks

FilesExpand file tree

06.4-backup-et-dr.md

Latest commit

History

06.4-backup-et-dr.md

File metadata and controls

19.6.4. Backup et DR (Disaster Recovery)

Introduction

1. Concepts Fondamentaux

1.1. RTO et RPO : Définir vos Objectifs

1.2. Types de Sauvegardes

2. Sauvegardes Logiques avec pg_dump

2.1. pg_dump : Sauvegarder une Base

2.2. Options Importantes

2.3. Exemples Pratiques

2.4. pg_dumpall : Sauvegarder Tout le Cluster

2.5. Restauration avec pg_restore

3. Sauvegardes Physiques avec pg_basebackup

3.1. Principe

3.2. Configuration Préalable

3.3. Créer un Backup Physique

3.4. Backup avec Slot de Réplication (Recommandé)

3.5. Script d'Automatisation

4. Point-In-Time Recovery (PITR)

4.1. Concept

4.2. Configuration de l'Archivage WAL

4.3. Restauration PITR : Étape par Étape

4.4. Options de recovery_target

5. Stratégies de Sauvegarde

5.1. La Règle 3-2-1

5.2. Fréquences Recommandées

5.3. Stratégies par Type d'Application

5.4. Tableau de Décision

6. Outils Avancés de Backup

6.1. pgBackRest

6.2. Barman

6.3. WAL-G

7. Backup vers le Cloud

7.1. AWS S3

7.2. Azure Blob Storage

7.3. Google Cloud Storage

8. Tests de Restauration

8.1. Pourquoi Tester ?

8.2. Plan de Test Mensuel

8.3. Tests PITR

9. Disaster Recovery : Plan d'Action

9.1. Scénarios de Sinistre

9.2. Runbook : Récupération Après Sinistre

Étape 3 : Choix de la Stratégie

Étape 4 : Restauration PITR (si applicable)

Étape 5 : Restauration Complète

Étape 6 : Validation

Étape 7 : Post-Mortem

12.2. Script de Vérification

13. Ressources et Documentation

Documentation Officielle

Outils

Guides et Blogs

Conclusion