Chapitre 11.5 — Sauvegardes de base

⏱️ TL;DR — Le chapitre le plus important de la partie, peut-être du cours. La base contient ce que tu ne peux pas reconstruire : sauvegarde-la, ou prépare-toi à tout perdre. Tu vas dumper ta base (pg_dump, format custom -Fc ; mysqldump côté MySQL), automatiser ces dumps par cron avec des noms horodatés et une rotation (garder N jours), les stocker ailleurs que sur le VPS (offsite — un serveur qui brûle emporte ses backups locaux), les chiffrer (ce sont des données personnelles, RGPD), et surtout restaurer pour de vrai. Le mantra à graver : « une sauvegarde non testée n’existe pas. » Un dump qu’on n’a jamais rejoué n’est pas une sauvegarde, c’est un espoir.

🎯 Objectifs

• Dumper une base PostgreSQL (pg_dump/pg_dumpall, formats) et MySQL (mysqldump).
• Automatiser les sauvegardes par cron avec horodatage et rotation.
• Stocker les dumps hors du VPS (offsite) et les chiffrer.
• Restaurer un dump (pg_restore, psql, mysql) et tester la restauration.
• Situer dump logique vs snapshot/PITR, et le lien avec la Partie 15.

Pourquoi c’est vital

Reprends la logique de l’ouverture de cette partie. Le VPS, Nginx, systemd, le code : reconstructibles. La base : non. Elle est la seule pièce dont la perte est définitive. Or les causes de perte sont nombreuses et banales : un DROP TABLE de trop, une migration ratée, un disque qui lâche, un serveur compromis, une erreur humaine à 2 h du matin. La question n’est pas si mais quand. Une stratégie de sauvegarde, c’est ce qui transforme une catastrophe en simple incident.

⚠️ Piège — Le pire piège de tout ce cours : ne pas avoir de sauvegarde du tout, ou croire en avoir une qui n’existe pas. « La base est sur un SSD fiable » n’est pas une sauvegarde. « Le fournisseur fait des snapshots » — en es-tu sûr, et sais-tu les restaurer ? Tant que tu n’as pas rejoué un dump dans une base neuve et vu tes données réapparaître, considère que tu n’as rien.

Dumper une base PostgreSQL

Un dump est un instantané logique de la base : un fichier d’où l’on peut la reconstruire entièrement. L’outil est pg_dump.

🐚 Au terminal — Dumper la base formacampus, au format recommandé :

# Format "custom" (-Fc) : compresse, et permet une restauration selective
pg_dump -U formacampus -h localhost -Fc formacampus > formacampus.dump
 
# Alternative lisible : un script SQL brut (plus gros, non compresse)
pg_dump -U formacampus -h localhost formacampus > formacampus.sql

Décryptage. pg_dump se connecte comme un client normal (-U le rôle, -h localhost l’hôte). Le drapeau -Fc choisit le format custom : compressé, et surtout restaurable sélectivement (une table précise) via pg_restore. Le chevron > redirige la sortie vers un fichier. Sans -Fc, tu obtiens un script SQL en clair : plus lisible et rejouable avec psql, mais plus volumineux.

Pour sauvegarder toute l’instance d’un coup (toutes les bases plus les rôles globaux), il existe pg_dumpall. Pratique pour une reprise complète, mais le dump par base (pg_dump -Fc) est plus souple au quotidien.

Dumper une base MySQL/MariaDB

Le pendant côté MySQL s’appelle mysqldump, dans le même esprit :

mysqldump -u formacampus -p formacampus > formacampus.sql
# -u l'utilisateur, -p demande le mot de passe, puis le nom de la base
# le chevron ">" redirige le SQL genere vers un fichier

Le résultat est un script SQL qui recrée schéma et données. On le compresse volontiers à la volée (par exemple en le passant dans gzip) pour gagner de la place.

Automatiser par cron

Une sauvegarde qu’il faut penser à lancer ne sera pas lancée. On l’automatise avec cron, le planificateur de tâches d’Unix. D’abord un script qui dumpe avec un nom horodaté et fait le ménage :

#!/usr/bin/env bash
set -euo pipefail                        # arret immediat en cas d'erreur
 
