Sauvegarde et récupération des bases de données PostgreSQL : Un guide complet pour les utilisateurs d’AlexHost

Pourquoi la stratégie de sauvegarde PostgreSQL est plus importante que vous ne le pensez

La perte de données n’est pas un risque hypothétique — c’est une certitude opérationnelle que chaque administrateur de base de données devra affronter à un moment donné. Les défaillances matérielles, les suppressions accidentelles, les transactions corrompues et les attaques par ransomware peuvent tous paralyser un environnement de production en quelques secondes. Pour les utilisateurs de PostgreSQL, disposer d’une stratégie de sauvegarde robuste, testée et automatisée est la différence entre un incident mineur et une défaillance commerciale catastrophique.

AlexHost Dedicated Servers fournissent une base idéale pour héberger et protéger les bases de données PostgreSQL. Avec un stockage NVMe SSD de qualité entreprise offrant un débit d’E/S exceptionnel, un accès root complet pour un contrôle de configuration total et une protection DDoS intégrée, AlexHost vous offre les performances d’infrastructure et la posture de sécurité que les charges de travail de base de données sérieuses exigent.

Que vous exécutiez une plateforme e-commerce à fort trafic, une application SaaS, une installation WordPress soutenue par une base de données relationnelle ou un système d’entreprise personnalisé, ce guide vous guide à travers chaque méthode majeure de sauvegarde et de récupération PostgreSQL — des simples vidages SQL à la récupération avancée à un point dans le temps (PITR) — tout optimisé pour les environnements de production.

Table des matières

1. Comprendre les options de sauvegarde PostgreSQL
2. Prérequis et exigences de privilèges
3. Méthode 1 — Vidage SQL avec pg_dump
4. Méthode 2 — Sauvegarde de toutes les bases de données avec pg_dumpall
5. Méthode 3 — Sauvegardes au format personnalisé pour les grandes bases de données
6. Restauration à partir de vidages SQL
7. Restauration à partir de vidages au format personnalisé
8. Méthode 4 — Archivage continu et récupération à un point dans le temps (PITR)
9. Automatisation des sauvegardes avec Cron
10. Sécurisation et stockage des sauvegardes hors site
11. Résumé des meilleures pratiques

1. Comprendre les options de sauvegarde PostgreSQL

PostgreSQL est livré avec plusieurs mécanismes de sauvegarde matures et bien documentés. Le choix du bon dépend de la taille de votre base de données, de vos objectifs de temps de récupération (RTO), de vos objectifs de point de récupération (RPO) et de votre tolérance à la complexité opérationnelle.

Comprendre ces compromis avant de commencer est essentiel. La plupart des environnements de production bénéficient de la combinaison d’au moins deux approches — par exemple, des vidages au format personnalisé nightly aux côtés de l’archivage WAL continu pour une capacité de récupération granulaire.

2. Prérequis et exigences de privilèges

Avant d’exécuter toute opération de sauvegarde, confirmez que les prérequis suivants sont en place :

Privilèges utilisateur :

• Vous devez être un superutilisateur PostgreSQL ou le propriétaire de la base de données cible pour effectuer une sauvegarde complète.
• Pour pg_dumpall, les privilèges de superutilisateur sont obligatoires.

Vérifiez votre version PostgreSQL :

psql --version

Vérifiez l’espace disque disponible avant la sauvegarde :

df -h /var/lib/postgresql/

Assurez-vous que votre destination de sauvegarde dispose d’un espace libre suffisant — au minimum 1,5× la taille de la base de données en cours de sauvegarde pour tenir compte des fichiers temporaires et de la surcharge de compression.

Connectez-vous à votre serveur via SSH :

ssh root@your-server-ip

Si vous utilisez un plan VPS Hosting, vous aurez un accès SSH complet et la possibilité d’installer, configurer et gérer PostgreSQL sans restriction.

3. Méthode 1 — Vidage SQL avec pg_dump

L’utilitaire pg_dump est l’outil de sauvegarde PostgreSQL le plus couramment utilisé. Il produit un snapshot cohérent d’une seule base de données, même pendant que la base de données est activement utilisée. La sortie est un script SQL en texte brut qui peut être examiné, édité et rejoué sur n’importe quelle installation PostgreSQL compatible.

Étape 1 : Ouvrez un terminal et accédez à votre serveur

ssh root@your-alexhost-server-ip

Étape 2 : Exécutez la commande pg_dump

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Décomposition des paramètres :

Exemple pratique :

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Conseil professionnel : Ajouter un horodatage en utilisant $(date +%Y%m%d_%H%M%S) à votre nom de fichier de sauvegarde garantit que vous ne remplacez jamais accidentellement une sauvegarde précédente et crée une archive chronologiquement naturelle.

Étape 3 : Vérifiez le fichier de sauvegarde

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

Le fichier doit commencer par des commentaires d’en-tête PostgreSQL et des déclarations SET, confirmant qu’un vidage valide a été créé.

4. Méthode 2 — Sauvegarde de toutes les bases de données avec pg_dumpall

Lorsque vous devez sauvegarder chaque base de données dans une instance PostgreSQL — y compris les objets globaux tels que les rôles et les espaces de table — pg_dumpall est l’outil approprié.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

Cette commande exporte :

• Toutes les bases de données
• Tous les rôles (utilisateurs et groupes)
• Tous les espaces de table
• Toute la configuration globale

