Des sauvegardes Postgres automatiques que vous pouvez vraiment restaurer

Une tâche cron qui exécute pg_dump chaque nuit et écrit un fichier quelque part n’est pas un système de sauvegarde. C’est un système de sauvegarde le jour où quelqu’un a réellement besoin de restaurer à partir de là, pas avant. Bien des équipes l’apprennent à leurs dépens : l’entrée cron a cessé de s’exécuter il y a trois mois à cause d’un changement de PATH que personne n’a remarqué, ou la sauvegarde nocturne écrit depuis des semaines un message d’erreur de 40 octets dans un fichier .dump au lieu d’une base de données, ou les sauvegardes sont assises juste à côté de la base qu’elles sont censées protéger et les deux disparaissent dans la même panne de serveur. Un fichier sur disque que personne n’a jamais restauré est une supposition, pas une sauvegarde.

À la fin de ce tutoriel, vous aurez un vrai système : un pg_dump scripté dans le bon format, une planification cron qui l’exécute réellement, la sauvegarde expédiée vers un espace de stockage qui n’est pas sur le même serveur, une politique de rétention qui purge les anciennes copies sans vous laisser à zéro sauvegarde, et surtout, une tâche planifiée qui restaure votre dernière sauvegarde dans une base jetable et vérifie qu’elle est vraiment bonne. C’est cette dernière partie qui fait la différence entre une sauvegarde et un espoir.

Ce dont vous avez besoin

• Un serveur Postgres que vous contrôlez : un VPS, une machine dédiée, ou n’importe quel serveur auto-hébergé exécutant Postgres avec pg_dump, pg_restore, psql et createdb/dropdb disponibles.
• Une destination hors serveur pour les sauvegardes : du stockage objet compatible S3 (AWS S3, DigitalOcean Spaces, Backblaze B2, Cloudflare R2 ou autre) et soit l’AWS CLI, soit rclone configuré avec les identifiants pour y accéder. Tout l’intérêt de cet exercice est que la sauvegarde ne vit pas sur le même disque que la base de données, donc sauter cette étape annule le système.
• Un accès shell pour planifier des tâches cron sur le serveur Postgres (ou partout où vous exécutez des tâches planifiées).
• Assez d’espace disque sur le serveur pour qu’une sauvegarde atterrisse localement avant d’être expédiée, et le même espace à nouveau pour une base de données de test de restauration lors de l’étape de vérification.

Un nom de domaine ne fait pas partie de tout ça. Vous sauvegardez des données, pas du trafic web.

Étape 1 : scripter la sauvegarde

Utilisez le format personnalisé de pg_dump (-Fc, équivalent à --format=custom). Il est compressé par défaut, et contrairement à une sauvegarde SQL en texte brut, c’est ce que pg_restore attend, ce qui vous donne des restaurations sélectives et un format que pg_restore peut paralléliser plus tard si besoin.

Ajoutez --no-owner et --no-acl pour que la sauvegarde ne contienne pas d’instructions ALTER OWNER et GRANT/REVOKE liées à des rôles qui pourraient ne pas exister (ou avoir des noms différents) là où vous la restaurerez éventuellement, y compris votre base de vérification à l’étape 5.

#!/usr/bin/env bash
set -euo pipefail

DB_NAME="myapp_production"
DB_USER="myapp"
BACKUP_DIR="/var/backups/postgres"
TIMESTAMP="$(date +%Y%m%d%H%M%S)"
DUMP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"

mkdir -p "${BACKUP_DIR}"

pg_dump -Fc --no-owner --no-acl \
  -U "${DB_USER}" -d "${DB_NAME}" \
  -f "${DUMP_FILE}"

if [ ! -s "${DUMP_FILE}" ]; then
  echo "pg_dump produced an empty file, aborting" >&2
  exit 1
fi

echo "Backed up ${DB_NAME} to ${DUMP_FILE}"

