Sauvegarde et restauration

Module I1

Dalibo SCOP

24.12

18 décembre 2024

Sur ce document

Formation	Module I1
Titre	Sauvegarde et restauration
Révision	24.12
PDF	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

Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse formation@dalibo.com !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Alexandre Anriot, Jean‑Paul Argudo, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Ronan Dunklau, Vik Fearing, Stefan Fercot, Dimitri Fontaine, Pierre Giraud, Nicolas Gollet, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Jehan-Guillaume de Rorthais, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Arnaud de Vathaire, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

Paternité
Pas d’utilisation commerciale
Partage des conditions initiales à l’identique

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.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.

PostgreSQL : Sauvegarde et restauration

Introduction

Les sauvegardes sont essentielles pour la sécurisation des données :

PostgreSQL propose différentes solutions
- de sauvegarde à froid ou à chaud, mais cohérentes
- des méthodes de restauration partielle ou complète

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.

Sauvegardes logiques
- et restauration
Sauvegarde physique à froid des fichiers
Sauvegardes à chaud

Sauvegardes logiques

Sauvegardes logiques : caractéristiques

À chaud
Cohérente
Locale ou à distance
2 outils
- pg_dump
- pg_dumpall
Pas d’impact sur les utilisateurs
- sauf certaines opérations DDL
Jamais inclus :
- tables systèmes
- fichiers de configuration

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

Sauvegarde une base de données :

  pg_dump nombase > nombase.dump

Sauvegarde complète ou partielle

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.

pg_dump : formats de sauvegarde

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 :

le format SQL, soit un fichier texte unique pour toute la base, non compressé ;
le format tar, un fichier binaire, non compressé, comprenant un index des objets ;
le format « personnalisé » (custom), un fichier binaire, compressé, avec un index des objets ;
le format « répertoire » (directory), arborescence de fichiers binaires généralement compressés, comprenant aussi un index des objets.

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 :

configuration de certaines variables ;
création des objets de la base : schémas, tables, vues, procédures stockées, etc., à l’exception des index, contraintes et triggers ;
ajout des données aux tables (ordres COPY par défaut) ;
ajout des index, contraintes et triggers ;
définition des droits d’accès aux objets ;
rafraîchissement des vues matérialisées.

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.

pg_dump : choisir le format de sauvegarde

Format plain (SQL)
- restaurations partielles très difficiles (ou manuelles)
Parallélisation de la sauvegarde :
- uniquement format directory
Préférer les formats binaires

pg_dump -Fc
pg_dump -Fd

Ajouter les objets globaux (rôles…)

pg_dumpall -g

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

pg_dump : compression

-z/--compress : compression par zlib
- de 0 à 9 (défaut : 6)
(v16) mieux : zstd, lz4

pg_dump -Z1  # gzip
# v16+
pg_dump -Z gzip:3
pg_dump -Z lz4:'level=12'
pg_dump -Z zstd:'level=22,long'

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.

pg_dump : fichier ou sortie standard

-f : fichier/répertoire où stocker la sauvegarde
sinon : sortie standard

pg_dump : structure ou données

--schema-only / -s : uniquement la structure
--data-only / -a : uniquement les données ou :
--section
- pre-data : définition des objets (hors contraintes et index)
- data : les données
- post-data : définition des contraintes et index

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 :

la définition des objets ;
les données ;
la définition des contraintes et index.

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.

pg_dump : sélection d’objets

-n <schema> : uniquement ce schéma
-N <schema> : tous les schémas sauf celui-là
-t <table> : uniquement cette relation (sans dépendances !)
-T <table> : toutes les tables sauf celle-là
En option
- possibilité d’en mettre plusieurs
- exclure les données : --exclude-table-data=<table>
- avoir une erreur si l’objet est inconnu : --strict-names
--filter & fichier avec include/exclude des objets (v17)
Attention : pas de dépendance automatique si sélection !

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.

L’option --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.
Le même système existe pour les tables avec les options --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.

pg_dump : parallélisation

--jobs <nombre_de_threads>
Format directory (-Fd) uniquement
Granularité : la table
- optimal quand la volumétrie est bien répartie

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.

pg_dump : objets binaires

Deux types dans PostgreSQL :
- bytea
- Large Objects
Option --large-objects
- si filtrage (-n/-N/-t/-T/--filter)
Option --no-blobs
- pour ne pas sauvegarder les Large Objects
Option bytea_output
- escape
- hex

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.

pg_dump : extensions

--extension (-e)
- uniquement si sélection/exclusion (-n/-N et/ou -t/-T)
--exclude-extension (v17)

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*

pg_dump : options diverses

--create (-C) : recréer la base
- y compris paramétrage utilisateur sur base (format plain)
- inutile dans les autres formats
--no-owner, --no-privileges
--no-tablespaces
--inserts/--rows-per-insert : INSERT au lieu de COPY
--on-conflict-do-nothing
divers paramètres pour les tables partitionnées
-v : progression

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
                   ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';
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

Outil dédié à la sauvegarde d’une instance complète
- objets globaux (utilisateurs, tablespaces…)
- toutes les bases de données
- sauf --exclude-database
Format texte (SQL) uniquement (--f nomfichier.dmp)

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 : sélection des objets

-g : tous les objets globaux (conseillé)
-r : uniquement les rôles
-t : uniquement les tablespaces
--no-role-passwords : sans les mots de passe
- permet de ne pas être superutilisateur

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 :

des rôles uniquement (--roles-only / -r) ;
des tablespaces uniquement (--tablespaces-only / -t) ;
de tous les objets globaux (--globals-only / -g), dont les rôles et tablespaces.

Par exemple, la sauvegarde des rôles renvoie ce script :

$ pg_dumpall -r
--
-- 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 NOBYPASSRLS
                 PASSWORD 'md5505548e69dafa281a5d676fe0dc7dc43';
CREATE ROLE durant;
ALTER ROLE durant WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
                 NOREPLICATION NOBYPASSRLS
                 PASSWORD 'md56100ff994522dbc6e493faf0ee1b4f41';
CREATE ROLE martin;
ALTER ROLE martin WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN
                 NOREPLICATION NOBYPASSRLS
                 PASSWORD '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.

pg_dump/pg_dumpall : options de connexions

-h / $PGHOST (serveur ou socket Unix)
-p / $PGPORT (5432)
-U / $PGUSER (utilisateur du système)
-W/ $PGPASSWORD
- ou .pgpass

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.

pg_dump/pg_dumpall : privilèges nécessaires

Les outils se comportent comme des clients pour PostgreSQL
Préférer un rôle superutilisateur
Sinon :
- connexion à autoriser
- le rôle doit pouvoir lire tous les objets à exporter (pg_read_all_data)

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 :

se connecter à la base de données : autorisation dans pg_hba.conf, être propriétaire ou avoir le privilège CONNECT ;
voir le contenu des différents schémas : être propriétaire ou avoir le privilège USAGE sur le schéma ;
lire le contenu des tables : être propriétaire ou avoir le privilège SELECT sur la table.

Pour restaurer, il faut pouvoir :

se connecter à la base de données : autorisation dans pg_hba.conf, être propriétaire ou avoir le privilège CONNECT ;
optionnellement, pouvoir créer la base de données cible et pouvoir s’y connecter (option -C de pg_restore)
pouvoir créer des schémas : être propriétaire de la base de données ou avoir le privilège CREATE sur celle-ci ;
pouvoir créer des objets dans les schémas : être propriétaire du schéma ou avoir le privilège CREATE sur celui-ci ;
pouvoir écrire dans les tablespaces cibles : être propriétaire du tablespace ou avoir le privilège CREATE sur celui-ci ;
avoir la capacité de donner ou retirer des privilèges : faire partie des rôles bénéficiant d’ACL dans le dump.

Le nombre de ces privilèges explique pourquoi il n’est parfois possible de restaurer qu’avec un superutilisateur.

pg_dump/pg_dumpall : compresser la sortie

Compresser 1 fichier
- utile avec formats plain,tar,custom
```
pg_dump | bzip2
```
Outils multithreads de compression, bien plus rapides :
- pbzip2, pigz
- xz, zstd…

À 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.

Restauration d’une sauvegarde logique

Quel outil pour restaurer une sauvegarde logique ?

psql
- restauration de SQL (option -Fp) :
pg_restore
- restauration binaire (-Ft/-Fc/-Fd)

psql