BACKUP_DIR="/var/backups/postgres"
STAMP="$(date +%F_%H-%M)"                # ex : 2026-07-15_02-30
FILE="$BACKUP_DIR/formacampus_$STAMP.dump"
 
mkdir -p "$BACKUP_DIR"
 
# 1. Dump compresse
pg_dump -U formacampus -h localhost -Fc formacampus > "$FILE"
 
# 2. Rotation : supprimer les dumps de plus de 14 jours
find "$BACKUP_DIR" -name "formacampus_*.dump" -mtime +14 -delete

Le set -euo pipefail fait échouer bruyamment le script à la moindre erreur (plutôt que produire un dump vide en silence). date +%F_%H-%M génère l’horodatage qui rend chaque fichier unique. La ligne find ... -mtime +14 -delete est la rotation : elle efface les dumps vieux de plus de 14 jours, pour ne pas remplir le disque indéfiniment. Adapte la rétention à ton besoin (7, 14, 30 jours).

On rend le script exécutable, puis on l’inscrit à cron :

chmod +x /usr/local/bin/backup-db.sh
crontab -e        # ouvre l'editeur de la table cron

Et on ajoute une ligne (ici : tous les jours à 2 h 30) :

30 2 * * *  /usr/local/bin/backup-db.sh >> /var/log/backup-db.log 2>&1

Les cinq champs de cron sont minute heure jour-du-mois mois jour-de-semaine ; les * signifient « chaque ». Le >> ... 2>&1 journalise sortie et erreurs dans un fichier, pour vérifier après coup que le backup a bien tourné. (Cron et les timers systemd sont détaillés en Partie 15.)

💡 Réflexe — Un backup silencieux est un backup en qui tu ne peux pas avoir confiance. Fais loguer ton script, et va plus loin : un healthcheck (un ping à un service de monitoring en fin de script) qui t’alerte si le backup n’a pas tourné. Le jour où le cron casse, tu veux l’apprendre tout de suite, pas le jour de la restauration.

Stocker ailleurs : l’offsite

Un dump posé à côté de la base, sur le même disque du même VPS, ne protège que du DROP TABLE. Si le serveur brûle, est volé, ou que le disque meurt, le dump part avec. La règle d’or de la sauvegarde (3-2-1, détaillée en Partie 15) exige au moins une copie hors-site.

🔒 Sécurité — Un dump de base, ce sont toutes les données personnelles de tes utilisateurs dans un seul fichier : le pire à laisser traîner en clair. Deux impératifs. Un — chiffre les dumps avant de les envoyer ailleurs (par exemple avec gpg, ou un outil de backup qui chiffre nativement comme restic/borg). Deux — un dump chiffré exige de protéger sa clé : une sauvegarde chiffrée dont tu perds la clé est aussi perdue qu’une base effacée. Ce chiffrement n’est pas optionnel côté RGPD : la fuite d’un backup en clair est une violation de données (voir le cours RGPD).

En pratique : le script dumpe, chiffre, puis pousse le fichier vers un stockage distant (objet type S3, un autre serveur, un espace de backup dédié). Les outils restic et borg (Partie 15) font dump distant + chiffrement + déduplication + rotation en une commande — la solution robuste quand on dépasse le script maison.

Restaurer — et TESTER la restauration

Voici le cœur du chapitre. Sauvegarder ne sert à rien si on ne sait pas restaurer. Les commandes selon le format :

# PostgreSQL, dump format custom (-Fc) -> pg_restore
pg_restore -U formacampus -h localhost -d formacampus_restore formacampus.dump
 
# PostgreSQL, dump SQL brut -> on le rejoue avec psql
psql -U formacampus -h localhost -d formacampus_restore < formacampus.sql
 
# MySQL/MariaDB, dump SQL -> on le rejoue avec mysql
mysql -u formacampus -p formacampus_restore < formacampus.sql

Pour un dump -Fc, pg_restore reconstruit dans la base cible (-d). Pour un dump SQL brut, on rejoue le fichier avec < qui envoie son contenu dans psql ou mysql. Note qu’on restaure ici dans une base formacampus_restore distincte : on ne restaure jamais directement par-dessus la prod lors d’un test.

