Documentation PostgreSQL 14.22 » Administration du serveur » Sauvegardes et restaurations » Sauvegarde SQL

Précédent | Niveau supérieur | Suivant
Chapitre 26. Sauvegardes et restaurations | Sommaire | 26.2. Sauvegarde de niveau système de fichiers

## 26.1. Sauvegarde SQL

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. L'usage basique est :

    pg_dump base_de_donnees > fichier_sauvegarde

pg_dump écrit son résultat sur la sortie standard. Son utilité est expliquée plus loin. Bien que la commande ci-dessus crée un fichier texte, pg_dump peut créer des fichiers dans d'autres formats qui permettent le parallélisme et un contrôle plus fin de la restauration des objets.

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. En particulier, il doit avoir un accès en lecture à toutes les tables que vous voulez sauvegarder, donc pour sauvegarder une base complète, vous devez pratiquement toujours utiliser un super-utilisateur. si vous n'avez pas les droits suffisants pour sauvegarder la base entière, vous pouvez toujours sauvegarder les parties pour lesquels vous avez le droit d'accès en utilisant des options telles que `-n `schéma`` et `-t `table``.)

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 21).

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 les sauvegardes continues sont toutes les deux très spécifiques à 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`.)

### 26.1.1. Restaurer la sauvegarde

Les fichiers texte créés par pg_dump peuvent être lus par le programme psql en utilisant sa configuration par défaut. La syntaxe générale d'une commande de restauration est :

    psql -X base_de_donnees < fichier_sauvegarde

où `fichier_sauvegarde` 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``). Pour s'assurer que psql s'exécute avec sa configuration par défaut, utilisez l'option `-X` (`--no-psqlrc`). 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 donne plus d'informations.

Les sauvegardes binaires doivent être restaurées en utilisant l'outil pg_restore.

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 pas toujours 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 -X --set ON_ERROR_STOP=on base_de_données < fichier_sauvegarde

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 rendu 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 -X -h serveur2 base_de_donnees

### Important

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 ci-dessus.

Après la restauration d'une sauvegarde, il est conseillé d'exécuter `ANALYZE` sur chaque base de données pour que l'optimiseur de requêtes dispose de statistiques utiles ; voir Section 25.1.3 et Section 25.1.6 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.

### 26.1.2. Utilisation de pg_dumpall

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'une instance, le programme pg_dumpall est fourni. pg_dumpall sauvegarde toutes les bases de données d'une instance PostgreSQL et préserve les données communes à l'instance, telles que les rôles et tablespaces. L'utilisation basique de cette commande est :

    pg_dumpall > fichier_sauvegarde

Le fichier de sauvegarde résultant peut être restauré avec psql :

    psql -X -f fichier_sauvegarde postgres

(N'importe quelle base de données peut être utilisée pour la connexion mais si le rechargement est exécuté sur une instance vide, il est préférable d'utiliser `postgres`.) Il faut obligatoirement avoir le profil super-utilisateur 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 ne sont pas synchronisées.

Les données globales à l'instance peuvent être sauvegardées seules en utilisant l'option `--globals-only` de pg_dumpall. Ceci est nécessaire pour sauvegarder entièrement l'instance si la commande pg_dump est utilisée pour sauvegarder les bases individuelles.

### 26.1.3. Gérer les grosses bases de données

Certains systèmes d'exploitation ont des limites sur la taille maximale des fichiers qui peuvent poser 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 2 Go :

    pg_dump base_de_donnees | split -b 2G - nom_fichier

Pour restaurer :

    cat nom_fichier* | psql base_de_donnees

Si vous utilisez GNU split, il est possible de l'utiliser avec gzip :

    pg_dump dbname | split -b 2G --filter='gzip > $FILE.gz'

Le résultat peut être restauré en utilisant `zcat`.

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 et pg_restore 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.

Utiliser la fonctionnalité de sauvegarde en parallèle de pg_dump. Pour accélérer la sauvegarde d'une grosse base de données, vous pouvez utiliser le mode parallélisé de pg_dump. Cela sauvegardera plusieurs tables à la fois. Vous pouvez contrôler le degré de parallélisme avec le paramètre `-j`. Les sauvegardes en parallèle n'acceptent que le format répertoire.

    pg_dump -j num -F d -f sortie.dir nom_base

Vous pouvez utiliser `pg_restore -j` pour restaurer une sauvegarde en parallèle. Ceci fonctionnera pour n'importe quelle archive, qu'elle soit dans le mode personnalisé ou répertoire. Elle n'a pas besoin d'avoir été créée avec le mode parallélisé de `pg_dump`.