PostgreSQL – Utilitaire – Sauvegarder

Grosse mise à jour de cet ancien article ô combien important lorsqu’il s’agit de gérer une instance PostgreSQL.

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 DBeaver ou pgAdmin.

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 :

• --file="mon_fichier" : spécifie le fichier de sortie pour le script généré.
• --format=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)

• --data-only : sauvegarde uniquement les données (le contenu).
  Peut être couplé à --disable-triggers pour désactiver les triggers lors de la restauration des données. Ne fonctionne qu’en utilisant un superutilisateur.
• --schema-only : sauvegarde uniquement la définition des données (le contenant).
• -t ma_table : sauvegarde uniquement la table ma_table (peut être utilisé avec -s).
  Il est possible de spécifier le schéma et/ou * comme caractère joker.
  L’option peut être répétée.
• -T ma_table : sauvegarde tout sauf la(s) table(s) ma_table.
  Il est possible de spécifier le schéma et/ou * comme caractère joker.
  L’option peut être répétée.
• -n mon_schema : sauvegarde uniquement le schéma mon_schéma.
  Il est possible d’utiliser * comme caractère joker.
  L’option peut être répétée.
• -N mon_schema : sauvegarde tout sauf le(s) schéma(s) mon_schéma.
  Il est possible d’utiliser * comme caractère joker.
  L’option peut être répétée.
• --large-objects : inclut les object larges dans la sauvegarde (comportement par défaut sauf avec les options -t et -n).

• --clean : nettoie (supprime) les objets de la base de données avant de les créer lors de la restauration (uniquement pour le format text).
  A utiliser avec -if-exists:, permet de nettoyer seulement si la données existe et empêche donc les erreurs.
• --verbose : spécifie le mode verbeux.
• --jobs=8 : nombre de jobs concurrents (processus). Permet de réduire le temps de sauvegarde avec des processeurs multicœurs.

• --no-owner : 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.
• --no-privileges : empêche la sauvegarde des droits d’accès (commandes grant/revoke).

Voici quelques exemples.

Tables

Exporter une table :

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=custom ^
  --table='mon_schema.ma_table' ^
  --file="mon_dump.backup"

Exporter plusieurs tables :

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=custom ^
  --table='mon_schema.table_*' ^
  --table='mon_schema2.table_2' ^
  --file="mon_dump.backup"

Exporter uniquement la structure (pas les données) :

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=custom ^
  --table='mon_schema.ma_table' ^
  --schema-only ^
  --file="mon_dump.backup"

Schéma

Exporter un schéma

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=custom ^
  --schema='mon_schema' ^
  --file="mon_dump.backup"

Exporter plusieurs schémas

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=custom ^
  --schema='schema_*' ^
  --schema='schema_2' ^
  --file="mon_dump.backup"

Exporter tous les schémas sauf public

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=custom ^
  --exclude-schema='public' ^
  --file="mon_dump.backup"

Export en SQL

Avec l’exclusion des propriétaires et des droits :

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=plain ^
  --no-owner ^
  --no-privileges ^
  --file="mon_dump.sql"

Pour n’exporter que la structure :

pg_dump ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --format=plain ^
  --table='mon_schema.ma_table' ^
  --schema-only ^
  --file="mon_dump.sql"

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 :

• --data-only : sauvegarde uniquement les données (le contenu).
  Peut être couplé à --disable-triggers pour désactiver les triggers lors de la restauration des données. Ne fonctionne qu’en utilisant un superutilisateur.
• --schema-only : sauvegarde uniquement la définition des données (le contenant).
• -roles-only : sauvegarde uniquement les rôles

• --clean : nettoie (supprime) les objets de la base de données avant de les créer lors de la restauration (uniquement pour le format text).
  A utiliser avec -if-exists:, permet de nettoyer seulement si la données existe et empêche donc les erreurs.
• --verbose : spécifie le mode verbeux.

• --no-owner : 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.
• --no-privileges : empêche la sauvegarde des droits d’accès (commandes grant/revoke).

• --file="mon_fichier" : spécifie le fichier de sortie pour le script généré.

Voici quelques exemples.

Exporter uniquement les rôles :

pg_dumpall ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --roles-only ^
  --file="mon_dump.sql"

Exporter le cluster complet sauf certaines BDD :

pg_dumpall ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --exclude-database='bdd_*'
  --file="mon_dump.sql"

Exporter uniquement la structure :