⚠️ Piège — La sauvegarde jamais testée. C’est le drame classique : des mois de dumps sagement empilés, et le jour de la vraie panne, on découvre qu’ils sont corrompus, vides (le script échouait en silence), ou impossibles à rejouer (mauvaise version, dépendances manquantes). Grave la phrase : une sauvegarde non testée n’existe pas. Un backup n’est réel que le jour où tu l’as rejoué avec succès.

🐚 Au terminal — Le test de restauration, à faire régulièrement (idéalement automatisé) :

# 1. Creer une base cible de test
sudo -u postgres psql -c "CREATE DATABASE formacampus_restore OWNER formacampus;"
 
# 2. Y restaurer le dernier dump
pg_restore -U formacampus -h localhost -d formacampus_restore /var/backups/postgres/formacampus_LATEST.dump
 
# 3. Verifier que les donnees sont bien la
psql -U formacampus -h localhost -d formacampus_restore -c "SELECT count(*) FROM utilisateurs;"
 
# 4. Nettoyer
sudo -u postgres psql -c "DROP DATABASE formacampus_restore;"

Ce cycle prouve que le dump est exploitable. L’idéal : l’automatiser (un restore hebdomadaire dans une base jetable, avec vérification et alerte). Un backup testé automatiquement, c’est la différence entre dormir tranquille et prier.

Dump logique vs snapshot/PITR (survol)

Le dump logique (pg_dump/mysqldump) que tu viens de voir est portable, lisible, sélectif — parfait pour la plupart des besoins. Il a une limite : il capture un instant ; entre deux dumps quotidiens, tu peux perdre jusqu’à 24 h d’écritures.

Pour aller plus fin, deux approches, citées en survol :

• Snapshots : une image du disque entier, prise par le fournisseur ou l’hyperviseur. Rapide, mais moins granulaire (toute la machine) et propre au fournisseur.
• PITR (Point-In-Time Recovery) : PostgreSQL peut archiver son journal des transactions (les WAL), ce qui permet de rejouer l’historique et de restaurer à n’importe quelle seconde passée. C’est la sauvegarde des exigences élevées (perte de données quasi nulle), plus complexe à opérer. On la mentionne ; sa mise en œuvre relève de la Partie 15 et de la doc.

📚 La doc — Pour la référence exacte des drapeaux (pg_dump, pg_restore, mysqldump) et pour PITR/WAL, la doc officielle PostgreSQL (chapitres « Backup and Restore » et « Continuous Archiving ») fait autorité, ainsi que man pg_dump. N’improvise pas une stratégie PITR sans la lire : c’est puissant mais exigeant.

🧭 Sur FormaCampus — La base d’apprenants est sauvegardée par un cron quotidien à 2 h 30 : pg_dump -Fc, dump chiffré avec gpg, puis poussé offsite vers un stockage objet dans une autre région (jamais uniquement sur le VPS). Rétention de 14 jours en rotation. Surtout : un test de restauration hebdomadaire rejoue le dernier dump dans une base jetable et compte les lignes — avec alerte si ça échoue. Le chiffrement et l’offsite couvrent l’obligation RGPD (cours dédié) ; le test garantit que « on a des backups » veut vraiment dire « on peut restaurer ». Quand le trafic grandira, l’équipe ajoutera du PITR (Partie 15).

✏️ Exercices

Exercice 1 — Écris la sauvegarde. Écris une commande pg_dump qui produit un dump compressé et restaurable sélectivement de la base boutique (rôle boutique, en local), vers un fichier horodaté. Puis la commande cron pour l’exécuter chaque nuit à 3 h.

pg_dump -U boutique -h localhost -Fc boutique > /var/backups/postgres/boutique_$(date +%F).dump

0 3 * * *  /usr/local/bin/backup-boutique.sh >> /var/log/backup.log 2>&1

Exercice 2 — La restauration qui rate. Un collègue affirme : « On est tranquilles, le cron de pg_dump tourne tous les jours depuis six mois. » Quelle question critique poses-tu, et quel test proposes-tu ?

🧠 Quiz de révision

Fin de la Partie 11. Ta base est installée, verrouillée, tunée et sauvegardée pour de vrai. On passe à l’industrialisation : Partie 12 — Docker & conteneurs, pour reproduire cet environnement (app + base) de façon portable et jetable.