PostgreSQL – Utilitaire – Sauvegarder

pg_dump

pg_dump est l’utilitaire dédié à l’export d’objet d’une base de données PostgreSQL. C’est lui qui est utilisé lorsque vous faite un « backup » (ou dump ou sauvegarde) de votre BDD via un client graphique.

Voici un exemple de ligne de commande :

# Exemple de dump
pg_dump -h 127.0.0.1 -p 5432 -U utilisateur -F c -f "C:\chemin\fichier_de_sortie.sqlc" "base_de_donnees"

Voici la liste des options les plus courantes :

• -a : Sauvegarde uniquement les données (le contenu).
• -s : Sauvegarde uniquement la définition des données (le contenant).
• -t ma_table : Sauvegarde uniquement la table ma_table (peut être utilisé avec -s). Attention, pas de spécification du schéma possible.
• -T ma_table : Sauvegarde tout sauf la(s) table(s) ma_table.
• -n mon_schema : Sauvegarde uniquement le schéma mon_schéma (répéter l’option pour sauvegarder plusieurs schémas).
• -N mon_schema : Sauvegarde tout sauf le(s) schéma(s) mon_schéma.
• -b : Inclut les object larges dans la sauvegarde (comportement par défaut sauf avec les options -t et -n).

• -c : Nettoie (supprime) les objets de la base de données avant de les créer lors de la restauration (uniquement pour le format text).
• -if-exist : Uniquement avec -c, permet de nettoyer seulement si la données existe et empêche donc les erreurs.
• -C : Crée la base de données avant de la restaurer. Combiné à -c, supprimer puis recréer la BDD cible.
• -v : Spécifie le mode verbeux.
• -j 8 : Nombre de jobs concurrents (processus). Permet de réduire le temps de sauvegarde avec des processeurs multicœurs.

• -O : Ne pas lancer les commandes initialisant les propriétaires des objets pour correspondre à la base de données originale. Ainsi, tout nom d’utilisateur peut être utilisé et ce dernier sera le propriétaire des objets créés.
• -x : Empêche la sauvegarde des droits d’accès (commandes grant/revoke).

• -f "mon_fichier" : Spécifie le fichier de sortie pour le script généré.
• -F format : Spécifie le format de sortie :
  • c : archive personnalisée utilisable par pg_restore : custom (.dump ou .sqlc)
  • t : archive au format tar utilisable par pg_restore (.tar)
  • p : fichier SQL : plain text (.sql)

• --disable-triggers : Désactivation des triggers lors de la sauvegarde de données uniquement (-a). Ne fonctionne qu’en utilisant un superutilisateur ou l’option -S.
• -S nom_utilisateur : Spécifie le nom du superutilisateur à utiliser. Ceci est seulement nécessaire pour l’option --disable-triggers.

Voici un exemple de sauvegarde d’un schéma :

# Exemple de dump d'un schéma
pg_dump -h 127.0.0.1 -p 5432 -U utilisateur -n mon.schema -F c -f C:\chemin\fichier_de_sortie.sqlc base_de_donnees

pg_dumpall

pg_dumpall permet de sauvegarder la totalité du cluster et pas seulement certains objet d’une BDD.

Voici un exemple de ligne de commande :

# Exemple de dump d'un cluster entier
pg_dumpall -h 127.0.0.1 -p 5432 -U utilisateur -f "C:\chemin\fichier_de_sortie.out"

Son fonctionnement est le même que celui de pg_dump à la différence que l’on peut exporter les rôles, les tablespaces et plusieurs bases de données d’un coup.

Autre différence, pg_dumpall ne créé que des fichier plat (en SQL) qu’il faut restaurer avec PSQL (voir le paragraphe restauration fichier).

Voici la liste des options les plus courantes :

• -a : Sauvegarde uniquement les données (le contenu).
• -s : Sauvegarde uniquement la définition des données (le contenant).
• -r : Sauvegarde uniquement les rôles
• -t : Sauvegarde uniquement les tablespaces
• -g : Sauvegarde uniquement les objets globaux (rôle et tablespace, pas les BDD)

• -c : Nettoie (supprime) les objets de la base de données avant de les créer lors de la restauration (uniquement pour le format text).
• -if-exist : Uniquement avec -c, permet de nettoyer seulement si la données existe et empêche donc les erreurs.
• -v : Spécifie le mode verbeux.

• -O : Ne pas lancer les commandes initialisant les propriétaires des objets pour correspondre à la base de données originale. Ainsi, tout nom d’utilisateur peut être utilisé et ce dernier sera le propriétaire des objets créés.
• -x : Empêche la sauvegarde des droits d’accès (commandes grant/revoke).

• -f "mon_fichier" : Spécifie le fichier de sortie pour le script généré.

• --disable-triggers : Désactivation des triggers lors de la sauvegarde de données uniquement (-a). Ne fonctionne qu’en utilisant un superutilisateur ou l’option -S.
• -S nom_utilisateur : Spécifie le nom du superutilisateur à utiliser. Ceci est seulement nécessaire pour l’option --disable-triggers.

pg_restore

pg_restore permet comme son nom l’indique de restaurer les données. Cette restauration va créer les objets qui n’existent pas dans la base (si une table est déja présente, elle n’est pas recréée) puis ajouter les données. Ainsi, une restauration sur une base qui vient d’être dumpée va dupliquer tous les enregistrements.

