Comme tout ce qui contient des données importantes, les bases de données PostgreSQL™ doivent être sauvegardées régulièrement. Bien que la procédure soit assez simple, il est important de comprendre les techniques et hypothèses sous-jacentes.
Il y a trois approches fondamentalement différentes pour sauvegarder les données de PostgreSQL™ :
la sauvegarde SQL ;
la sauvegarde au niveau du système de fichiers ;
l'archivage continu.
Chacune a ses avantages et ses inconvénients. Elles sont toutes analysées, chacune leur tour, dans les sections suivantes.
Le principe est de produire un fichier texte de commandes SQL (appelé « fichier dump »), qui, si on le renvoie au serveur, recrée une base de données identique à celle sauvegardée. PostgreSQL™ propose pour cela le programme utilitaire pg_dump(1). L'usage basique est :
pg_dump base_de_donnees > fichier_de_sortie
pg_dump écrit son résultat sur la sortie standard. Son utilité est expliquée plus loin.
pg_dump est un programme client PostgreSQL™ classique (mais plutôt intelligent). Cela signifie que la sauvegarde peut être effectuée depuis n'importe quel ordinateur ayant accès à la base. Mais pg_dump n'a pas de droits spéciaux. Il doit, en particulier, avoir accès en lecture à toutes les tables à sauvegarder, si bien qu'il doit être lancé pratiquement toujours en tant que superutilisateur de la base.
Pour préciser le serveur de bases de données que pg_dump doit contacter, on utilise les options de ligne de commande -h serveur et -p port. Le serveur par défaut est le serveur local ou celui indiqué par la variable d'environnement PGHOST. De la même façon, le port par défaut est indiqué par la variable d'environnement PGPORT ou, en son absence, par la valeur par défaut précisée à la compilation. Le serveur a normalement reçu les mêmes valeurs par défaut à la compilation.
Comme tout programme client PostgreSQL™, pg_dump se connecte par défaut avec l'utilisateur de base de données de même nom que l'utilisateur système courant. L'utilisation de l'option -U ou de la variable d'environnement PGUSER permettent de modifier le comportement par défaut. Les connexions de pg_dump sont soumises aux mécanismes normaux d'authentification des programmes clients (décrits dans le Chapitre 19, Authentification du client).
Un des gros avantages de pg_dump sur les autres méthodes de sauvegarde décrites après est que la sortie de pg_dump peut être généralement re-chargée dans des versions plus récentes de PostgreSQL™, alors que les sauvegardes au niveau fichier et l'archivage continu sont tous les deux très spécifique à la version du serveur pg_dump est aussi la seule méthode qui fonctionnera lors du transfert d'une base de données vers une machine d'une architecture différente (comme par exemple d'un serveur 32 bits à un serveur 64 bits).
Les sauvegardes créées par pg_dump sont cohérentes, ce qui signifie que la sauvegarde représente une image de la base de données au moment où commence l'exécution de pg_dump. pg_dump ne bloque pas les autres opérations sur la base lorsqu'il fonctionne (sauf celles qui nécessitent un verrou exclusif, comme la plupart des formes d'ALTER TABLE.)
![[Important]](images/important.png)
Si la base de données utilise les OID (par exemple en tant que clés étrangères), il est impératif d'indiquer à pg_dump de sauvegarder aussi les OID. Pour cela, on utilise l'option -o sur la ligne de commande.
Les fichiers texte créés par pg_dump peuvent être lus par le programme psql. La syntaxe générale d'une commande de restauration est
psql base_de_donnees < fichier_d_entree
où fichier_d_entree est le fichier en sortie de la commande pg_dump. La base de données base_de_donnees n'est pas créée par cette commande. Elle doit être créée à partir de template0 avant d'exécuter psql (par exemple avec createdb -T template0 base_de_donnees). psql propose des options similaires à celles de pg_dump pour indiquer le serveur de bases de données sur lequel se connecter et le nom d'utilisateur à utiliser. La page de référence de psql(1) donne plus d'informations.
Tous les utilisateurs possédant des objets ou ayant certains droits sur les objets de la base sauvegardée doivent exister préalablement à la restauration de la sauvegarde. S'ils n'existent pas, la restauration échoue pour la création des objets dont ils sont propriétaires ou sur lesquels ils ont des droits (quelque fois, cela est souhaitable mais ce n'est habituellement pas le cas).
Par défaut, le script psql continue de s'exécuter après la détection d'une erreur SQL. Vous pouvez exécuter psql avec la variable ON_ERROR_STOP configurée pour modifier ce comportement. psql quitte alors avec un code d'erreur 3 si une erreur SQL survient :
psql --set ON_ERROR_STOP=on base_de_données < infile
Dans tous les cas, une sauvegarde partiellement restaurée est obtenue. Si cela n'est pas souhaitable, il est possible d'indiquer que la sauvegarde complète doit être restaurée au cours d'une transaction unique. De ce fait, soit la restauration est validée dans son ensemble, soit elle est entièrement annulée. Ce mode est choisi en passant l'option -1 ou --single-transaction en ligne de commande à psql. Dans ce mode, la plus petite erreur peut annuler une restauration en cours depuis plusieurs heures. Néanmoins, c'est probablement préférable au nettoyage manuel d'une base rendue complexe par une sauvegarde partiellement restaurée.
La capacité de pg_dump et psql à écrire et à lire dans des tubes permet de sauvegarder une base de données directement d'un serveur sur un autre. Par exemple :
pg_dump -h serveur1 base_de_donnees | psql -h serveur2 base_de_donnees
Les fichiers de sauvegarde produits par pg_dump sont relatifs à template0. Cela signifie que chaque langage, procédure, etc. ajouté à template1 est aussi sauvegardé par pg_dump. En conséquence, si une base template1 modifiée est utilisée lors de la restauration, il faut créer la base vide à partir de template0, comme dans l'exemple plus haut.
Après la restauration d'une sauvegarde, il est conseillé d'exécuter ANALYZE(7) sur chaque base de données pour que l'optimiseur de requêtes dispose de statistiques utiles ; voir Section 23.1.3, « Maintenir les statistiques du planificateur » et Section 23.1.6, « Le démon auto-vacuum » pour plus d'informations. Pour plus de conseils sur le chargement efficace de grosses quantités de données dans PostgreSQL™, on peut se référer à la Section 14.4, « Remplir une base de données ».
pg_dump ne sauvegarde qu'une seule base à la fois, et ne sauvegarde pas les informations relatives aux rôles et tablespaces (parce que ceux-ci portent sur l'ensemble des bases du cluster, et non sur une base particulière). Pour permettre une sauvegarde aisée de tout le contenu d'un cluster, le programme pg_dumpall(1) est fourni. pg_dumpall sauvegarde toutes les bases de données d'un cluster (ensemble des bases d'une instance) PostgreSQL™ et préserve les données communes au cluster, telles que les rôles et tablespaces. L'utilisation basique de cette commande est :
pg_dumpall > fichier_de_sortie
Le fichier de sauvegarde résultant peut être restauré avec psql :
psql -f fichier_d_entree postgres
(N'importe quelle base de données peut être utilisée pour la connexion mais si le rechargement est exécuté sur un cluster vide, il est préférable d'utiliser postgres.) Il faut obligatoirement avoir le profil superutilisateur pour restaurer une sauvegarde faite avec pg_dumpall, afin de pouvoir restaurer les informations sur les rôles et les tablespaces. Si les tablespaces sont utilisés, il faut s'assurer que leurs chemins sauvegardés sont appropriés à la nouvelle installation.
pg_dumpall fonctionne en émettant des commandes pour recréer les rôles, les tablespaces et les bases vides, puis en invoquant pg_dump pour chaque base de données. Cela signifie que, bien que chaque base de données est cohérente en interne, les images des différentes bases de données peuvent ne pas être tout à fait synchronisées.
Certains systèmes d'exploitation ont des limites sur la taille maximum des fichiers qui posent des problème lors de la création de gros fichiers de sauvegarde avec pg_dump. Heureusement, pg_dump peut écrire sur la sortie standard, donc vous pouvez utiliser les outils Unix standards pour contourner ce problème potentiel. Il existe plusieurs autres méthodes :
Compresser le fichier de sauvegarde. Tout programme de compression habituel est utilisable. Par exemple gzip :
pg_dump base_de_donnees | gzip > nom_fichier.gz
Pour restaurer :
gunzip -c nom_fichier.gz | psql base_de_donnees
ou
cat nom_fichier.gz | gunzip | psql base_de_donnees
Couper le fichier avec split. La commande split permet de découper le fichier en fichiers plus petits, de taille acceptable par le système de fichiers sous-jacent. Par exemple, pour faire des morceaux de 1 Mo :
pg_dump base_de_donnees | split -b 1m - nom_fichier
Pour restaurer :
cat nom_fichier* | psql base_de_donnees
Utilisation du format de sauvegarde personnalisé de pg_dump. Si PostgreSQL™ est installé sur un système où la bibliothèque de compression zlib est disponible, le format de sauvegarde personnalisé peut être utilisé pour compresser les données à la volée. Pour les bases de données volumineuses, cela produit un fichier de sauvegarde d'une taille comparable à celle du fichier produit par gzip, avec l'avantage supplémentaire de permettre de restaurer des tables sélectivement. La commande qui suit sauvegarde une base de données en utilisant ce format de sauvegarde :
pg_dump -Fc base_de_donnees > nom_fichier
Le format de sauvegarde personnalisé ne produit pas un script utilisable par psql. Ce script doit être restauré avec pg_restore, par exemple :
pg_restore -d nom_base nom_fichier
Voir les pages de référence de pg_dump(1) et pg_restore(1) pour plus de détails.
Pour les très grosses bases de données, il peut être nécessaire de combiner split avec une des deux autres approches.
Maintenance du fichier de traces
