2024-

2025

Sauvegarde et Restauration de Don

Prof : Mr ANAKPA Manawa

Augustin Awè KOURATE & ABI TCHAA WAZAM & ISRAEL AHOLOU

| Ecole Polytechnique de Lomé

Table des matières
Introduction................................................................................................................. 2
I. Méthodes de Sauvegarde..................................................................................... 2
1. Sauvegarde SQL (pg_dump et pg_dumpall).......................................................2
2. Sauvegarde au Niveau du Système de Fichiers.................................................3
3. Archivage Continu et Récupération à un Instant Donné (PITR)..........................4
II. Méthodes de Restauration.................................................................................... 5
1. Restauration d'une Sauvegarde SQL.................................................................5
2. Restauration d'une Sauvegarde au Niveau du Système de Fichiers..................5
3. Restauration à partir d'un Archivage Continu (PITR).........................................5
Conclusion................................................................................................................... 7
Introduction

La sauvegarde régulière des données est une tâche essentielle pour tout système
de gestion de base de données (SGBD) sérieux comme PostgreSQL. Sans une
sauvegarde récente, il est impossible de récupérer les données après un dommage
grave, tel qu'une perte de disque, un incendie ou une suppression accidentelle de
table. PostgreSQL offre trois approches fondamentales et distinctes pour
sauvegarder les données :
La sauvegarde SQL.
La sauvegarde au niveau du système de fichiers.
L'archivage continu.
Chacune de ces méthodes présente ses propres avantages et inconvénients. La
fiabilité du système de base de données est une propriété fondamentale, et
PostgreSQL met tout en œuvre pour la garantir, notamment en s'assurant que
toutes les données validées par une transaction sont stockées dans un espace non
volatile, résistant aux coupures de courant, aux erreurs du système d'exploitation et
aux problèmes matériels.

I. Méthodes de Sauvegarde
1. Sauvegarde SQL (pg_dump et pg_dumpall)
La sauvegarde SQL consiste à produire un fichier texte contenant des commandes
SQL qui, lorsqu'elles sont rejouées sur le serveur, recréent une base de données
identique à celle sauvegardée. L'outil principal pour cela est pg_dump.
 Fonctionnement de pg_dump :
pg_dump est un programme client PostgreSQL "intelligent". Il écrit son
résultat sur la sortie standard, mais peut aussi créer des fichiers dans
d'autres formats permettant le parallélisme et un contrôle plus fin de la
restauration.
Il ne bloque pas les autres opérations sur la base pendant son
exécution (sauf celles nécessitant un verrou exclusif, comme la plupart
des ALTER TABLE).
La sauvegarde est cohérente ; elle représente une image de la base
de données au moment où pg_dump commence.
Avantages : La sortie de pg_dump peut généralement être rechargée
dans des versions plus récentes de PostgreSQL, et c'est la seule
méthode qui fonctionne pour transférer une base de données vers une
machine d'une architecture différente (par exemple, de bits à bits).
Droits : Pour sauvegarder une base complète, pg_dump nécessite un
accès en lecture à toutes les tables, ce qui implique souvent d'utiliser
un superutilisateur.
 pg_dumpall :
 Sauvegarde toutes les bases de données d'une instance PostgreSQL
et préserve les informations communes à l'instance, telles que les
rôles et les tablespaces.
Contrairement à pg_dump, les images des différentes bases de
données ne sont pas synchronisées0.
Les données globales à l'instance peuvent être sauvegardées seules
avec l'option --globals-only de pg_dumpall0.
 Options de connexion : Les options de ligne de commande -h serveur et -p
port sont utilisées pour spécifier le serveur, et -U ou la variable
d'environnement PGUSER pour l'utilisateur. Les connexions de pg_dump sont
soumises aux mécanismes d'authentification clients normaux.
 Sauvegarde parallélisée : L'option -j num de pg_dump permet de
paralléliser la sauvegarde de plusieurs tables à la fois, en utilisant le format
de répertoire.

2. Sauvegarde au Niveau du Système de Fichiers

Cette stratégie consiste à copier directement les fichiers utilisés par PostgreSQL
pour le stockage des données.
 Restrictions :