Important : Le fichier de sortie de pg_dumpall peut être très volumineux sur les serveurs occupés. Assurez-vous que votre partition de sauvegarde dispose d’un espace adéquat et envisagez de compresser la sortie immédiatement :

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Méthode 3 — Sauvegardes au format personnalisé pour les grandes bases de données

Pour les bases de données de production dépassant quelques gigaoctets, le format personnalisé (-F c) est fortement recommandé par rapport aux vidages SQL bruts. Les sauvegardes au format personnalisé sont :

• Compressées par défaut — réduisant considérablement les exigences de stockage
• Plus rapides à restaurer — supportant les opérations de restauration parallèle avec le drapeau -j
• Sélectivement restaurables — vous permettant de restaurer des tables ou des schémas individuels

Création d’une sauvegarde au format personnalisé

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Création d’une sauvegarde au format répertoire compressé (supporte le parallélisme)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

Cette approche réduit considérablement la durée de sauvegarde sur les serveurs multi-cœurs, ce qui la rend idéale pour les environnements de serveur dédié haute performance disponibles chez AlexHost.

6. Restauration à partir de vidages SQL

Restaurer une seule base de données à partir d’un vidage SQL brut

D’abord, assurez-vous que la base de données cible existe. Si ce n’est pas le cas, créez-la :

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Puis restaurez :

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Décomposition des paramètres :

Surveiller la progression de la restauration

Pour les grands fichiers SQL, vous pouvez surveiller la progression en utilisant pv :

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Restauration à partir de vidages au format personnalisé

Les vidages au format personnalisé nécessitent l’utilitaire pg_restore plutôt que psql.

Restauration de base

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Restaurer et créer la base de données automatiquement

Utilisez le drapeau -C pour instruire pg_restore de créer la base de données avant de la remplir :

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Restauration parallèle pour une récupération plus rapide

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

L’utilisation de -j 4 avec une sauvegarde au format répertoire peut réduire le temps de restauration jusqu’à 75% sur un serveur quad-core — un avantage significatif lors de la minimisation des temps d’arrêt pendant la récupération après sinistre.

Restaurer une table spécifique uniquement

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

Cette capacité granulaire est l’un des principaux avantages du format personnalisé par rapport aux vidages SQL bruts.

8. Méthode 4 — Archivage continu et récupération à un point dans le temps (PITR)

PITR est l’étalon-or pour les déploiements PostgreSQL critiques. Il vous permet de restaurer votre base de données à n’importe quel moment spécifique — pas seulement la dernière sauvegarde — en relisant les segments du journal d’écriture anticipée (WAL) au-dessus d’une sauvegarde de base. Ceci est essentiel pour les scénarios où vous devez récupérer d’une erreur logique (telle qu’un DROP TABLE accidentel) qui s’est produit à un horodatage connu.

Étape 1 : Activez l’archivage WAL dans postgresql.conf

Localisez et modifiez votre fichier de configuration PostgreSQL :

nano /etc/postgresql/15/main/postgresql.conf

Ajoutez ou modifiez les directives suivantes :

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Explication des paramètres :

Créez le répertoire d’archive et définissez les permissions correctes :

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Redémarrez PostgreSQL pour appliquer les modifications :

systemctl restart postgresql

Étape 2 : Prenez une sauvegarde de base avec pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Étape 3 : Restaurer à un point spécifique dans le temps

Pour restaurer à partir d’une sauvegarde de base et des archives WAL :

1. Arrêtez PostgreSQL :

systemctl stop postgresql

1. Effacez le répertoire de données existant :

rm -rf /var/lib/postgresql/15/main/*

1. Extrayez la sauvegarde de base :

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. Créez un recovery.conf (PostgreSQL 11 et antérieur) ou configurez postgresql.conf et créez un fichier recovery.signal (PostgreSQL 12+) :

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

Ajoutez à postgresql.conf :

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. Définissez la propriété correcte et démarrez PostgreSQL :

chown -R postgres:postgres /var/lib/postgresql/15/main/
systemctl start postgresql

PostgreSQL relira les segments WAL jusqu’à l’horodatage spécifié, puis passera à un état normal de lecture-écriture.

9. Automatisation des sauvegardes avec Cron

Les sauvegardes manuelles ne sont pas fiables. Automatiser votre calendrier de sauvegarde avec cron assure la cohérence et élimine le facteur d’erreur humaine.

Créer un script de sauvegarde

nano /usr/local/bin/pg_backup.sh

#!/bin/bash

# PostgreSQL Automated Backup Script
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="my_production_db"
RETENTION_DAYS=14

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Perform the backup
pg_dump -U "$DB_USER" -F c "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +"$RETENTION_DAYS" -delete

# Log completion
echo "[$TIMESTAMP] Backup of $DB_NAME completed successfully." >> /var/log/pg_backup.log

Rendez le script exécutable :

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

Planifier avec Cron

crontab -e

Ajoutez la ligne suivante pour exécuter une sauvegarde chaque nuit à 2h00 :

0 2 * * * /usr/local/bin/pg_backup.sh

Pour les sauvegardes complètes hebdomadaires plus l’archivage WAL quotidien incrémental, combinez ceci avec la configuration PITR décrite dans la section précédente.

10. Sécurisation et stockage des sauvegardes hors site

Une sauvegarde stockée sur le même serveur que votre base de données de production n’est pas une vraie sauvegarde — c’est un point de défaillance unique.