Dalibo SCOP
Formation | Module I1 |
Titre | Sauvegarde et restauration |
Révision | 24.12 |
https://dali.bo/i1_pdf | |
EPUB | https://dali.bo/i1_epub |
HTML | https://dali.bo/i1_html |
Slides | https://dali.bo/i1_slides |
TP | https://dali.bo/i1_tp |
TP (solutions) | https://dali.bo/i1_solutions |
Vous trouverez en ligne les différentes versions complètes de ce document.
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.
Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.
Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.
Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode
Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.
PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.
Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.
Sauf précision contraire, le système d’exploitation utilisé est Linux.
La mise en place d’une solution de sauvegarde est une des opérations les plus importantes après avoir installé un serveur PostgreSQL. En effet, nul n’est à l’abri d’un bogue logiciel, d’une panne matérielle, voire d’une erreur humaine.
Cette opération est néanmoins plus complexe qu’une sauvegarde standard car elle doit pouvoir s’adapter aux besoins des utilisateurs. Quand le serveur ne peut jamais être arrêté, la sauvegarde à froid des fichiers ne peut convenir. Il faudra passer dans ce cas par un outil qui pourra sauvegarder les données alors que les utilisateurs travaillent et qui devra respecter les contraintes ACID pour fournir une sauvegarde cohérente des données.
PostgreSQL va donc proposer des méthodes de sauvegardes à froid (autrement dit serveur arrêté) comme à chaud, mais de toute façon cohérentes. Les sauvegardes pourront être partielles ou complètes, suivant le besoin des utilisateurs.
La méthode de sauvegarde dictera l’outil de restauration. Suivant l’outil, il fonctionnera à froid ou à chaud, et permettra même dans certains cas de faire une restauration partielle.
La sauvegarde logique nécessite que le serveur soit en cours d’exécution. Un outil se connecte à la base et récupère la déclaration des différents objets ainsi que les données des tables.
La technique alors utilisée permet de s’assurer de la cohérence des
données : lors de la sauvegarde, l’outil ne voit pas les modifications
faites par les autres utilisateurs. Pour cela, quand il se connecte à la
base à sauvegarder, il commence une transaction pour que sa vision des
enregistrements de l’ensemble des tables soit cohérente. Cela empêche le
recyclage des enregistrements par VACUUM
pour les
enregistrements dont il pourrait avoir besoin. Par conséquent, que la
sauvegarde dure 10 minutes ou 10 heures, le résultat correspondra au
contenu de la base telle qu’elle était au début de la transaction.
Des verrous sont placés sur chaque table, mais leur niveau est très
faible (Access Share). Il visent juste à éviter la suppression
des tables pendant la sauvegarde, ou la modification de leur structure.
Les opérations habituelles sont toutes permises en lecture ou écriture,
sauf quand elles réclament un verrou très invasif, comme
TRUNCATE
, VACUUM FULL
ou certains
LOCK TABLE
. Les verrous ne sont relâchés qu’à la fin de la
sauvegarde.
Par ailleurs, pour assurer une vision cohérente de la base durant toute la durée de son export, cette transaction de longue durée est de type REPEATABLE READ, et non de type READ COMMITED, celui utilisé par défaut.
Il existe deux outils pour la sauvegarde logique dans la distribution officielle de PostgreSQL :
pg_dump
, pour sauvegarder uniquement des bases de
l’instance, complètement ou partiellement, avec de nombreuses options et
formats ;pg_dumpall
pour sauvegarder toutes les définitions et
données des bases en un seul script SQL, ainsi que les objets globaux
(rôles, tablespaces).Pour la restauration :
psql
exécute les ordres SQL contenus dans des
dumps (sauvegardes) au format texte ;pg_restore
traite uniquement les dumps au
format binaire, et produit le SQL qui permet de restaurer les
données.Il est important de bien comprendre que ces outils n’échappent pas au fonctionnement client-serveur de PostgreSQL. Ils « dialoguent » avec l’instance PostgreSQL uniquement en SQL, aussi bien pour la sauvegarde que la restauration.
Comme ce type d’outil n’a besoin que d’une connexion standard à la
base de données, il peut se connecter en local comme à distance. Cela
implique qu’il doive aussi respecter les autorisations de connexion
configurées dans le fichier pg_hba.conf
.
L’export ne concerne que les données des utilisateurs : les tables
systèmes ne sont jamais concernées, il n’y a pas de risque de les
écraser lors d’un import. En effet, les schémas systèmes
pg_catalog
et information_schema
et leurs
objets sont gérés uniquement par PostgreSQL. Vous n’êtes d’ailleurs pas
censé modifier leur contenu, ni y ajouter ou y effacer quoi que ce
soit !
La configuration du serveur (fichiers postgresql.conf
,
pg_hba.conf
…) n’est jamais incluse et doit être sauvegardée
à part. Un export logique ne concerne que des données et structures.
Les extensions ne posent pas de problème non plus : la sauvegarde contiendra une mention de l’extension, et les données des éventuelles tables gérées par cette extension. Mais à la restauration, il faudra que les binaires de l’extension soient installés sur le système cible.
pg_dump
est l’outil le plus utilisé pour sauvegarder une
base de données PostgreSQL. Une sauvegarde peut se faire de façon très
simple. Par exemple :
$ pg_dump b1 > b1.dump
sauvegardera la base b1 de l’instance locale sur le
port 5432 dans un fichier b1.dump
.
Sous Windows avec le Powershell, préférer la syntaxe
pg_dump -f b1.dump b1
, car une redirection avec
>
peut corrompre la sauvegarde.
Mais pg_dump
permet d’aller bien plus loin que la
sauvegarde d’une base de données complète. Il existe pour cela de
nombreuses options en ligne de commande.
Format | Dump | Restore |
---|---|---|
plain (SQL) | pg_dump -Fp ou pg_dumpall |
psql |
tar | pg_dump -Ft |
pg_restore |
custom | pg_dump -Fc |
pg_restore |
directory | pg_dump -Fd |
pg_restore |
Un élément important est le format des données extraites. Selon l’outil de sauvegarde utilisé et les options de la commande, l’outil de restauration diffère. Le tableau indique les outils compatibles selon le format choisi.
pg_dump
accepte d’enregistrer la sauvegarde suivant
quatre formats :
Pour choisir le format, il faut utiliser l’option
--format
(ou -F
) et le faire suivre par le nom
ou le caractère indiquant le format sélectionné :
plain
ou p
pour un fichier SQL
(texte) ;tar
ou t
pour un fichier tar ;custom
ou c
pour un fichier
« personnalisé » ;directory
ou d
pour le répertoire.Le format plain
est lisible directement par psql. Les
autres nécessitent de passer par pg_restore
pour restaurer
tout ou partie de la sauvegarde.
Le fichier SQL (plain
) est naturellement lisible par
n’importe quel éditeur texte. Le fichier texte est divisé en plusieurs
parties :
COPY
par
défaut) ;Les index figurent vers la fin pour des raisons de performance : il est plus rapide de créer un index à partir des données finales que de le mettre à jour en permanence pendant l’ajout des données.
Les contraintes et le rafraîchissement des vues matérialisées sont aussi à la fin parce qu’il faut que les données soient déjà restaurées dans leur ensemble.
Les triggers ne devant pas être déclenchés pendant la restauration, ils sont aussi restaurés vers la fin.
Les propriétaires et droits sont restaurés sur chacun des objets après création.
Voici un exemple de sauvegarde d’une base de 2 Go pour chaque format :
$ time pg_dump -Fp b1 > b1.Fp.dump
real 0m33.523s
user 0m10.990s
sys 0m1.625s
$ time pg_dump -Ft b1 > b1.Ft.dump
real 0m37.478s
user 0m10.387s
sys 0m2.285s
$ time pg_dump -Fc b1 > b1.Fc.dump
real 0m41.070s
user 0m34.368s
sys 0m0.791s
$ time pg_dump -Fd -f b1.Fd.dump b1
real 0m38.085s
user 0m30.674s
sys 0m0.650s
La sauvegarde la plus longue est la sauvegarde au format personnalisé
(custom
) car elle est compressée.
La sauvegarde au format répertoire se trouve entre la sauvegarde au
format personnalisée et la sauvegarde au format tar
: elle
est aussi compressée mais sur des fichiers plus petits.
En terme de taille, la comparaison donne :
$ du -sh b1.F?.dump
116M b1.Fc.dump
116M b1.Fd.dump
379M b1.Fp.dump
379M b1.Ft.dump
Le format compressé est évidemment le plus petit. Le format texte et
le format tar sont les
plus lourds à cause du manque de compression. Le format tar
est même généralement un peu plus lourd que le format texte à cause de
l’entête des fichiers tar. Évidemment, le taux de compression dépendra
beaucoup des données.
Il convient de bien appréhender les limites de chaque outil de dump et des formats.
Tout d’abord, le format tar
est à éviter. Il n’apporte
aucune plus-value par rapport au format custom
.
Ensuite, même si c’est le plus portable (et le seul disponible avec
pg_dumpall
), le format plain
rend les
restaurations partielles difficiles car il faut extraire manuellement le
SQL d’un fichier texte souvent très volumineux. Ce peut être
ponctuellement pratique cependant, mais pg_restore
régénère
facilement un fichier SQL complet à partir d’une sauvegarde binaire.
Certaines informations (notamment les commandes
ALTER DATABASE … SET
pour modifier un paramètre pour une
base précise) ne sont pas incluses dans le format plain
, à
moins de penser à rajouter --create
(pour les ordres de
création).
Par contre, elles sont incluses dans les entêtes des formats
custom
ou directory
, où un
pg_restore --create
saura les retrouver.
On privilégiera donc les formats custom
et
directory
pour plus de flexibilité à la restauration.
Le format directory
a l’énorme intérêt de permettre la
sauvegarde en parallèle de pg_dump --jobs
, avec de gros
gains de temps de sauvegarde (ou de migration) à la clé.
Enfin, l’outil pg_dumpall
, initialement prévu pour les
montées de versions majeures, permet en fait de sauvegarder les objets
globaux d’une instance.
Ainsi, pour avoir la sauvegarde la plus complète possible d’une
instance, il faut combiner pg_dumpall -g
(pour la
définition des objets globaux), et pg_dump
(pour
sauvegarder les bases de données une par une au format
custom
ou directory
).
La compression permet de réduire énormément la volumétrie d’une sauvegarde logique par rapport à la taille physique des fichiers de données.
Par défaut, pg_dump -Fc
ou -Fd
utilise le
niveau de compression par défaut de la zlib, à priori le meilleur
compromis entre compression et vitesse, correspondant à la valeur 6.
-Z1
comprimera peu mais rapidement, et -Z9
sera nettement plus lent mais compressera au maximum. Seuls des tests
permettent de déterminer le niveau acceptable pour un cas d’utilisation
particulier.
Depuis PostgreSQL 16, des algorithmes de compression plus modernes
sont disponibles : zstd
et lz4
sont
généralement préférables. Une nouvelle syntaxe de l’option
-Z
permet de choisir l’algorithme, le niveau de compression
(1 à 22 pour zstd
, 1 à 12 pour lz4
),
éventuellement d’autres paramètres propres à l’algorithme (comme le mode
long
de zstd
, plus consommateur en RAM).
lz4
a la réputation d’être très rapide mais bien moins bon
en compression. Cependant, le choix du bon algorithme doit se faire en
testant sur vos données réelles sur votre machine avec vos contraintes
en CPU, RAM, place disque et temps.
Le format plain
(pur texte) accepte aussi l’option
-Z
, ce qui permet d’obtenir un export texte compressé.
Cependant, cela ne remplace pas complètement un format
custom
, plus souple.
Même les nouveaux algorithmes sont par défaut monothread. Si l’on
veut paralléliser la compression sur plusieurs processeurs, nous verrons
plus bas qu’il faut soit paralléliser pg_dump
avec
--jobs
, soit compresser la sortie plain avec un
autre outil.
Par défaut, et en dehors du format répertoire, toutes les données
d’une sauvegarde sont renvoyées sur la sortie standard de
pg_dump
. Il faut donc utiliser une redirection pour
renvoyer dans un fichier.
Cependant, il est aussi possible d’utiliser l’option -f
pour spécifier le fichier (ou répertoire pour le format
directory
) de la sauvegarde. L’utilisation de cette option
est conseillée car elle permet à pg_restore
de trouver plus
efficacement les objets à restaurer dans le cadre d’une restauration
partielle.
Il est possible de ne sauvegarder que la structure avec l’option
--schema-only
(ou -s
). De cette façon, seules
les requêtes de création d’objets seront générées. Cette sauvegarde est
généralement très rapide. Cela permet de créer un serveur de tests très
facilement. Dans ce cas, le format plain
suffit, pour
obtenir une suite de commandes SQL.
Il est aussi possible de ne sauvegarder que les données pour les
réinjecter dans une base préalablement créée avec l’option
--data-only
(ou -a
).
Il est possible de sauvegarder plus finement, par section. En fait, un fichier de sauvegarde complet est composé de trois sections :
Il est parfois intéressant de sauvegarder par section plutôt que de sauvegarder schéma et données séparément, car cela permet d’avoir la partie contrainte et index dans un script à part. Cela peut aider à optimiser le temps de certaines restaurations complexes.
Sélection objet par objet :
En dehors de la distinction structure/données, il est possible de
demander de ne sauvegarder qu’un objet. Les seuls objets sélectionnables
au niveau de pg_dump
sont les tables et les schémas d’une
base.
--schema
(ou -n
) permet de
sauvegarder seulement le schéma cité après alors que l’option
--exclude-schema
(ou -N
) permet de sauvegarder
tous les schémas sauf celui cité après l’option.--table
/-t
et
--exclude-table
/-T
.Ni la stucture ni les données des tables ou schémas concernés ne figureront dans la sauvegarde.
Ignorer le contenu de plusieurs tables ou schémas est possible en
répétant l’option ou avec des patterns
(motifs). Par exemple, -T '(log|temp)_'
excluera toutes les
tables dont le nom commence par log_
ou
temp_
.
Les dépendances ne sont pas gérées. Si vous demandez à sauvegarder
une vue avec pg_dump -t unevue
, la sauvegarde ne contiendra
pas les définitions des objets nécessaires à la
construction de cette vue. Toute sauvegarde partielle possède ce risque
d’incohérence.
Par défaut, si certains objets sont trouvés et d’autres non,
pg_dump
ne dit rien, et l’opération se termine avec succès.
Ajouter l’option --strict-names
permet de s’assurer d’être
averti avec une erreur sur le fait que pg_dump
n’a pas
sauvegardé tous les objets souhaités. En voici un exemple
(t1
existe, t20
n’existe pas) :
$ pg_dump -t t1 -t t20 -f postgres.dump postgres
$ echo $?
0
$ pg_dump -t t1 -t t20 --strict-names -f postgres.dump postgres
pg_dump: no matching tables were found for pattern "t20"
$ echo $? 1
Exclure les données :
Pour sauvegarder les données de la base, sauf celles d’une table
donnée, tout en conservant la structure de celle-ci, utiliser l’option
--exclude-table-data=nomtable
. Elle peut servir pour
ignorer le contenu d’une table de trace ou d’une table de travail, par
exemple. Ignorer le contenu de plusieurs tables est possible en répétant
l’option ou avec des patterns
(motifs) : --exclude-table-data="(log|temp)_*"
Fichiers de filtrage :
À partir de PostgreSQL 17, une option plus puissante apparaît :
--filter
. Elle reprend sous une autre forme le mécanisme
d’inclusion/exclusion en ligne de commande.
--filter
est utilisable aussi à la restauration avec
pg_restore
.
Voici un exemple de fichier de filtrage, nommé ici
filter1.txt
:
# ceci est un commentaire
include table t1? exclude table t12
L’appel se fait ainsi :
pg_dump --filter filter1.txt b1
Seront ainsi sauvegardées toutes les tables dont le nom correspond à
l’expression t1?
à l’exception de la table
t12
.
Le premier champ indique si l’on exclue ou inclue des objets. Si une
ligne include
est présente, les objets non inclus
ne seront pas sauvés. S’il n’y a pas de ligne include
, tous
les objets seront par défaut sauvegardés, sauf exclusion explicite.
L’objet en seconde colonne indique le type d’objet et vaut, au choix :
schema
, table
;table_data
(pour les données de table, exclusion
uniquement) ;extension
, foreign_data
(tables
étrangères, inclusion uniquement) ;table_and_children
,
table_data_and_children
(variantes des options
table
et table_data
pour les tables
partitionnées)Le troisième champ est un motif pour la sélection des objets.
Il peut y avoir plusieurs fichiers de filtrage.
--filter -
lit les filtres depuis l’entrée standard
(STDIN
).
Des exemples de ces options sont aussi disponibles dans la documentation officielle.
Par défaut, pg_dump
n’utilise qu’une seule connexion à
la base de données pour sauvegarder la définition des objets et les
données. Cependant, une fois que la première étape de récupération de la
définition des objets est réalisée, l’étape de sauvegarde des données
peut être parallélisée pour profiter des nombreux processeurs
disponibles sur un serveur. La compression est en effet souvent le
facteur limitant d’une sauvegarde.
Cette option n’est compatible qu’avec le format de sortie
directory
(option -Fd
).
La parallélisation de requêtes ne s’applique hélas pas aux ordres
COPY
utilisés par pg_dump
, qui concernent la
table à sauvegarder entière. Mais pg_dump
peut lancer
plusieurs sessions simultanément avec l’option --jobs
(-j
). Elle permet de préciser le nombre de connexions vers
la base de données, et aussi de connexions (Unix) ou threads
(Windows). Cela permet d’améliorer considérablement la vitesse de
sauvegarde.
Une limite est qu’il faut pouvoir réellement paralléliser. Par
exemple, si une table occupe 15 Go sur une base de données de 20 Go, il
y a peu de chance que la parallélisation de pg_dump
change
fondamentalement la durée de sauvegarde. Par contre, la parallélisation
profitera du partitionnement de cette table.
Il existe deux types d’objets binaires dans PostgreSQL : les bytea et les Large Objects.
bytea :
bytea
est un type de données tout à fait classique,
stocké dans les tables utilisateur.
Il est possible d’indiquer le format de sortie des données binaires,
grâce au paramètre bytea_output
qui se trouve dans le
fichier postgresql.conf
. Le défaut est hex
. Le
format hex
utilise deux octets pour enregistrer un octet
binaire de la base alors que le format escape
utilise un
nombre variable d’octets. Dans le cas de données ASCII, ce format
n’utilisera qu’un octet. Dans les autres cas, il en utilisera quatre
pour afficher textuellement la valeur octale de la donnée (un caractère
d’échappement suivi des trois caractères du codage octal). La taille de
la sauvegarde s’en ressent, sa durée de création aussi (surtout en
activant la compression).
Si vous restaurez une sauvegarde d’une base antérieure à la 9.0,
notez que la valeur par défaut était différente
(escape
).
Large Objects :
Les Larges Objects ne sont pas des types de données habituels. L’utilisation en est découragée.
Ces objets sont stockés dans une table système appelé
pg_largeobjects
, et non pas dans les tables utilisateurs.
Du coup, en cas d’utilisation des options
-n
/-N
et/ou -t
/-T
,
la table système contenant les Large Objects sera généralement exclue.
Pour être sûr que les Large Objects soient inclus, il faut en plus
ajouter l’option --large-objects
(-b
).
Il arrive que des applications oublient de purger les Large
Objects inutilisés (un des défauts de ce type est que la purge
n’est pas automatique). pg_dump
peut alors durer bien plus
longtemps, et consommer beaucoup de mémoire.
Par défaut, les extensions sont sauvegardées. Rappelons que la
sauvegarde contiendra généralement une simple mention de l’extension
(CREATE EXTENSION
). Toutefois, l’extension peut indiquer
que ses propres tables système doivent être sauvegardées.
Cependant, dans le cas où les options de sélection/exclusion de
schéma (-n
/-N
) ou de tables
(-t
/-T
) sont utilisées, les extensions ne sont
pas sauvegardées. Or, elles pourraient être nécessaires pour les schémas
et tables sélectionnées. L’option -e
permet de forcer la
sauvegarde des extensions précisées.
À l’inverse, depuis PostgreSQL 17, il est possible de ne pas sauvegarder des extensions d’une base :
# exclure toute les extensions dont le nom commence par 'pg'
pg_dump -U postgres -Fc --exclude-extension=pg*
Il reste quelques options plus anecdotiques mais qui peuvent se révéler très pratiques.
–create :
--create
(ou -C
) n’a d’intérêt qu’en format
texte (-Fp
), pour ajouter les instructions de création de
base. (Avec les autres formats, il vaut mieux utiliser l’option
équivalente lors de la restauration avec pg_restore
. Le nom
de la base est dans la sauvegarde.)
CREATE DATABASE b1 WITH TEMPLATE = template0
= 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';
ENCODING ALTER DATABASE b1 OWNER TO postgres;
et éventuellement, s’il y a un paramétrage propre à la base :
ALTER DATABASE b1 SET work_mem TO '100MB' ;
ALTER ROLE chef IN DATABASE b1 SET work_mem TO '1GB';
À partir de la version 11, ces dernières informations sont incluses
d’office aux formats custom
ou directory
, où
pg_restore --create
peut les retrouver.
Jusqu’en version 10, elles ne se retrouvaient que dans
pg_dumpall
(sans -g
), ce qui n’était pas
pratique quand on ne restaurait qu’une base.
Dans tous les cas, il faut bien vérifier que votre procédure de restauration reprend ce paramétrage.
On peut rajouter --clean
pour ajouter un
DROP DATABASE
préalable dans le script généré (option
dangereuse !), et même --clean --if-exists
pour éviter une
erreur si la base cible n’existait en fait pas.
Masquer des droits et autres propriétés :
--no-owner
, --no-privileges
,
--no-comments
et --no-tablespaces
permettent
de ne pas récupérer respectivement le propriétaire, les droits, le
commentaire et le tablespace de l’objet dans la sauvegarde s’ils posent
problème.
Ordres INSERT au lieu de COPY :
Par défaut, pg_dump
génère des commandes
COPY
, qui sont bien plus rapides que les
INSERT
. Cependant, notamment pour pouvoir restaurer plus
facilement la sauvegarde sur un autre moteur de bases de données, il est
possible d’utiliser des INSERT
au lieu des
COPY
. Il faut forcer ce comportement avec l’option
--inserts
.
S’il s’agit de réimporter ces données dans PostgreSQL, un intérêt des
INSERT
est la flexibilité pour rejeter des lignes :
l’option --on-conflict-do-nothing
permet d’éviter des
messages d’erreur si un INSERT
tente d’insérer une ligne
violant une contrainte existante. Très souvent, ce sera pour éviter des
problèmes de doublons (même clé primaire) dans une table déjà
partiellement chargée, ou des soucis de cohérence avec d’autres données
(non respect de clés étrangères).
Par contre, l’inconvénient des INSERT
ligne à ligne est
la lenteur, par rapport à un COPY
qui traite tout en bloc
(même avec des astuces comme synchronous_commit=off
). Pour
diminuer cet inconvénient, il est possible d’insérer les lignes par
paquets avec l’option --rows-per-insert
: si un
INSERT
échoue, seulement ce nombre de lignes sera
annulé.
(Avec PostgreSQL 17, --inserts
devient un peu moins
utile : la nouvelle syntaxe COPY … WITH (ON_ERROR ignore)
permet de ne pas arrêter l’insertion, par exemple si un champ est trop
grand. Par contre, une violation de contrainte, comme un doublon,
continuera de mener à un rejet par COPY
de toutes les
lignes).
Tables partitionnées :
Autres options :
Il existe des options pour gérer l’export des tables partitionnées,
que nous ne détaillerons pas ici :
--load-via-partition-root
, et depuis la version 16 :
--table-and-children
,
--exclude-table-and-children
et
--exclude-table-data-and-children
.
Enfin, l’option -v
(ou --verbose
) permet de
voir la progression de la sauvegarde.
pg_dumpall
sauvegarde toute l’instance par défaut. Cet
outil possède peu d’options de sélection. Il ne permet ni compression ni
parallélisation.
pg_dumpall
peut toutefois être intéressant
ponctuellement. Il permet notamment la sauvegarde de la définition des
objets globaux, c’est-à-dire les utilisateurs et les tablespaces, entre
autres. Il est donc complémentaire de pg_dump
, qui ne
sauvegarde que la structure et les données d’une base de données
précise, sans rôle ni tablespace.
S’il y a plusieurs bases et peu de volumétrie, reconstruire
l’instance, rôles compris, est très simple avec pg_dumpall
car il suffit de rejouer un seul fichier de sauvegarde, ou d’utiliser un
pipe :
pg_dumpall -h ancienserveur | psql -h nouveauserveur
Contrairement à pg_dump
, pg_dumpall
ne
dispose que d’un format en sortie : des ordres SQL en texte. La
sauvegarde est envoyée sur la sortie standard, ou le fichier précisé
avec l’option --file
(-f
).
En général, on effectuera la sauvegarde logique d’une instance avec
un appel à pg_dumpall -g
pour les objets globaux, et des
appels à pg_dump
pour chaque base, pour profiter de la
parallélisation et de la compression des données.
Si l’on veut presque tout sauvegarder avec pg_dumpall
,
il existe une option --exclude-database
pour ignorer une
des bases, option qui peut être répétée ou contenir des motifs.
pg_dumpall
a quelques options communes avec
pg_dump
, notamment les options de connexion,
--schema-only
, --no-owner
ou
--no-subscriptions
. D’autres options communes, comme
--inserts
, --rows-per-insert
, sont rarement
utilisées avec pg_dumpall
. À partir de PostgreSQL 17,
l’option --filter
peut indiquer un fichier de filtrage, qui
ne peut servir qu’à exclure des objets de type
database
.
pg_dumpall
étant créé pour sauvegarder l’instance
complète, il ne dispose quasiment pas d’options de sélection d’objets.
Le plus important est la sauvegarde en SQL :
--roles-only
/
-r
) ;--tablespaces-only
/
-t
) ;--globals-only
/
-g
), dont les rôles et tablespaces.Par exemple, la sauvegarde des rôles renvoie ce script :
-r
$ pg_dumpall --
-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE admin;
ALTER ROLE admin WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN
NOREPLICATION NOBYPASSRLS;CREATE ROLE dupont;
ALTER ROLE dupont WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
NOREPLICATION NOBYPASSRLSPASSWORD 'md5505548e69dafa281a5d676fe0dc7dc43';
CREATE ROLE durant;
ALTER ROLE durant WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
NOREPLICATION NOBYPASSRLSPASSWORD 'md56100ff994522dbc6e493faf0ee1b4f41';
CREATE ROLE martin;
ALTER ROLE martin WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
NOREPLICATION NOBYPASSRLSPASSWORD 'md5d27a5199d9be183ccf9368199e2b1119';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN
REPLICATION BYPASSRLS;CREATE ROLE utilisateur;
ALTER ROLE utilisateur WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN
NOREPLICATION NOBYPASSRLS;
--
-- User Configurations
--
--
-- User Config "u1"
--
ALTER ROLE u1 SET maintenance_work_mem TO '256MB';
--
-- Role memberships
--
GRANT admin TO dupont GRANTED BY postgres;
GRANT admin TO durant GRANTED BY postgres;
GRANT utilisateur TO martin GRANTED BY postgres;
--
-- PostgreSQL database cluster dump complete
--
On remarque que le mot de passe est sauvegardé sous forme de hash.
La sauvegarde des rôles se fait en lisant le catalogue système
pg_authid
. Seuls les superutilisateurs ont accès à ce
catalogue système car il contient les mots de passe des utilisateurs.
pg_dumpall
peut s’utiliser sans rôle superutilisateur, avec
l’option --no-role-passwords
. Celle-ci a pour effet de ne
pas sauvegarder les mots de passe. Dans ce cas, pg_dumpall
va lire le catalogue système pg_roles
qui est accessible
par tout le monde.
Les commandes pg_dump
et pg_dumpall
se
connectent au serveur PostgreSQL comme n’importe quel autre outil (psql,
pgAdmin, etc.). Ils disposent donc des options habituelles pour se
connecter :
-h
ou --host
pour indiquer l’alias ou
l’adresse IP du serveur ;-p
ou --port
pour préciser le numéro de
port ;-U
ou --username
pour spécifier
l’utilisateur ;-W
ne permet pas de saisir le mot de passe en ligne de
commande, mais force pg_dump
à le demander (en interactif
donc, et qu’il soit vérifié ou non, ceci dépendant de la méthode
d’authentification).En général, une sauvegarde automatique est effectuée sur le serveur
directement par l’utilisateur système postgres
(connexion
par peer
sans mot de passe), ou à distance avec le mot de
passe stocké dans un fichier .pgpass
.
Il est possible d’indiquer plusieurs hôtes et ports. C’est utile pour lancer une sauvegarde depuis une instance en réplication, ou à défaut depuis le primaire. L’hôte sélectionné est le premier qui répond au paquet de démarrage. Si l’authentication ne passe pas, la connexion sera en erreur. Il est aussi possible de préciser si la connexion doit se faire sur un serveur en lecture/écriture ou en lecture seule. Par exemple on effectuera une sauvegarde depuis le premier serveur disponible ainsi :
pg_dumpall -h secondaire,primaire -p 5432,5433 -U postgres -f sauvegarde.sql
Si la connexion nécessite un mot de passe, ce dernier sera réclamé
lors de la connexion. Il faut donc faire attention avec
pg_dumpall
, qui se connecte à chaque base de données, une
par une.
Il est préférable d’utiliser un fichier .pgpass
qui
indique les mots de passe de connexion. Ce fichier est créé à la racine
du répertoire personnel de l’utilisateur qui exécute la sauvegarde. Il
contient les informations suivantes :
hote:port:base:utilisateur:mot de passe
Ce fichier est sécurisé, dans le sens où seul l’utilisateur doit
avoir le droit de lire et écrire ce fichier (c’est-à-dire des droits
600
). L’outil vérifie cela avant d’accepter d’utiliser les
informations qui s’y trouvent.
Même si ce n’est pas obligatoire, il est recommandé d’utiliser un rôle de connexion disposant des droits de superutilisateur pour la sauvegarde et la restauration. En effet, pour sauvegarder, il faut pouvoir :
pg_hba.conf
, être propriétaire ou avoir le privilège
CONNECT
;USAGE
sur le schéma ;SELECT
sur la table.Pour restaurer, il faut pouvoir :
pg_hba.conf
, être propriétaire ou avoir le privilège
CONNECT
;-C
de pg_restore
)CREATE
sur celle-ci ;CREATE
sur celui-ci ;CREATE
sur celui-ci ;dump
.Le nombre de ces privilèges explique pourquoi il n’est parfois possible de restaurer qu’avec un superutilisateur.
À part pour le format directory
, il est possible
d’envoyer la sortie standard à un autre outil. Pour les formats non
compressés (tar
et plain
), cela permet d’abord
de compresser avec l’outil de son choix.
Il y a un intérêt même avec le format custom
: celui
d’utiliser des outils de compression plus performants que la zlib, comme
bzip2
ou lzma
(compression plus forte au prix
d’une exécution plus longue) ou pigz
, pbzip2
, ou encore xz
,
zstd
… beaucoup plus rapides grâce à l’utilisation de
plusieurs threads. On met ainsi à profit les nombreux processeurs des
machines récentes, au prix d’un très léger surcoût en taille. Ces outils
sont présents dans les distributions habituelles.
Au format custom
, il faut penser à désactiver la
compression par défaut, comme dans cet exemple avec
pigz
:
$ pg_dump -Fc -Z0 -v foobar | pigz > sauvegarde.dump.gzip
On peut aussi utiliser n’importe quel autre outil Unix. Par exemple, pour répartir sur plusieurs fichiers :
$ pg_dump | split
Nous verrons plus loin que la sortie de pg_dump
peut
même être fournie directement à pg_restore
ou
psql
, ce qui est fort utile dans certains cas.
pg_dump
permet de réaliser deux types de sauvegarde :
une sauvegarde texte (via le format plain
) et une
sauvegarde binaire (via les formats tar, personnalisé et
répertoire).
Chaque type de sauvegarde aura son outil :
psql
pour les sauvegardes textes ;pg_restore
pour les sauvegardes binaires.psql
est la console interactive de PostgreSQL. Elle
permet de se connecter à une base de données et d’y exécuter des
requêtes, soit une par une, soit un script complet. Or, la sauvegarde
texte de pg_dump
et de pg_dumpall
fournit un
script SQL. Ce dernier est donc exécutable via psql
.
C’est parfois très pratique, mais il faut savoir que
psql
applique les ordres reçus à la suite, donc sans aucune
parallélisation (sauf à lancer plusieurs sessions différentes sur des
parties du script). Il ne permet pas de choisir les objets
restaurés.
psql
étant le client standard de PostgreSQL, le dump au
format plain
se trouve être un script SQL qui peut
également contenir des commandes psql, comme \connect
pour
se connecter à une base de données (ce que fait pg_dumpall
pour changer de base de données).
On bénéficie alors de toutes les options de psql
, les
plus utiles étant celles relatives au contrôle de l’aspect
transactionnel de l’exécution.
On peut restaurer avec psql
de plusieurs manières :
cat b1.dump | psql b1
psql -f b1.dump b1
b1 =# \i b1.dump
Dans les deux premiers cas, la restauration peut se faire à distance alors que dans le dernier cas, le fichier de la sauvegarde doit se trouver sur le serveur de bases de données.
Le script est exécuté comme tout autre script SQL. Comme il n’y a pas
d’instruction BEGIN
au début, l’échec d’une requête ne va
pas empêcher l’exécution de la suite du script, ce qui va généralement
apporter un flot d’erreurs. De plus psql
fonctionne par
défaut en autocommit : après une erreur, les requêtes précédentes sont
déjà validées. La base de données sera donc dans un état à moitié
modifié, ce qui peut poser un problème s’il ne s’agissait pas d’une base
vierge.
Il est donc souvent conseillé d’utiliser l’option en ligne de
commande -1
pour que le script complet soit exécuté dans
une seule transaction. Dans ce cas, si une requête échoue, aucune
modification n’aura réellement lieu sur la base, et il sera possible de
relancer la restauration après correction du problème.
Enfin, il est à noter qu’une restauration partielle de la sauvegarde est assez complexe à faire. Deux solutions existent, parfois pénibles :
grep
et/ou
sed
pour extraire les portions voulues, ce qui peut
facilement devenir long et complexe.Deux variables psql peuvent être modifiées, ce qui permet d’affiner le comportement de psql lors de l’exécution du script :
ON_ERROR_ROLLBACK :
Par défaut il est à off
, et toute erreur dans
une transaction entraîne le ROLLBACK
de
toute la transaction. Les commandes suivantes échouent toutes. Activer
ON_ERROR_ROLLBACK
permet de n’annuler que la commande en
erreur. psql effectue des savepoints avant chaque ordre, et y
retourne en cas d’erreur, avant de continuer le script, toujours dans la
même transaction.
Cette option n’est donc pas destinée à tout arrêter
en cas de problème, au contraire. Mais elle peut être utile pour passer
outre à une erreur quand on utilise -1
pour enrober le
script dans une transaction.
ON_ERROR_ROLLBACK
peut valoir interactive
(ne s’arrêter dans le script qu’en mode interactif, c’est-à-dire quand
c’est une commande \i
qui est lancée) ou on
dans quel cas il est actif en permanence.
ON_ERROR_STOP :
Par défaut, dans un script, une erreur n’arrête pas
le déroulement du script. On se retrouve donc souvent avec un ordre en
erreur, et beaucoup de mal pour le retrouver, puisqu’il est noyé dans la
masse des messages. Quand ON_ERROR_STOP
est positionné à
on
, le script est interrompu dès qu’une erreur est
détectée.
C’est l’option à privilégier quand on veut arrêter un script au
moindre problème. Si -1
est utilisé, et que
ON_ERROR_ROLLBACK
est resté à off
, le script
entier est bien sûr annulé, et on évite les nombreux messages de
type :
ERROR: current transaction is aborted,
commands ignored until end of transaction block
après la première requête en erreur.
Les variables psql peuvent être modifiées :
.psqlrc
(à déconseiller, cela va
modifier le comportement de psql pour toute personne utilisant le
compte) :cat .psqlrc
\set ON_ERROR_ROLLBACK interactive
psql --set=ON_ERROR_ROLLBACK='on'
psql -v ON_ERROR_ROLLBACK='on'
psql>\set ON_ERROR_ROLLBACK on
pg_restore
est un outil capable de restaurer les
sauvegardes au format binaire, quel qu’en soit le format. Il offre de
nombreuses options très intéressantes, la plus essentielle étant de
permettre une restauration partielle de façon aisée. L’exemple typique
d’utilisation de pg_restore
est le suivant :
pg_restore -d b1 b1.dump
La base de données où la sauvegarde va être restaurée est indiquée
avec l’option -d
et le nom du fichier de sauvegarde est le
dernier argument dans la ligne de commande.
Avec pg_restore
, il est indispensable de fournir le nom
de la base de données de connexion avec l’option -d
. Le
fichier à restaurer s’indique en dernier argument sur la ligne de
commande.
L’option --create
(ou C
) permet de créer la
base de données cible. Dans ce cas l’option -d
doit
indiquer une autre base déjà existante (postgres
souvent), afin que pg_restore
se connecte pour exécuter
l’ordre CREATE DATABASE
. Après cela, il se connecte à la
base nouvellement créée pour exécuter les ordres SQL de restauration.
Pour vérifier cela, on peut lancer la commande sans l’option
-d
. En observant le code SQL renvoyé on remarque un
\connect
:
$ pg_restore -C b1.dump
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: b1; Type: DATABASE; Schema: -; Owner: postgres
--
CREATE DATABASE b1 WITH TEMPLATE = template0 ENCODING = 'UTF8'
= 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
LC_COLLATE
ALTER DATABASE b1 OWNER TO postgres;
connect b1
\
SET statement_timeout = 0;
-- Suite du dump…
Noter que le propriétaire de la base est conservé.
Avec l’option --create
, il n’est pas possible de
restaurer directement dans une base avec un nom différent de la base de
données d’origine, car le nom est stocké dans la sauvegarde. Il faut
créer la nouvelle base auparavant.
--create
peut être associé à
--clean --if-exists
pour détruire une base préexistante, et
sans message d’erreur si elle n’existe en fait pas. Il vaut mieux être
sûr d’être sur le bon serveur.
L’option -f
envoie le SQL généré dans un script, qui
sera donc du SQL parfaitement lisible :
$ pg_restore base.dump -f script_restauration.sql
-f
n’indique pas le fichier de
sauvegarde, mais bien la sortie de pg_restore
quand on ne
restaure pas vers une base. N’écrasez pas votre sauvegarde !
Avec -f -
, le SQL transmis au serveur est affiché sur la
sortie standard, ce qui est très pratique pour voir ce qui va être
restauré, et par exemple valider les options d’une restauration
partielle, récupérer des définitions d’index ou de table, voire
« piper » le contenu vers un autre outil.
pg_restore
exige soit -d
, soit
-f
: soit on restaure dans une base, soit on génère un
fichier SQL. (Avant la version 12, -f
était le défaut si ni
-d
ni -f
n’étaient précisés ; il était alors
conseillé de rediriger la sortie standard plutôt que d’utiliser
-f
pour éviter toute ambiguïté.)
Pour obtenir le journal d’activité complet d’une restauration, il suffit classiquement de rediriger la sortie :
$ pg_restore -d cible --verbose base.dump > restauration.log 2>&1
Comme pour pg_dump
, il est possible de ne restaurer que
la structure, ou que les données.
Il est possible de restaurer une base section par section. En fait, un fichier de sauvegarde complet est composé de trois sections : la définition des objets, les données, la définition des contraintes et index. Il peut être intéressant de restaurer par section pour optimiser la durée de certaines restaurations complexes.
C’est encore plus intéressant dans les cas un peu délicats (modification des fichiers, imports partiels). On peut alors traiter séparément chaque étape. Par exemple, si l’on veut modifier le SQL (modifier des noms de champs, renommer des index…) tout en tenant à compresser ou paralléliser la sauvegarde pour des raisons de volume :
$ mkdir data.dump
$ pg_dump -d source --section=pre-data -f predata.sql
$ pg_dump -d source --section=data -Fd --jobs=8 -f data.dump
$ pg_dump -d source --section=post-data -f postdata.sql
Après modification, on réimporte :
$ psql -d cible < predata.sql
$ pg_restore -d cible --jobs=8 data.dump
$ psql -d cible < postdata.sql
Le script issu de --section=pre-data
(ci-dessous, allégé
des commentaires) contient les CREATE TABLE
, les
contraintes de colonne, les attributions de droits mais aussi les
fonctions, les extensions, etc. :
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
CREATE EXTENSION IF NOT EXISTS plperl WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plperl IS 'PL/Perl procedural language';
CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
COMMENT ON EXTENSION pg_stat_statements
IS 'track execution statistics of all SQL statements executed';
CREATE FUNCTION public.impair(i integer) RETURNS boolean
LANGUAGE sql IMMUTABLEAS $$
select mod(i,2)=1 ;
$$;
ALTER FUNCTION public.impair(i integer) OWNER TO postgres;
SET default_tablespace = '';
SET default_table_access_method = heap;
CREATE TABLE public.fils (
integer,
i CONSTRAINT impair_ck CHECK ((public.impair(i) IS TRUE)),
CONSTRAINT nonzero_ck CHECK ((i > 0))
);ALTER TABLE public.fils OWNER TO postgres;
CREATE TABLE public.pere (
integer NOT NULL
i
);
ALTER TABLE public.pere OWNER TO postgres;
La partie --section=data
, compressée ou non, ne contient
que des ordres COPY
:
# lecture du fichier data.dump sur la sortie standard (-)
$ pg_restore -f - data.dump
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
COPY public.fils (i) FROM stdin;
1
2
…
\.COPY public.pere (i) FROM stdin;
1
2
… \.
Quant au résultat de --section=pre-data
, il regroupe
notamment les contraintes de clés primaire, de clés étrangères, et les
créations d’index. Il est nettement plus rapide de charger la table
avant de poser contraintes et index que l’inverse.
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
ALTER TABLE ONLY public.pere
ADD CONSTRAINT pere_pkey PRIMARY KEY (i);
CREATE UNIQUE INDEX fils_i_idx ON public.fils USING btree (i);
ALTER TABLE ONLY public.fils
ADD CONSTRAINT fk FOREIGN KEY (i) REFERENCES public.pere(i);
pg_restore
fournit quelques options supplémentaires pour
sélectionner les objets à restaurer. Les options
--schema
/-n
,
--exclude-schema
/-N
et
--table
/-t
ont la même signification que pour
pg_dump
. Par contre, -T
a une signification
différente entre les deux outils : -T
précise un trigger
dans pg_restore
.
Il existe en plus les options --index
/-I
et
--function
/-P
pour restaurer respectivement un
index et une routine stockée spécifique.
Là aussi, il est possible de mettre plusieurs fois les options pour restaurer plusieurs objets de même type ou de type différent.
Par défaut, si le nom de l’objet est inconnu, pg_restore
ne dit rien, et l’opération se termine avec succès. Ajouter l’option
--strict-names
permet de s’assurer d’être averti avec une
erreur sur le fait que pg_restore
n’a pas restauré l’objet
souhaité. En voici un exemple :
$ pg_restore -t t2 -d postgres pouet.dump
$ echo $?
0
$ pg_restore -t t2 --strict-names -d postgres pouet.dump
pg_restore: [archiver] table "t2" not found
$ echo $?
1
Depuis PostgreSQL 17, pg_restore
accepte des fichiers de
filtrage, via l’option --filter
, similaire à l’option de
pg_dump
vue plus haut. Les types d’objets que l’on peut
inclure ou exclure sont : schema
, table
, et,
en inclusion uniquement : index
, function
trigger
.
pg_restore
fournit un autre moyen avancé pour
sélectionner les objets.
L’option -l
(--list
) permet de connaître la
liste des actions que réalisera pg_restore
avec un fichier
particulier. Par exemple :
$ pg_restore -l b1.dump
;
; Archive created at 2020-09-16 15:44:35 CET
; dbname: b1
; TOC Entries: 15
; Compression: -1
; Dump Version: 1.14-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 13.0
; Dumped by pg_dump version: 13.0
;
;
; Selected TOC Entries:
;
200; 1255 24625 FUNCTION public f1() postgres
201; 1255 24626 PROCEDURE public p1() postgres
197; 1259 24630 TABLE public t2 postgres
199; 1259 24637 MATERIALIZED VIEW public mv1 postgres
196; 1259 24627 TABLE public t1 postgres
198; 1259 24633 VIEW public v1 postgres
3902; 0 24627 TABLE DATA public t1 postgres
3903; 0 24630 TABLE DATA public t2 postgres
3778; 2606 24642 CONSTRAINT public t2 t2_pkey postgres
3776; 1259 24643 INDEX public t1_c1_idx postgres
3904; 0 24637 MATERIALIZED VIEW DATA public mv1 postgres
Toutes les lignes qui commencent avec un point-virgule sont des
commentaires. Le reste indique les objets à créer : la fonction
f1
, la procédure stockée p1
, les tables
t1
et t2
, la vue v1
, la clé
primaire sur t2
et l’index sur t1
. Il indique
aussi les données à restaurer avec des lignes du type
TABLE DATA
. Donc, dans cette sauvegarde, il y a les données
pour les tables t1
et t2
. Enfin, il y a le
rafraîchissement des données de la vue matérialisée
mv1
.
Il est possible de stocker cette information dans un fichier, de
modifier le fichier pour qu’il ne contienne que les objets que l’on
souhaite restaurer, et de demander à pg_restore
, avec
l’option -L
(--use-list
), de ne prendre en
compte que les actions contenues dans le fichier. Voici un exemple
complet :
$ pg_restore -l b1.dump > liste_actions
$ cat liste_actions | \
grep -v "f1" | \
grep -v "TABLE DATA public t2" | \
grep -v "INDEX public t1_c1_idx" \
> liste_actions_modifiee
$ createdb b1_new
$ pg_restore -L liste_actions_modifiee -d b1_new -v b1.dump
pg_restore: connecting to database for restore
pg_restore: creating PROCEDURE "public.p1()"
pg_restore: creating TABLE "public.t2"
pg_restore: creating MATERIALIZED VIEW "public.mv1"
pg_restore: creating TABLE "public.t1"
pg_restore: creating VIEW "public.v1"
pg_restore: processing data for table "public.t1"
pg_restore: creating CONSTRAINT "public.t2 t2_pkey"
pg_restore: creating MATERIALIZED VIEW DATA "public.mv1"
L’option -v
de pg_restore
permet de
visualiser sa progression dans la restauration. On remarque bien que la
fonction f1
ne fait pas partie des objets restaurés, tout
comme l’index sur t1
et les données de la table
t2
.
Répétons qu’il est à la charge de l’utilisateur de fournir une liste
cohérente en terme de dépendances. Par exemple, sélectionner seulement
l’entrée TABLE DATA
alors que la table n’existe pas dans la
base de données cible provoquera une erreur.
Historiquement, pg_restore
n’utilise qu’une seule
connexion à la base de données pour y exécuter en série toutes les
requêtes nécessaires pour restaurer la base. Cependant, une fois que la
première étape de création des objets est réalisée, l’étape de copie des
données et celle de création des index peuvent être parallélisées pour
profiter des nombreux processeurs disponibles sur un serveur. L’option
--jobs
/-j
permet de préciser le nombre de
connexions réalisées vers la base de données. Chaque connexion est gérée
dans pg_restore
par un processus sous Unix et par un thread
sous Windows.
Cela permet d’améliorer considérablement la vitesse de restauration, en partie parce que les index peuvent être calculés en parallèle. Par exemple, dans un test, une restauration d’une base de 150 Go prenait 5 h avec une seule connexion, mais seulement 3 h avec plusieurs connexions.
Il est à noter que, même si PostgreSQL supporte la parallélisation de
certains types de requêtes, cela ne concerne pas la commande
COPY
de pg_restore
. Il est donc possible qu’il
n’y ait pas de gain si une table contient l’essentiel de la volumétrie :
la table ne sera importée que par une seule connexion.
Le format plain
(texte) n’est pas compatible avec cette
option.
Il reste quelques options plus anecdotiques mais qui peuvent se révéler très pratiques.
--no-owner
, --no-privileges
,
--no-comments
et --no-tablespaces
permettent
de ne pas restaurer, respectivement, le propriétaire, les droits, le
commentaire et le tablespace des objets.
Regroupement des transactions :
L’option --single-transaction
/-1
permet
d’exécuter pg_restore
dans une seule transaction. Le
premier intérêt est bien sûr de garantir l’atomicité de l’import. Un
autre intérêt est de supprimer l’essentiel des synchronisations sur
disque à chaque ordre. (Sur ce dernier point, une alternative est de
paramétrer PGOPTIONS='-c synchronous_commit=off
avant
l’appel à pg_restore
.)
Malheureusement, --single-transaction
est incompatible
avec le parallélisme (--jobs
) car on ne peut pas avoir
plusieurs sessions qui partagent la même transaction. Un autre problème
est le maintien d’un grand nombre de verrous pendant toute la durée de
la restauration. Avec des milliers de tables, la mémoire dédiée aux
verrous peut ne pas suffire.
À partir de PostgreSQL 17, l’option --transaction-size
est voisine : il s’agit de regrouper les ordres par paquets (par exemple
100 pour --transaction-size=100
) et d’intercaler des
BEGIN
et COMMIT
. Il n’y a plus d’atomicité,
mais le nombre de synchronisations est réduit sans poser trop de
verrous. Ce peut être intéressant avec de très nombreux objets. De plus,
cette option est compatible avec le parallélisme.
Suppression préalable :
L’option --clean
/-c
permet d’exécuter des
DROP
des objets avant de les restaurer. Ce qui évite les
conflits à la restauration de tables par exemple : l’ancienne est
détruite avant de restaurer la nouvelle. On ajoutera souvent
--if-exists
pour éviter un message d’erreur si l’objet
n’existe en fait pas.
La sauvegarde logique est très simple à mettre en place. Mais certaines considérations sont à prendre en compte lors du choix de cette solution : comment gérer les statistiques, quelle est la durée d’exécution d’une sauvegarde, quelle est la taille d’une sauvegarde, etc.
Il est fréquent de générer une sauvegarde sur une version de PostgreSQL pour l’importer sur un serveur de version différente, en général plus récente. Une différence de version mineure n’est d’habitude pas un problème, ce sont les versions majeures (9.6, 12, 17…) qui importent.
Il faut bien distinguer les versions des instances source et cible,
et les versions des outils pg_dump
et
pg_restore
de celles des instances. Attention, il peut y
avoir plusieurs versions des outils sur un serveur ou un client. La
version sur le poste où l’on sauvegarde peut différer de la version du
serveur. Or, leurs formats de sauvegarde custom
ou
directory
diffèrent. C’est une bonne idée de préciser les
chemins complets des binaires pg_dump
et
pg_restore
, qui contiennent en général le numéro de
version.
pg_dump
sait sauvegarder depuis une instance de version
antérieure à la sienne Il refusera de tenter une sauvegarde d’une
instance de version postérieure. Donc, pour sauvegarder une base
PostgreSQL 14, utilisez un pg_dump
de version 14, 15 ou
supérieure.
PostgreSQL 15 et supérieurs ne savent plus sauvegarder des serveurs
antérieurs à PostgreSQL 9.2 (publié en 2012). Au besoin, il faudra
générer la sauvegarde avec un client pg_dump
ou
pg_dumpall
en version 14 ou moins, ou les outils du
serveur. La sauvegarde générée sera compatible avec les versions
récentes (sauf incompatibilité à un autre niveau, comme une
fonctionnalité abandonnée).
pg_restore
sait lire les sauvegardes des versions
antérieures à la sienne. Il peut restaurer vers une instance de version
supérieure à la sienne, même s’il vaut mieux utiliser le
pg_restore
de la même version que l’instance cible. La
restauration vers une version antérieure à l’outil a de bonnes chances
d’échouer en raison d’une évolution de la syntaxe.
En dernier recours, le format plain
(SQL pur) est
toujours lisible par psql
, et
pg_restore -f dump.sql
permet toujours d’en regénérer un
depuis une sauvegarde plain
ou directory
. Il
peut même être envoyé directement sans fichier intermédiaire, par
exemple ainsi :
pg_restore -f dump.sql | psql -h serveurcible -d basecible
S’il y a une nouveauté ou une régression que l’instance cible ne sait
pas interpréter, il est possible de modifier ce SQL. Dans beaucoup de
cas, il suffira d’adapter le SQL dans les parties générées par
--section=pre-data
et --section=post-data
, et
de charger directement les données avec
pg_restore --section=data
.
Enfin, puisqu’il s’agit de sauvegardes logiques, des différences de système d’exploitation ne devraient pas poser de problème de compatibilité supplémentaire.
pg_dumpall
n’est intéressant que pour récupérer les
objets globaux. Le fait qu’il ne supporte pas les formats binaires
entraîne que sa sauvegarde n’est utilisable que dans un cas : la
restauration de toute une instance. C’est donc une sauvegarde très
spécialisée, ce qui ne conviendra pas à la majorité des cas.
Le mieux est donc d’utiliser pg_dumpall
avec l’option
-g
, puis d’utiliser pg_dump
pour sauvegarder
chaque base dans un format binaire.
Avant la version 11, les paramètres sur les bases
(ALTER DATABASE xxx SET param=valeur;
) ne seront pas du
tout dans les sauvegardes : pg_dumpall -g
n’exporte pas les
définitions des bases (voir pg_dump --create
plus
haut).
Les paramètres sur les rôles dans les bases figureront dans
l’export de pg_dumpall -g
ainsi :
ALTER role xxx IN DATABASE xxx SET param=valeur;
mais les bases n’existeront pas forcément au moment où
l’ALTER ROLE
sera exécuté ! Il faudra donc penser à les
restaurer à la main…
Voici un exemple de script minimaliste :
#!/bin/sh
# Script de sauvegarde pour PostgreSQL
REQ="SELECT datname FROM pg_database WHERE datallowconn ORDER BY datname"
pg_dumpall -g > globals.dump
psql -XAtc "$REQ" postgres | while read base
do
pg_dump -Fc $base > ${base}.dump
done
Évidemment, il ne conviendra pas à tous les cas, mais donne une idée
de ce qu’il est possible de faire. (Voir plus bas pg_back
pour un outil plus complet.)
Exemple de script de sauvegarde adapté pour un serveur Windows :
@echo off
SET PGPASSWORD=super_password
SET PATH=%PATH%;C:\Progra~1\PostgreSQL\11\bin\
pg_dumpall -g -U postgres postgres > c:\pg-globals.sql
for /F %%v in ('psql -XAt -U postgres -d cave
-c "SELECT datname FROM pg_database WHERE NOT datistemplate"') do (
echo "dump %%v"
pg_dump -U postgres -Fc %%v > c:\pg-%%v.dump
)
pause
Autre exemple plus complet de script de sauvegarde totale de toutes les bases, avec une période de rétention :
#!/bin/sh
#------------------------------------------------------------------
#
# Script used to perform a full backup of all databases from a
# PostgreSQL Cluster. The pg_dump use the custom format is done
# into one file per database. There's also a backup of all global
# objects using pg_dumpall -g.
#
# Backup are preserved following the given retention days (default
# to 7 days).
#
# This script should be run daily as a postgres user cron job:
#
# 0 23 * * * /path/to/pg_fullbackup.sh >/tmp/fullbackup.log 2>&1
#
#------------------------------------------------------------------