Le serveur de base de données doit être arrêté pour obtenir une
sauvegarde utilisable, car les outils de copie ne créent pas une image
atomique de l'état du système de fichiers, et à cause du cache disque
du serveur.
Cette méthode ne permet pas de sauvegarder et de restaurer des
tables ou des bases de données particulières de manière isolée ; elle
fonctionne uniquement pour des sauvegardes et restaurations
complètes d'une instance.
 Image cohérente (Consistent Snapshot) : Si le système de fichiers
supporte cette fonctionnalité (par exemple, via LVM ou ZFS), il est possible de
créer une "image gelée" du volume contenant la base de données pendant
que le serveur est en cours d'exécution. La copie du répertoire de données
est ensuite effectuée à partir de cette image. Cependant, il est crucial
d'inclure les journaux de transactions (WAL) dans la sauvegarde, car
PostgreSQL rejouera ces journaux au démarrage à partir des données
sauvegardées.
 Utilisation de rsync : Une approche avec un arrêt minimal consiste à
exécuter rsync une première fois pendant que le serveur est en marche, puis
à arrêter le serveur brièvement pour exécuter rsync --checksum une seconde
fois. Cette deuxième exécution est rapide car peu de données sont
transférées.
 Taille de la sauvegarde : Généralement, une sauvegarde au niveau des
fichiers de données est plus volumineuse qu'une sauvegarde SQL, mais elle
peut être plus rapide.
 Exclusions recommandées : Il est souvent judicieux d'exclure les fichiers
du sous-répertoire pg_wal/, les répertoires temporaires (pgsql_tmp/), et les
 slots de réplication (pg_replslot/) de la sauvegarde directe, car ils sont soit
réinitialisés au démarrage, soit peuvent causer des problèmes si copiés....

3. Archivage Continu et Récupération à un Instant Donné (PITR)

Cette méthode combine une sauvegarde au niveau du système de fichiers avec
l'archivage continu des journaux de transactions (Write-Ahead Log ou WAL). Les
WAL enregistrent chaque modification effectuée sur les fichiers de données.
 Bénéfices majeurs :
Pas besoin de sauvegarde parfaitement cohérente : Toute
incohérence dans la sauvegarde de base est corrigée par le rejeu des
journaux.
Sauvegarde continue : Particulièrement utile pour les grandes bases
de données où des sauvegardes complètes fréquentes sont difficiles.
Point-in-Time Recovery (PITR) : La capacité de restaurer l'état de la
base de données tel qu'il était à n'importe quel moment depuis la
dernière sauvegarde de base.
Serveur de reprise intermédiaire (Warm Standby) : Permet de
maintenir une copie quasi-complète de la base de données sur une
autre machine. Le Hot Standby permet même des requêtes en lecture
seule sur le secondaire.
 Configuration de l'archivage WAL :
Activez l'archivage en configurant wal_level à replica ou supérieur,
archive_mode à on, et en spécifiant une commande shell
(archive_command) ou une bibliothèque d'archivage (archive_library).
La archive_command doit renvoyer un statut de sortie zéro en cas de
succès et gérer les fichiers déjà présents sans les écraser.
Les WALs sont exécutés sous l'identité de l'utilisateur propriétaire du
serveur PostgreSQL et doivent être protégés0.
Le paramètre archive_timeout peut forcer le serveur à changer de
fichier segment WAL après un certain délai pour limiter l'ancienneté
des données archivées.
 Sauvegarde de base (pg_basebackup) :
L'outil pg_basebackup est la manière la plus simple d'effectuer une
sauvegarde de base. Il peut créer une sauvegarde sous forme de
fichiers standards ou d'archive tar.
Pour les cas plus complexes, une API de bas niveau peut être utilisée,
impliquant l'exécution de pg_backup_start() et pg_backup_stop().
Un fichier historique de sauvegarde est créé pour identifier le premier
fichier segment WAL à conserver.
 Sauvegarde incrémentale :