Client standard PostgreSQL
Capable d’exécuter des requêtes
- donc de restaurer une sauvegarde texte (plain)
Très limité dans les options de restauration
- ni sélection ni parallélisation

psql : options pour une restauration

-f
- pour indiquer le fichier contenant la sauvegarde
- sans -f : lit l’entrée standard
-1 (--single-transaction)
- pour tout restaurer en une seule transaction
-e
- pour afficher les ordres SQL exécutés
ON_ERROR_ROLLBACK/ON_ERROR_STOP

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 :

envoyer le script sur l’entrée standard de psql :

cat b1.dump | psql b1

utiliser l’option en ligne de commande -f :

psql -f b1.dump b1

utiliser la méta-commande :

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 :

modifier le script SQL dans un éditeur de texte, ce qui peut être impossible si ce fichier est suffisamment gros ;
utiliser des outils tels que 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 :

par édition du .psqlrc (à déconseiller, cela va modifier le comportement de psql pour toute personne utilisant le compte) :

cat .psqlrc
\set ON_ERROR_ROLLBACK interactive

en option de ligne de commande de psql :

psql --set=ON_ERROR_ROLLBACK='on'
psql -v ON_ERROR_ROLLBACK='on'

de façon interactive dans psql:

psql>\set ON_ERROR_ROLLBACK on

pg_restore

Restaure uniquement les sauvegardes au format binaire
- format autodétecté (-F inutile)
Nombreuses options très intéressantes
Restaure une base de données
- complètement ou partiellement

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.

pg_restore : indiquer la base de données

-d : base de données de connexion
-C (--create) :
- connexion (-d) et CREATE DATABASE
- connexion à la nouvelle base et exécute le SQL
- aussi : --clean --if-exists

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'
                        LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


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.

pg_restore : fichiers en entrée/sortie

Entrée : Fichier à restaurer en dernier argument de la ligne de commande
Sortie :
- -l : liste
- -f : fichier SQL
- -f - pour sortie standard
Attention à ne pas écraser la sauvegarde !

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

pg_restore : structure ou données

--schema-only : uniquement la structure
--data-only : uniquement les données

ou :