set -euo pipefail compte ici : sans ça, un pg_dump qui échoue peut quand même laisser le script continuer et rapporter un succès. La vérification [ ! -s "${DUMP_FILE}" ] attrape le cas précis où pg_dump se termine proprement mais le fichier écrit est vide, ce qui arrive plus souvent qu’on le pense quand le disque de destination est plein.

Vous devriez voir un nouveau fichier dans /var/backups/postgres avec une taille qui suit à peu près la taille réelle de votre base (une sauvegarde au format personnalisé est compressée, donc ne vous attendez pas à un rapport 1:1, mais si une base de 500 Mo produit un fichier de 2 Ko, c’est le signe que quelque chose cloche).

Étape 2 : planifier avec cron

Les champs d’une crontab vont dans cet ordre : minute, heure, jour du mois, mois, jour de la semaine. Une exécution nocturne à 2 h 00 ressemble à ça :

0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Deux détails qui causent la plupart des incidents du type « mes sauvegardes se sont arrêtées sans que je m’en aperçoive » :

• cron s’exécute avec un environnement minimal, pas celui de votre shell interactif. Si votre script appelle pg_dump, aws ou rclone par leur nom et compte sur le PATH de votre shell pour les trouver, le PATH réduit de cron peut ne pas inclure le répertoire où ils se trouvent. Utilisez des chemins absolus dans le script (/usr/bin/pg_dump, /usr/local/bin/aws) ou définissez PATH explicitement en haut du script.
• Redirigez à la fois stdout et stderr vers un fichier journal (le >> ... 2>&1 ci-dessus). Sans ça, cron envoie les échecs par courriel à une boîte que personne ne lit, ou les laisse tomber silencieusement selon la configuration du système, et vous ne le découvrez que lorsqu’une restauration échoue.

Vous devriez voir une nouvelle ligne ajoutée à /var/log/pg_backup.log chaque nuit, et un nouveau fichier .dump dans /var/backups/postgres avec un horodatage frais. Si une journée passe sans nouveau fichier et sans nouvelle ligne dans le journal, cron n’a tout simplement pas exécuté la tâche, ce qui est un échec différent d’une tâche qui s’exécute et qui échoue.

Étape 3 : expédier la sauvegarde hors serveur

Une sauvegarde qui reste sur le même disque que la base qu’elle protège ne vous protège pas contre la panne qui compte le plus : la perte du serveur. Poussez-la ailleurs immédiatement après son écriture.

Avec l’AWS CLI, vers S3 ou n’importe quel point de terminaison compatible S3 :

aws s3 cp "${DUMP_FILE}" "s3://my-backups-bucket/postgres/" --only-show-errors

Pour un fournisseur compatible S3 autre qu’AWS, ajoutez --endpoint-url :

aws s3 cp "${DUMP_FILE}" "s3://my-backups-bucket/postgres/" \
  --endpoint-url "https://nyc3.digitaloceanspaces.com" \
  --only-show-errors

Ou avec rclone, après avoir configuré un remote une fois avec rclone config :

rclone copy "${DUMP_FILE}" "myremote:my-backups-bucket/postgres/"

Ajoutez l’une ou l’autre commande à la fin de votre script de sauvegarde, et vérifiez son code de sortie de la même façon que celui de pg_dump :

if ! aws s3 cp "${DUMP_FILE}" "s3://my-backups-bucket/postgres/" --only-show-errors; then
  echo "upload to S3 failed for ${DUMP_FILE}" >&2
  exit 1
fi