Utilisez pg_basebackup avec l'option --incremental.
Le serveur utilise des résumés de journaux de transactions
(stockés dans pg_wal/summaries) pour trouver les blocs à sauvegarder.
Nécessite la sauvegarde incrémentale et toutes les sauvegardes
précédentes dont elle dépend pour la restauration.
 Lignes temporelles (Timelines) :
 Chaque fois qu'une restauration à un instant donné est effectuée, une
nouvelle "timeline" est créée pour distinguer les séries
d'enregistrements des journaux de transactions0.
Ces fichiers d'historique de timelines sont essentiels pour choisir les
bons fichiers WAL lors de la restauration à partir d'une archive
contenant plusieurs timelines.
 Limitations : La modification des bases de données modèles pendant une
sauvegarde continue peut entraîner des incohérences. Les commandes
CREATE TABLESPACE sont tracées avec des chemins absolus, ce qui peut
poser problème lors de la restauration sur une machine différente. Le volume
des journaux de transactions peut être très important en raison des images
complètes des blocs disques.

II. Méthodes de Restauration

1. Restauration d'une Sauvegarde SQL

 Les fichiers texte créés par pg_dump peuvent être lus par le programme
psql.
 La base de données cible doit être créée à l'avance à partir de
template0 avant d'exécuter psql, pour éviter les conflits avec des objets
ajoutés à template....
 Les utilisateurs possédant des objets ou des droits spécifiques doivent exister
avant la restauration.
 Pour garantir l'intégrité, utilisez --set ON_ERROR_STOP=on pour arrêter la
restauration en cas d'erreur SQL, ou - ou --single-transaction pour restaurer la
sauvegarde entière dans une seule transaction.
 pg_dump et psql peuvent être chaînés via des tubes pour restaurer une base
de données directement d'un serveur à un autre.
 Il est fortement conseillé d'exécuter ANALYZE sur chaque base de données
après la restauration pour mettre à jour les statistiques de l'optimiseur de
requêtes....

2. Restauration d'une Sauvegarde au Niveau du Système de Fichiers

 Le serveur doit être arrêté avant de restaurer les données.

 Effacez tous les fichiers et sous-répertoires existants sous le répertoire de
données de l'instance et les répertoires racines des tablespaces.
 Copiez les fichiers de la base de données dans les répertoires cibles, en
assurant le bon propriétaire et les bons droits. Si des tablespaces sont
utilisés, vérifiez que les liens symboliques sont correctement restaurés.
 Incluez tous les WALs générés avant l'arrêt du serveur lors de la sauvegarde.

3. Restauration à partir d'un Archivage Continu (PITR)

Cette procédure permet de récupérer la base de données à n'importe quel moment
précis.
 Étapes de restauration :
a. Arrêter le serveur s'il est en cours d'exécution.
b. Sauvegarder les WAL non archivés : Si de l'espace est disponible, copiez
le répertoire complet de données de l'instance et les tablespaces vers un
emplacement temporaire, en particulier le sous-répertoire pg_wal pour les
WAL non archivés.
c. Effacer l'instance actuelle : Supprimez tous les fichiers et sous-répertoires
existants sous le répertoire de données de l'instance et sous les répertoires
racines des tablespaces.
d. Restaurer la sauvegarde de base : Copiez les fichiers de la sauvegarde de
base dans les répertoires cibles. Assurez-vous que les permissions et les liens
symboliques des tablespaces sont corrects. Pour une sauvegarde
incrémentale, utilisez pg_combinebackup pour reconstruire la sauvegarde
complète synthétique.
e. Gérer les WAL : Supprimez les fichiers de pg_wal/ provenant de la
sauvegarde et copiez-y les WAL non archivés que vous avez sauvegardés à
l'étape .
f. Configuration de la restauration : Modifiez postgresql.conf pour définir le
restore_command et les recovery_target_* (voir ci-dessous), et créez un
fichier recovery.signal dans le répertoire des données de l'instance. Il est
recommandé de restreindre temporairement l'accès aux utilisateurs
ordinaires.
g. Démarrer le serveur : Le serveur entrera en mode de restauration et
commencera à rejouer les fichiers segments WAL archivés.
h. Inspection et finalisation : Vérifiez le contenu de la base de données. Si
tout est correct, supprimez le fichier recovery.signal et restaurez la
configuration normale de pg_hba.conf.
 restore_command : Indique à PostgreSQL comment récupérer les fichiers