pg_restore ne traite que les archives générées par pg_dump de type archive personnalisée ou archive tar.

Voici un exemple de ligne de commande :

# Exemple de restauration
pg_restore -h 127.0.0.1 -p 5432 -U "postgres" -d "base_de_donnees" -n "mon_schema" "C:\chemin\fichier_de_sortie.sqlc"

Voici la liste des options les plus courantes :

-d ma_base : Se connecte à la base de données ma_base et restaure directement dans celle-ci.
-a : Restaure uniquement les données (le contenu).
-s : Restaure uniquement la définition des données (le contenant).
-t ma_table : Restaure uniquement la table ma_table (peut être utilisé avec -s). Attention, pas de spécification du schéma possible.
-n mon_schema : Restaure uniquement le schéma mon_schéma.
-I mon_index : Restaure uniquement la définition de mon_index.
-T mon_trigger : Restaure uniquement le déclencheur mon_trigger.
-P ma_fonction(...) : Restaure uniquement ma_fonction. Attention à épeler les arguments exactement comme ils apparaissent dans la table des matières du fichier de sauvegarde.

-c : Nettoie (supprime) les objets de la base de données avant de les créer.
-if-exist : Uniquement avec -c, permet de nettoyer seulement si la données existe et empêche donc les erreurs.
-C : Crée la base de données avant de la restaurer. Combiné à -c, supprimer puis recréer la BDD cible.
-i : Ignore la vérification de version de la base de données.
-v : Spécifie le mode verbeux.
--no-data-for-failed-tables : si la commande de création de la table échoue (par exemple parce qu’elle existe déjà) alors les données de la table en question ne sont pas restaurées.
-j 8 : Nombre de jobs concurrents (processus). Permet de réduire le temps de restauration avec des processeurs multicoeurs.
-e : Dès qu’une erreur est levée, on quitte.
-1 : La restauration est effectuée en une seule transaction (commande placées entre BEGIN et COMMIT). Si on a une erreur, aucun changement n’est appliqué. A utiliser obligatoirement avec -e.

-O : Ne pas lancer les commandes initialisant les propriétaires des objets pour correspondre à la base de données originale. Ainsi, tout nom d’utilisateur peut être utilisé et ce dernier sera le propriétaire des objets créés.
-x : Empêche la restauration des droits d’accès (commandes grant/revoke).

-l : Liste le contenu de l’archive. A utiliser avec -f ou la sortie > mon_fichier_liste.txt. Peut être combiné avec -n et -t pour filtrer la sortie. Le fichier de liste peut être commenté et réordonné.
-f "mon_fichier" : Spécifie le fichier de sortie pour le script ou la liste généré.
-L mon_fichier_liste : Restaure seulement les éléments de l’archive qui sont listés dans fichier_liste dans l’ordre dans lequel ils apparaissent dans ce fichier.

--disable-triggers : Désactivation des triggers lors de la restauration de données uniquement (-a). Ne fonctionne qu’en utilisant un superutilisateur ou l’option -S.
-S nom_utilisateur : Spécifie le nom du superutilisateur à utiliser. Ceci est seulement nécessaire pour l’option --disable-triggers.

Voici quelques exemples de restauration :

# Sortir une liste dans le répertoire "Téléchargement"
pg_restore -l -f "C:\Users\mon_user\Downloads\ma_liste.txt"  "C:\Users\mon_user\Downloads\mon_dump.backup"

# Sortir le script SQL du dump dans le répertoire "Téléchargement"
pg_restore -f "C:\Users\mon_user\Downloads\ma_liste.sql"  "C:\Users\mon_user\Downloads\mon_dump.backup"

# Restauration d'une table dans un schéma
pg_restore -h 127.0.0.1 -p 5432 -U postgres -n mon.schema base_de_donnees C:\chemin\fichier_de_dump.sqlc

Restauration fichier

Pour restaurer un dump fait sous forme de fichier plat, il faut demander à PSQL de lire directement le fichier.

Voici un exemple de ligne de commande :

# Restaurer un cluster
psql -U postgres -h localhost -p 5432 -f fichier_de_sortie_pg_dumpall.out


# Restaurer une BDD
psql -f C:\chemin\fichier_de_sortie.sql -U utilisateur -d base_de_donnees

# Autre possibilité (attention, les utilisateurs doivent déjà être présents sinon la restauration échoue)
psql ma_base < C:\chemin\fichier_de_sortie.sql

Le cas PostGIS

Avec les base Postgis, il peut être nécessaire de passer par un script spécifique en PERL lors des montées de version.

Dans ce cas, vous devrez installer PERL sur votre ordinateur et vérifier que le chemin vers les binaires de PostgreSQL se trouve bien dans la variable d’environnement « PATH ».

Utilisez ensuite le script suivant (à adapter bien sur) :

perl "C:\Program Files\PostgreSQL\9.3\utils\postgis_restore.pl" "chemin\mon_dump.backup" | "C:\Program Files\PostgreSQL\9.3\bin\psql" -h localhost -p 5432 -U mon_user ma_base 2> "chemin\mon_log.txt"

Pour aller plus loin

La documentation :

• pg_dump
• pg_dumpall
• pg_restore

Cet article fait parti du cours sur PostgreSQL, partie 8 du Vade-mecum.

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQL commandeshelltuto