pg_dumpall ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --no-data
  --file="mon_dump.sql"

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 (format=custom) ou archive tar (format=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 :

• --data-only : restaure uniquement les données (le contenu).
• --schema-only : restaure uniquement la définition des données (le contenant).
• --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.
• -t ma_table : restaure uniquement la table ma_table.
  Pas de joker possible contrairement avec pg_dump.
  Pas de spécification du schéma possible, il faut combiner avec l’option -n.
• -n mon_schema : restaure uniquement le schéma spécifié.
• -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 à lister les arguments tels qu’ils apparaissent dans la table des matières du fichier de sauvegarde.

• --clean : nettoie (supprime) les objets de la base de données avant de les créer lors de la restauration (uniquement pour le format text).
  A utiliser avec -if-exists:, permet de nettoyer seulement si la données existe et empêche donc les erreurs.
• --create : crée la base de données avant de la restaurer.
  Combiné à --clean pour supprimer puis recréer la BDD cible.
• --verbose : spécifie le mode verbeux.
• --jobs=8 : nombre de jobs concurrents (processus). Permet de réduire le temps de restauration avec des processeurs multicoeurs.
• --exit-on-error : quitter dès qu’une erreur est levée.
  Peut être combiné à --single-transaction : englobe la restauration dans une seule transaction. Si une erreur est levée, aucun changement n’est appliqué.

• --no-owner : 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.
• --no-privileges : empêche la sauvegarde des droits d’accès (commandes grant/revoke).

• --list : liste le contenu de l’archive.
  A combiner avec --file="mon_fichier_liste.txt" pour spécifier le fichier de sortie pour le script ou la liste généré.
• --use-list="mon_fichier_liste.txt" : 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 pour désactiver les triggers lors de la restauration des données. Ne fonctionne qu’en utilisant un superutilisateur.

Voici quelques exemples.

Import classique (attention au nombre de job devant correspondre au nombre de CPU disponibles) :

pg_restore ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --jobs=4 ^
  "mon_dump.backup"

Restreindre à certains schémas :

pg_restore ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --jobs=4 ^
  --schema=schema_1 ^
  --schema=schema_2 ^
  "mon_dump.backup"

Ne pas remonter les données (uniquement la structure) :

pg_restore ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --jobs=4 ^
  --schema-only ^
  "mon_dump.backup"

Ne remonter les données que pour les tables dont la création a fonctionnée ; si une table existe déjà, sa création échoue, les données ne seront donc pas remontées non plus :

pg_restore ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --jobs=4 ^
  --no-data-for-failed-tables ^
  "mon_dump.backup"

Ne pas récupérer le propriétaire ni les droits :

pg_restore ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --jobs=4 ^
  --no-owner ^
  --no-privileges ^
  "mon_dump.backup"

Lister les objets présents dans le dump :

pg_restore ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --jobs=4 ^
  --list ^
  --file="ma_liste.txt"
  "mon_dump.backup"

Restauration fichier plat (SQL)

La restauration se fait en jouant le script SQL créé lors de l’export. Il n’est pas possible de filtrer les éléments à importer, il faut soit le faire à l’export, soit modifier le script SQL manuellement.

Voici un exemple de ligne de commande :

psql ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --file="mon_dump.sql"

Automatisation

Les commandes décrites précédemment peuvent être utilisées au sein d’un script afin d’être exécutées régulièrement via une tâche planifiée par exemple.

Voici un exemple de script bat :

@echo off

REM Définition des variables initiales
SET path_script=%~dp0
SET path_log="C:\chemin\vers\fichier\mon_log.log"
SET path_dump="C:\chemin\vers\fichier\mon_dump.backup"
SET path_dir_script="C:\chemin\vers\fichier"
SET path_bin_pg="C:\chemin\vers\binaires_pg\bin"


echo "Démarrage"
echo "Démarrage" > "%path_log%"
echo %date:~6,4%-%date:~3,2%-%date:~0,2% %time:~0,2%:%time:~3,2%:%time:~6,2% >> "%path_log%"


echo "Export d'un schéma"
echo "Export d'un schéma" >> "%path_log%"

"%path_bin_pg%\pg_dump.exe" ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --schema='schema_2' ^
  --file="%path_dump%" ^
  >> "%path_log%" 2>&1 


echo "Restauration"
echo "Restauration" >> "%path_log%"

"%path_bin_pg%\pg_restore.exe" ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  "%path_dump%" ^
  >> "%path_log%" 2>&1 


echo "Exécution d'un script SQL"
echo "Exécution d'un script SQL" >> "%path_log%"

"%path_bin_pg%\psql.exe" ^
  --dbname=postgres://user:pwd@127.0.0.1:5432/my_bdd ^
  --file="%path_dir_script%\mon_script.sql" ^
  >> "%path_log%" 2>&1 


echo %date:~6,4%-%date:~3,2%-%date:~0,2% %time:~0,2%:%time:~3,2%:%time:~6,2% >> "%path_log%"

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