Vous devriez voir l’objet apparaître dans votre bucket (aws s3 ls s3://my-backups-bucket/postgres/ ou l’équivalent dans le tableau de bord de votre fournisseur) presque aussitôt que la tâche cron se termine, avec une taille correspondant au fichier local.

Étape 4 : purger les anciennes sauvegardes

Sans politique de rétention, votre répertoire de sauvegardes et votre bucket grossissent indéfiniment. Avec une politique trop agressive, vous pouvez purger jusqu’à zéro sauvegarde juste au moment où vous en avez besoin. La règle la plus sûre a deux volets : conserver au moins un nombre minimum de sauvegardes récentes peu importe leur âge, et supprimer séparément tout ce qui est plus vieux que N jours et qui n’en fait pas partie.

En local :

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=30
KEEP_MIN=7

mapfile -t ALL_DUMPS < <(ls -1t "${BACKUP_DIR}"/*.dump 2>/dev/null)

for i in "${!ALL_DUMPS[@]}"; do
  if [ "$i" -lt "${KEEP_MIN}" ]; then
    continue
  fi
  file="${ALL_DUMPS[$i]}"
  if [ -n "$(find "${file}" -mtime "+${KEEP_DAYS}")" ]; then
    rm -f "${file}"
    echo "pruned ${file}"
  fi
done

ls -1t liste les sauvegardes des plus récentes aux plus vieilles, donc la boucle saute sans condition les KEEP_MIN fichiers les plus récents, puis ne supprime que les plus vieux qui échouent aussi le test d’âge. Ça veut dire qu’une semaine tranquille sans nouvelle sauvegarde ne vous laisse jamais avec moins de KEEP_MIN sauvegardes.

Pour la copie distante, la plupart des fournisseurs compatibles S3 offrent une règle de cycle de vie de bucket qui expire les objets après N jours, ce qui est plus simple que de scripter la purge à distance vous-même ; réglez-la sur une valeur au moins aussi longue que KEEP_DAYS pour ne jamais purger la seule copie hors serveur qu’il vous reste.

Exécutez ça dans une deuxième entrée cron, séparée du script de sauvegarde, pour qu’un bug de purge ne puisse pas accidentellement s’exécuter dans le même script qui essaie de créer une sauvegarde fraîche :

30 2 * * * /usr/local/bin/pg_prune.sh >> /var/log/pg_backup.log 2>&1

Vous devriez voir disparaître les anciens fichiers .dump de /var/backups/postgres avec le temps, tout en gardant toujours au moins KEEP_MIN sauvegardes récentes, et une ligne « pruned … » dans le journal chaque fois qu’une sauvegarde est retirée.

Étape 5 : restaurer et vérifier, sur une base planifiée

C’est l’étape que presque tout le monde saute, et c’est celle qui compte vraiment. Un fichier de sauvegarde qui existe et dont la taille n’est pas nulle vous dit que pg_dump s’est exécuté. Ça ne vous dit rien sur l’intégrité des données qu’il contient, sur la validité du format, ou sur le fait qu’une restauration fonctionnerait vraiment le jour où vous en aurez besoin. La seule façon de savoir qu’une sauvegarde est bonne, c’est de la restaurer et de vérifier ce qui en revient.

Automatisez ça vers une base de données jetable sur le même serveur (ou un serveur séparé, si vous préférez ne pas ajouter de charge de restauration à la production) :

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgres"
LATEST_DUMP="$(ls -1t "${BACKUP_DIR}"/*.dump | head -n1)"
VERIFY_DB="restore_check_$(date +%Y%m%d)"
MIN_EXPECTED_ROWS=1

createdb "${VERIFY_DB}"

pg_restore --no-owner --no-acl -d "${VERIFY_DB}" "${LATEST_DUMP}"

ROW_COUNT="$(psql -d "${VERIFY_DB}" -tAc "SELECT count(*) FROM users;")"

dropdb "${VERIFY_DB}"

if [ "${ROW_COUNT}" -lt "${MIN_EXPECTED_ROWS}" ]; then
  echo "restore verification failed: ${ROW_COUNT} rows in users table" >&2
  exit 1
fi

echo "restore verified: ${LATEST_DUMP}, ${ROW_COUNT} rows in users table"

Remplacez users par n’importe quelle table pour laquelle vous pouvez raisonnablement affirmer un nombre minimum de lignes ; l’intérêt de la requête n’est pas d’être exhaustive, c’est de prouver que la restauration a produit une base de données fonctionnelle avec de vraies données dedans, pas juste une base de données qui existe. pg_restore lui-même échouera aussi bruyamment sur une sauvegarde vraiment corrompue ou tronquée, donc un code de sortie non nul de l’étape de restauration à lui seul est déjà un signal utile, indépendant de la vérification du nombre de lignes.

Planifiez ça après votre tâche de rétention, et moins fréquemment que la sauvegarde elle-même, une fois par semaine est un point de départ raisonnable pour la plupart des équipes :

0 3 * * 0 /usr/local/bin/pg_restore_check.sh >> /var/log/pg_backup.log 2>&1

Vous devriez voir une ligne « restore verified: … » dans le journal chaque semaine, avec un nombre de lignes qui semble correct pour vos données. Si cette ligne cesse d’apparaître, ou commence à rapporter un nombre de lignes anormalement bas, vous venez d’attraper une mauvaise sauvegarde pendant que vous avez encore de bonnes sauvegardes plus anciennes sur lesquelles vous rabattre, au lieu de le découvrir pendant une vraie panne.

Le raccourci : faites-le avec Railyard

Tout ce qui précède est la version honnête, construite de zéro. Railyard exécute la même boucle pour vous sur chaque base Postgres qu’il gère, sans les scripts.

Quand vous provisionnez une base de données sur un serveur géré par Railyard, elle planifie automatiquement ses propres sauvegardes selon un intervalle que vous pouvez ajuster par base de données (une journée est un défaut raisonnable pour la plupart des applications). Chaque sauvegarde est vérifiée par Railyard en la restaurant réellement et en la contrôlant, pas juste en vérifiant qu’un fichier existe, donc vous pouvez voir d’un coup d’œil si votre sauvegarde la plus récente est bonne au lieu de le présumer. Restaurer se fait en un clic depuis la page de la base de données, sans script à retenir et sans base jetable à nettoyer vous-même ensuite. La purge est gérée exactement comme vous voudriez la gérer vous-même : Railyard conserve un nombre configurable de vos sauvegardes réussies les plus récentes et supprime tout ce qui est plus vieux qu’un nombre configurable de jours et qui n’en fait pas partie, donc vous n’êtes jamais à une mauvaise purge près de vous retrouver à zéro.

Pour Postgres en particulier, vous pouvez aussi activer la restauration à un point dans le temps par serveur depuis la page du serveur, ce qui complète les sauvegardes planifiées : au lieu de pouvoir seulement restaurer au moment de votre dernière sauvegarde, vous pouvez récupérer n’importe quel instant couvert par la fenêtre de récupération. Plus de détails, incluant la façon dont les bases de données sont provisionnées et connectées, se trouvent dans Bases de données et sauvegardes.

Si vous démarrez une nouvelle application plutôt que d’ajouter des sauvegardes à une application existante, le guide de démarrage rapide et le déploiement depuis un dépôt couvrent la mise en route de l’application et de sa base de données sur un serveur en premier lieu.

Dépannage

La tâche cron fonctionnait avant et ne fonctionne plus, sans erreur nulle part. C’est presque toujours le problème de PATH de l’étape 2 : un script qui fonctionnait bien à l’essai en interactif échoue silencieusement sous l’environnement minimal de cron, parce que pg_dump, aws ou rclone ne se trouvent pas là où cron s’attend à les trouver. Utilisez des chemins absolus pour chaque binaire que le script appelle, ou définissez PATH explicitement en haut du script, et confirmez que la tâche s’exécute réellement en vérifiant l’horodatage du fichier journal, pas seulement l’absence d’erreur visible.

Le fichier de sauvegarde existe mais est vide ou tronqué. La cause la plus fréquente est le disque de destination qui se remplit en cours d’écriture, surtout si le répertoire de sauvegardes partage un disque avec le répertoire de données de la base elle-même ou avec un fichier journal qui grossit. pg_dump peut se terminer avec un code non nul dans ce cas, ce qui est exactement pourquoi le script vérifie le code de sortie et la taille du fichier séparément plutôt que de faire confiance à l’un ou l’autre seul.

Personne n’a jamais testé une restauration, et les sauvegardes sont silencieusement brisées depuis des mois. C’est le scénario que l’étape 5 existe pour prévenir. Une sauvegarde corrompue ou incomplète peut rester dans votre bucket en ayant l’air parfaitement saine tant que personne n’essaie de s’en servir. Planifier la tâche de restauration et vérification, pas juste la documenter comme quelque chose à faire manuellement un jour, est ce qui ferme réellement cette brèche.

Les identifiants pour la destination hors serveur ont expiré ou ont été renouvelés. Une clé d’accès S3 ou les identifiants d’un remote rclone peuvent expirer ou être révoqués indépendamment de tout ce qui se passe sur le serveur Postgres, et l’étape de téléversement va commencer à échouer pendant que la sauvegarde locale continue de réussir. Vérifier le code de sortie de la commande de téléversement (étape 3) attrape ça immédiatement plutôt que des semaines plus tard, quand vous cherchez une copie hors serveur qui n’a jamais vraiment été écrite.

FAQ

À quelle fréquence les sauvegardes devraient-elles s’exécuter ? Ça dépend de la quantité de données que vous pouvez vous permettre de perdre, votre objectif de point de récupération. Une fois par jour est un défaut raisonnable pour la plupart des applications. Si vos données changent assez vite pour qu’une perte même d’une journée serait vraiment problématique, exécutez pg_dump plus souvent, ou ajoutez l’archivage WAL pour la récupération à un point dans le temps entre les sauvegardes.

Est-ce que pg_dump suffit, ou faut-il l’archivage WAL et la récupération à un point dans le temps ? pg_dump vous donne un instantané propre et cohérent au moment précis où il s’est exécuté. L’archivage WAL continu (archive_mode, archive_command, et des sauvegardes de base périodiques avec pg_basebackup) vous permet de rejouer les changements jusqu’à un instant précis avec recovery_target_time, donc vous pouvez récupérer juste avant une mauvaise migration ou une suppression accidentelle, pas seulement jusqu’à la sauvegarde de la nuit dernière. Pour la plupart des équipes, les sauvegardes planifiées avec pg_dump couvrent le cas courant (perte de serveur, corruption), et la récupération à un point dans le temps vaut la peine d’être ajoutée une fois que perdre ne serait-ce que quelques heures de données entre deux sauvegardes ferait vraiment mal.

Comment savoir qu’une sauvegarde est vraiment bonne ? Seule une restauration réussie le prouve. Un fichier qui existe, qui a une taille raisonnable, et un pg_dump qui se termine avec un code 0 sont tous nécessaires mais pas suffisants ; le seul vrai test est de la restaurer quelque part et de vérifier que les données obtenues correspondent à ce que vous attendez, ce qui est exactement ce que l’étape 5 automatise.

Où devrait vraiment vivre la copie hors serveur ? N’importe où sauf sur le même disque, idéalement même pas chez le même fournisseur ou dans la même région que le serveur de base de données, pour qu’une seule panne de fournisseur ou un problème de compte ne puisse pas emporter à la fois votre base de données et ses sauvegardes.

Faut-il chiffrer les fichiers de sauvegarde ? Si votre destination de stockage objet prend en charge le chiffrement côté serveur au repos, l’activer ne coûte rien et vaut la peine. Si vous voulez un chiffrement indépendant du fournisseur de stockage, vous pouvez faire passer la sauvegarde par gpg avant de l’expédier, même si ça ajoute une clé qu’il faut maintenant aussi sauvegarder correctement, ce qui est un risque en soi qui mérite réflexion avant de l’ajouter.

Une sauvegarde que vous n’avez jamais restaurée est une croyance, pas un fait. Les cinq étapes ci-dessus transforment cette croyance en quelque chose que vous vérifiez réellement sur une base planifiée, pour que la première fois que vous restaurez une sauvegarde ne soit pas aussi la première fois que vous découvrez si elle fonctionne.