pg_dump — sauvegarder une base de données PostgreSQL™ dans un script ou tout autre fichier d'archive
pg_dump [ option ...] [ nom_base ]
pg_dump est un outil de sauvegarde d'une base de données PostgreSQL™. Les sauvegardes réalisées sont cohérentes, même lors d'accès concurrents à la base de données. pg_dump ne bloque pas l'accès des autres utilisateurs (ni en lecture ni en écriture).
Les extractions peuvent être réalisées sous la forme de scripts ou de fichiers d'archive. Les scripts sont au format texte et contiennent les commandes SQL nécessaires à la reconstruction de la base de données dans l'état où elle était au moment de la sauvegarde. La restauration s'effectue en chargeant ces scrits avec psql(1). Ces scripts permettent de reconstruire la base de données sur d'autres machines et d'autres architectures, et même, au prix de quelques modifications, sur d'autres bases de données SQL.
La reconstruction de la base de données à partir d'autres formats de fichiers archive est obtenue avec pg_restore(1). pg_restore permet, à partir de ces formats, de sélectionner les éléments à restaurer, voire de les réordonner avant restauration. Les fichiers d'archive sont conçus pour être portables au travers d'architectures différentes.
Lorsqu'il est utilisé avec un des formats de fichier d'archivé en combinaison avec pg_restore, pg_dump fournit un mécanisme d'archivage et de transfert flexible. pg_dump peut être utilisé pour sauvegarder une base de données dans son intégralité ; pg_restore peut alors être utilisé pour examiner l'archive et/ou sélectionner les parties de la base de données à restaurer. Le format de fichier de sortie le plus flexible est le format « personnalisé » ( custom en anglais, -Fc). Compressé par défaut, il permet de sélectionner et réordonner les éléments archivés. Nom compressé, le format tar (-Ft) ne permet pas de réordonner les données au chargement mais est somme toute assez flexible. Qui plus est, il est utilisable par des outils Unix standard comme tar .
Lors de l'exécution de pg_dump, il est utile de surveiller les messages d'avertissement (affichés sur la sortie erreur standard), en particulier en ce qui concerne les limitations indiquées ci-dessous.
Les options suivantes de la ligne de commande contrôlent le contenu et le format de la sortie.
Le nom de la base de données à sauvegarder. En l'absence de précision, la variable d'environnement PGDATABASE est utilisée. Si cette variable n'est pas positionnée, le nom de l'utilisateur de la connexion est utilisé.
Seules les données sont sauvegardées, pas le schéma (définition des données).
Cette option n'a d'intérêt que pour le format texte. Pour les formats archive, l'option est spécifiée à l'appel de pg_restore .
Inclut les objets larges dans la sauvegarde. C'est le comportement par défaut sauf si vous ajoutez une des options suivantes : --schema, --table ou --schema-only. Donc cette option -b est seulement utile pour ajouter les objets larges dans le cas des sauvegardes sélectives.
Les commandes de nettoyage (suppression) des objets de la base sont écrites avant les commandes de création.
Cette option n'a d'intérêt que pour le format texte. Pour les formats archive, l'option est spécifiée à l'appel de pg_restore .
La sortie débute par une commande de création de la base de données et de connexion à cette base. Peu importe dans ce cas la base de données à laquelle la connexion est faite avant la restauration.
Cette option n'a d'intérêt que pour le format texte. Pour les formats archive, l'option est spécifiée à l'appel de pg_restore .
Les données sont copiées à l'aide de commandes INSERT plutôt que COPY . La restauration en est considérablement ralentie. Cela s'avère essentiellement utile pour créer des sauvegardes rechargées dans des bases de données autres que PostgreSQL™. De plus, comme cette option génère une commande séparée pour chaque ligne, une erreur lors du chargement d'une ligne ne fera que perdre cette ligne plutôt que le contenu entier de la table. La restauration échoue tout de même si les colonnes ont été réorganisées. L'option -D est sûre même avec des modifications dans l'ordre des colonnes, mais elle est du coup bien plus lente.
Les données sont copiées à l'aide de commandes INSERT qui explicitent les noms de colonnes (INSERT INTO table ( colonne , ...) VALUES ...). La restauration en est considérablement ralentie. Cela s'avère essentiellement utile pour créer des sauvegardes rechargées dans des bases de données autres que PostgreSQL™. De plus, comme cette option génère une commande séparée pour chaque ligne, une erreur lors du chargement d'une ligne ne fera que perdre cette ligne plutôt que le contenu entier de la table.
La sortie est redirigée vers le fichier spécifié. Sans précision, la sortie standard est utilisée.
La sauvegarde est créée dans l'encodage spécifié. Par défaut, la sauvegarde utilise celui de la base de données. Le même résultat peut être obtenu en positionnant la variable d'environnement PGCLIENTENCODING avec le codage désiré pour la sauvegarde.
Le format de la sortie. format correspond à un des éléments suivants :
Fichier de scripts SQL en texte simple (défaut).
Archive tar utilisable par pg_restore. Ce format permet d'ordonner et/ou d'exclure des objets lors de la restauration de la base. Il est également possible de restreindre les données rechargées lors de la restauration.
Archive personnalisée utilisable par pg_restore. C'est le format le plus flexible puisqu'il permet d'ordonner le chargement des données et la définition des objets. Ce format est compressé par défaut.
Les différences de version entre pg_dump et le serveur de bases de données sont ignorées.
pg_dump peut créer des sauvegardes de bases de données de versions différentes, mais les très anciennes versions ne sont plus supportées (actuellement celles antérieures à la 7.0). Sauvegarder à partir d'une version de serveur plus récente que celle de pg_dump a toutes les chances de ne pas fonctionner du tout. Cette option est utilisée lorsque la vérification de version ne doit pas être effectuée. Dans ce cas, il se peut que pg_dump échoue.
Sauvegarde seulements les schémas correspondant à schema ; la sélection se fait à la fois sur le schéma et sur les objets qu'il contient. Quand cette option n'est pas indiquée, tous les schémas non système de la base cible seront sauvegardés. Plusieurs schémas peuvent être indiqués en utilisant plusieurs fois l'option -n. De plus, le paramètre schéma est interprété comme un modèle suivant les règles utilisées par les commandes \d de psql (voir Modèles). Du coup, plusieurs schémas peuvent être sélectionnés en utilisant des caractères joker dans le modèle. Lors de l'utilisation de ces caractères, faites attention à placer le modèle entre guillemets si nécessaire pour empêcher le shell de remplacer les jokers.
![[Note]](images/note.png)
Quand -n est indiqué, pg_dump ne sauvegarde aucun autre objets de la base que ceux dont les schémas sélectionnés dépendent. Du coup, il n'est pas garantie que la sauvegarde d'un schéma puisse être restaurée avec succès dans une base vide.
Les objets qui ne font pas partie du schéma comme les objets larges ne sont pas sauvegardés quand -n est précisé. Vous pouvez de nouveau ajouter les objets larges avec l'option --blobs.
Ne sauvegarde pas les schémas correspondant au modèle schéma . Le modèle est interprété suivant les même règles que -n. -N peut aussi être indiqué plus d'une fois pour exclure des schémas correspondant à des modèles différents.
Quand les options -n et -N sont indiquées, seront seulement sauvegardés les schémas qui correspondent à au moins une option -n et à aucune option -N. Si -N apparaît sans -n, alors les schémas correspondant à -N sont exclus de ce qui est une sauvegarde normale.
Les identifiants d'objets (OID) sont sauvegardés comme donnée des tables. Cette option est utilisée dans le cas d'applications utilisant des références aux colonnes OID (dans une contrainte de clé étrangère, par exemple). Elle ne devrait pas être utilisée dans les autres cas.
Les commandes d'initialisation des possessions des objets au regard de la base de données originale ne sont pas produites. Par défaut, pg_dump engendre des instructions ALTER OWNER ou SET SESSION AUTHORIZATION pour fixer ces possessions. Ces instructions échouent lorsque le script n'est pas lancé par un superutilisateur (ou par l'utilisateur qui possède tous les objets de ce script). L'option -O est utilisée pour créer un script qui puisse être restauré par n'importe quel utilisateur. En revanche, c'est cet utilisateur qui devient propriétaire de tous les objets.
Cette option n'a d'intérêt que pour le format texte. Pour les formats archive, l'option est spécifiée à l'appel de pg_restore .
Cette option, obsolète, est toujours acceptée pour des raisons de compatibilité ascendante.
Seule la définition des objets (le schéma) est sauvegardée, pas les données.
Le nom du superutilisateur à utiliser lors de la désactivation des déclencheurs. Cela n'a d'intérêt que si l'option --disable-triggers est précisée. (En règle générale, il est préférable de ne pas utiliser cette option et de lancer le script engendré en tant que superutilisateur.)
Sauvegarde seulement les tables (ou vues ou séquences) correspondant à table . Plusieurs tables sont sélectionnables en utilisant plusieurs fois l'option -t. De plus, le paramètre table est interprété comme un modèle suivant les règles utilisées par les commandes \d de psql (voir Modèles). Du coup, plusieurs tables peuvent être sélectionnées en utilisant des caractères joker dans le modèle. Lors de l'utilisation de ces caractères, faites attention à placer le modèle entre guillemets si nécessaire pour empêcher le shell de remplacer les jokers.
Les options -n et -N n'ont aucun effet quand l'option -t est utilisée car les tables sélectionnées par -t seront sauvegardées quelque soit la valeur des options relatives aux schémas et les objets qui ne sont pas des tables ne seront pas sauvegardés.
Quand -t est indiqué, pg_dump ne sauvegarde aucun autre objet de la base que la (ou les) table(s) sélectionnée(s) dépendent. Du coup, il n'est pas garantie que la sauvegarde spécifique d'une table puisse être restaurée avec succès dans une base vide.
Le comportement de l'option -t n'est pas entièrement compatible avec les versions de PostgreSQL™ antérieures à la 8.2. Auparavant, écrire -t tab aurait sauvegardé toutes les tables nommées tab mais maintenant, seules sont sauvegardées celles qui sont visibles dans votre chemin de recherche des objets. Pour obtenir l'ancien comportement, vous pouvez écrire -t '*.tab'. De plus, vous devez aussi écrire quelque chose comme -t sch.tab pour sélectionner une table dans un schéma particulier plutôt que l'ancienne syntaxe -n sch -t tab.
Ne sauvegarde pas les tables correspondant au modèle table . Le modèle est interprété suivant les même règles que -t. -T peut aussi être indiqué plus d'une fois pour exclure des tables correspondant à des modèles différents.
Quand les options -t et -T sont indiquées, seront seulement sauvegardées les tables qui correspondent à au moins une option -t et à aucune option -T. Si -T apparaît sans -t, alors les tables correspondant à -T sont exclues de ce qui est une sauvegarde normale.
Mode verbeux. pg_dump affiche des commentaires détaillés sur les objets et les heures de début et de fin dans le fichier de sauvegarde. Des messages de progression sont également affichés sur la sortie erreur standard.
Les droits d'accès (commandes grant/revoke) ne sont pas sauvegardés.
Cette option désactive l'utilisation des guillemets dollar pour le corps des fonctions. La syntaxe SQL standard des chaînes est utilisée pour la mise entre guillemets du corps des fonctions.
Cette option n'a d'intérêt que dans le cas de sauvegarde des données seules. Dans ce cas, pg_dump inclut à la sortie des commandes permettant de désactiver temporairement les déclencheurs sur les tables cibles lors du chargement des données. Cette commande est utilisée lorsqu'il existe sur les tables des vérifications d'intégrité référentielles ou des déclencheurs dont l'exécution n'est pas souhaitable lors du chargement des données.
À l'heure actuelle, les commandes engendrées par --disable-triggers doivent être exécutées par le superutilisateur. Il est donc nécessaire que le nom du superutilisateur soit également précisé, à l'aide de -S. Le script résultant peut aussi être lancé par le superutilisateur (c'est d'ailleurs préférable).
Cette option n'a d'intérêt que pour le format texte. Pour les formats archive, l'option est spécifiée à l'appel de pg_restore .
Les commandes SET SESSION AUTHORIZATION du standard SQL sont affichées à la place des commandes ALTER OWNER pour préciser le propriétaire d'un objet. Cela rend la sauvegarde plus compatible avec le standard mais, en fonction de l'historique des objets dans la sauvegarde, il se peut que la restauration soit incorrecte. De plus, une sauvegarde qui utilise SET SESSION AUTHORIZATION nécessite pour la restauration des droits superutilisateur tandis que ALTER OWNER requiert des privilèges moins étendus.
Le niveau de compression, si le format d'archive la supporte. Actuellement, seul le format personnalisé supporte la compression.
Les options de ligne de commande suivantes gèrent les paramètres de connexion :
Le nom d'hôte de la machine sur laquelle le serveur de bases de données est exécuté. Si la valeur commence par une barre oblique (/), elle est utilisée comme répertoire pour le socket de domaine Unix. La valeur par défaut est fournie par la variable d'environnement PGHOST, si elle est initialisée. Dans le cas contraire, une connexion sur la socket de domaine Unix est tentée.
Le port TCP ou le fichier local de socket de domaine Unix sur lequel le serveur écoute les connexions. La valeur par défaut est fournie par la variable d'environnement PGPORT, si elle est initialisée. Dans le cas contraire, il s'agit de la valeur fournie à la compilation.
Le nom d'utilisateur utilisé pour la connexion.
Un mot de passe est demandé dans tous les cas. Ce comportement est celui automatiquement obtenu lorsque le serveur requiert une authentification par mot de passe.
Paramètres de connexion par défaut.
Cet outil, comme la plupart des autres outils PostgreSQL™, utilise aussi les variables d'environnement supportées par la bibliothèque libpq (voir Section 29.12, « Variables d'environnement »).
pg_dump exécute intrinsèquement des instructions SELECT . Si des problèmes apparaissent à l'exécution de pg_dump, psql(1) peut être utilisé pour s'assurer de la possibilité de sélectionner des informations dans la base de données. De plus, tout paramètre de connexion par défaut et toute variable d'environnement utilisé par la bibliothèque libpq s'appliqueront.
Si des ajouts locaux à la base template1 ont été effectués dans le groupe de bases de données, il est impératif de s'assurer que la sortie de pg_dump est effectivement restaurée dans une base vide ; dans le cas contraire, il est fort probable que la duplication des définitions des objets ajoutés engendre des erreurs. Pour obtenir une base vide de tout ajout local, on utilise template0 à la place de template1 comme modèle. Par exemple :
CREATE DATABASE foo WITH TEMPLATE template0;
pg_dump souffre quelques limitations :
Lorsqu'une sauvegarde des seules données est sélectionnée et que l'option --disable-triggers est utilisée, pg_dump engendre des commandes de désactivation des déclencheurs sur les tables utilisateur avant l'insertion des données et des commandes de réactivation après l'insertion. Si la restauration est interrompue, il se peut que les catalogues systèmes conservent cette position.
Les fichiers d'une archive tar sont limités à une taille inférieure à 8 Go. (C'est une limitation inhérente au format des fichiers tar.) Ce format ne peut donc pas être utilisé si la représentation textuelle d'une table dépasse cette taille. La taille totale d'une archive tar et de tout autre format de sortie n'est pas limitée, sauf peut-être par le système d'exploitation.
Le fichier de sauvegarde engendré par pg_dump ne contient pas les statistiques utilisées par l'optimiseur pour la planification des requêtes. Il est donc conseillé, pour assurer des performances correctes, de lancer ANALYZE après la restauration d'une sauvegarde.
pg_dump est utilisé pour transférer des données vers les nouvelles versions de PostgreSQL™. De ce fait, le résultat de pg_dump peut être chargé dans des bases de données PostgreSQL™ de versions supérieures. Ce résultat peut aussi être lu par des serveurs PostgreSQL™ plus anciens. Néanmoins, pg_dump ne peut généralement pas lire des bases de données plus récentes ou produire une sauvegarde qui puisse être chargée par des versions antérieures. Pour cela, une édition manuelle de la sauvegarde peut se révéler nécessaire.
Pour sauvegarder une base appelée ma_base dans un script SQL :
$ pg_dump ma_base > base.sql
Pour charger ce script dans une base nouvellement créée et nommée nouvelle_base:
$ psql -d nouvelle_base -f base.sql
Pour sauvegarder une base dans un fichier au format personnalisé :
$ pg_dump -Fc ma_base > base.dump
Pour charger un fichier d'archive dans une nouvelle base nommée nouvelle_base :
$ pg_restore -d nouvelle_base base.dump
Pour sauvegarder la table nommée mytab :
$ pg_dump -t ma_table ma_base > base.sql
Pour sauvegarder toutes les tables du schéma detroit et dont le nom commence par emp sauf la table nommée traces_employes :
$ pg_dump -t 'detroit.emp*' -T detroit.traces_employes ma_base > base.sql
Pour sauvegarder tous les schémas dont le nom commence avec est ou ouest et termine avec gsm, en excluant les schémas dont le nom contient le mot test :
$ pg_dump -n 'est*gsm' -n 'ouest*gsm' -N '*test*' ma_base > base.sql
Idem mais en utilisant des expressions rationnelles dans les options :
$ pg_dump -n '(est|ouest)*gsm' -N '*test*' ma_base > base.sql
Pour sauvegarder tous les objets de la base sauf les tables dont le nom commence par ts_ :
$ pg_dump -T 'ts_*' ma_base > base.sql
Pour indiquer un nom qui comporte des majuscules dans les options -t et assimilées, vous avez besoin d'ajouter des guillemets doubles ; sinon le nom sera converti en minuscule (voirModèles). Les guillemets doubles sont interprétés par le shell, donc vous devez les placer entre guillemets. Du coup, pour sauvegarder une seule table dont le nom comporte des majuscules, vous devez utiliser une commande du style :
$ pg_dump -t '"NomAMajuscule"' ma_base > ma_base.sql
pg_config