segments WAL archivés. Elle utilise %f (nom du fichier WAL) et %p (chemin de
destination). La commande doit renvoyer un code de sortie non nul si le
fichier n'est pas trouvé dans les archives (ce n'est pas une erreur).
 Cible de récupération (recovery_target) : Par défaut, la restauration
continue jusqu'à la fin des WAL. Vous pouvez spécifier un point d'arrêt précis
en utilisant :
recovery_target = 'immediate' : Arrête dès qu'un point de cohérence
est atteint (le plus tôt possible).
recovery_target_name : Un point de restauration nommé (créé avec
pg_create_restore_point()).
recovery_target_time : Une date et heure0.
recovery_target_xid : Un identifiant de transaction0.
recovery_target_lsn : Un Log Sequence Number.
recovery_target_inclusive : Détermine si la cible est incluse ou non (on
ou off).
recovery_target_timeline : Spécifie la timeline sur laquelle restaurer
(current, latest, ou un ID numérique).
 recovery_target_action : Détermine l'action à prendre une fois la cible
atteinte (pause, promote, shutdown). pause permet de vérifier l'état de
la base de données avant de continuer ou de s'arrêter.
 Conseils sur les Performances et Fiabilité
 COPY vs INSERT : Pour l'insertion de grandes quantités de données, utilisez
la commande COPY, qui est plus efficace que des séries de INSERT.
 Paramètres de configuration :
maintenance_work_mem et max_wal_size : Augmentez ces valeurs
pour améliorer les performances lors des rechargements de données et
de la création d'index.
Désactiver archivage/réplication : Envisagez de désactiver
temporairement l'archivage des journaux de transactions et la
réplication en flux (wal_level à minimal, archive_mode à off,
max_wal_senders à zéro) lors de très gros chargements de données
pour réduire la surcharge de journalisation. Cela nécessite un
redémarrage du serveur et rend les sauvegardes précédentes
inutilisables pour le PITR.
 fsync et synchronous_commit :
fsync activé garantit que les mises à jour sont écrites physiquement sur
le disque pour assurer la cohérence après une panne0. Le désactiver
améliore les performances mais risque une corruption de données
irrécupérable.
synchronous_commit : Contrôle si les validations de transactions
attendent la confirmation de l'écriture des enregistrements WAL sur le
disque (local ou distant pour la réplication synchrone). Le désactiver
augmente la performance mais risque une perte de transactions
récentes en cas de crash du serveur.
 full_page_writes : Activé par défaut, il garantit la restauration correcte des
pages disque même en cas d'écriture partielle suite à une panne. Le
désactiver peut réduire le volume des WAL mais augmente les risques de
corruption après un échec système.
 Sommes de contrôle (Checksums) : Peuvent être activées lors de
l'initialisation de l'instance avec initdb pour protéger les blocs de données
contre la corruption.
 Emplacement des WAL : Il est avantageux de placer les journaux de
transactions sur un disque différent de celui des fichiers de données
principaux pour améliorer les performances.
 ANALYZE : Toujours exécuter ANALYZE après la restauration ou des
modifications massives de données pour mettre à jour les statistiques de
l'optimiseur de requêtes, ce qui est crucial pour des plans d'exécution
efficaces....

Conclusion
Ce document couvre les aspects essentiels de la sauvegarde et de la restauration
dans PostgreSQL, en soulignant l'importance de la planification et de la
compréhension des mécanismes sous-jacents pour assurer la fiabilité et la
performance de votre base de données.
Reference :

Formation administration d’une base de données : Fiche 6 Sauvegarde et

restauration - MAGEWEB INFORMATIQUE SERVICES
Guide Complet sur les Sauvegardes de Base de Données SQL pour Débutants - Base
de données SQL - W3schools
Cours Sauvegarde et restauration des bases de données PDF - Modélisation
relationnelle
Chapitre 25. Sauvegardes et restaurations