--section
- pre-data
- data
- post-data

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 IMMUTABLE
    AS $$
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 (
    i integer,
    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 (
    i integer NOT NULL
);

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 : sélection d’objets

-n <schema> : uniquement ce schéma
-N <schema> : tous les schémas sauf ce schéma
-t <table> : cette relation
-T <trigger> : ce trigger
-I <index> : cet index
-P <fonction> : cette fonction
En option
- possibilité d’en mettre plusieurs
- --strict-names, pour avoir une erreur si l’objet est inconnu
--filter & fichier avec include/exclude des objets (v17)

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 : sélection avancée

-l : récupération de la liste des objets
-L <liste_objets> : restauration uniquement des objets listés dans ce fichier

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.

pg_restore : parallélisation

--jobs <nombre_de_threads> / -jN
- formats custom ou directory

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.

pg_restore : options diverses

-O/--no-owner : ignorer le propriétaire
-x/--no-privileges : ignorer les droits
--no-comments : ignorer les commentaires
--no-tablespaces : ignorer les tablespaces
-1/--single-transaction : tout restaurer en une seule transaction
- ou --transaction-size = N pour regrouper les ordres (v17)
-c/--clean --if-exists : détruire un objet avant de le restaurer
-v/--verbose

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.

Autres considérations sur la sauvegarde logique

Versions des outils & version du serveur
Script de sauvegarde
Sauvegarder sans passer par un fichier
Statistiques et maintenance après import
Durée d’exécution d’une sauvegarde
Taille d’une sauvegarde

Versions des outils clients et version de l’instance

pg_dump :
- reconnaît les versions de PG antérieures
- serveur 9.2 minimum si pg_dump récent (v15+)
pg_restore
- minimum la version du pg_dump utilisé
- si possible celle du serveur cible
Pas de problème entre OS différents

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.

Script de sauvegarde idéal

Objets globaux :

pg_dumpall -g

Chaque base :

pg_dump -Fc
pg_dump -Fd

Bien tester !

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
#
#------------------------------------------------------------------

# Backup user who must run this script, most of the time it should be postgres
BKUPUSER=postgres
# Number of days you want to preserve your backup
RETENTION_DAYS=7
# Where the backup files should be saved
#BKUPDIR=/var/lib/pgsql/backup
BKUPDIR=/var/lib/postgresql/backup_bases
# Prefix used to prefix the name of all backup files
PREFIX=bkup
# Optional hostname of remote server
# Leave empty to use unix socket
HOSTNAME=""
# Optional user to log in to the remote server
USERNAME=""

WHO=`whoami`
if [ "${WHO}" != "${BKUPUSER}" ]; then
        echo "FATAL: you must run this script as ${BKUPUSER} user."
        exit 1;
fi

# Testing backup directory
if [ ! -e "${BKUPDIR}" ]; then
        mkdir -p "${BKUPDIR}"
fi
echo "Begining backup at "`date`

# Set the query to list the available databases
REQ="SELECT datname FROM pg_database WHERE datistemplate = 'f'
     AND datallowconn ORDER BY datname"

# Set the date variable to be used in the backup file names
DATE=$(date +%Y-%m-%d_%Hh%M)

# Define the additional pg program options
PG_OPTION=""
if [ $HOSTNAME != "" ]; then
        PG_OPTION="${PG_OPTION} -h $HOSTNAME"
fi;
if [ $USERNAME != "" ]; then
        PG_OPTION="${PG_OPTION} -U $USERNAME"
fi;

# Dump PostgreSQL Cluster global objects
echo "Dumping global object into ${BKUPDIR}/${PREFIX}_globals_${DATE}.dump"
pg_dumpall ${PG_OPTION} -g > "${BKUPDIR}/${PREFIX}_globals_${DATE}.dump"
if [ $? -ne 0 ]; then
        echo "FATAL: Can't dump global objects with pg_dumpall."
        exit 2;
fi

# Extract the list of database
psql ${PG_OPTION} -Atc "$REQ" postgres | while read base

# Dump content of all databases
do
  echo "Dumping database $base into ${BKUPDIR}/${PREFIX}_${base}_${DATE}.dump"
  pg_dump ${PG_OPTION} -Fc $base > "${BKUPDIR}/${PREFIX}_${base}_${DATE}.dump"
  if [ $? -ne 0 ]; then
          echo "FATAL: Can't dump database ${base} with pg_dump."
          exit 3;
  fi
done
if [ $? -ne 0 ]; then
        echo "FATAL: Can't list databases with psql query."
        exit 4;
fi

# Starting deletion of obsolete backup files
if [ ${RETENTION_DAYS} -gt 0 ]; then
  echo "Removing obsolete backup files older than ${RETENTION_DAYS} day(s)."
  find ${BKUPDIR}/ -maxdepth 1 -name "${PREFIX}_*" -mtime ${RETENTION_DAYS} \
                   -exec rm -rf '{}' ';'
fi

echo "Backup ending at "`date`

exit 0

pg_back - Présentation

https://github.com/orgrim/pg_back
Type de sauvegardes : logiques (pg_dump)
Langage : bash (v1) / go (v2)
Licence : BSD (libre)
Type de stockage : local + export cloud
Planification : crontab
Unix/Linux (v1 & 2) / Windows (v2)
Compression : via pg_dump
Versions compatibles : toutes
Rétention : durée

pg_back a été écrit par Nicolas Thauvin, alors consultant de Dalibo, également auteur original de pitrery.

Ce programme assez complet vise à gérer le plus simplement possible des sauvegardes logiques en utilisant pg_dump et pg_dumpall.

La version 1 est en bash, directement utilisable et éprouvée, mais ne sera plus maintenue à terme. La version 2, parue en 2021, a été réécrite en go. Le binaire est directement utilisable, et permet notamment une configuration différente par base, une meilleure gestion des paramètres de connexion à PostgreSQL et le support de Windows. Pour les instances avant la 11, le script pg_dumpacl est intégré (v2) ou supporté (v1) pour sauvegarder le paramétrage au niveau des bases.

Les sauvegardes sont bien sûr aux formats gérés par pg_dump : SQL, custom, par répertoire, compressées ou non…

Le premier intérêt de l’outil est la gestion de la rétention. Les anciennes sauvegardes sont automatiquement purgées, et l’on peut en conserver un nombre minimum.

Il est possible de chiffrer les sauvegardes avec une phrase de passe (chiffrage AGE), de générer des sommes de contrôle, et d’exporter vers Azure, Google Cloud, Amazon S3 ou Backblaze B2, ou n’importe quel serveur distant accessible avec ssh en SFTP.

pg_back ne propose pas d’option pour restaurer les données : il faut utiliser ceux de PostgreSQL (pg_restore, psql).

Sauvegarde et restauration sans fichier intermédiaire

pg_dump -Fp | psql
pg_dump -Ft | pg_restore
pg_dump -Fc | pg_restore
Utilisation des options -h, -p, -d
Attention à la gestion des erreurs !

La duplication d’une base ne demande pas forcément de passer par un fichier intermédiaire. Il est possible de fournir la sortie de pg_dump (format plain implicite) à psql ou pg_restore. Par exemple :

$ createdb nouvelleb1
$ pg_dump -Fp b1 | psql nouvelleb1

Ces deux commandes permettent de dupliquer b1 dans nouvelleb1.

L’utilisation des options -h, -p, -d permet de sauvegarder et restaurer une base sur des instances différentes, qu’elles soient locales ou à distance.

Le gain de temps est appréciable : l’import peut commencer avant la fin de l’export. Comme toujours avec pg_dump, les données restaurées correspondent à celles qui existaient au début de la sauvegarde. On épargne aussi la place nécessaire au stockage du backup. Par contre on ne peut pas paralléliser l’export.

Avec -Fp, le flux circule en pur texte : il est possible d’intercaler des appels à des outils comme awk ou sed pour effectuer certaines opérations à la volée (renommage…).

Cette version peut être intéressante :

$ pg_dump -Fc  | pg_restore

car elle permet de profiter des options propres au format custom à commencer par la compression. Il n’y a alors pas besoin d’intercaler des commandes comme gzip/gunzip pour alléger la charge réseau.

Le point délicat est la gestion des erreurs puisqu’il y a deux processus à surveiller : par exemple dans un script bash, on testera le contenu de ${PIPESTATUS[@]} (et pas seulement $?) pour vérifier que tout s’est bien déroulé, et l’on ajoutera éventuellement set -o pipefail en début de script.

Statistiques et maintenance après import

Statistiques non sauvegardées
- ANALYZE impérativement après une restauration !
Pour les performances :
- VACUUM (ou VACUUM ANALYZE)
À plus long terme :
- VACUUM FREEZE

Les statistiques sur les données, utilisées par le planificateur pour sélectionner le meilleur plan d’exécution possible, ne font pas partie de la sauvegarde. Il faut donc exécuter un ANALYZE après avoir restauré une sauvegarde. Sans cela, les premières requêtes pourraient s’exécuter très mal du fait de statistiques non à jour.

Lancer un VACUUM sur toutes les tables restaurées est également conseillé. S’il n’y a pas besoin de les défragmenter, certaines opérations de maintenance effectuées par un VACUUM ont un impact sur les performances. En premier lieu, les hint bits (« bits d’indice ») de chaque enregistrement seront mis à jour au plus tard à la relecture suivante, et généreront de nombreuses écritures. Un VACUUM explicite forcera ces écritures, si possible avant la mise en production de la base. Il créera aussi la visibility map des tables, ce qui autorisera les Index Only Scans, une optimisation extrêmement puissante, sans laquelle certaines requêtes seront beaucoup plus lentes.

L’autovacuum se chargera bien sûr progressivement de tout cela. Il est cependant par défaut bridé pour ne pas gêner les opérations, et pas toujours assez réactif. (De plus, jusqu’en PostgreSQL 12 compris, les insertions ne provoquaient que l’ANALYZE, pas le VACUUM, qui pouvait donc tarder sur les tables un peu statiques.)

Il est donc préférable de lancer un VACUUM ANALYZE manuel à la fin de la restauration, afin de procéder immédiatement au passage des statistiques et aux opérations de maintenance.

Il est possible de séparer les deux étapes. L’ANALYZE est impératif et rapide ; le VACUUM est beaucoup plus lent mais peut avoir lieu durant la production si le temps presse.

À plus long terme, dans le cas d’un gros import ou d’une restauration de base, existe un autre danger : le VACUUM FREEZE. Les numéros de transaction étant cycliques, l’autovacuum les « nettoie » des tables quand il sont assez vieux. Les lignes ayant été importées en même temps, cela peut générer l’écriture de gros volumes de manière assez soudaine. Pour prévenir cela, lancez un VACUUM FREEZE dans une période calme quelques temps après l’import.

Durée d’exécution

Difficile à chiffrer
Dépend de l’activité sur le serveur
Option -v
Suivre les COPY
- vue pg_stat_progress_copy (v14+)
Suivre l’indexation
- vue pg_stat_progress_create_index

La durée d’exécution d’une sauvegarde et d’une restauration est difficile à estimer. Cela dépend beaucoup de l’activité présente sur le serveur, de la volumétrie, du matériel, etc.

L’option -v de pg_dump et de pg_restore permet de suivre les opérations, action par action (donc la création des objets, mais aussi leur remplissage). Cependant, sans connaître la base sauvegardée ou restaurée, il est difficile de prédire le temps restant pour la fin de l’opération. Nous savons seulement qu’il a fini de traiter tel objet.

Depuis la version 14, il est possible de suivre individuellement les opérations de copie des données, si ces dernières passent par l’instruction COPY (qui est le défaut, mais forcer INSERT est possible). La vue dédiée pg_stat_progress_copy affiche des informations sur le COPY en cours. Là encore, il est difficile d’en tirer beaucoup d’informations sans bien connaître la base en cours de traitement. Cependant, cela permet de savoir si une table a bientôt fini d’être traitée par le COPY en cours. Pour les tables volumineuses, c’est intéressant.

La partie finale de création des index peut être suivie dans la vue système pg_stat_progress_create_index.

Taille d’une sauvegarde logique

Difficile à évaluer
Contenu des index non sauvegardé
- donc sauvegarde plus petite
Objets binaires :
- entre 2 et 4 fois plus gros
- donc sauvegarde plus grosse

Il est très difficile de corréler la taille d’une base avec celle de sa sauvegarde.

Le contenu des index n’est pas sauvegardé. Donc, sur une base contenant 10 Go de tables et 10 Go d’index, avoir une sauvegarde de 10 Go ne serait pas étonnant. Le contenu des index est généré lors de la restauration.

Par contre, les données des tables prennent généralement plus de place. Un objet binaire est stocké sous la forme d’un texte, soit de l’octal (donc 4 octets), soit de l’hexadécimal (2 octets). Donc un objet binaire prend 2 à 4 fois plus de place dans la sauvegarde que dans la base. Mais même un entier ne va pas avoir la même occupation disque. La valeur 10 ne prend que 2 octets dans la sauvegarde, alors qu’il en prend quatre dans la base. Et la valeur 1 000 000 prend 7 octets dans la sauvegarde alors qu’il en prend toujours 4 dans la base.

Tout ceci permet de comprendre que la taille d’une sauvegarde n’a pas tellement de lien avec la taille de la base. Il est par contre plus intéressant de comparer la taille de la sauvegarde de la veille avec celle du jour. Tout gros changement peut être annonciateur d’un changement de volumétrie de la base, changement voulu ou non.

Avantages de la sauvegarde logique

Simple et rapide
Sans interruption de service
Indépendante de la version de PostgreSQL
Granularité de sélection à l’objet
Taille réduite
Ne conserve pas la fragmentation des tables et des index
Éventuellement depuis un serveur secondaire

La sauvegarde logique ne nécessite aucune configuration particulière de l’instance, hormis l’autorisation de la connexion du client effectuant l’opération. La sauvegarde se fait sans interruption de service. Par contre, l’instance doit être disponible, ce qui n’est pas un problème dans la majorité des cas.

Elle est indépendante de la version du serveur PostgreSQL, source et cible. Elle ne contient que des ordres SQL nécessaires à la création des objets à l’identique et permet donc de s’abstraire du format de stockage sur le serveur. De ce fait, la fragmentation des tables et des index disparaît à la restauration.

Une restauration de sauvegarde logique est d’ailleurs la méthode officielle de montée de version majeure. Même s’il existe d’autres méthodes de migration de version majeure, elle reste le moyen le plus sûr parce que le plus éprouvé.

Une sauvegarde logique ne contenant que les données utiles, sa taille est généralement beaucoup plus faible que la base de données source, sans parler de la compression qui peut encore réduire l’occupation sur disque. Par exemple, seuls les ordres DDL permettant de créer les index sont stockés, pas leur contenu. Ils sont alors créés de zéro à la restauration.

Les outils permettent de sélectionner très finement les objets sur lesquels on travaille, à la sauvegarde comme à la restauration.

Il est assez courant d’effectuer une sauvegarde logique à partir d’un serveur secondaire (réplica de la production), pour ne pas charger les disques du primaire ; quitte à mettre la réplication du secondaire en pause le temps de la sauvegarde.

Inconvénients de la sauvegarde logique

Durée : dépendante des données et de l’activité
Restauration : uniquement au démarrage de l’export
Efficace si < 200 Go
Plusieurs outils pour sauvegarder une instance complète
ANALYZE, VACUUM ANALYZE, VACUUM FREEZE après import

L’un des principaux inconvénients d’une sauvegarde et restauration porte sur la durée d’exécution. Elle est proportionnelle à la taille de la base de données, ou à la taille des objets choisis pour une sauvegarde partielle.

En conséquence, il est généralement nécessaire de réduire le niveau de compression pour les formats custom et directory afin de gagner du temps. Avec des disques mécaniques en RAID 10, il est généralement nécessaire d’utiliser d’autres méthodes de sauvegarde lorsque la volumétrie dépasse 200 Go.

Le second inconvénient majeur de la sauvegarde logique est l’absence de granularité temporelle. Une « photo » des données est prise au démarrage de la sauvegarde, et on ne peut restaurer qu’à cet instant, quelle que soit la durée d’exécution de l’opération. Il faut cependant se rappeler que cela garantit la cohérence du contenu de la sauvegarde d’un point de vue transactionnel.

Comme les objets et leur contenu sont effectivement recréés à partir d’ordres SQL lors de la restauration, on se débarrasse de la fragmentation des tables et index, mais on perd aussi les statistiques de l’optimiseur, ainsi que certaines méta-données des tables. Il est donc nécessaire de lancer ces opérations de maintenance après l’import.

Sauvegarde physique à froid des fichiers

Sauvegarde physique à froid : principe

Instance arrêtée : sauvegarde cohérente
Ne pas oublier : journaux, tablespaces, configuration !
Outils : système, aucun spécifique à PostgreSQL
- cp, tar…
- souvent : rsync en 2 étapes : à chaud puis à froid
- snapshots SAN/LVM (attention à la cohérence)

Toutes les données d’une instance PostgreSQL se trouvent dans des fichiers. Donc sauvegarder les fichiers permet de sauvegarder une instance. Cependant, cela ne peut pas se faire aussi simplement que ça. Lorsque PostgreSQL est en cours d’exécution, il modifie certains fichiers du fait de l’activité des utilisateurs ou des processus (interne ou non) de maintenances diverses. Sauvegarder les fichiers de la base sans plus de manipulation ne peut donc se faire qu’à froid. Il faut donc arrêter PostgreSQL pour disposer d’une sauvegarde cohérente si la sauvegarde se fait au niveau du système de fichiers.

Il est cependant essentiel d’être attentif aux données qui ne se trouvent pas directement dans le répertoire des données (PGDATA), notamment :

le répertoire des journaux de transactions (pg_walou pg_xlog), qui est souvent placé dans un autre système de fichiers pour gagner en performances : sans lui, la sauvegarde ne sera pas utilisable ;
les répertoires des tablespaces s’il y en a, sinon une partie des données manquera ;
les fichiers de configuration (sous /etc sous Debian, notamment), y compris ceux des outils annexes à PostgreSQL.

Voici un exemple de sauvegarde (Cent OS 7) :

$ systemctl stop postgresql-12
$ tar cvfj data.tar.bz2 /var/lib/pgsql/12/data
$ systemctl start postgresql-12

Le gros avantage de cette sauvegarde se trouve dans le fait que vous pouvez utiliser tout outil de sauvegarde de fichier : cp, scp, tar, ftp, rsync, etc. et tout outil de sauvegarde utilisant lui-même ces outils.

Comme la sauvegarde doit être effectuée avec l’instance arrêtée, la durée de l’arrêt est dépendante du volume de données à sauvegarder. On peut optimiser les choses en réduisant le temps d’interruption avec l’utilisation de snapshots au niveau système de fichier ou avec rsync.

Pour utiliser des snapshots, il faut soit disposer d’un SAN offrant cette possibilité ou bien utiliser la fonctionnalité équivalente de LVM voire du système de fichier. Dans ce cas, la procédure est la suivante :

arrêt de l’instance PostgreSQL ;
création des snapshots de l’ensemble des systèmes de fichiers ;
démarrage de l’instance ;
sauvegarde des fichiers à partir des snapshots ;
destruction des snapshots.

Si on n’a pas la possibilité d’utiliser des snapshots, on peut utiliser rsync de cette manière :

rsync de l’ensemble des fichiers de l’instance PostgreSQL en fonctionnement pour obtenir une première copie (incohérente) ;
arrêt de l’instance PostgreSQL ;
rsync de l’ensemble des fichiers de l’instance pour ne transférer que les différences ;
redémarrage de l’instance PostgreSQL.

Avantages des sauvegardes à froid

Simple
Rapide à la sauvegarde
Rapide à la restauration
Beaucoup d’outils disponibles

Inconvénients des sauvegardes à froid

Arrêt de la production
Sauvegarde de l’instance complète (donc aucune granularité)
Restauration de l’instance complète
Conservation de la fragmentation
Impossible de changer d’architecture
- Réindexation si changement OS

Il existe aussi de nombreux inconvénients à cette méthode.

Le plus important est certainement le fait qu’il faut arrêter la production. L’instance PostgreSQL doit être arrêtée pour que la sauvegarde puisse être effectuée.

Il ne sera pas possible de réaliser une sauvegarde ou une restauration partielle, il n’y a pas de granularité. C’est forcément l’intégralité de l’instance qui sera prise en compte.

Étant donné que les fichiers sont sauvegardés, toute la fragmentation des tables et des index est conservée.

De plus, la structure interne des fichiers implique l’architecture où cette sauvegarde sera restaurée. Donc une telle sauvegarde impose de conserver un serveur 32 bits pour la restauration si la sauvegarde a été effectuée sur un serveur 32 bits. De même, l’architecture little endian/big endian doit être respectée.

De plus, des différences entre deux systèmes, et même entre deux versions d’une même distribution, peuvent mener à devoir réindexer toute l’instance.

Tous ces inconvénients ne sont pas présents pour la sauvegarde logique. Cependant, cette sauvegarde a aussi ses propres inconvénients, comme une lenteur importante à la restauration.

Diminuer l’immobilisation

Utilisation de rsync
Une fois avant l’arrêt
Une fois après

Il est possible de diminuer l’immobilisation d’une sauvegarde de fichiers en utilisant la commande rsync.

rsync permet de synchroniser des fichiers entre deux répertoires, en local ou à distance. Il va comparer les fichiers pour ne transférer que ceux qui ont été modifiés. Il est donc possible d’exécuter rsync avec PostgreSQL en cours d’exécution pour récupérer un maximum de données, puis d’arrêter PostgreSQL, de relancer rsync pour ne récupérer que les données modifiées entre temps, et enfin de relancer PostgreSQL. Notez que l’utilisation des options --delete et --checksum est fortement conseillée lors de la deuxième passe, pour rendre la copie totalement fiable.

Voici un exemple de ce cas d’utilisation :

$ rsync -a /var/lib/postgresql/ /var/lib/postgresql2
$ /etc/init.d/postgresql stop
$ rsync -a --delete --checksum /var/lib/postgresql /var/lib/postgresql2
$ /etc/init.d/postgresql start

Sauvegarde à chaud

Snapshot, ou les outils de PostgreSQL ?

Sauvegarde à chaud des fichiers par snapshot de partition : principe

Avec certains systèmes de fichiers
Avec LVM
Avec la majorité des SAN
Attention : cohérence entre partitions

Certains systèmes de fichiers (principalement les systèmes de fichiers ZFS et BTRFS) ainsi que la majorité des SAN sont capables de faire une sauvegarde d’un système de fichiers en instantané. En fait, ils figent les blocs utiles à la sauvegarde. S’il est nécessaire de modifier un bloc figé, ils utilisent un autre bloc pour stocker la nouvelle valeur. Cela revient un peu au fonctionnement de PostgreSQL dans ses fichiers.

L’avantage est de pouvoir sauvegarder instantanément un système de fichiers. L’inconvénient est que cela ne peut survenir que sur un seul système de fichiers : impossible dans ce cas de déplacer les journaux de transactions sur un autre système de fichiers pour gagner en performance ou d’utiliser des tablespaces pour gagner en performance et faciliter la gestion de la volumétrie des disques. De plus, comme PostgreSQL n’est pas arrêté au moment de la sauvegarde, au démarrage de PostgreSQL sur la sauvegarde restaurée, ce dernier devra rejouer les journaux de transactions.

Une baie SAN assez haut de gamme pourra disposer d’une fonctionnalité de snapshot cohérent sur plusieurs volumes (« LUN »), ce qui permettra, si elle est bien paramétrée, de réaliser un snapshot de tous les systèmes de fichiers composant la base de façon cohérente.

Néanmoins, cela reste une méthode de sauvegarde très appréciable quand on veut qu’elle ait le moins d’impact possible sur les utilisateurs.

Sauvegarde à chaud des fichiers avec PostgreSQL

pg_basebackup
- sauvegarde ponctuelle
PITR : Point In Time Recovery
- restauration à la transaction près
- technique avancée, complexe à mettre en place et à maintenir
- pas de coupure de service
- outils dédiés (pgBackRest, barman)
- et archivage des journaux de transaction
- outils conseillés : pgBackRest, barman

Pour une sauvegarde physique à chaud ponctuelle, on peut utiliser pg_basebackup, fourni avec PostgreSQL. Cet outil se base sur le protocole de réplication par flux (streaming) et les slots de réplications pour créer une copie des répertoires de données depuis une instance en cours de fonctionnement. Son maniement est très simple. Il existe en plus un outil de vérification de la sauvegarde (pg_verifybackup). La sauvegarde sera l’image de l’instance au moment de la fin de sauvegarde. À partir de PostgreSQL 17, l’outil sait même faire des sauvegardes incrémentales.

La sauvegarde la plus intéressante est la sauvegarde PITR (Point In Time Recovery) Elle consiste à réaliser les sauvegardes des fichiers sans arrêter l’instance (à chaud), sans perte ou presque de données, et avec une possible restauration à un point précis dans le temps. On peut donc restaurer l’instance à la seconde précédant un sinistre ou une fausse manipulation. Les autres méthodes de sauvegarde ne sont qu’une image à un moment précis du passé, souvent la nuit précédente.

La sauvegarde PITR est cependant une technique avancée, qui nécessite la compréhension de concepts comme l’archivage des journaux de transaction, non abordés ici. Il est fortement conseillé de la mettre en place à l’aide d’outils dédiés et éprouvés comme pgBackRest ou barman.

En raison de la complexité de ces méthodes, y compris à la restauration, on testera d’autant plus soigneusement les procédures.

Recommandations générales

Prendre le temps de bien choisir sa méthode
Ne pas oublier les fichiers de configuration
Bien la tester
Bien tester la restauration
Et tester régulièrement !

Matrice des options de sauvegarde

	Simplicité	Coupure	Restauration	Fragmentation
copie à froid	facile	longue	rapide	conservée
snapshot FS	facile	aucune	rapide	conservée
pg_dump	facile	aucune	lente	perdue
rsync + copie à froid	moyen	courte	rapide	conservée
PITR	difficile	aucune	rapide	conservée

Conclusion

Plusieurs solutions pour la sauvegarde et la restauration
Sauvegarde/Restauration complète ou partielle
Toutes cohérentes
La plupart à chaud
Méthode de sauvegarde avancée : PITR

Questions

N’hésitez pas, c’est le moment !

Quiz

https://dali.bo/i1_quiz

Travaux pratiques

Ce TP suppose que vous avez déjà quelques bases et rôles dans votre instance. Créez au moins ceci, qui existe peut-être déjà suite à d’autres TP :

createuser testperf
createuser patron
createdb entreprise --owner patron

createdb pgbench --owner testperf
/usr/pgsql-15/bin/pgbench -i -s1  pgbench --foreign-keys

Sauvegardes logiques

But : Sauvegarder des bases

Créer un répertoire backups dans le répertoire home de postgres pour y déposer les fichiers d’export.

À l’aide de pg_dumpall, sauvegarder toutes les bases de données de l’instance PostgreSQL dans le fichier ~postgres/backups/base_all.sql.

Quelle est la taille de la sauvegarde ?

Avec quel outil consulter le contenu du fichier de sauvegarde ?

Ouvrir le fichier de sauvegarde. Quelles en sont les principales parties ?

Recommencer la sauvegarde en compressant la sauvegarde avec gzip. Quelle en est la taille ?

Qu’affiche un pg_dump -d entreprise ?

À l’aide de pg_dump, sauvegarder la base de données pgbench au format custom dans le fichier ~postgres/backups/base_pgbench.dump. Quelle est sa taille ?

Avec pg_restore et son option -l, consulter le contenu de ~postgres/backups/base_pgbench.dump.

Exporter uniquement les objets globaux de l’instance (rôles, définitions de tablespaces) à l’aide de pg_dumpall dans le fichier ~postgres/backups/base_globals.sql.

Consulter le contenu de ce fichier.

Sauvegarder la table pgbench_accounts dans le fichier ~postgres/backups/table_pgbench_accounts.sql au format plain text. Que contient ce fichier ?

Restaurations logiques

But : Restaurer des sauvegardes

Que se passe-t-il si l’on exécute ceci ?
pg_restore ~postgres/backups/base_pgbench.dump -f -

Créer une nouvelle base pgbench2 appartenant au rôle testperf.

Ajuster .pgpass et pg_hba.conf pour que testperf puisse se connecter à toute base par son mot de passe.

Restaurer le contenu de la base de donnée pgbench dans pgbench2 en utilisant le fichier de sauvegarde base_pgbench.dump. Toutes les tables sont-elles restaurées à l’identique ?

Puis renommer la base de données pgbench en pgbench_old et la base de données pgbench2 en pgbench.

Dans la base pgbench, détruire la table pgbench_history.

À partir de la sauvegarde de la base pgbench au format custom faite précédemment, restaurer uniquement la table pgbench_history dans la nouvelle base pgbench, avec pg_restore -t. La table est-elle complète ?

Rechercher les morceaux manquants dans la section post-data de la sauvegarde.

Dans une fenêtre, lancer une instance pgbench sur la base pgbench
/usr/pgsql-14/bin/pgbench -U testperf -T 600 pgbench

Pour dupliquer la base pbench :

Créer une nouvelle base pgbench3 appartenant à testperf.

Avec pg_dump, copier les données de la base pgbench dans cette nouvelle base pgbench3 sans passer par un fichier de sauvegarde, avec un pipe.

Sauvegarde et restauration partielle

(Optionnel) Sauvegarde et restauration par parties avec modification

(Optionnel) Sauvegardes d’objets isolés

Travaux pratiques (solutions)

Ce TP suppose que vous avez déjà quelques bases et rôles dans votre instance. Créez au moins ceci, qui existe peut-être déjà suite à d’autres TP :

createuser testperf
createuser patron
createdb entreprise --owner patron

createdb pgbench --owner testperf
/usr/pgsql-15/bin/pgbench -i -s1  pgbench --foreign-keys

Sauvegardes logiques

Répertoire cible

Créer un répertoire backups dans le répertoire home de postgres pour y déposer les fichiers d’export.

En tant qu’utilisateur postgres :

$ mkdir ~postgres/backups

Sauvegarde logique de toutes les bases

À l’aide de pg_dumpall, sauvegarder toutes les bases de données de l’instance PostgreSQL dans le fichier ~postgres/backups/base_all.sql.

En tant qu’utilisateur postgres, puis exécuter la commande suivante :

$ pg_dumpall > ~postgres/backups/base_all.sql

Quelle est la taille de la sauvegarde ?

$ ls -lh ~postgres/backups/
total 37M
-rw-r--r-- 1 postgres postgres 37M 1 août 17:17 base_all.sql

Avec quel outil consulter le contenu du fichier de sauvegarde ?

Ne jamais ouvrir un fichier potentiellement volumineux avec un éditeur comme vi car il pourrait alors consommer des gigaoctets de mémoire. Sous Unix, less convient en général :

$ less ~postgres/backups/base_all.sql

Ouvrir le fichier de sauvegarde. Quelles en sont les principales parties ?

Le fichier contient en clair des ordres SQL :

d’abord du paramétrage destiné à la restauration :

SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

la déclaration des rôles avec des mots de passe hachés, et les affectations des groupes :

CREATE ROLE boulier;
ALTER ROLE boulier WITH NOSUPERUSER INHERIT
NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS
PASSWORD 'SCRAM-SHA-256$4096:ksvTos26fH+0qUov4zssLQ==$un1…';
…
GRANT secretariat TO boulier GRANTED BY postgres;
GRANT secretariat TO tina GRANTED BY postgres;

des ordres de création des bases :

CREATE DATABASE entreprise WITH TEMPLATE = template0 ENCODING = 'UTF8'
LOCALE = 'en_GB.UTF-8';

ALTER DATABASE entreprise OWNER TO patron;

les ordres pour se connecter à chaque base et y déclarer des objets :

\connect entreprise
…
CREATE TABLE public.brouillon (
    id integer,
    objet text,
    creationts timestamp without time zone
);
ALTER TABLE public.brouillon OWNER TO boulier;
…

les données elles-mêmes, sous formes d’ordres COPY :

COPY public.produit (appellation) FROM stdin;
Gewurtzraminer vendanges tardives
Cognac
Eau plate
Eau gazeuse
Jus de framboise
\.

ainsi que divers ordres comme des contraintes ou des créations d’index :

ALTER TABLE ONLY public.pgbench_accounts
    ADD CONSTRAINT pgbench_accounts_pkey PRIMARY KEY (aid);

Recommencer la sauvegarde en compressant la sauvegarde avec gzip. Quelle en est la taille ?

$ pg_dumpall | gzip > ~postgres/backups/base_all.sql.gz

$ ls -lh ~postgres/backups/base_all.sql.gz
-rw-r--r--. 1 postgres postgres 6,0M  1 août  17:39 /var/lib/pgsql/backups/base_all.sql.gz

Le taux de compression de 80 % n’est pas inhabituel, mais il dépend bien entendu énormément des données.

Sauvegarde logique d’une base

Qu’affiche un pg_dump -d entreprise ?

Le résultat est un script, en clair, de création des objets de la base entreprise, sans ordre de création de la base ni d’utilisateur.

Notamment, les GRANT suppose la pré-existence des rôles adéquats.

À l’aide de pg_dump, sauvegarder la base de données pgbench au format custom dans le fichier ~postgres/backups/base_pgbench.dump. Quelle est sa taille ?

En tant qu’utilisateur postgres, puis exécuter la commande suivante :

$ pg_dump -Fc -f ~postgres/backups/base_pgbench.dump pgbench

La taille de 6 Mo environ indique que cette base contenait l’essentiel de la volumétrie de l’instance, et que la sauvegarde custom est bien compressée.

Avec pg_restore et son option -l, consulter le contenu de ~postgres/backups/base_pgbench.dump.

$ pg_restore -l ~postgres/backups/base_pgbench.dump
;
; Archive created at 2021-11-22 16:36:31 CET
;     dbname: pgbench
;     TOC Entries: 27
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.2
;     Dumped by pg_dump version: 15.2
;
;
; Selected TOC Entries:
;
200; 1259 18920 TABLE public copie1 postgres
201; 1259 18923 TABLE public copie2 postgres
202; 1259 18928 TABLE public copie3 postgres
198; 1259 18873 TABLE public pgbench_accounts testperf
199; 1259 18876 TABLE public pgbench_branches testperf
196; 1259 18867 TABLE public pgbench_history testperf
197; 1259 18870 TABLE public pgbench_tellers testperf
3712; 0 18920 TABLE DATA public copie1 postgres
3713; 0 18923 TABLE DATA public copie2 postgres
3714; 0 18928 TABLE DATA public copie3 postgres
3710; 0 18873 TABLE DATA public pgbench_accounts testperf
3711; 0 18876 TABLE DATA public pgbench_branches testperf
3708; 0 18867 TABLE DATA public pgbench_history testperf
3709; 0 18870 TABLE DATA public pgbench_tellers testperf
3579; 2606 18888 CONSTRAINT public pgbench_accounts pgbench_accounts_pkey testperf
3581; 2606 18884 CONSTRAINT public pgbench_branches pgbench_branches_pkey testperf
3577; 2606 18886 CONSTRAINT public pgbench_tellers pgbench_tellers_pkey testperf
3586; 2606 18894 FK CONSTRAINT public pgbench_accounts pgbench_accounts_bid_fkey…
3584; 2606 18909 FK CONSTRAINT public pgbench_history pgbench_history_aid_fkey…
3582; 2606 18899 FK CONSTRAINT public pgbench_history pgbench_history_bid_fkey…
3583; 2606 18904 FK CONSTRAINT public pgbench_history pgbench_history_tid_fkey…
3585; 2606 18889 FK CONSTRAINT public pgbench_tellers pgbench_tellers_bid_fkey…

Export des objets globaux

Exporter uniquement les objets globaux de l’instance (rôles, définitions de tablespaces) à l’aide de pg_dumpall dans le fichier ~postgres/backups/base_globals.sql.

$ pg_dumpall -g > ~postgres/backups/base_globals.sql

Consulter le contenu de ce fichier.

Le contenu du fichier est très réduit et ne reprend que les premiers éléments du fichier créé plus haut :

CREATE ROLE patron;
…
CREATE ROLE testperf;
…

Sauvegarde logique de tables

Sauvegarder la table pgbench_accounts dans le fichier ~postgres/backups/table_pgbench_accounts.sql au format plain text. Que contient ce fichier ?

$ pg_dump -t pgbench_accounts pgbench > ~postgres/backups/table_pgbench_accounts.sql

Le contenu se consulte avec :

less  ~postgres/backups/table_pgbench_accounts.sql

On y trouvera entre autre les ordres de création :

CREATE TABLE public.pgbench_accounts (
    aid integer NOT NULL,
    bid integer,
    abalance integer,
    filler character(84)
)
WITH (fillfactor='100');

l’affectation du propriétaire :

ALTER TABLE public.pgbench_accounts OWNER TO testperf;

les données :

COPY public.pgbench_accounts (aid, bid, abalance, filler) FROM stdin;
35      1       0
38      1       -11388
18      1       7416
7       1       -1124
...

Tout à la fin, les contraintes :

ALTER TABLE ONLY public.pgbench_accounts
    ADD CONSTRAINT pgbench_accounts_pkey PRIMARY KEY (aid);

ALTER TABLE ONLY public.pgbench_accounts
    ADD CONSTRAINT pgbench_accounts_bid_fkey FOREIGN KEY (bid)
    REFERENCES public.pgbench_branches(bid);

Restaurations logiques

Restauration d’une base

Que se passe-t-il si l’on exécute ceci ?
pg_restore ~postgres/backups/base_pgbench.dump -f -

La sortie affiche les ordres qui seront envoyés lors de la restauration. C’est très pratique pour consulter le contenu d’une sauvegarde compressée avant de l’exécuter.

À partir de la version 12, pg_restore exige d’avoir soit un fichier (-f) soit une base (-d) en destination. -f - envoie le résultat sur la sortie standard. Pour envoyer vers un fichier, demander -f dump.sql, et l’on obtiendra le SQL contenu dans la sauvegarde.

Pour une simple consultation du contenu :

$ pg_restore ~postgres/backups/base_pgbench.dump -f - |less

Créer une nouvelle base pgbench2 appartenant au rôle testperf.

En tant que postgres :

$ createdb --owner testperf pgbench2

Ajuster .pgpass et pg_hba.conf pour que testperf puisse se connecter à toute base par son mot de passe.

La connexion se fait par exemple en ajoutant le mot de passe dans le .pgpass si ce n’est déjà fait :

localhost:5432:*:testperf:860e74ea6eba6fdee4574c54aadf4f98

ainsi que ceci en tête du pg_hba.conf (ne pas oublier de recharger la configuration) :

local   all             testperf                                scram-sha-256

En tant qu’utilisateur postgres :

$ psql -U testperf -d pgbench2 -c "SELECT 'test de connexion' "

Restaurer le contenu de la base de donnée pgbench dans pgbench2 en utilisant le fichier de sauvegarde base_pgbench.dump. Toutes les tables sont-elles restaurées à l’identique ?

$ pg_restore -v -U testperf -d pgbench2 ~postgres/backups/base_pgbench.dump

L’option -v est optionnelle. Elle permet d’avoir le détail suivant :

pg_restore: connecting to database for restore
pg_restore: creating TABLE "public.copie1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 200;1259 18920
                            TABLE copie1 postgres
pg_restore: [archiver (db)] could not execute query:
    ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.copie1 OWNER TO postgres;
…
pg_restore: creating TABLE "public.pgbench_accounts"
pg_restore: creating TABLE "public.pgbench_branches"
pg_restore: creating TABLE "public.pgbench_history"
pg_restore: creating TABLE "public.pgbench_tellers"
…
pg_restore: processing data for table "public.pgbench_history"
pg_restore: processing data for table "public.pgbench_tellers"
pg_restore: creating CONSTRAINT "public.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating FK CONSTRAINT "public.pgbench_accounts
                                    pgbench_accounts_bid_fkey"
…

Un simple \d+ sur les bases pgbench et pgbench2 montre deux différences :

la taille des tables n’est pas forcément la même (bien que les données soient identiques) car l’organisation sur le disque est plus compacte sur une base qui n’a pas de vécu ;
les propriétaires des tables copie* ne sont pas les mêmes : en effet, on voit ci-dessus que les ordres ALTER TABLE échouent : testperf n’a pas les droits suffisants pour changer le propriétaire des tables. L’option --no-owner permet d’éviter ces messages.

Puis renommer la base de données pgbench en pgbench_old et la base de données pgbench2 en pgbench.

En tant qu’utilisateur postgres connecté à la base de même nom :

postgres@postgres=# ALTER DATABASE pgbench RENAME TO pgbench_old;
postgres@postgres=# ALTER DATABASE pgbench2 RENAME TO pgbench;

Les ordres peuvent échouer si une connexion est toujours en cours à la base.

postgres@postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collate  |   Ctype   | Access privileges
-----------+----------+----------+-----------+-----------+---------------------
entreprise | patron   | UTF8     |en_GB.UTF-8|en_GB.UTF-8|
pgbench    | testperf | UTF8     |en_GB.UTF-8|en_GB.UTF-8|
pgbench_old| postgres | UTF8     |en_GB.UTF-8|en_GB.UTF-8|
postgres   | postgres | UTF8     |en_GB.UTF-8|en_GB.UTF-8|
template0  | postgres | UTF8     |en_GB.UTF-8|en_GB.UTF-8|=c/postgres         +
           |          |          |           |           |postgres=CTc/postgres
template1  | postgres | UTF8     |en_GB.UTF-8|en_GB.UTF-8|=c/postgres         +
           |          |          |           |           |postgres=CTc/postgres

Restauration d’une table

Dans la base pgbench, détruire la table pgbench_history.

$ psql -U testperf -d pgbench

testperf@pgbench=> \d pgbench_history
                         Table « public.pgbench_history »
 Colonne |            Type             | Collationnement | NULL-able | Par défaut
---------+-----------------------------+-----------------+-----------+------------
 tid     | integer                     |                 |           |
 bid     | integer                     |                 |           |
 aid     | integer                     |                 |           |
 delta   | integer                     |                 |           |
 mtime   | timestamp without time zone |                 |           |
 filler  | character(22)               |                 |           |
Contraintes de clés étrangères :
    "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
    "pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
    "pgbench_history_tid_fkey" FOREIGN KEY (tid) REFERENCES pgbench_tellers(tid)

testperf@pgbench=> DROP TABLE pgbench_history ;
DROP TABLE

À partir de la sauvegarde de la base pgbench au format custom faite précédemment, restaurer uniquement la table pgbench_history dans la nouvelle base pgbench, avec pg_restore -t. La table est-elle complète ?

$ pg_restore -d pgbench -U testperf -t pgbench_history \
                                      ~postgres/backups/base_pgbench.dump

Les données sont bien là mais les contraintes n’ont pas été restaurées : en effet, pg_restore -t ne considère que les tables proprement dites.

postgres@pgbench=# \d pgbench_history
                         Table « public.pgbench_history »
 Colonne |            Type             | Collationnement | NULL-able | Par défaut
---------+-----------------------------+-----------------+-----------+------------
 tid     | integer                     |                 |           |
 bid     | integer                     |                 |           |
 aid     | integer                     |                 |           |
 delta   | integer                     |                 |           |
 mtime   | timestamp without time zone |                 |           |
 filler  | character(22)               |                 |           |

Rechercher les morceaux manquants dans la section post-data de la sauvegarde.

La manière la plus propre de récupérer une table depuis une sauvegarde est la technique avec -L décrite plus loin.

Une autre possibilité est d’avoir précédemment écrit un script de sauvegarde table à table comme ci-dessus. pg_dump -t, lui, sauvegarde les contraintes des tables.

Une autre possibilité est de retrouver les ordres manquants dans la sauvegarde. Ils sont dans la section post-data :

$ pg_restore --section=post-data  ~postgres/backups/base_pgbench.dump -f -

On a donc une version en SQL de la fin d’une sauvegarde, et on peut y piocher les trois ordres ALTER TABLE ... ADD CONSTRAINT ... concernant la table.

Migration de données

Dans une fenêtre, lancer une instance pgbench sur la base pgbench
/usr/pgsql-14/bin/pgbench -U testperf -T 600 pgbench

Cet ordre va générer de l’activité sur la base pendant la sauvegarde suivante.

Pour dupliquer la base pbench :

Créer une nouvelle base pgbench3 appartenant à testperf.

Avec pg_dump, copier les données de la base pgbench dans cette nouvelle base pgbench3 sans passer par un fichier de sauvegarde, avec un pipe.

$ createdb -O testperf  pgbench3
$ pg_dump -Fp  -U testperf pgbench | psql -U testperf -d pgbench3

Cet ordre ne doit pas générer d’erreur bien que les données soient modifiées pendant le backup. L’état des données restaurées sera celui au moment du début du pg_dump.

Le format custom est possible aussi, mais il utilise par défaut une compression ici inutile :

$ pg_dump -Fc -Z0  -U testperf pgbench | pg_restore -U testperf -d pgbench3

Sauvegarde et restauration partielle

Il faut restaurer dans une base pgbench4 tout le contenu de la sauvegarde de la base pgbench, sauf les données de la table pgbench_accounts ni les tables copie*. La définition de la table pgbench_accounts et toutes les contraintes s’y rapportant doivent être restaurées. Pour cela :

utiliser les options -l de pg_restore pour créer le fichier des objets à charger ;

adapter ce fichier en fonction des critères ci-dessus ;

utiliser ce fichier grâce à l’option -L de pg_restore.

On procède en deux étapes :

lister le contenu de l’archive dans un fichier :

$ pg_restore -l ~postgres/backups/base_pgbench.dump > /tmp/contenu_archive

dans /tmp/contenu_archive, supprimer ou commenter la ligne TABLE DATA de la table pgbench_accounts et tout ce qui peut concerner des tables copie* :

;200; 1259 18920 TABLE public copie1 postgres
;201; 1259 18923 TABLE public copie2 postgres
;202; 1259 18928 TABLE public copie3 postgres
...
;3712; 0 18920 TABLE DATA public copie1 postgres
;3713; 0 18923 TABLE DATA public copie2 postgres
;3714; 0 18928 TABLE DATA public copie3 postgres
;3710; 0 18873 TABLE DATA public pgbench_accounts testperf

Créer la base de données pgbench4 appartenant à testperf, puis restaurer en utilisant ce fichier :

$ createdb -O testperf  pgbench4
$ pg_restore -U testperf -d pgbench4 -L /tmp/contenu_archive \
                                        ~postgres/backups/base_pgbench.dump

S’il y a une contrainte vers la table pgbench_accounts, il est alors normal qu’elle échoue. Il y aura simplement un message d’erreur. Le plus propre aurait été de l’exclure d’entrée dans le fichier plus haut.

(Optionnel) Sauvegarde et restauration par parties avec modification

Effectuer une sauvegarde de pgbench_old, puis une restauration vers une nouvelle base test, lors de laquelle on ne rechargera que les tables pgbench_* en les renommant en test_*. Pour cela :

créer plusieurs fichiers (format plain) grâce aux options --section= de pg_dump ;

modifier les > fichiers obtenus ;

les charger ;

vérifier que les tables sont bien là avec les nouveaux noms.

On peut séparer la sauvegarde en 3 fichiers texte :

$ pg_dump -Fp -U testperf -d pgbench_old -t 'pgbench_*' --section=pre-data \
                                    -f ~postgres/backups/base_pgbench_old-1.sql
$ pg_dump -Fp -U testperf -d pgbench_old -t 'pgbench_*' --section=data \
                                    -f ~postgres/backups/base_pgbench_old-2.sql
$ pg_dump -Fp -U testperf -d pgbench_old -t 'pgbench_*' --section=post-data \
                                    -f ~postgres/backups/base_pgbench_old-3.sql

Le premier contient les déclarations d’objets. Supprimer les tables non voulues s’il y en a. Renommer les tables pgbench_* en test_*.

Procéder de même dans le troisième fichier, qui contient les contraintes et les index.

Le second contient les données elles-mêmes. Modifier les noms de table ici aussi. Heureusement le fichier est encore assez peu volumineux pour permettre une modification manuelle.

Noter que ces fichiers ne forment une sauvegarde cohérente que s’il n’y a pas eu de modification pendant leur création.

Restaurer les fichiers l’un après l’autre :

$ createdb -O testperf test

$ psql -U testperf -d test < ~postgres/backups/base_pgbench_old-1.sql
...
$ psql -U testperf -d test < ~postgres/backups/base_pgbench_old-2.sql
...
COPY 100000
COPY 1
COPY 38051
COPY 10

$ psql -U testperf -d test < ~postgres/backups/base_pgbench_old-3.sql
...
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

Vérifier le résultat :

$ psql -d test -c '\d+'
                          List of relations
 Schema |     Name      | Type  |  Owner   |    Size    | Description
--------+---------------+-------+----------+------------+-------------
 public | test_accounts | table | testperf | 13 MB      |
 public | test_branches | table | testperf | 8192 bytes |
 public | test_history  | table | testperf | 1968 kB    |
 public | test_tellers  | table | testperf | 8192 bytes |

Recommencer avec une sauvegarde des données en format custom en scriptant les modifications à la volée.

Jusque là il n’y a pas une grande différence avec une restauration depuis un fichier de sauvegarde unique. Les sections sont plus intéressantes s’il y a plus de données et qu’il n’est pas réaliste de les stocker sur le disque dans un fichier texte. Le deuxième fichier sera donc au format custom ou tar :

$ pg_dump -Fc -U testperf -d pgbench_old -t 'pgbench_*' --section=data \
                                  -f ~postgres/backups/base_pgbench_old-2.dump

Pour le restaurer, il faudra modifier les ordres COPY à la volée en sortie de pg_restore, sortie qui est réinjectée ensuite dans la cible avec psql. Le script ressemble par exemple à ceci :

$ pg_restore ~postgres/backups/base_pgbench_old-2.dump -f - | \
    awk '/^COPY/ {gsub("COPY public.pgbench_", "COPY public.test_") ; print }
         ! /^COPY/ {print} ' | \
    psql -U testperf -d test

La technique est utilisable pour toute manipulation des données par script. Pour éviter tout fichier intermédiaire, on peut même appliquer la technique à la sortie d’un pg_dump -Fp --section=data.

(Optionnel) Sauvegardes d’objets isolés

On veut sauvegarder les tables toutes les tables pgbench_* dans des fichiers séparés :

Créer un script shell nommé sauvegarde_tables.sh

il utilisera psql pour lister les tables concernées depuis la vue pg_tables ou \d+, dans la base pgbench ;

pour simplifier la sortie : utiliser les options -A, -t, -F de psql ;

enfin, boucler sur ces tables pour lancer un pg_dump, au format plain text, sur chaque table, vers des fichiers nommés table_<nom_table>.sql.

En tant qu’utilisateur système postgres, créer le script avec un éditeur de texte comme vi :

$ vi ~postgres/backups/sauvegarde_tables.sh

Le script peut ressemble à ceci :

#!/bin/bash
set -xue
export PGDATABASE=pgbench
export PGUSER=postgres
export PGPORT=5432
export PGHOST=/var/run/postgresql
for t in $(psql -XAt -F'.' -c "SELECT schemaname, tablename
                              FROM pg_tables
                              WHERE schemaname = 'public'
                              AND tablename LIKE 'pgbench_%' " )
do
  pg_dump -t "$t" pgbench > ~postgres/backups/table_$t.sql
done

Les options de psql sont importantes pour avoir une sortie propre :

-X court-circuite un éventuel ~/.psqlrc, qui peut contenir beaucoup de choses pouvant polluer l’affichage ;
-c permet de fournir la commande SQL ;
-A simplifie l’affichate unaligned) ;
-t réduit l’affichage aux lignes (tuples) ;
-F permet de préciser un séparateur, ci-dessus le point entre les deux champs.

Une variante utilise \dt et analyse la sortie avec awk :

#!/bin/bash
set -xue  # pour débogage et arrêt immédiat en cas d'erreur
LIST_TABLES=$(psql -XAt -c '\dt public.pgbench_*' -d pgbench | \
              awk -F'|' '{print $2}')
for t in $LIST_TABLES ; do
  echo "Sauvegarde $t..."
  pg_dump -t $t pgbench > ~postgres/backups/table_${t}.sql
done

NB : En production, il faudra bien sûr une gestion d’erreur.

Enfin, il faut donner les droits d’exécution au script :

$ chmod +x ~postgres/backups/sauvegarde_tables.sh

Et on teste :

$ ~postgres/backups/sauvegarde_tables.sh

Quel est le problème de principe de ces sauvegardes séparées sur une base active ?

Ce genre de script possède un problème fondamental : les sauvegardes sont faites séparément et la cohérence entre les tables exportées n’est plus du tout garantie !

Cela peut réserver de très mauvaises surprises à la restauration. pg_dump pare à cela en utilisant une transaction en repeatable read.

Pour des exports d’objets isolés, il est donc conseillé d’utiliser plutôt un même ordre pg_dump avec plusieurs paramètres -t. Noter que pg_dump ne peut garantir l’intégrité des données par rapport aux objets non exportés de la base.

Sauvegarde et restauration

Sur ce document

Licence Creative Commons CC-BY-NC-SA

Marques déposées

Versions de PostgreSQL couvertes

PostgreSQL : Sauvegarde et restauration

Introduction

Au menu

Sauvegardes logiques

Sauvegardes logiques : caractéristiques

pg_dump

pg_dump : formats de sauvegarde

pg_dump : choisir le format de sauvegarde

pg_dump : compression

pg_dump : fichier ou sortie standard

pg_dump : structure ou données

pg_dump : sélection d’objets

pg_dump : parallélisation

pg_dump : objets binaires

pg_dump : extensions

pg_dump : options diverses

pg_dumpall

pg_dumpall : sélection des objets

pg_dump/pg_dumpall : options de connexions

pg_dump/pg_dumpall : privilèges nécessaires

pg_dump/pg_dumpall : compresser la sortie

Restauration d’une sauvegarde logique

Quel outil pour restaurer une sauvegarde logique ?

psql

psql : options pour une restauration

pg_restore

pg_restore : indiquer la base de données

pg_restore : fichiers en entrée/sortie

pg_restore : structure ou données

pg_restore : sélection d’objets

pg_restore : sélection avancée

pg_restore : parallélisation

pg_restore : options diverses

Autres considérations sur la sauvegarde logique

Versions des outils clients et version de l’instance

Script de sauvegarde idéal

pg_back - Présentation

Sauvegarde et restauration sans fichier intermédiaire

Statistiques et maintenance après import

Durée d’exécution

Taille d’une sauvegarde logique

Avantages de la sauvegarde logique

Inconvénients de la sauvegarde logique

Sauvegarde physique à froid des fichiers

Sauvegarde physique à froid : principe

Avantages des sauvegardes à froid

Inconvénients des sauvegardes à froid

Diminuer l’immobilisation

Sauvegarde à chaud

Sauvegarde à chaud des fichiers par snapshot de partition : principe

Sauvegarde à chaud des fichiers avec PostgreSQL

Recommandations générales

Matrice des options de sauvegarde

Conclusion

Questions

Quiz

Travaux pratiques

Sauvegardes logiques

Restaurations logiques

Sauvegarde et restauration partielle

(Optionnel) Sauvegarde et restauration par parties avec modification

(Optionnel) Sauvegardes d’objets isolés

Travaux pratiques (solutions)