Vous trouverez en ligne les différentes versions complètes de ce document. La version imprimée ne contient pas les travaux pratiques. Ils sont présents dans la version numérique (PDF ou HTML).

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.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Photo de l’incendie du datacenter OVHcloud à Strasbourg du 10 mars 2021 fournie gracieusement par l’ITBR67.

Cet incendie a provoqué de nombreux arrêts et pertes de données dans toute la France et ailleurs.

Afin d’assurer la sécurité des données, il est nécessaire de faire des sauvegardes régulières.

Ces sauvegardes vont servir, en cas de problème, à restaurer les bases de données dans un état le plus proche possible du moment où le problème est survenu.

Cependant, le jour où une restauration sera nécessaire, il est possible que la personne qui a mis en place les sauvegardes ne soit pas présente. C’est pour cela qu’il est essentiel d’écrire et de maintenir un document qui indique la mise en place de la sauvegarde et qui détaille comment restaurer une sauvegarde.

En effet, suivant les besoins, les outils pour sauvegarder, le contenu de la sauvegarde, sa fréquence ne seront pas les mêmes.

Par exemple, il n’est pas toujours nécessaire de tout sauvegarder. Une base de données peut contenir des données de travail, temporaires et/ou faciles à reconstruire, stockées dans des tables standards. Il est également possible d’avoir une base dédiée pour stocker ce genre d’objets. Pour diminuer le temps de sauvegarde (et du coup de restauration), il est possible de sauvegarder partiellement son serveur pour ne conserver que les données importantes.

La fréquence peut aussi varier. Un utilisateur peut disposer d’un serveur PostgreSQL pour un entrepôt de données, serveur qu’il n’alimente qu’une fois par semaine. Dans ce cas, il est inutile de sauvegarder tous les jours. Une sauvegarde après chaque alimentation (donc chaque semaine) est suffisante. En fait, il faut déterminer la fréquence de sauvegarde des données selon :

• le volume de données à sauvegarder et/ou restaurer ;
• la criticité des données ;
• la quantité de données qu’il est « acceptable » de perdre en cas de problème.

Le support de sauvegarde est lui aussi très important. Il est possible de sauvegarder les données sur un disque réseau (à travers SMB/CIFS ou NFS), sur des disques locaux dédiés, sur des bandes ou tout autre support adapté. Dans tous les cas, il est fortement déconseillé de stocker les sauvegardes sur les disques utilisés par la base de données.

Ce document doit aussi indiquer comment effectuer la restauration. Si la sauvegarde est composée de plusieurs fichiers, l’ordre de restauration des fichiers peut être essentiel. De plus, savoir où se trouvent les sauvegardes permet de gagner un temps important, qui évitera une immobilisation trop longue.

De même, vérifier la restauration des sauvegardes de façon régulière est une précaution très utile.

L’objectif essentiel de la sauvegarde est la sécurisation des données. Autrement dit, l’utilisateur cherche à se protéger d’une panne matérielle ou d’une erreur humaine (un utilisateur qui supprimerait des données essentielles). La sauvegarde permet de restaurer les données perdues. Mais ce n’est pas le seul objectif d’une sauvegarde.

Une sauvegarde peut aussi servir à dupliquer une base de données sur un serveur de test ou de préproduction. Elle permet aussi d’archiver des tables. Cela se voit surtout dans le cadre des tables partitionnées où l’archivage de la table la plus ancienne permet ensuite sa suppression de la base pour gagner en espace disque.

Un autre cas d’utilisation de la sauvegarde est la mise à jour majeure de versions PostgreSQL. Il s’agit de la solution historique de mise à jour (export/import). Historique, mais pas obsolète.

À ces différents objectifs vont correspondre différentes approches de la sauvegarde.

La sauvegarde au niveau système de fichiers permet de conserver une image cohérente de l’intégralité des répertoires de données d’une instance arrêtée. C’est la sauvegarde à froid. Cependant, l’utilisation d’outils de snapshots pour effectuer les sauvegardes peut accélérer considérablement les temps de sauvegarde des bases de données, et donc diminuer d’autant le temps d’immobilisation du système.

La sauvegarde logique permet de créer un fichier texte de commandes SQL ou un fichier binaire contenant le schéma et les données de la base de données.

La sauvegarde à chaud des fichiers est possible avec le Point In Time Recovery.

Suivant les prérequis et les limitations de chaque méthode, il est fort possible qu’une seule de ces solutions soit utilisable. Par exemple :

• si le serveur ne peut pas être arrêté, la sauvegarde à froid est exclue d’office ;
• si la base de données est très volumineuse, la sauvegarde logique devient très longue ;
• si l’espace disque est limité et que l’instance génère beaucoup de journaux de transactions, la sauvegarde PITR sera difficile à mettre en place.

Le RPO et RTO sont deux concepts déterminants dans le choix des politiques de sauvegardes.

La RPO (ou PDMA) est la perte de données maximale admissible, ou quantité de données que l’on peut tolérer de perdre lors d’un sinistre majeur, souvent exprimée en heures ou minutes.

Pour un système mis à jour épisodiquement ou avec des données non critiques, ou facilement récupérables, le RPO peut être important (par exemple une journée). Peuvent alors s’envisager des solutions comme :

• les sauvegardes logiques (dump) ;
• les sauvegardes des fichiers à froid.

Dans beaucoup de cas, la perte de données admissible est très faible (heures, quelques minutes), voire nulle. Il faudra s’orienter vers des solutions de type :

• sauvegarde à chaud ;
• sauvegarde d’instantané à un point donnée dans le temps (PITR) ;
• réplication asynchrone, voire synchrone.

La RTO (ou DMIA) est la durée maximale d’interruption du service.

Dans beaucoup de cas, les utilisateurs peuvent tolérer une indisponibilité de plusieurs heures, voire jours. La durée de reprise du service n’est alors pas critique, on peut utiliser des solutions simples comme :

• la restauration des fichiers ;
• la restauration d’une sauvegarde logique (dump).

Si elle est plus courte, le service doit très vite remonter. Cela nécessite des procédures avec un minimum d’acteurs et de manipulation :

• réplication ;
• solutions HA (Haute Disponibilité).

Plus le besoin en RTO/RPO sera court, plus les solutions seront complexes à mettre en œuvre — et chères. Inversement, pour des données non critiques, un RTO/RPO long permet d’utiliser des solutions simples et peu coûteuses.

Les moyens nécessaires pour la mise en place, le maintien et l’intégration de la sauvegarde dans le SI ont un coût financier qui apporte une contrainte supplémentaire sur la politique de sauvegarde.

Du point de vue matériel, il faut disposer principalement d’un volume de stockage qui peut devenir conséquent. Cela dépend de la volumétrie à sauvegarder, il faut considérer les besoins suivants :

• Stocker plusieurs sauvegardes. Même avec une rétention d’une sauvegarde, il faut pouvoir stocker la suivante durant sa création : il vaut mieux purger les anciennes sauvegardes une fois qu’on est sûr que la sauvegarde s’est correctement déroulée.
• Avoir suffisamment de place pour restaurer sans avoir besoin de supprimer la base ou l’instance en production. Un tel espace de travail est également intéressant pour réaliser des restaurations partielles. Cet espace peut être mutualisé. On peut utiliser également le serveur de pré-production s’il dispose de la place suffisante.

Avec une rétention d’une sauvegarde unique, il est bon de prévoir 3 fois la taille de la base ou de l’instance. Pour une faible volumétrie, cela ne pose pas de problèmes, mais quand la volumétrie devient de l’ordre du téraoctet, les coûts augmentent significativement.

L’autre poste de coût est la mise en place de la sauvegarde. Une équipe de DBA peut tout à fait décider de créer ses propres scripts de sauvegarde et restauration, pour diverses raisons, notamment :

• maîtrise complète de la sauvegarde, maintien plus aisé du code ;
• intégration avec les moyens de sauvegardes communs au SI (bandes, externalisation…) ;
• adaptation au PRA/PCA plus fine.

Enfin, le dernier poste de coût est la maintenance, à la fois des scripts et par le test régulier de la restauration.

Comme pour n’importe quelle procédure, il est impératif de documenter la politique de sauvegarde, les procédures de sauvegarde et de restauration ainsi que les scripts.

Au strict minimum, la documentation doit permettre à un DBA non familier de l’environnement de comprendre la sauvegarde, retrouver les fichiers et restaurer les données le cas échéant, le plus rapidement possible et sans laisser de doute. En effet, en cas d’avarie nécessitant une restauration, le service aux utilisateurs finaux est généralement coupé, ce qui génère un climat de pression propice aux erreurs qui ne fait qu’empirer la situation.

L’idéal est de réviser la documentation régulièrement en accompagnant ces révisions de tests de restauration : avoir un ordre de grandeur de la durée d’une restauration est primordial. On demandera toujours au DBA qui restaure une base ou une instance combien de temps cela va prendre.

L’un des points les plus importants à prendre en compte est l’endroit où sont stockés les fichiers des sauvegardes. Laisser les sauvegardes sur la même machine n’est pas suffisant : si une défaillance matérielle se produisait, les sauvegardes pourraient être perdues en même temps que l’instance sauvegardée, rendant ainsi la restauration impossible.

Il est conseillé de suivre au moins la règle 3-2-1. Les données elles-mêmes sont le premier exemplaire.

Les deux copies doivent se trouver sur des supports physiques différents (et de préférence sur un autre serveur) pour parer à la destruction du support original (notamment une perte de disques durs). La première copie peut être à proximité pour faciliter une restauration.

Le troisième exemplaire doit se trouver à un autre endroit, pour parer aux scénarios les plus catastrophiques (cambriolage, incendie…). Selon la criticité, le délai nécessaire pour remonter rapidement un système fonctionnel, et le budget, ce troisième exemplaire peut être une copie manuelle sur un disque externe stocké dans un coffre, ou une infrastructure répliquée complète avec sa copie des sauvegardes à l’autre bout de la ville, voire du pays.

Pour limiter la consommation d’espace disque des copies multiples, les durées de rétention peuvent différer. La dernière sauvegarde peut résider sur la machine, les 5 dernières sur un serveur distant, des bandes déposées dans un site sécurisé tous les mois.

Stocker vos données dans le cloud n’est pas une solution miracle. Un datacenter peut brûler entièrement. Dans la formule choisie, il faut donc bien vérifier que le fournisseur sauvegarde les données sur un autre site. Ce n’est pas forcément suffisant : en 2024, Google Cloud a effacé par erreur tout le cloud privé (deux zones) de l’assureur australien UniSuper, qui n’a pu remonter son infrastructure que grâce à des sauvegardes hors de ce cloud.

Il ne faut pas non-plus sous-estimer le risque d’une attaque (piratage, malveillance ou ransomware…), qui s’en prendra aussi à toute sauvegarde accessible en ligne. En 2023 l’hébergeur danois CloudNordic a perdu toutes les données de ses clients à cause d’un ransomware.

La sauvegarde ne concerne pas uniquement les données. Il est également fortement conseillé de sauvegarder les fichiers de configuration du serveur et les scripts d’administration. Ils sont parfois générés par l’outil d’industrialisation, gérés par un outil externe (Patroni…), ou versionnés ailleurs. L’idéal est de les copier avec les sauvegardes. On peut également déléguer cette tâche à une sauvegarde au niveau système, vu que ce sont de simples fichiers. Les principaux fichiers de PostgreSQL à prendre en compte sont : postgresql.conf, postgresql.auto.conf, pg_hba.conf, pg_ident.conf. Ces fichiers ont parfois des clauses d’inclusion pour en appeler d’autres. Cette liste n’est en aucun cas exhaustive.

Il s’agit donc de recenser l’ensemble des fichiers et scripts nécessaires si l’on désirait recréer le serveur depuis zéro.

Enfin, même si les sauvegardes se déroulent correctement, il est indispensable de tester si elles se restaurent sans erreur. Une erreur de copie lors de l’externalisation peut, par exemple, rendre la sauvegarde inutilisable.

Le test de restauration permet de vérifier l’ensemble de la procédure : ensemble des objets sauvegardés, intégrité de la copie, commandes à passer pour une restauration complète (en cas de stress, vous en oublierez probablement une partie). De plus, cela permet d’estimer la durée nécessaire à la restauration.

Nous rencontrons régulièrement en clientèle des scripts de sauvegarde qui ne fonctionnent pas, et jamais testés. Vous trouverez sur Internet de nombreuses histoires de catastrophes qui auraient été évitées par un simple test. Entre mille autres :

• une base disparaît à l’arrêt et ses sauvegardes sont vides : erreur de manipulation, script ne vérifiant pas qu’il a bien fait sa tâche, monitoring défaillant ;
• perte de 6 heures de données chez Gitlab en 2017 : procédures de sauvegarde et de réplication incomplètes, complexes et peu claires, outils mal choisis ou mal connus, sauvegardes vides et jamais testées, distraction d’un opérateur — Gitlab a le mérite d’avoir tout soigneusement documenté.

Restaurer régulièrement les bases de test ou de préproduction à partir des sauvegardes de la production est une bonne idée. Cela vous évitera de découvrir la procédure dans l’urgence, le stress, voire la panique, alors que vous serez harcelé par de nombreux utilisateurs ou clients bloqués.

L’écosystème de PostgreSQL offre tout le nécessaire pour effectuer des sauvegardes fiables. Le plan de sauvegarde doit être fait sérieusement, et les sauvegardes testées. Cela a un coût, mais un désastre détruisant toutes vos données sera incommensurablement plus ruineux.

La sauvegarde traditionnelle, qu’elle soit logique ou physique à froid, répond à beaucoup de besoins. Cependant, ce type de sauvegarde montre de plus en plus ses faiblesses pour les gros volumes : la sauvegarde est longue à réaliser et encore plus longue à restaurer. Et plus une sauvegarde met du temps, moins fréquemment on l’exécute. La fenêtre de perte de données devient plus importante.

PostgreSQL propose une solution à ce problème avec la sauvegarde physique à chaud. On peut l’utiliser comme un simple mode de sauvegarde supplémentaire, mais elle permet bien d’autres possibilités, d’où le nom de PITR (Point In Time Recovery).

Ce module fait le tour de la sauvegarde PITR, de la mise en place de l’archivage (manuelle ou avec l’outil pg_receivewal) à la sauvegarde des fichiers (en manuel, ou avec l’outil pg_basebackup). Il discute aussi de la restauration d’une telle sauvegarde. Nous évoquerons très rapidement quelques outils externes pour faciliter ces sauvegardes.

PITR est l’acronyme de Point In Time Recovery, autrement dit restauration à un point dans le temps.

C’est une sauvegarde à chaud et surtout en continu. Là où une sauvegarde logique du type pg_dump se fait au mieux une fois toutes les 24 h, la sauvegarde PITR se fait en continu grâce à l’archivage des journaux de transactions. De ce fait, ce type de sauvegarde diminue très fortement la fenêtre de perte de données.

Bien qu’elle se fasse à chaud, la sauvegarde est cohérente.

Quand une transaction est validée, les données à écrire dans les fichiers de données sont d’abord écrites dans un journal de transactions. Ces journaux décrivent donc toutes les modifications survenant sur les fichiers de données, que ce soit les objets utilisateurs comme les objets systèmes. Pour reconstruire un système, il suffit donc d’avoir ces journaux et d’avoir un état des fichiers du répertoire des données à un instant t. Toutes les actions effectuées après cet instant t pourront être rejouées en demandant à PostgreSQL d’appliquer les actions contenues dans les journaux. Les opérations stockées dans les journaux correspondent à des modifications physiques de fichiers, il faut donc partir d’une sauvegarde au niveau du système de fichier, un export avec pg_dump n’est pas utilisable.

Il est donc nécessaire de conserver ces journaux de transactions. Or PostgreSQL les recycle dès qu’il n’en a plus besoin. La solution est de demander au moteur de les archiver ailleurs avant ce recyclage. On doit aussi disposer de l’ensemble des fichiers qui composent le répertoire des données (incluant les tablespaces si ces derniers sont utilisés).

La restauration a besoin des journaux de transactions archivés. Il ne sera pas possible de restaurer et éventuellement revenir à un point donné avec la sauvegarde seule. En revanche, une fois la sauvegarde des fichiers restaurée et la configuration réalisée pour rejouer les journaux archivés, il sera possible de les rejouer tous ou seulement une partie d’entre eux (en s’arrêtant à un certain moment). Ils doivent impérativement être rejoués dans l’ordre de leur écriture (et donc de leur nom).

Tout le travail est réalisé à chaud, que ce soit l’archivage des journaux ou la sauvegarde des fichiers de la base. En effet, il importe peu que les fichiers de données soient modifiés pendant la sauvegarde car les journaux de transactions archivés permettront de corriger toute incohérence par leur application.

Il est possible de rejouer un très grand nombre de journaux (une journée, une semaine, un mois, etc.). Évidemment, plus il y a de journaux à appliquer, plus cela prendra du temps. Mais il n’y a pas de limite au nombre de journaux à rejouer.

Dernier avantage, c’est le système de sauvegarde qui occasionnera le moins de perte de données. Généralement, une sauvegarde pg_dump s’exécute toutes les nuits, disons à 3 h du matin. Supposons qu’un gros problème survienne à midi. S’il faut restaurer la dernière sauvegarde, la perte de données sera de 9 h. Le volume maximum de données perdu correspond à l’espacement des sauvegardes. Avec l’archivage continu des journaux de transactions, la fenêtre de perte de données va être fortement réduite. Plus l’activité est intense, plus la fenêtre de temps sera petite : il faut changer de fichier de journal pour que le journal précédent soit archivé et les fichiers de journaux sont de taille fixe.

Pour les systèmes n’ayant pas une grosse activité, il est aussi possible de forcer un changement de journal à intervalle régulier, ce qui a pour effet de forcer son archivage, et donc dans les faits de pouvoir s’assurer une perte correspondant au maximum à cet intervalle.

Certains inconvénients viennent directement du fait qu’on copie les fichiers : sauvegarde et restauration complète (impossible de ne restaurer qu’une seule base ou que quelques tables), restauration sur la même architecture (32/64 bits, little/big endian). Il est même fortement conseillé de restaurer dans la même version du même système d’exploitation, sous peine de devoir réindexer l’instance (différence de définition des locales notamment).

Elle nécessite en plus un plus grand espace de stockage car il faut sauvegarder les fichiers (dont les index) ainsi que les journaux de transactions sur une certaine période, ce qui peut être volumineux (en tout cas beaucoup plus que des pg_dump).

En cas de problème dans l’archivage et selon la méthode choisie, l’instance ne voudra pas effacer les journaux non archivés. Il y a donc un risque d’accumulation de ceux-ci. Il faudra donc surveiller la taille du pg_wal. En cas de saturation, PostgreSQL s’arrête !

Enfin, la sauvegarde PITR est plus complexe à mettre en place qu’une sauvegarde pg_dump. Elle nécessite plus d’étapes, une réflexion sur l’architecture à mettre en œuvre et une meilleure compréhension des mécanismes internes à PostgreSQL pour en avoir la maîtrise.

Description :

pg_basebackup est un produit qui a beaucoup évolué dans les dernières versions de PostgreSQL. De plus, le paramétrage par défaut le rend immédiatement utilisable.

Il permet de réaliser toute la sauvegarde de l’instance, à distance, via deux connexions de réplication : une pour les données, une pour les journaux de transactions qui sont générés pendant la copie. Sa compression permet d’éviter une durée de transfert ou une place disque occupée trop importante. Cela a évidemment un coût, notamment au niveau CPU, sur le serveur ou sur le client suivant le besoin. Il est simple à mettre en place et à utiliser, et permet d’éviter de nombreuses étapes que nous verrons par la suite.

Par contre, il ne permet pas de réaliser une sauvegarde incrémentale, et ne permet pas de continuer à archiver les journaux, contrairement aux outils de PITR classiques. Cependant, ceux-ci peuvent l’utiliser pour réaliser la première copie des fichiers d’une instance.

Mise en place :

pg_basebackup nécessite des connexions de réplication. Il peut utiliser un slot de réplication, une technique qui fiabilise la sauvegarde ou la réplication en indiquant à l’instance quels journaux elle doit conserver. Par défaut, tout est en place pour une connexion en local :

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

Ensuite, il faut configurer le fichier pg_hba.conf pour accepter la connexion du serveur où est exécutée pg_basebackup. Dans notre cas, il s’agit du même serveur avec un utilisateur dédié :

host  replication  sauve  127.0.0.1/32  scram-sha-256

Enfin, il faut créer un utilisateur dédié à la réplication (ici sauve) qui sera le rôle créant la connexion et lui attribuer un mot de passe :

CREATE ROLE sauve LOGIN REPLICATION;
\password sauve

Dans un but d’automatisation, le mot de passe finira souvent dans un fichier .pgpass ou équivalent.

Il ne reste plus qu’à :

• lancer pg_basebackup, ici en lui demandant une archive au format tar ;
• archiver les journaux en utilisant une connexion de réplication par streaming ;
• forcer le checkpoint.

Cela donne la commande suivante, ici pour une sauvegarde en local :

$ pg_basebackup --format=tar --wal-method=stream \
 --checkpoint=fast --progress -h 127.0.0.1 -U sauve \
 -D /var/lib/postgresql/backups/

644320/644320 kB (100%), 1/1 tablespace

Le résultat est ici un ensemble des deux archives : les journaux sont à part et devront être dépaquetés dans le pg_wal à la restauration.

$ ls -l /var/lib/postgresql/backups/
total 4163772
-rw------- 1 postgres postgres 659785216 Oct  9 11:37 base.tar
-rw------- 1 postgres postgres  16780288 Oct  9 11:37 pg_wal.tar

La cible doit être vide. En cas d’arrêt avant la fin, il faudra tout recommencer de zéro, c’est une limite de l’outil.

Restauration :

Pour restaurer, il suffit de remplacer le PGDATA corrompu par le contenu de l’archive, ou de créer une nouvelle instance et de remplacer son PGDATA par cette sauvegarde. Au démarrage, l’instance repérera qu’elle est une sauvegarde restaurée et réappliquera les journaux. L’instance contiendra les données telles qu’elles étaient à la fin du pg_basebackup.

Noter que les fichiers de configuration ne sont PAS inclus s’ils ne sont pas dans le PGDATA, notamment sur Debian et ses versions dérivées.

Différences entre les versions :

Un slot temporaire sera créé par défaut pour garantir que le serveur gardera les journaux jusqu’à leur copie intégrale.

À partir de la version 13, la commande pg_basebackup crée un fichier manifeste contenant la liste des fichiers sauvegardés, leur taille et une somme de contrôle. Cela permet de vérifier la sauvegarde avec l’outil pg_verifybackup (ce dernier ne fonctionne hélas que sur une sauvegarde au format plain, ou décompressée).

L’outil est développé plus en détail dans notre module I4.

Même si la mise en place est plus complexe qu’un pg_dump, la sauvegarde PITR demande peu d’étapes. La première chose à faire est de mettre en place l’archivage des journaux de transactions. Un choix est à faire entre un archivage classique et l’utilisation de l’outil pg_receivewal.

Lorsque cette étape est réalisée (et fonctionnelle), il est possible de passer à la seconde : la sauvegarde des fichiers. Là-aussi, il y a différentes possibilités : soit manuellement, soit pg_basebackup, soit son propre script ou un outil extérieur.

La méthode historique est la méthode utilisant le processus archiver. Ce processus fonctionne sur le serveur à sauvegarder et est de la responsabilité du serveur PostgreSQL. Seule sa (bonne) configuration incombe au DBA.

Une autre méthode existe : pg_receivewal. Cet outil livré aussi avec PostgreSQL se comporte comme un serveur secondaire. Il reconstitue les journaux de transactions à partir du flux de réplication.

Chaque solution a ses avantages et inconvénients qu’on étudiera après avoir détaillé leur mise en place.

Dans le cas de l’archivage historique, le serveur PostgreSQL va exécuter une commande qui va copier les journaux à l’extérieur de son répertoire de travail :

• sur un disque différent du même serveur ;
• sur un disque d’un autre serveur ;
• sur des bandes, un CDROM, etc.

Dans le cas de l’archivage avec pg_receivewal, c’est cet outil qui va écrire les journaux dans un répertoire de travail. Cette écriture ne peut se faire qu’en local. Cependant, le répertoire peut se trouver dans un montage NFS.

L’exemple pris ici utilise le répertoire /mnt/nfs1/archivage comme répertoire de copie. Ce répertoire est en fait un montage NFS. Il faut donc commencer par créer ce répertoire et s’assurer que l’utilisateur Unix (ou Windows) postgres peut écrire dedans :

 # mkdir /mnt/nfs1/archivage
 # chown postgres:postgres /mnt/nfs1/archivage

Après avoir créé le répertoire d’archivage, il faut configurer PostgreSQL pour lui indiquer comment archiver.

Niveau d’archivage :

La valeur par défaut de wal_level est adéquate :

wal_level = replica

Ce paramètre indique le niveau des informations écrites dans les journaux de transactions. Avec un niveau minimal, les journaux ne servent qu’à garantir la cohérence des fichiers de données en cas de crash. Dans le cas d’un archivage, il faut écrire plus d’informations, d’où la nécessité du niveau replica (qui est celui par défaut). Le niveau logical, nécessaire à la réplication logique, convient également.

Mode d’archivage :

Il s’active ainsi sur une instance seule ou primaire :

archive_mode = on

(La valeur always permet d’archiver depuis un secondaire). Le changement nécessite un redémarrage !

Commande d’archivage :

Enfin, une commande d’archivage doit être définie par le paramètre archive_command. archive_command sert à archiver un seul fichier à chaque appel.

PostgreSQL l’appelle une fois pour chaque fichier WAL, impérativement dans l’ordre des fichiers. En cas d’échec, elle est répétée indéfiniment jusqu’à réussite, avant de passer à l’archivage du fichier suivant. C’est la technique encore la plus utilisée.

(Noter qu’à partir de la version 15, il existe une alternative, avec l’utilisation du paramètre archive_library. Il est possible d’indiquer une bibliothèque partagée qui fera ce travail d’archivage. Une telle bibliothèque, écrite en C, devrait être plus puissante et performante. Un module basique est fourni avec PostgreSQL : basic_archive. Notre blog présente un exemple fonctionnel de module d’archivage utilisant une extension en C pour compresser les journaux de transactions. Mais en production, il vaudra mieux utiliser une bibliothèque fournie par un outil PITR reconnu. Cependant, à notre connaissance (en décembre 2023), aucun n’utilise encore cette fonctionnalité. L’utilisation simultanée de archive_command et archive_library est déconseillée, et interdite depuis PostgreSQL 16.)

Exemples d’archive_command :

PostgreSQL laisse le soin à l’administrateur de définir la méthode d’archivage des journaux de transactions suivant son contexte. Si vous utilisez un outil de sauvegarde, la commande vous sera probablement fournie. Une simple commande de copie suffit dans la plupart des cas. La directive archive_command peut alors être positionnée comme suit :

archive_command = 'cp %p /mnt/nfs1/archivage/%f'

Le joker %p est remplacé par le chemin complet vers le journal de transactions à archiver, alors que le joker %f correspond au nom du journal de transactions une fois archivé.

En toute rigueur, une copie du fichier ne suffit pas. Par exemple, dans le cas de la commande cp, le nouveau fichier n’est pas immédiatement écrit sur disque. La copie est effectuée dans le cache disque du système d’exploitation. En cas de crash juste après la copie, il est tout à fait possible de perdre l’archive. Il est donc essentiel d’ajouter une étape de synchronisation du cache sur disque.

La commande d’archivage suivante est donnée dans la documentation officielle à titre d’exemple :

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

Cette protection est une bonne chose. Cependant, il faut être vigilant lorsque l’on rétablit le fonctionnement de l’archiver suite à un incident ayant provoqué des écritures partielles dans le répertoire d’archive, comme une saturation de l’espace disque.

Il est aussi possible de placer dans archive_command le nom d’un script bash, perl ou autre. L’intérêt est de pouvoir faire plus qu’une simple copie. On peut y ajouter la demande de synchronisation du cache sur disque. Il peut aussi être intéressant de tracer l’action de l’archivage par exemple, ou encore de compresser le journal avant archivage.

Il est donc important de surveiller le processus d’archivage et de faire remonter les problèmes à un opérateur. Les causes d’échec sont nombreuses : problème réseau, montage inaccessible, erreur de paramétrage de l’outil, droits insuffisants ou expirés, génération de journaux trop rapide…

À titre d’exemple encore, les commandes fournies par pgBackRest ou barman ressemblent à ceci :

# pgBackRest
archive_command='/usr/bin/pgbackrest --stanza=prod archive-push %p'

# barman
archive_command='/usr/bin/barman-wal-archive backup prod %p'

archive_mode = on
archive_command = '/bin/true'

Période minimale entre deux archivages :

Si l’activité en écriture est très réduite, il peut se passer des heures entre deux archivages de journaux. Il est alors conseillé de forcer un archivage périodique, même si le journal n’a pas été rempli complètement, en indiquant un délai maximum entre deux archivages :

archive_timeout = '5min'

(La valeur par défaut, 0, désactive ce comportement.) Comme la taille d’un fichier journal, même incomplet, reste fixe (16 Mo par défaut), la consommation en terme d’espace disque sera plus importante (la compression par l’outil d’archivage peut compenser cela), et le temps de restauration plus long.

Il ne reste plus qu’à indiquer à PostgreSQL de recharger sa configuration pour que l’archivage soit en place (avec SELECT pg_reload_conf(); ou la commande reload adaptée au système). Dans le cas où l’un des paramètres wal_level et archive_mode a été modifié, il faudra relancer PostgreSQL.

PostgreSQL archive les journaux impérativement dans l’ordre.

La supervision se fait de quatre manières complémentaires.

Taille :

Si le répertoire pg_wal commence à grossir fortement, c’est que PostgreSQL n’arrive plus à recycler ses journaux de transactions : c’est un indicateur d’une commande d’archivage n’arrivant pas à faire son travail pour une raison ou une autre. Ce peut être temporaire si l’archivage est juste lent. Si l’archivage est bloqué, ce répertoire grossira indéfiniment.

Vue pg_stat_archiver :

La vue système pg_stat_archiver indique les derniers journaux archivés et les dernières erreurs. Dans l’exemple suivant, il y a eu un problème pendant quelques secondes, d’où 6 échecs, avant que l’archivage reprenne :

# SELECT * FROM pg_stat_archiver \gx

-[ RECORD 1 ]------+------------------------------
archived_count     | 156
last_archived_wal  | 0000000200000001000000D9
last_archived_time | 2020-01-17 18:26:03.715946+00
failed_count       | 6
last_failed_wal    | 0000000200000001000000D7
last_failed_time   | 2020-01-17 18:24:24.463038+00
stats_reset        | 2020-01-17 16:08:37.980214+00

Comme dit plus haut, pour que cette supervision soit fiable, la commande exécutée doit renvoyer un code retour inférieur ou égal à 125. Dans le cas contraire, le processus archiver redémarre et l’erreur n’apparaît pas dans la vue !

Traces :

On trouvera la sortie et surtout les messages d’erreurs du script d’archivage dans les traces (qui dépendent bien sûr du script utilisé) :

2020-01-17 18:24:18.427 UTC [15431] LOG:  archive command failed with exit code 3
2020-01-17 18:24:18.427 UTC [15431] DETAIL:  The failed archive command was:
  rsync pg_wal/0000000200000001000000D7 /opt/pgsql/archives/0000000200000001000000D7
rsync: change_dir#3 "/opt/pgsql/archives" failed: No such file or directory (2)
rsync error: errors selecting input/output files, dirs (code 3) at main.c(695)
  [Receiver=3.1.2]
2020-01-17 18:24:19.456 UTC [15431] LOG:  archive command failed with exit code 3
2020-01-17 18:24:19.456 UTC [15431] DETAIL:  The failed archive command was:
  rsync pg_wal/0000000200000001000000D7 /opt/pgsql/archives/0000000200000001000000D7
rsync: change_dir#3 "/opt/pgsql/archives" failed: No such file or directory (2)
rsync error: errors selecting input/output files, dirs (code 3) at main.c(695)
  [Receiver=3.1.2]
2020-01-17 18:24:20.463 UTC [15431] LOG:  archive command failed with exit code 3

C’est donc le premier endroit à regarder en cas de souci ou lors de la mise en place de l’archivage.

pg_wal/archive_status :

Enfin, on peut monitorer les fichiers présents dans pg_wal/archive_status. Les fichiers .ready, de taille nulle, indiquent en permanence quels sont les journaux prêts à être archivés. Théoriquement, leur nombre doit donc rester faible et retomber rapidement à 0 ou 1. Le service ready_archives de la sonde Nagios check_pgactivity se base sur ce répertoire.

postgres=# SELECT * FROM pg_ls_dir ('pg_wal/archive_status') ORDER BY 1;

           pg_ls_dir
--------------------------------
 0000000200000001000000DE.done
 0000000200000001000000DF.done
 0000000200000001000000E0.done
 0000000200000001000000E1.ready
 0000000200000001000000E2.ready
 0000000200000001000000E3.ready
 0000000200000001000000E4.ready
 0000000200000001000000E5.ready
 0000000200000001000000E6.ready
 00000002.history.done

pg_receivewal est un outil permettant de se faire passer pour un serveur secondaire utilisant la réplication en flux (streaming replication) dans le but d’archiver en continu les journaux de transactions. Il fonctionne habituellement sur un autre serveur, où seront archivés les journaux. C’est une alternative à l’archiver.

Comme il utilise le protocole de réplication, les journaux archivés ont un retard bien inférieur à celui induit par la configuration du paramètre archive_command ou du paramètre archive_library, les journaux de transactions étant écrits au fil de l’eau avant d’être complets. Cela permet donc de faire de l’archivage PITR avec une perte de données minimum en cas d’incident sur le serveur primaire. On peut même utiliser une réplication synchrone (paramètres synchronous_commit et synchronous_standby_names) pour ne perdre aucune transaction, si l’on accepte un impact certain sur la latence des transactions.

Cet outil utilise les mêmes options de connexion que la plupart des outils PostgreSQL, avec en plus l’option -D pour spécifier le répertoire où sauvegarder les journaux de transactions. L’utilisateur spécifié doit bien évidemment avoir les attributs LOGIN et REPLICATION.

Comme il s’agit de conserver toutes les modifications effectuées par le serveur dans le cadre d’une sauvegarde permanente, il est nécessaire de s’assurer qu’on ne puisse pas perdre des journaux de transactions. Il n’y a qu’un seul moyen pour cela : utiliser la technologie des slots de réplication. En utilisant un slot de réplication, pg_receivewal s’assure que le serveur ne va pas recycler des journaux dont pg_receivewal n’aurait pas reçu les enregistrements. On retrouve donc le risque d’accumulation des journaux sur le serveur principal si pg_receivewal ne fonctionne pas.

Voici l’aide de cet outil en v15 :

$ pg_receivewal --help
pg_receivewal reçoit le flux des journaux de transactions PostgreSQL.

Usage :
  pg_receivewal [OPTION]...

Options :
  -D, --directory=RÉPERTOIRE     reçoit les journaux de transactions dans ce
                                 répertoire
  -E, --endpos=LSN               quitte après avoir reçu le LSN spécifié
      --if-not-exists            ne pas renvoyer une erreur si le slot existe
                                 déjà lors de sa création
  -n, --no-loop                  ne boucle pas en cas de perte de la connexion
      --no-sync                  n'attend pas que les modifications soient
                                 proprement écrites sur disque
  -s, --status-interval=SECS     durée entre l'envoi de paquets de statut au
                                 (par défaut 10)
  -S, --slot=NOMREP              slot de réplication à utiliser
      --synchronous              vide le journal de transactions immédiatement
                                 après son écriture
  -v, --verbose                  affiche des messages verbeux
  -V, --version                  affiche la version puis quitte
  -Z, --compress=METHOD[:DETAIL]
                                 compresse comme indiqué
  -?, --help                     affiche cette aide puis quitte

Options de connexion :
  -d, --dbname=CHAÎNE_CONNEX     chaîne de connexion
  -h, --host=HÔTE                hôte du serveur de bases de données ou
                                 répertoire des sockets
  -p, --port=PORT                numéro de port du serveur de bases de données
  -U, --username=UTILISATEUR     se connecte avec cet utilisateur
  -w, --no-password              ne demande jamais le mot de passe
  -W, --password                 force la demande du mot de passe (devrait
                                 survenir automatiquement)

Actions optionnelles :
      --create-slot              crée un nouveau slot de réplication
                                 (pour le nom du slot, voir --slot)
      --drop-slot                supprime un nouveau slot de réplication
                                 (pour le nom du slot, voir --slot)

Rapporter les bogues à <pgsql-bugs@lists.postgresql.org>.
Page d'accueil de PostgreSQL : <https://www.postgresql.org/>

Le paramètre max_wal_senders indique le nombre maximum de connexions de réplication sur le serveur. Logiquement, une valeur de 1 serait suffisante, mais il faut compter sur quelques soucis réseau qui pourraient faire perdre la connexion à pg_receivewal sans que le serveur primaire n’en soit mis au courant, et du fait que certains autres outils peuvent utiliser la réplication. max_replication_slots indique le nombre maximum de slots de réplication. Pour ces deux paramètres, le défaut est 10 et suffit dans la plupart des cas.

Si l’on modifie un de ces paramètres, il est nécessaire de redémarrer le serveur PostgreSQL.

Les connexions de réplication nécessitent une configuration particulière au niveau des accès. D’où la modification du fichier pg_hba.conf. Le sous-réseau (192.168.0.0/24) est à modifier suivant l’adressage utilisé. Il est d’ailleurs préférable de n’indiquer que le serveur où est installé pg_receivewal (plutôt que l’intégralité d’un sous-réseau).

L’utilisation d’un utilisateur de réplication n’est pas obligatoire mais fortement conseillée pour des raisons de sécurité.

Enfin, nous devons créer le slot de réplication qui sera utilisé par pg_receivewal. La fonction pg_create_physical_replication_slot() est là pour ça. Il est à noter que la liste des slots est disponible dans le catalogue système pg_replication_slots.

On peut alors lancer pg_receivewal :

pg_receivewal -h 192.168.0.1 -U repli_user -D /data/archives -S archivage

Les journaux de transactions sont alors créés en temps réel dans le répertoire indiqué (ici, /data/archives) :

-rwx------  1 postgres postgres  16MB juil. 27 00000001000000000000000E*
-rwx------  1 postgres postgres  16MB juil. 27 00000001000000000000000F*
-rwx------  1 postgres postgres  16MB juil. 27 000000010000000000000010.partial*

En cas d’incident sur le serveur primaire, il est alors possible de partir d’une sauvegarde physique et de rejouer les journaux de transactions disponibles (sans oublier de supprimer l’extension .partial du dernier journal).

Il faut mettre en place un script de démarrage pour que pg_receivewal soit redémarré en cas de redémarrage du serveur.

La méthode archiver est la méthode la plus simple à mettre en place. Elle se lance au lancement du serveur PostgreSQL, donc il n’est pas nécessaire de créer et installer un script de démarrage. Cependant, un journal de transactions n’est archivé que quand PostgreSQL l’ordonne, soit parce qu’il a rempli le journal en question, soit parce qu’un utilisateur a forcé un changement de journal (avec la fonction pg_switch_wal ou suite à un pg_backup_stop), soit parce que le délai maximum entre deux archivages a été dépassé (paramètre archive_timeout). Il est donc possible de perdre un grand nombre de transactions (même si cela reste bien inférieur à la perte qu’une restauration d’une sauvegarde logique occasionnerait).

La méthode pg_receivewal est plus complexe à mettre en place. Il faut exécuter ce démon, généralement sur un autre serveur. Un script de démarrage doit donc être configuré. Par contre, elle a le gros avantage de ne perdre pratiquement aucune transaction, surtout en mode synchrone. Les enregistrements de transactions sont envoyés en temps réel à pg_receivewal. Ce dernier les place dans un fichier de suffixe .partial, qui est ensuite renommé pour devenir un journal de transactions complet.

Une fois l’archivage en place, une sauvegarde à chaud a lieu en trois temps :

• l’appel à la fonction de démarrage ;
• la copie elle-même par divers outils externes (PostgreSQL ne s’en occupe pas) ;
• l’appel à la fonction d’arrêt.

La fonction de démarrage s’appelle pg_backup_start() à partir de la version 15 mais avait pour nom pg_start_backup() auparavant. De la même façon, la fonction d’arrêt s’appelle pg_backup_stop() à partir de la version 15, mais pg_stop_backup() avant.

Tout ce qui suit ne concerne plus que la sauvegarde concurrente.

La sauvegarde concurrente, apparue avec PostgreSQL 9.6, peut être lancée plusieurs fois en parallèle. C’est utile pour créer des secondaires alors qu’une sauvegarde physique tourne, par exemple. Elle est nettement plus complexe à gérer par script. Elle peut être exécutée depuis un serveur secondaire, ce qui allège la charge sur le primaire.

Pendant la sauvegarde, l’utilisateur ne verra aucune différence (à part peut-être la conséquence d’I/O saturées pendant la copie). Aucun verrou n’est posé. Lectures, écritures, suppression et création de tables, archivage de journaux et autres opérations continuent comme si de rien n’était.

Les sauvegardes manuelles servent cependant encore quand on veut utiliser une sauvegarde par snapshot, ou avec rsync (car pg_basebackup ne sait pas synchroniser vers une sauvegarde interrompue ou ancienne), et quand les outils conseillés ne sont pas utilisables ou disponibles sur le système.

L’exécution de pg_backup_start() peut se faire depuis n’importe quelle base de données de l’instance.

(Rappelons que pour les versions avant la 15, la fonction s’appelle pg_start_backup(). Pour effectuer une sauvegarde non-exclusive avec ces versions, il faudra positionner un troisième paramètre à false.)

Le label (le texte en premier argument) n’a aucune importance pour PostgreSQL (il ne sert qu’à l’administrateur, pour reconnaître le backup).

Le deuxième argument est un booléen qui permet de demander un checkpoint immédiat, si l’on est pressé et si un pic d’I/O n’est pas gênant. Sinon il faudra attendre souvent plusieurs minutes (selon la configuration du déclenchement du prochain checkpoint, dépendant des paramètres checkpoint_timeout et max_wal_size et de la rapidité d’écriture imposée par checkpoint_completion_target).

La session qui exécute la commande pg_backup_start() doit être la même que celle qui exécutera plus tard pg_backup_stop(). Nous verrons que cette dernière fonction fournira de quoi créer deux fichiers, qui devront être nommés backup_label et tablespace_map. Si la connexion est interrompue avant pg_backup_stop(), alors la sauvegarde doit être considérée comme invalide.

En plus de rester connectés à la base, les scripts qui gèrent la sauvegarde concurrente doivent donc récupérer et conserver les informations renvoyées par la commande de fin de sauvegarde.

La sauvegarde PITR est donc devenue plus complexe au fil des versions, et il est donc recommandé d’utiliser plutôt pg_basebackup ou des outils la supportant (barman, pgBackRest…).

La deuxième étape correspond à la sauvegarde des fichiers. Le choix de l’outil dépend de l’administrateur. Cela n’a aucune incidence au niveau de PostgreSQL.

La sauvegarde doit comprendre aussi les tablespaces si l’instance en dispose.

Snapshot :

Il est possible d’effectuer cette étape de copie des fichiers par snapshot au niveau de la baie, de l’hyperviseur, ou encore de l’OS (LVM, ZFS…).

Copie manuelle :

Sur les plateformes Microsoft Windows, peu d’outils sont capables de copier des fichiers en cours de modification. Assurez-vous d’en utiliser un possédant cette fonctionnalité (il existe différents émulateurs des outils GNU sous Windows). Le plus sûr et simple est sans doute de renoncer à une copie manuelle des fichiers et d’utiliser pg_basebackup.

Exclusions :

Des fichiers et répertoires sont à ignorer, pour gagner du temps ou faciliter la restauration. Voici la liste exhaustive (disponible aussi dans la documentation officielle) :

• postmaster.pid, postmaster.opts, pg_internal.init ;
• les fichiers de données des tables non journalisées (unlogged) ;
• pg_wal, ainsi que les sous-répertoires (mais à archiver séparément !) ;
• pg_replslot : les slots de réplication seront au mieux périmés, au pire gênants sur l’instance restaurée ;
• pg_dynshmem, pg_notify, pg_serial, pg_snapshots, pg_stat_tmp et pg_subtrans ne doivent pas être copiés (ils contiennent des informations propres à l’instance, ou qui ne survivent pas à un redémarrage) ;
• les fichiers et répertoires commençant par pgsql_tmp (fichiers temporaires) ;
• les fichiers autres que les fichiers et les répertoires standards (donc pas les liens symboliques).

On n’oubliera pas les fichiers de configuration s’ils ne sont pas dans le PGDATA.

La dernière étape correspond à l’exécution de la procédure stockée SELECT * FROM pg_backup_stop().

PostgreSQL va alors :

• marquer cette fin de backup dans le journal des transactions (étape capitale pour la restauration) ;
• forcer la finalisation du journal de transactions courant et donc son archivage, afin que la sauvegarde (fichiers + archives) soit utilisable même en cas de crash juste l’appel à la fonction : pg_backup_stop() ne rendra pas la main (par défaut) tant que ce dernier journal n’aura pas été archivé avec succès.

La fonction renvoie :

• le lsn de fin de backup ;
• un champ destiné au fichier backup_label ;
• un champ destiné au fichier tablespace_map.

# SELECT * FROM pg_stop_backup() \gx

NOTICE:  all required WAL segments have been archived
-[ RECORD 1 ]---------------------------------------------------------------
lsn        | 22/2FE5C788
labelfile  | START WAL LOCATION: 22/2B000028 (file 00000001000000220000002B)+
           | CHECKPOINT LOCATION: 22/2B000060                               +
           | BACKUP METHOD: streamed                                        +
           | BACKUP FROM: master                                            +
           | START TIME: 2019-12-16 13:53:41 CET                            +
           | LABEL: rr                                                      +
           | START TIMELINE: 1                                              +
           |
spcmapfile | 134141 /tbl/froid                                              +
           | 134152 /tbl/quota                                              +
           |

Ces informations se retrouvent aussi dans un fichier .backup mêlé aux journaux :

# cat /var/lib/postgresql/12/main/pg_wal/00000001000000220000002B.00000028.backup

START WAL LOCATION: 22/2B000028 (file 00000001000000220000002B)
STOP WAL LOCATION: 22/2FE5C788 (file 00000001000000220000002F)
CHECKPOINT LOCATION: 22/2B000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-12-16 13:53:41 CET
LABEL: rr
START TIMELINE: 1
STOP TIME: 2019-12-16 13:54:04 CET
STOP TIMELINE: 1

Il faudra créer le fichier tablespace_map avec le contenu du champ spcmapfile :

134141 /tbl/froid
134152 /tbl/quota

… ce qui n’est pas trivial à scripter.

Ces deux fichiers devront être placés dans la sauvegarde, pour être présent d’entrée dans le PGDATA du serveur restauré.

À partir du moment où pg_backup_stop() rend la main, il est possible de restaurer la sauvegarde obtenue puis de rejouer les journaux de transactions suivants en cas de besoin, sur un autre serveur ou sur ce même serveur.

Tous les journaux archivés avant celui précisé par le champ START WAL LOCATION dans le fichier backup_label ne sont plus nécessaires pour la récupération de la sauvegarde du système de fichiers et peuvent donc être supprimés. Attention, il y a plusieurs compteurs hexadécimaux différents dans le nom du fichier journal, qui ne sont pas incrémentés de gauche à droite.

pg_basebackup a été décrit plus haut. Il a l’avantage d’être simple à utiliser, de savoir quels fichiers ne pas copier, de fiabiliser la sauvegarde par un slot de réplication. Il ne réclame en général pas de configuration supplémentaire.

Si l’archivage est déjà en place, copier les journaux est inutile (--wal-method=none). Nous verrons plus tard comment lui indiquer où les chercher.

L’inconvénient principal de pg_basebackup reste son incapacité à reprendre une sauvegarde interrompue ou à opérer une sauvegarde différentielle ou incrémentale.

La fréquence dépend des besoins. Une sauvegarde par jour est le plus commun, mais il est possible d’espacer encore plus la fréquence.

Cependant, il faut conserver à l’esprit que plus la sauvegarde est ancienne, plus la restauration sera longue car un plus grand nombre de journaux seront à rejouer.

La version 13 permet de suivre la progression de la sauvegarde de base, quelque soit l’outil utilisé à condition qu’il passe par le protocole de réplication.

Cela permet ainsi de savoir à quelle phase la sauvegarde se trouve, quelle volumétrie a été envoyée, celle à envoyer, etc.

La restauration se déroule en trois voire quatre étapes suivant qu’elle est effectuée sur le même serveur ou sur un autre serveur.

Dans le cas où la restauration a lieu sur le même serveur, quelques étapes préliminaires sont à effectuer.

Il faut arrêter PostgreSQL s’il n’est pas arrêté. Cela arrivera quand la restauration a pour but, par exemple, de récupérer des données qui ont été supprimées par erreur.

Ensuite, il faut supprimer (ou archiver) l’ancien répertoire des données pour pouvoir y placer la sauvegarde des fichiers. Écraser l’ancien répertoire n’est pas suffisant, il faut le supprimer, ainsi que les répertoires des tablespaces au cas où l’instance en possède.

La sauvegarde des fichiers peut enfin être restaurée. Il faut bien porter attention à ce que les fichiers soient restaurés au même emplacement, tablespaces compris.

Une fois cette étape effectuée, il peut être intéressant de faire un peu de ménage. Par exemple, le fichier postmaster.pid peut poser un problème au démarrage. Conserver les journaux applicatifs n’est pas en soi un problème mais peut porter à confusion. Il est donc préférable de les supprimer. Quant aux journaux de transactions compris dans la sauvegarde, bien que ceux en provenance des archives seront utilisés même s’ils sont présents aux deux emplacements, il est préférable de les supprimer. La commande sera similaire à celle-ci :

$ rm postmaster.pid log/* pg_wal/[0-9A-F]*

Enfin, s’il est possible d’accéder au journal de transactions courant au moment de l’arrêt de l’ancienne instance, il est intéressant de le restaurer dans le répertoire pg_wal fraîchement nettoyé. Ce dernier sera pris en compte en toute fin de restauration des journaux depuis les archives et permettra donc de restaurer les toutes dernières transactions validées sur l’ancienne instance, mais pas encore archivées.

Quand PostgreSQL démarre après avoir subi un arrêt brutal, il ne restaure que les journaux en place dans pg_wal, puis il s’ouvre en écriture. Pour une restauration, il faut lui indiquer qu’il doit aller demander les journaux quelque part, et les rejouer tous jusqu’à épuisement, avant de s’ouvrir.

Pour cela, il suffit de créer un fichier vide recovery.signal dans le répertoire des données.

Pour la récupération des journaux, le paramètre essentiel est restore_command. Il contient une commande symétrique des paramètres archive_command (ou archive_library) pour l’archivage. Il s’agit d’une commande copiant un journal dans le pg_wal. Cette commande est souvent fournie par l’outil de sauvegarde PITR s’il y en a un. Si nous poursuivons notre exemple, ce paramètre pourrait être :

restore_command = 'cp /mnt/nfs1/archivage/%f %p'

Cette commande sera appelée après la restauration de chaque journal pour récupérer le suivant, qui sera restauré et ainsi de suite. Il n’y a aucune paralléllisation prévue, mais des outils de sauvegarde PITR peuvent le faire au sein de la commande.

Si le but est de restaurer tous les journaux archivés, il n’est pas nécessaire d’aller plus loin dans la configuration. La restauration se poursuivra jusqu’à ce que restore_command tombe en erreur, ce qui signifie l’épuisement de tous les journaux disponibles, et la fin de la restauration.

Si l’on ne veut pas simplement restaurer tous les journaux, par exemple pour s’arrêter avant une fausse manipulation désastreuse, plusieurs paramètres permettent de préciser le point d’arrêt :

• jusqu’à un certain nom, grâce au paramètre recovery_target_name (le nom correspond à un label enregistré précédemment dans les journaux de transactions grâce à la fonction pg_create_restore_point()) ;
• jusqu’à une certaine heure, grâce au paramètre recovery_target_time ;
• jusqu’à un certain identifiant de transaction, grâce au paramètre recovery_target_xid, numéro de transaction qu’il est possible de chercher dans les journaux eux-mêmes grâce à l’utilitaire pg_waldump ;
• jusqu’à un certain LSN (Log Sequence Number), grâce au paramètre recovery_target_lsn, que là aussi on doit aller chercher dans les journaux eux-mêmes.

Avec le paramètre recovery_target_inclusive (par défaut à true), il est possible de préciser si la restauration se fait en incluant les transactions au nom, à l’heure ou à l’identifiant de transaction demandé, ou en les excluant.

Dans les cas complexes, nous verrons plus tard que choisir la timeline peut être utile (avec recovery_target_timeline, en général à latest).

Il est possible de demander à la restauration de s’arrêter une fois arrivée au stade voulu avec :

recovery_target_action = pause

C’est même l’action par défaut si une des options d’arrêt ci-dessus a été choisie : cela permet à l’utilisateur de vérifier que le serveur est bien arrivé au point qu’il désirait. Les alternatives sont promote et shutdown.

Si la cible est atteinte mais que l’on décide de continuer la restauration jusqu’à un autre point (évidemment postérieur), il faut modifier la cible de restauration dans le fichier de configuration, et redémarrer PostgreSQL. C’est le seul moyen de rejouer d’autres journaux sans ouvrir l’instance en écriture.

Si l’on est arrivé au point de restauration voulu, un message de ce genre apparaît :

LOG:  recovery stopping before commit of transaction 8693270, time 2021-09-02 11:46:35.394345+02
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

(Le terme promote pour une restauration est un peu abusif.) pg_wal_replay_resume() — malgré ce que pourrait laisser croire son nom ! — provoque ici l’arrêt immédiat de la restauration, donc ignore les opérations contenues dans les WALs que l’on n’a pas souhaités restaurer, puis le serveur s’ouvre en écriture sur une nouvelle timeline.

La documentation officielle complète sur le sujet est sur le site du projet.

La dernière étape est particulièrement simple. Il suffit de démarrer PostgreSQL. PostgreSQL va comprendre qu’il doit rejouer les journaux de transactions.

Les éventuels journaux présents sont rejoués, puis restore_command est appelé pour fournir d’autres journaux, jusqu’à ce que la commande ne trouve plus rien dans les archives.

Les journaux doivent se dérouler au moins jusqu’à rencontrer le « point de cohérence », c’est-à-dire la mention insérée par pg_backup_stop(). Avant ce point, il n’est pas possible de savoir si les fichiers issus du base backup sont à jour ou pas, et il est impossible de démarrer l’instance. Le message apparaît dans les traces et, dans le doute, on doit vérifier sa présence :

2020-01-17 16:08:37.285 UTC [15221] LOG: restored log file "000000010000000100000031"…
2020-01-17 16:08:37.789 UTC [15221] LOG: restored log file "000000010000000100000032"…
2020-01-17 16:08:37.949 UTC [15221] LOG: consistent recovery state reached
                                         at 1/32BFDD88
2020-01-17 16:08:37.949 UTC [15217] LOG: database system is ready to accept
                                         read only connections
2020-01-17 16:08:38.009 UTC [15221] LOG: restored log file "000000010000000100000033"…

Si le message apparaît, le rejeu n’est pas terminé, mais on a au moins Au moment où ce message apparaît, le rejeu n’est pas terminé, mais il a atteint un stade où l’instance est cohérente et utilisable.

PostgreSQL continue ensuite jusqu’à arriver à la limite fixée, jusqu’à ce qu’il ne trouve plus de journal à rejouer (restore_command tombe en erreur), ou que le bloc de journal lu soit incohérent (ce qui indique qu’on est arrivé à la fin d’un journal qui n’a pas été terminé, le journal courant au moment du crash par exemple). PostgreSQL vérifie qu’il n’existe pas une timeline supérieure sur laquelle basculer (par exemple s’il s’agit de la deuxième restauration depuis la sauvegarde du PGDATA).

Puis il va s’ouvrir en écriture (sauf si vous avez demandé recovery_target_action = pause).

2020-01-17 16:08:45.938 UTC [15221] LOG: restored log file "00000001000000010000003C"
                                         from archive
2020-01-17 16:08:46.116 UTC [15221] LOG: restored log file "00000001000000010000003D"…
2020-01-17 16:08:46.547 UTC [15221] LOG: restored log file "00000001000000010000003E"…
2020-01-17 16:08:47.262 UTC [15221] LOG: restored log file "00000001000000010000003F"…
2020-01-17 16:08:47.842 UTC [15221] LOG: invalid record length at 1/3F0000A0:
                                         wanted 24, got 0
2020-01-17 16:08:47.842 UTC [15221] LOG: redo done at 1/3F000028
2020-01-17 16:08:47.842 UTC [15221] LOG: last completed transaction was
                                         at log time 2020-01-17 14:59:30.093491+00
2020-01-17 16:08:47.860 UTC [15221] LOG: restored log file "00000001000000010000003F"…
cp: cannot stat ‘/opt/pgsql/archives/00000002.history’: No such file or directory
2020-01-17 16:08:47.966 UTC [15221] LOG:  selected new timeline ID: 2
2020-01-17 16:08:48.179 UTC [15221] LOG:  archive recovery complete
cp: cannot stat ‘/opt/pgsql/archives/00000001.history’: No such file or directory
2020-01-17 16:08:51.613 UTC [15217] LOG:  database system is ready
                                          to accept connections

Le fichier recovery.signal est effacé pour ne pas poser problème en cas de crash immédiat. (Ne l’effacez jamais manuellement !)

Le fichier backup_label d’une sauvegarde exclusive est renommé en backup_label.old.

La durée de la restauration est fortement dépendante du nombre de journaux. Ils sont rejoués séquentiellement. Mais avant cela, un fichier journal peut devoir être récupéré, décompressé, et restauré dans pg_wal.

Il est donc préférable qu’il n’y ait pas trop de journaux à rejouer, et donc qu’il n’y ait pas trop d’espaces entre sauvegardes complètes successives.

La version 15 a optimisé le rejeu en permettant l’activation du prefetch des blocs de données lors du rejeu des journaux.

Un outil comme pgBackRest en mode asynchrone permet de paralléliser la récupération des journaux, ce qui permet de les récupérer via le réseau et de les décompresser par avance pendant que PostgreSQL traite les journaux précédents.

Lorsque le mode recovery s’arrête, au point dans le temps demandé ou faute d’archives disponibles, l’instance accepte les écritures. De nouvelles transactions se produisent alors sur les différentes bases de données de l’instance. Dans ce cas, l’historique des données prend un chemin différent par rapport aux archives de journaux de transactions produites avant la restauration. Par exemple, dans ce nouvel historique, il n’y a pas le DROP TABLE malencontreux qui a imposé de restaurer les données. Cependant, cette transaction existe bien dans les archives des journaux de transactions.

On a alors plusieurs historiques des transactions, avec des « bifurcations » aux moments où on a réalisé des restaurations. PostgreSQL permet de garder ces historiques grâce à la notion de timeline. Une timeline est donc l’un de ces historiques. Elle est identifiée par un numéro et se matérialise par un ensemble de journaux de transactions. Le numéro de la timeline est le premier nombre hexadécimal du nom des segments de journaux de transactions, en 8ᵉ position (le second est le numéro du journal, et le troisième, à la fin, le numéro du segment). Ainsi, lorsqu’une instance s’ouvre après une restauration PITR, elle peut archiver immédiatement ses journaux de transactions au même endroit, les fichiers ne seront pas écrasés vu qu’ils seront nommés différemment. Par exemple, après une restauration PITR s’arrêtant à un point situé dans le segment 000000010000000000000009 :

$ ls -1 /backup/postgresql/archived_wal/
000000010000000000000007
000000010000000000000008
000000010000000000000009
00000001000000000000000A
00000001000000000000000B
00000001000000000000000C
00000001000000000000000D
00000001000000000000000E
00000001000000000000000F
000000010000000000000010
000000010000000000000011
000000020000000000000009
00000002000000000000000A
00000002000000000000000B
00000002000000000000000C
00000002.history

Noter les timelines 1 et 2 en 8ᵉ position des noms des fichiers. Il y a deux fichiers finissant par 09 : le premier 000000010000000000000009 contient des informations communes aux deux timelines mais sa fin ne figure pas dans la timeline 2. Les fichiers 00000001000000000000000A à 000000010000000000000011 contiennent des informations qui ne figurent que dans la timeline 1. Les fichiers 00000002000000000000000Ajusque 00000002000000000000000C sont uniquement dans la timeline 2. Le fichier 00000002.history contient l’information sur la transition entre les deux timelines.

Ce fichier sert pendant le recovery, quand l’instance doit choisir les timelines à suivre et les fichiers à restaurer. Les timelines connues avec leur point de départ sont suivies grâce aux fichiers d’historique, nommés d’après le numéro hexadécimal sur huit caractères de la timeline et le suffixe .history, et archivés avec les journaux. En partant de la timeline qu’elle quitte, l’instance restaure les fichiers historiques des timelines suivantes pour choisir la première disponible. Une fois la restauration finie, avant de s’ouvrir en écriture, l’instance archive un nouveau fichier .history pour la nouvelle timeline sélectionnée. Il contient l’adresse du point de départ dans la timeline qu’elle quitte, c’est-à-dire le point de bifurcation entre la 1 et la 2 :

$ cat 00000002.history
1   0/9765A80   before 2015-10-20 16:59:30.103317+02

Puis l’instance continue normalement, et archive ses journaux commençant par 00000002.

Après une seconde restauration, repartant de la timeline 2, l’instance choisit la timeline 3 et écrit un nouveau fichier :

$ cat 00000003.history
1   0/9765A80   before 2015-10-20 16:59:30.103317+02
2   0/105AF7D0  before 2015-10-22 10:25:56.614316+02

Ce fichier reprend les timelines précédemment suivies par l’instance. En effet, l’enchaînement peut être complexe s’il y a eu plusieurs retours en arrière ou restauration.

À la restauration, on peut choisir la timeline cible en configurant le paramètre recovery_target_timeline. Il vaut par défaut latest, et la restauration suit donc les changements de timeline depuis le moment de la sauvegarde.

Pour choisir une autre timeline que la dernière, il faut donner le numéro de la timeline cible comme valeur du paramètre recovery_target_timeline. Les timelines permettent ainsi d’effectuer plusieurs restaurations successives à partir du même base backup, et d’archiver au même endroit sans mélanger les journaux.

Bien sûr, pour restaurer dans une timeline précise, il faut que le fichier .history correspondant soit encore présent dans les archives, sous peine d’erreur.

Le changement de timeline ne se produit que lors d’une restauration explicite, et pas en cas de recovery après crash, notamment. (Cela arrive aussi quand un serveur secondaire est promu : il crée une nouvelle timeline.)

Il y a quelques pièges :

Ce schéma illustre ce processus de plusieurs restaurations successives, et la création de différentes timelines qui en résulte.

On observe ici les éléments suivants avant la première restauration :

• la fin de la dernière sauvegarde se situe en haut à gauche sur l’axe des transactions, à la transaction x12 ;
• cette sauvegarde a été effectuée alors que l’instance était en activité sur la timeline 1.

On décide d’arrêter l’instance alors qu’elle est arrivée à la transaction x47, par exemple parce qu’une nouvelle livraison de l’application a introduit un bug qui provoque des pertes de données. L’objectif est de restaurer l’instance avant l’apparition du problème afin de récupérer les données dans un état cohérent, et de relancer la production à partir de cet état. Pour cela, on restaure les fichiers de l’instance à partir de la dernière sauvegarde, puis on modifie le fichier de configuration pour que l’instance, lors de sa phase de recovery :

• restaure les WAL archivés jusqu’à l’état de cohérence (transaction x12) ;
• restaure les WAL archivés jusqu’au point précédant immédiatement l’apparition du bug applicatif (transaction x42).

On démarre ensuite l’instance et on l’ouvre en écriture, on constate alors que celle-ci bascule sur la timeline 2, la bifurcation s’effectuant à la transaction x42. L’instance étant de nouveau ouverte en écriture, elle va générer de nouveaux WAL, qui seront associés à la nouvelle timeline : ils n’écrasent pas les fichiers WAL archivés de la timeline 1, ce qui permet de les réutiliser pour une autre restauration en cas de besoin (par exemple si la transaction x42 utilisée comme point d’arrêt était trop loin dans le passé, et que l’on désire restaurer de nouveau jusqu’à un point plus récent).

Un peu plus tard, on a de nouveau besoin d’effectuer une restauration dans le passé - par exemple, une nouvelle livraison applicative a été effectuée, mais le bug rencontré précédemment n’était toujours pas corrigé. On restaure donc de nouveau les fichiers de l’instance à partir de la même sauvegarde, puis on configure PostgreSQL pour suivre la timeline 2 (paramètre recovery_target_timeline = 2) jusqu’à la transaction x55. Lors du recovery, l’instance va :

• restaurer les WAL archivés jusqu’à l’état de cohérence (transaction x12) ;
• restaurer les WAL archivés jusqu’au point de la bifurcation (transaction x42) ;
• suivre la timeline indiquée (2) et rejouer les WAL archivés jusqu’au point précédant immédiatement l’apparition du bug applicatif (transaction x55).

On démarre ensuite l’instance et on l’ouvre en écriture, on constate alors que celle-ci bascule sur la timeline 3, la bifurcation s’effectuant cette fois à la transaction x55.

Enfin, on se rend compte qu’un problème bien plus ancien et subtil a été introduit précédemment aux deux restaurations effectuées. On décide alors de restaurer l’instance jusqu’à un point dans le temps situé bien avant, jusqu’à la transaction x20. On restaure donc de nouveau les fichiers de l’instance à partir de la même sauvegarde, et on configure le serveur pour restaurer jusqu’à la transaction x20. Lors du recovery, l’instance va :

• restaurer les WAL archivés jusqu’à l’état de cohérence (transaction x12) ;
• restaurer les WAL archivés jusqu’au point précédant immédiatement l’ apparition du bug applicatif (transaction x20).

Comme la création des deux timelines précédentes est archivée dans les fichiers history, l’ouverture de l’instance en écriture va basculer sur une nouvelle timeline (4). Suite à cette restauration, toutes les modifications de données provoquées par des transactions effectuées sur la timeline 1 après la transaction x20, ainsi que celles effectuées sur les timelines 2 et 3, ne sont donc pas présentes dans l’instance.

Une fois le nouveau primaire en place, la production peut reprendre, mais il faut vérifier que la sauvegarde PITR est elle aussi fonctionnelle.

Ce nouveau primaire a généralement commencé à archiver ses journaux à partir du dernier journal récupéré de l’ancien primaire, renommé avec l’extension .partial, juste avant la bascule sur la nouvelle timeline. Il faut bien sûr vérifier que l’archivage des nouveaux journaux fonctionne.

Sur l’ancien primaire, les derniers journaux générés juste avant l’incident n’ont pas forcément été archivés. Ceux-ci possèdent un fichier témoin .ready dans pg_wal/archive_status. Même s’ils ont été copiés manuellement vers le nouveau primaire avant sa promotion, celui-ci ne les a pas archivés.

Il est possible de forcer l’archivage des fichiers .ready depuis l’ancien primaire, avant la bascule, en exécutant à la main les archive_command que PostgreSQL aurait générées, mais la facilité pour le faire dépend de l’outil. La copie de journaux à la main est donc risquée.

De plus, s’il y a eu plusieurs restaurations successives, qui ont provoqué quelques archivages et des apparitions de timelines dans le même dépôt d’archives, avant d’être abandonnées, il faut faire attention au paramètre recovery_target_timeline (latest ne convient plus), ce qui complique une future restauration.

Quant aux éventuelles instances secondaires, il est vraisemblable qu’elles doivent être reconstruites suite à la restauration de l’instance primaire. (Si elles ont appliqué des journaux qui ont été perdus et n’ont pas été repris par le primaire restauré, ces secondaires ne pourront se raccrocher. Consulter les traces.)

L’un des problèmes de la sauvegarde PITR est la place prise sur disque par les journaux de transactions. Si un journal de 16 Mo (par défaut) est généré toutes les minutes, le total est de 23 Go de journaux par jour, et parfois beaucoup plus. Il n’est pas forcément possible de conserver autant de journaux.

Un premier moyen est de reduire la volumétrie à la source en espaçant les checkpoints. Le graphique ci-dessus représente la volumétrie générée par un simple test avec pgbench (OLTP classique donc) avec checkpoint_timeout variant entre 1 et 30 minutes : les écarts sont énormes.

La raison est que, pour des raisons de fiabilité, un bloc modifié est intégralement écrit (8 ko) dans les journaux à sa première modification après un checkpoint. Par la suite, seules les modifications de ce bloc, souvent beaucoup plus petites, sont journalisées. (Ce comportement dépend du paramètre full_page_writes, activé par défaut et qu’il est impératif de laisser tel quel, sauf peut-être sur ZFS.)

Espacer les checkpoints permet d’économiser des écritures de blocs complets, si l’activité s’y prête (en OLTP surtout). Il y a un intérêt en performances, mais surtout en place disque économisée quand les journaux sont archivés, aussi accessoirement en CPU s’ils sont compressés, et en trafic réseau s’ils sont répliqués.

Par cohérence, si l’on monte checkpoint_timeout, il faut penser à augmenter aussi max_wal_size, et vice-versa. Des valeurs courantes sont respectivement 15 minutes, parfois plus, et plusieurs gigaoctets.

Il y a cependant un inconvénient : un écart plus grand entre checkpoints peut allonger la restauration après un arrêt brutal, car il y aura plus de journaux à rejouer, parfois des centaines ou des milliers.

PostgreSQL peut compresser les journaux à la source, si le paramètre wal_compression (désactivé par défaut) est passé à on. La compression est opérée par PostgreSQL au niveau de la page, avec un coût en CPU à l’écriture des journaux, très minime, et un gros gain en volumétrie (souvent plus de 50 % !). Comme il y a moins de journaux, leur rejeu est aussi plus rapide, ce qui accélère la réplication et la reprise après un crash. Le prix est une augmentation de la consommation en CPU. Les détails et un exemple figurent dans ce billet du blog Dalibo.

Une autre solution est la compression à la volée des journaux archivés dans l’archive_command. Les outils classiques comme gzip, bzip2, lzma, xz, etc. conviennent. Tous les outils PITR incluent plusieurs de ces algorithmes. Un fichier de 16 Mo aura généralement une taille compressée comprise entre 3 et 6 Mo.

Il n’est pas conseillé de réinventer la roue et d’écrire soi-même des scripts de sauvegarde, qui doivent prévoir de nombreux cas et bien gérer les erreurs. La sauvegarde concurrente est également difficile à manier. Des outils reconnus existent, dont nous évoquerons brièvement les plus connus. Il en existe d’autres. Ils ne font pas partie du projet PostgreSQL à proprement parler et doivent être installés séparément.

Les outils décrits succinctement plus bas fournissent :

• un outil pour procéder aux sauvegardes, gérer la péremption des archives… ;
• un outil qui sera appelé par archive_command.

Leur philosophie peut différer, notamment en terme de centralisation ou de compromis entre simplicité et fonctionnalités. Ces dernières s’enrichissent d’ailleurs au fil du temps.

pgBackRest est un outil de gestion de sauvegardes PITR écrit en perl et en C, par David Steele de Crunchy Data.

Il met l’accent sur les performances avec de gros volumes et les fonctionnalités, au prix d’une complexité à la configuration :

• un protocole dédié pour le transfert et la compression des données ;
• des opérations parallélisables en multithread ;
• la possibilité de réaliser des sauvegardes complètes, différentielles et incrémentielles ;
• la possibilité d’archiver ou restaurer les WAL de façon asynchrone, et donc plus rapide ;
• la possibilité d’abandonner l’archivage en cas d’accumulation et de risque de saturation de pg_wal ;
• la gestion de dépôts de sauvegarde multiples (pour sécuriser, ou avoir plusieurs niveaux d’archives) ;
• le support intégré de dépôts S3 ou Azure ;
• le support d’un accès TLS géré par pgBackRest en alternative à SSH ;
• la sauvegarde depuis un serveur secondaire ;
• le chiffrement des sauvegardes ;
• la restauration en mode delta, très pratique pour restaurer un serveur qui a décroché mais n’a que peu divergé ;
• la reprise d’une sauvegarde échouée.

pgBackRest n’utilise pas pg_receivewal pour garantir la sauvegarde du dernier journal (non terminé) avant un sinistre. Les auteurs considèrent que dans ce cas un secondaire synchrone est plus adapté et plus fiable.

Le projet est très actif et considéré comme fiable, et les fonctionnalités proposées sont intéressantes.

Pour la supervision de l’outil, une sonde Nagios est fournie par un des développeurs : check_pgbackrest.

barman est un outil créé par 2ndQuadrant (racheté depuis par EDB). Il a pour but de faciliter la mise en place de sauvegardes PITR. Il gère à la fois la sauvegarde et la restauration.

La commande barman dispose de plusieurs actions :

• list-server, pour connaître la liste des serveurs configurés ;
• backup, pour lancer une sauvegarde de base ;
• list-backup, pour connaître la liste des sauvegardes de base ;
• show-backup, pour afficher des informations sur une sauvegarde ;
• delete, pour supprimer une sauvegarde ;
• recover, pour restaurer une sauvegarde (la restauration peut se faire à distance).

Contrairement aux autre outils présentés ici, barman permet d’utiliser pg_receivewal.

Il supporte aussi les dépôts S3 ou blob Azure.

Site web de barman

pitrery a été créé par la société Dalibo. Il mettait l’accent sur la simplicité de sauvegarde et la restauration de la base.

Site Web de pitrery.

Cette méthode de sauvegarde est la seule utilisable dès que les besoins de performance de sauvegarde et de restauration augmentent (Recovery Time Objective ou RTO), ou que le volume de perte de données doit être drastiquement réduit (Recovery Point Objective ou RPO).

L’installation est détaillée ici pour Rocky Linux 8 et 9 (similaire à Red Hat et à d’autres variantes comem Oracle Linux et Fedora), et Debian/Ubuntu.

Elle ne dure que quelques minutes.

Sur Rocky Linux 8 ou 9

Installation du dépôt communautaire  :

Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent sont inspirées de celles générées par l’assistant sur https://www.postgresql.org/download/linux/redhat/, en précisant :

• la version majeure de PostgreSQL (ici la 16) ;
• la distribution (ici Rocky Linux 8) ;
• l’architecture (ici x86_64, la plus courante).

Les commandes sont à lancer sous root :

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms\
/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# dnf -qy module disable postgresql

Installation de PostgreSQL 16 (client, serveur, librairies, extensions) : 

# dnf install -y postgresql16-server postgresql16-contrib

Les outils clients et les librairies nécessaires seront automatiquement installés.

Une fonctionnalité avancée optionnelle, le JIT (Just In Time compilation), nécessite un paquet séparé.

# dnf install postgresql16-llvmjit

Création d’une première instance :

Il est conseillé de déclarer PG_SETUP_INITDB_OPTIONS, notamment pour mettre en place les sommes de contrôle et forcer les traces en anglais :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-16/bin/postgresql-16-setup initdb
# cat /var/lib/pgsql/16/initdb.log

Ce dernier fichier permet de vérifier que tout s’est bien passé et doit finir par :

Success. You can now start the database server using:

    /usr/pgsql-16/bin/pg_ctl -D /var/lib/pgsql/16/data/ -l logfile start

Chemins :

Configuration :

Modifier postgresql.conf est facultatif pour un premier lancement.

Commandes d’administration habituelles :

Démarrage, arrêt, statut, rechargement à chaud de la configuration, redémarrage :

# systemctl start postgresql-16
# systemctl stop postgresql-16
# systemctl status postgresql-16
# systemctl reload postgresql-16
# systemctl restart postgresql-16

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Démarrage de l’instance au lancement du système d’exploitation :

# systemctl enable postgresql-16

Ouverture du firewall pour le port 5432 :

Voir si le firewall est actif :

# systemctl status firewalld

Si c’est le cas, autoriser un accès extérieur :

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all

(Rappelons que listen_addresses doit être également modifié dans postgresql.conf.)

Création d’autres instances :

Si des instances de versions majeures différentes doivent être installées, il faut d’abord installer les binaires pour chacune (adapter le numéro dans dnf install …) et appeler le script d’installation de chaque version. l’instance par défaut de chaque version vivra dans un sous-répertoire numéroté de /var/lib/pgsql automatiquement créé à l’installation. Il faudra juste modifier les ports dans les postgresql.conf pour que les instances puissent tourner simultanément.

Si plusieurs instances d’une même version majeure (forcément de la même version mineure) doivent cohabiter sur le même serveur, il faut les installer dans des PGDATA différents.

• Ne pas utiliser de tiret dans le nom d’une instance (problèmes potentiels avec systemd).
• Respecter les normes et conventions de l’OS : placer les instances dans un nouveau sous-répertoire de /var/lib/pgsqsl/16/ (ou l’équivalent pour d’autres versions majeures).

Pour créer une seconde instance, nommée par exemple infocentre :

• Création du fichier service de la deuxième instance :

# cp /lib/systemd/system/postgresql-16.service \
        /etc/systemd/system/postgresql-16-infocentre.service

• Modification de ce dernier fichier avec le nouveau chemin :

Environment=PGDATA=/var/lib/pgsql/16/infocentre

• Option 1 : création d’une nouvelle instance vierge :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-16/bin/postgresql-16-setup initdb postgresql-16-infocentre

• Option 2 : restauration d’une sauvegarde : la procédure dépend de votre outil.

• Adaptation de /var/lib/pgsql/16/infocentre/postgresql.conf (port surtout).

• Commandes de maintenance de cette instance :

# systemctl [start|stop|reload|status] postgresql-16-infocentre
# systemctl [enable|disable] postgresql-16-infocentre

• Ouvrir le nouveau port dans le firewall au besoin.

Sur Debian / Ubuntu

Sauf précision, tout est à effectuer en tant qu’utilisateur root.

Référence : https://apt.postgresql.org/

Installation du dépôt communautaire :

L’installation des dépôts du PGDG est prévue dans le paquet Debian :

# apt update
# apt install -y  gnupg2  postgresql-common 
# /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Ce dernier ordre créera le fichier du dépôt /etc/apt/sources.list.d/pgdg.list adapté à la distribution en place.

Installation de PostgreSQL 16 :

La méthode la plus propre consiste à modifier la configuration par défaut avant l’installation :

Dans /etc/postgresql-common/createcluster.conf, paramétrer au moins les sommes de contrôle et les traces en anglais :

initdb_options = '--data-checksums --lc-messages=C'

Puis installer les paquets serveur et clients et leurs dépendances :

# apt install postgresql-16 postgresql-client-16

La première instance est automatiquement créée, démarrée et déclarée comme service à lancer au démarrage du système. Elle porte un nom (par défaut main).

Elle est immédiatement accessible par l’utilisateur système postgres.

Chemins :

Configuration

Modifier postgresql.conf est facultatif pour un premier essai.

Démarrage/arrêt de l’instance, rechargement de configuration :

Debian fournit ses propres outils, qui demandent en paramètre la version et le nom de l’instance :

# pg_ctlcluster 16 main [start|stop|reload|status|restart]

Démarrage de l’instance avec le serveur :

C’est en place par défaut, et modifiable dans /etc/postgresql/16/main/start.conf.

Ouverture du firewall :

Debian et Ubuntu n’installent pas de firewall par défaut.

Statut des instances du serveur :

# pg_lsclusters

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Destruction d’une instance :

# pg_dropcluster 16 main

Création d’autres instances :

Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour mettre les checksums en place :

• optionnellement, /etc/postgresql-common/createcluster.conf permet de mettre en place tout d’entrée les checksums, les messages en anglais, le format des traces ou un emplacement séparé pour les journaux :

initdb_options = '--data-checksums --lc-messages=C'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal'

• créer une instance :

# pg_createcluster 16 infocentre

Il est également possible de préciser certains paramètres du fichier postgresql.conf, voire les chemins des fichiers (il est conseillé de conserver les chemins par défaut) :

# pg_createcluster 16 infocentre \
  --port=12345 \
  --datadir=/PGDATA/16/infocentre \
  --pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \
  --  --data-checksums --waldir=/ssd/postgresql/16/infocentre/journaux

• adapter au besoin /etc/postgresql/16/infocentre/postgresql.conf ;

• démarrage :

# pg_ctlcluster 16 infocentre start

Accès à l’instance depuis le serveur même (toutes distributions)

Par défaut, l’instance n’est accessible que par l’utilisateur système postgres, qui n’a pas de mot de passe. Un détour par sudo est nécessaire :

$ sudo -iu postgres psql
psql (16.0)
Type "help" for help.
postgres=#

Ce qui suit permet la connexion directement depuis un utilisateur du système :

Pour des tests (pas en production !), il suffit de passer à trust le type de la connexion en local dans le pg_hba.conf :

local   all             postgres                               trust

La connexion en tant qu’utilisateur postgres (ou tout autre) n’est alors plus sécurisée :

dalibo:~$ psql -U postgres
psql (16.0)
Type "help" for help.
postgres=#

Une authentification par mot de passe est plus sécurisée :

• dans pg_hba.conf, paramétrer une authentification par mot de passe pour les accès depuis localhost (déjà en place sous Debian) :

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

(Ne pas oublier de recharger la configuration en cas de modification.)

• ajouter un mot de passe à l’utilisateur postgres de l’instance :

dalibo:~$ sudo -iu postgres psql
psql (16.0)
Type "help" for help.
postgres=# \password
Enter new password for user "postgres":
Enter it again:
postgres=# quit

dalibo:~$ psql -h localhost -U postgres
Password for user postgres:
psql (16.0)
Type "help" for help.
postgres=#

• Pour se connecter sans taper le mot de passe à une instance, un fichier .pgpass dans le répertoire personnel doit contenir les informations sur cette connexion :

localhost:5432:*:postgres:motdepassetrèslong

Ce fichier doit être protégé des autres utilisateurs :

$ chmod 600 ~/.pgpass

• Pour n’avoir à taper que psql, on peut définir ces variables d’environnement dans la session voire dans ~/.bashrc :

export PGUSER=postgres
export PGDATABASE=postgres
export PGHOST=localhost

Rappels :

• en cas de problème, consulter les traces (dans /var/lib/pgsql/16/data/log ou /var/log/postgresql/) ;
• toute modification de pg_hba.conf ou postgresql.conf impliquant de recharger la configuration peut être réalisée par une de ces trois méthodes en fonction du système :

root:~# systemctl reload postgresql-16

root:~# pg_ctlcluster 16 main reload

postgres:~$ psql -c 'SELECT pg_reload_conf()'

pgbench est un outil de test livré avec PostgreSQL. Son but est de faciliter la mise en place de benchmarks simples et rapides. Par défaut, il installe une base assez simple, génère une activité plus ou moins intense et calcule le nombre de transactions par seconde et la latence. C’est ce qui sera fait ici dans cette introduction. On peut aussi lui fournir ses propres scripts.

La documentation complète est sur https://docs.postgresql.fr/current/pgbench.html. L’auteur principal, Fabien Coelho, a fait une présentation complète, en français, à la PG Session #9 de 2017.

Installation

L’outil est installé avec les paquets habituels de PostgreSQL, client ou serveur suivant la distribution.

Dans le cas des paquets RPM du PGDG, l’outil n’est pas dans le PATH par défaut ; il faudra donc fournir le chemin complet :

/usr/pgsql-16/bin/pgbench

Il est préférable de créer un rôle non privilégié dédié, qui possédera la base de données :

CREATE ROLE pgbench LOGIN PASSWORD 'unmotdepassebienc0mplexe';
CREATE DATABASE pgbench OWNER pgbench ;

Le pg_hba.conf doit éventuellement être adapté.

La base par défaut s’installe ainsi (indiquer la base de données en dernier ; ajouter -p et -h au besoin) :

pgbench -U pgbench --initialize --scale=100 pgbench

--scale permet de faire varier proportionnellement la taille de la base. À 100, la base pèsera 1,5 Go, avec 10 millions de lignes dans la table principale pgbench_accounts :

pgbench@pgbench=# \d+
                           Liste des relations
 Schéma |       Nom        | Type  | Propriétaire | Taille  | Description
--------+------------------+-------+--------------+---------+-------------
 public | pg_buffercache   | vue   | postgres     | 0 bytes |
 public | pgbench_accounts | table | pgbench      | 1281 MB |
 public | pgbench_branches | table | pgbench      | 40 kB   |
 public | pgbench_history  | table | pgbench      | 0 bytes |
 public | pgbench_tellers  | table | pgbench      | 80 kB   |

Générer de l’activité

Pour simuler une activité de 20 clients simultanés, répartis sur 4 processeurs, pendant 100 secondes :

pgbench -U pgbench -c 20 -j 4 -T100 pgbench

NB : ne pas utiliser -d pour indiquer la base, qui signifie --debug pour pgbench, qui noiera alors l’affichage avec ses requêtes :

 UPDATE pgbench_accounts SET abalance = abalance + -3455 WHERE aid = 3789437;
 SELECT abalance FROM pgbench_accounts WHERE aid = 3789437;
 UPDATE pgbench_tellers SET tbalance = tbalance + -3455 WHERE tid = 134;
 UPDATE pgbench_branches SET bbalance = bbalance + -3455 WHERE bid = 78;
 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
  VALUES (134, 78, 3789437, -3455, CURRENT_TIMESTAMP);

À la fin, s’affichent notamment le nombre de transactions (avec et sans le temps de connexion) et la durée moyenne d’exécution du point de vue du client (latency) :

 scaling factor: 100
 query mode: simple
 number of clients: 20
 number of threads: 4
 duration: 10 s
 number of transactions actually processed: 20433
 latency average = 9.826 ms
 tps = 2035.338395 (including connections establishing)
 tps = 2037.198912 (excluding connections establishing)

Modifier le paramétrage est facile grâce à la variable d’environnement PGOPTIONS :

PGOPTIONS='-c synchronous_commit=off -c commit_siblings=20' \
             pgbench -d pgbench -U pgbench -c 20 -j 4 -T100   2>/dev/null

latency average = 6.992 ms
tps = 2860.465176 (including connections establishing)
tps = 2862.964803 (excluding connections establishing)

Configurer la réplication dans postgresql.conf et pg_hba.conf :

• désactiver l’archivage s’il est actif
• autoriser des connexions de réplication en streaming en local.

Pour insérer des données :

• générer de l’activité avec pgbench en tant qu’utilisateur postgres :

$ createdb bench
$ /usr/pgsql-16/bin/pgbench -i -s 100 bench
$ /usr/pgsql-16/bin/pgbench bench -n -P 5 -R 20 -T 720

• laisser tourner en arrière-plan
• surveiller l’évolution de l’activité sur la table pgbench_history, par exemple ainsi :

$ watch -n 1 "psql -d bench -c 'SELECT max(mtime) FROM pgbench_history ;'"

En parallèle, sauvegarder l’instance avec :

• pg_basebackup au format tar, compressé avec gzip ;
• sans oublier les journaux ;
• avec l’option --max-rate=16M pour ralentir la sauvegarde ;
• le répertoire de sauvegarde sera /var/lib/pgsql/16/backups/basebackup ;
• surveillez la progression dans une autre session avec la vue système adéquate.

Une fois la sauvegarde terminée :

• regarder les fichiers générés ;
• arrêter la session pgbench ; Afficher la date de dernière modification dans pgbench_history.

• Arrêter l’instance.
• Faire une copie à froid des données (par exemple avec cp -rfp) vers /var/lib/pgsql/16/data.old (cette copie resservira plus tard).

• Vider le répertoire des données.
• Restaurer la sauvegarde pg_basebackup en décompressant ses deux archives.
• Redémarrer l’instance.

Une fois l’instance restaurée et démarrée, vérifier les traces : la base doit accepter les connexions.

Quelle est la dernière donnée restaurée ?

Tenter une nouvelle restauration depuis l’archive pg_basebackup sans restaurer les journaux de transaction. Que se passe-t-il ?

Remettre en place la copie à froid de l’instance prise précédemment dans /var/lib/pgsql/16/data.old. Configurer l’archivage vers un répertoire /var/lib/pgsql/16/archives, par exemple avec rsync. Configurer la commande de restauration inverse. Démarrer PostgreSQL.

Générer à nouveau de l’activité avec pgbench. Vérifier que l’archivage fonctionne.

En parallèle, lancer une nouvelle sauvegarde avec pg_basebackup au format plain.

Utiliser pg_verify_backup pour contrôler l’intégrité de la sauvegarde.

À quoi correspond le fichier finissant par .backup dans les archives ?

Arrêter pgbench et noter la date des dernières données insérées.

Effacer le PGDATA. Restaurer la sauvegarde précédente sans les journaux. Configurer la restore_command. Créer le fichier recovery.signal. Démarrer PostgreSQL.

Vérifier les traces, ainsi que les données restaurées une fois le service démarré.

Vérifier quelles données ont été restaurées.

pg_basebackup : sauvegarde ponctuelle & restauration

Dans ce qui suit, la plupart des commandes seront à lancer en tant que postgres, les ordres sudo nécessitant un utilisateur privilégié.

Configurer la réplication dans postgresql.conf et pg_hba.conf :

• désactiver l’archivage s’il est actif
• autoriser des connexions de réplication en streaming en local.

On n’aura ici pas besoin de l’archivage. S’il est déjà actif, on peut se contenter d’inhiber ainsi la commande d’archivage :

archive_mode = on
archive_command = '/bin/true'

(Cela permet d’épargner le redémarrage à chaque modification de archive_mode.)

Vérifier la configurer de l’autorisation de connexion en réplication dans pg_hba.conf. Si besoin, mettre à jour la ligne en fin de fichier :

local   replication     all                                     peer

Cela va ouvrir l’accès sans mot de passe depuis l’utilisateur système postgres.

Redémarrer PostgreSQL :

sudo systemctl restart postgresql-16

Pour insérer des données :

• générer de l’activité avec pgbench en tant qu’utilisateur postgres :

$ createdb bench
$ /usr/pgsql-16/bin/pgbench -i -s 100 bench
$ /usr/pgsql-16/bin/pgbench bench -n -P 5 -R 20 -T 720

• laisser tourner en arrière-plan
• surveiller l’évolution de l’activité sur la table pgbench_history, par exemple ainsi :

$ watch -n 1 "psql -d bench -c 'SELECT max(mtime) FROM pgbench_history ;'"

En parallèle, sauvegarder l’instance avec :

• pg_basebackup au format tar, compressé avec gzip ;
• sans oublier les journaux ;
• avec l’option --max-rate=16M pour ralentir la sauvegarde ;
• le répertoire de sauvegarde sera /var/lib/pgsql/16/backups/basebackup ;
• surveillez la progression dans une autre session avec la vue système adéquate.

En tant que postgres :

pg_basebackup -D /var/lib/pgsql/16/backups/basebackup -Ft \
--checkpoint=fast --gzip --progress --max-rate=16M

1583675/1583675 kB (100%), 1/1 tablespace

La progression peut se suivre depuis psql avec :

\x on
SELECT * FROM pg_stat_progress_basebackup ;
\watch

Thu Nov 11 16:58:05 2023 (every 2s)            
                                                      
-[ RECORD 1 ]--------+---------------------------------                                 
pid                  | 19763                                                             
phase                | waiting for checkpoint to finish
backup_total         |         
backup_streamed      | 0
tablespaces_total    | 0
tablespaces_streamed | 0
                                                                                                                     
Thu Nov 11 16:58:07 2023 (every 2s)            
                                               
-[ RECORD 1 ]--------+-------------------------
pid                  | 19763
phase                | streaming database files
backup_total         | 1611215360
backup_streamed      | 29354496
tablespaces_total    | 1
tablespaces_streamed | 0
…

Évidemment, en production, il ne faut pas sauvegarder en local.

Une fois la sauvegarde terminée :

• regarder les fichiers générés ;
• arrêter la session pgbench ; Afficher la date de dernière modification dans pgbench_history.

$ ls -lha /var/lib/pgsql/16/backups/basebackup

…
-rw-------. 1 postgres postgres 180K Nov 11 17:00 backup_manifest
-rw-------. 1 postgres postgres  91M Nov 11 17:00 base.tar.gz
-rw-------. 1 postgres postgres  23M Nov 11 17:00 pg_wal.tar.gz

On obtient donc :

• une archive de la sauvegarde « de base » ;
• une archive des journaux nécessaires ;
• un fichier manifeste au format texte contenant les sommes de contrôles des fichiers archivés.

pgbench s’arrête avec un simple Ctrl-C. L’heure de dernière modification est :

psql -d bench -c 'SELECT max(mtime) FROM pgbench_history;'

            max
----------------------------
  2023-11-05 17:01:51.595414

• Arrêter l’instance.
• Faire une copie à froid des données (par exemple avec cp -rfp) vers /var/lib/pgsql/16/data.old (cette copie resservira plus tard).

En tant qu’utilisateur privilégié :

sudo systemctl stop postgresql-16

En tant que postgres :

cp -rfp /var/lib/pgsql/16/data /var/lib/pgsql/16/data.old

• Vider le répertoire des données.
• Restaurer la sauvegarde pg_basebackup en décompressant ses deux archives.
• Redémarrer l’instance.

On restaure dans le répertoire de données l’archive de base, puis les journaux dans leur sous-répertoire. La suppression des traces est optionnelle, mais elle nous permettra de ne pas mélanger celles d’avant et d’après la restauration.

En tant que postgres :

rm -rf /var/lib/pgsql/16/data/*
tar -C /var/lib/pgsql/16/data \
    -xzf /var/lib/pgsql/16/backups/basebackup/base.tar.gz
tar -C /var/lib/pgsql/16/data/pg_wal \
    -xzf /var/lib/pgsql/16/backups/basebackup/pg_wal.tar.gz
rm -rf /var/lib/pgsql/16/data/log/*

sudo systemctl start postgresql-16

Une fois l’instance restaurée et démarrée, vérifier les traces : la base doit accepter les connexions.

tail -F /var/lib/pgsql/16/data/log/postgresql-*.log

…
… LOG:  database system was interrupted; last known up at 2023-11-05 16:59:03 UTC
… LOG:  redo starts at 0/830000B0
… LOG:  consistent recovery state reached at 0/8E8450F0
… LOG:  redo done at 0/8E8450F0 system usage: CPU: user: 0.28 s, system: 0.24 s, elapsed: 0.59 s
… LOG:  checkpoint starting: end-of-recovery immediate wait
… LOG:  checkpoint complete: wrote 16008 buffers (97.7%); …
… LOG:  database system is ready to accept connections

PostgreSQL considère qu’il a été interrompu brutalement et part en recovery. Noter en particulier la mention consistent recovery state reached : la sauvegarde est bien cohérente.

Quelle est la dernière donnée restaurée ?

psql -d bench -c 'SELECT max(mtime) FROM pgbench_history;'

            max
----------------------------
 2023-11-05 17:00:40.936925

Grâce aux journaux (pg_wal) restaurés, l’ensemble des modifications survenues pendant la sauvegarde ont bien été récupérées. Par contre, les données générées après la sauvegarde n’ont, elles, pas été récupérées.

Tenter une nouvelle restauration depuis l’archive pg_basebackup sans restaurer les journaux de transaction. Que se passe-t-il ?

sudo systemctl stop postgresql-16

rm -rf /var/lib/pgsql/16/data/*
tar -C /var/lib/pgsql/16/data \
    -xzf /var/lib/pgsql/16/backups/basebackup/base.tar.gz
rm -rf /var/lib/pgsql/16/data/log/*
systemctl start postgresql-16

sudo systemctl start postgresql-16

Résultat :

Job for postgresql-16.service failed because the control process exited with error code.
See "systemctl status postgresql-16.service" and "journalctl -xe" for details.

Pour trouver la cause :

tail -F /var/lib/pgsql/16/data/log/postgresql-*.log

…
… LOG:  database system was interrupted; last known up at 2023-11-05 16:59:03 UTC
… LOG:  invalid checkpoint record
2023-11-05 17:16:52.134 UTC [20177] FATAL:  could not locate required checkpoint record
2023-11-05 17:16:52.134 UTC [20177] HINT:  If you are restoring from a backup, touch "/var/lib/pgsql/16/data/recovery.signal" and add required recovery options.
        If you are not restoring from a backup, try removing the file "/var/lib/pgsql/16/data/backup_label".
        Be careful: removing "/var/lib/pgsql/16/data/backup_label" will result in a corrupt cluster if restoring from a backup.

PostgreSQL ne trouve pas les journaux nécessaires à sa restauration à un état cohérent, le service refuse de démarrer. Il a trouvé un checkpoint dans le fichier backup_label créé au début de la sauvegarde, mais aucun checkpoint postérieur dans les journaux (et pour cause).

Les traces contiennent ensuite des suggestions qui peuvent être utiles.

Cependant, un fichier recovery.signal ne sert à rien sans recovery_command, et nous n’en avons pas encore paramétré ici.

PANIC:  could not locate a valid checkpoint record

En résumé : la restauration des journaux n’est pas optionnelle !

pg_basebackup : sauvegarde ponctuelle & restauration des journaux suivants

Remettre en place la copie à froid de l’instance prise précédemment dans /var/lib/pgsql/16/data.old. Configurer l’archivage vers un répertoire /var/lib/pgsql/16/archives, par exemple avec rsync. Configurer la commande de restauration inverse. Démarrer PostgreSQL.

sudo systemctl stop postgresql-16  # si nécessaire

rm -rf /var/lib/pgsql/16/data
cp -rfp /var/lib/pgsql/16/data.old /var/lib/pgsql/16/data

Créer le répertoire d’archivage s’il n’existe pas déjà :

mkdir /var/lib/pgsql/16/archives

Là encore, en production, ce sera pluôt un partage distant. L’utilisateur système postgres doit avoir le droit d’y écrire.

L’archivage se définit dans postgresql.conf :

archive_mode = on   
archive_command = 'rsync %p /var/lib/pgsql/16/archives/%f'

et on peut y définir aussi tout de suite la commande de restauration :

restore_command = 'rsync /var/lib/pgsql/16/archives/%f %p'

sudo systemctl start postgresql-16

Générer à nouveau de l’activité avec pgbench. Vérifier que l’archivage fonctionne.

/usr/pgsql-16/bin/pgbench bench -n -P 5 -R 20 -T 720

ls -lha /var/lib/pgsql/16/archives

…
-rw-------. 1 postgres postgres 16M Jan  5 18:32 0000000100000000000000BB
-rw-------. 1 postgres postgres 16M Jan  5 18:32 0000000100000000000000BC
-rw-------. 1 postgres postgres 16M Jan  5 18:32 0000000100000000000000BD
…

En parallèle, lancer une nouvelle sauvegarde avec pg_basebackup au format plain.

rm -rf /var/lib/pgsql/16/backups/basebackup

pg_basebackup -D /var/lib/pgsql/16/backups/basebackup -Fp \
--checkpoint=fast --progress --max-rate=16M

1586078/1586078 kB (100%), 1/1 tablespace

Le répertoire cible devra avoir été vidé.

La taille de la sauvegarde sera bien sûr nettement plus grosse qu’en tar compressé.

Utiliser pg_verify_backup pour contrôler l’intégrité de la sauvegarde.

Si tout va bien, le message sera lapidaire :

/usr/pgsql-16/bin/pg_verifybackup  /var/lib/pgsql/16/backups/basebackup

backup successfully verified

S’il y a un problème, des messages de ce genre apparaîtront :

pg_verifybackup: error: "global/TEST" is present on disk but not in the manifest
pg_verifybackup: error: "global/2671" is present in the manifest but not on disk
pg_verifybackup: error: "postgresql.conf" has size 29507 on disk but size 29506 in the manifest

À quoi correspond le fichier finissant par .backup dans les archives ?

En effet, parmi les journaux archivés, figure ce fichier :

ls -1 /var/lib/pgsql/16/archives

…
0000000100000000000000BE
0000000100000000000000BE.00003E00.backup
0000000100000000000000BF
…

Son contenu correspond au futur backup_label :

START WAL LOCATION: 0/BE003E00 (file 0000000100000000000000BE)
STOP WAL LOCATION: 0/C864D0F8 (file 0000000100000000000000C8)
CHECKPOINT LOCATION: 0/BE0AB340
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2023-11-05 18:32:52 UTC
LABEL: pg_basebackup base backup
START TIMELINE: 1
STOP TIME: 2023-11-05 18:34:29 UTC
STOP TIMELINE: 1

Arrêter pgbench et noter la date des dernières données insérées.

psql -d bench -c 'SELECT max(mtime) FROM pgbench_history;'

            max
----------------------------
 2023-11-05 18:41:23.068948

Effacer le PGDATA. Restaurer la sauvegarde précédente sans les journaux. Configurer la restore_command. Créer le fichier recovery.signal. Démarrer PostgreSQL.

sudo systemctl stop postgresql-16

La sauvegarde étant au format plain, il s’agit d’une simple copie de fichiers :

rm -rf /var/lib/pgsql/16/data/*
rsync -a --exclude 'pg_wal/*' --exclude 'log/*' \
 /var/lib/pgsql/16/backups/basebackup/ \
 /var/lib/pgsql/16/data/

Créer le fichier recovery.signal :

touch /var/lib/pgsql/16/data/recovery.signal

Démarrer le service :

sudo systemctl start postgresql-16

Vérifier les traces, ainsi que les données restaurées une fois le service démarré.

Les traces sont plus complexes à cause de la restauration depuis les archives :

tail -F /var/lib/pgsql/16/data/log/postgresql-*.log

…
… LOG:  database system was interrupted; last known up at 2023-11-05 18:32:52 UTC
rsync: link_stat "/var/lib/pgsql/16/archives/00000002.history" failed: No such file or directory (2)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1189) [sender=3.1.3]
… LOG:  starting archive recovery
… LOG:  restored log file "0000000100000000000000BE" from archive
… LOG:  redo starts at 0/BE003E00
… LOG:  restored log file "0000000100000000000000BF" from archive
… LOG:  restored log file "0000000100000000000000C0" from archive
… LOG:  restored log file "0000000100000000000000C1" from archive
…
… LOG:  restored log file "0000000100000000000000C8" from archive
… LOG:  restored log file "0000000100000000000000C9" from archive
… LOG:  consistent recovery state reached at 0/C864D0F8
… LOG:  database system is ready to accept read-only connections
… LOG:  restored log file "0000000100000000000000CA" from archive
… LOG:  restored log file "0000000100000000000000CB" from archive
…
… LOG:  restored log file "0000000100000000000000E0" from archive
… LOG:  restored log file "0000000100000000000000E1" from archive
… LOG:  redo in progress, elapsed time: 10.25 s, current LSN: 0/E0FF3438
… LOG:  restored log file "0000000100000000000000E2" from archive
… LOG:  restored log file "0000000100000000000000E3" from archive
…
… LOG:  restored log file "0000000100000000000000EF" from archive
… LOG:  restored log file "0000000100000000000000F0" from archive
rsync: link_stat "/var/lib/pgsql/16/archives/0000000100000000000000F1" failed: No such file or directory (2)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1189) …
rsync: link_stat "/var/lib/pgsql/16/archives/0000000100000000000000F1" failed: No such file or directory (2)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1189) …
… LOG:  redo done at 0/F0A6C9E0 system usage:
                                          CPU: user: 2.51 s, system: 2.28 s, elapsed: 15.77 s
… LOG:  last completed transaction
                                          was at log time 2023-11-05 18:41:23.077219+00
… LOG:  restored log file "0000000100000000000000F0" from archive
rsync: link_stat "/var/lib/pgsql/16/archives/00000002.history" failed: No such file or directory (2)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1189) …
… LOG:  selected new timeline ID: 2
rsync: link_stat "/var/lib/pgsql/16/archives/00000001.history" failed: No such file or directory (2)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1189) …
… LOG:  archive recovery complete
… LOG:  checkpoint starting: end-of-recovery immediate wait
… LOG:  checkpoint complete: wrote 16012 buffers (97.7%); …
… LOG:  database system is ready to accept connections

Les messages d’erreur de rsync ne sont pas inquiétants : celui-ci ne trouve simplement pas les fichiers demandés à la restore_command. PostgreSQL sait ainsi qu’il n’y a pas de fichier 00000002.history et donc pas de timeline de ce numéro. Il devine aussi qu’il a restauré tous les journaux quand la récupération de l’un d’entre eux échoue.

Les erreurs sur les fichiers 00000001.history et 00000002.history sont normales. PostgreSQL cherche à tout hasrd ces fichiers pour voir quel est l’enchaînement des timelines et quelle est la dernière.

La progression de la restauration peut être suivie grâce aux différents messages, repris ci-dessous, de démarrage, d’atteinte du point de cohérence, de statut… jusqu’à l’heure exacte de restauration. Enfin, il y a bascule sur une nouvelle timeline, et un checkpoint.

LOG:  starting archive recovery
LOG:  redo starts at 0/BE003E00
LOG:  consistent recovery state reached at 0/C864D0F8
LOG:  redo in progress, elapsed time: 10.25 s, current LSN: 0/E0FF3438
LOG:  redo done at 0/F0A6C9E0 …
LOG:  last completed transaction was at log time 2023-11-05 18:41:23.077219+00
LOG:  selected new timeline ID: 2
LOG:  archive recovery complete
LOG:  checkpoint complete:

Noter que les journaux portent une nouvelle timeline numérotée 2 :

ls -l /var/lib/pgsql/16/data/pg_wal/

…
-rw-------. 1 postgres postgres 16777216 Jan  5 18:43 000000020000000100000023
-rw-------. 1 postgres postgres 16777216 Jan  5 18:43 000000020000000100000024
-rw-------. 1 postgres postgres       42 Jan  5 18:43 00000002.history
drwx------. 2 postgres postgres       35 Jan  5 18:43 archive_status

Vérifier quelles données ont été restaurées.

Cette fois, toutes les données générées après la sauvegarde ont bien été récupérées :

psql -d bench -c 'SELECT max(mtime) FROM pgbench_history;'

            max
----------------------------
 2023-11-05 18:41:23.068948

Nous avons vu le fonctionnement interne du mécanisme de sauvegarde physique. Celui-ci étant en place nativement dans le moteur PostgreSQL depuis de nombreuses versions, sa robustesse n’est plus à prouver. Cependant, son industrialisation reste fastidieuse.

Des outils tiers existent et vont permettre de faciliter la gestion des sauvegardes, de leur mise en place jusqu’à la restauration. Dans ce module nous allons voir en détail certains de ces outils et étudier les critères qui vont nous permettre de choisir la meilleure solution selon notre contexte.

Lors de cette présentation, nous allons passer en revue les différents outils principaux de gestion de sauvegardes, leurs forces, le paramétrage, l’installation et l’exploitation.

Où les sauvegardes doivent-elles être stockées ?

Quelles ressources sont à disposition : serveur de sauvegarde dédié ? quelle puissance pour la compression ?

De quel type d’accès aux serveurs de base de données dispose-t-on ? Quelle est la version du système d’exploitation ?

Il est très important de se poser toutes ces questions, les réponses vont servir à établir le contexte et permettre de choisir l’outil et la méthode la plus appropriée.

pg_basebackup est une application cliente intégrée à PostgreSQL, au même titre que pg_dump ou pg_dumpall.

pg_basebackup a été conçu pour permettre l’initialisation d’une instance secondaire, et il peut donc être utilisé pour effectuer facilement une sauvegarde physique ponctuelle. Celle-ci inclut les fichiers et journaux nécessaires pour une restauration telle que l’instance était à la fin de la sauvegarde.

pg_basebackup peut aussi être à la base d’outils permettant le PITR (par exemple barman). Ces outils s’occupent en plus de l’archivage des journaux générés pendant et après la sauvegarde initiale, pour une restauration dans un état postérieur à la fin de cette sauvegarde.

Le format par défaut de la sauvegarde est plain, ce qui signifie que les fichiers seront créés tels quels dans le répertoire de destination (ou les répertoires en cas de tablespaces). C’est idéal pour obtenir une copie immédiatement utilisable.

Pour une archive à proprement parler, préférer l’option --format tar. pg_basebackup génère alors une archive base.tar pour le PGDATA de l’instance, puis une archive <oid>.tar par tablespace. Les journaux récupérés seront également dans un fichier .tar.

L’option --gzip (-z) ajoute la compression gzip. Le niveau de compression peut également être spécifié avec --compress=1 à 9 (-Z). Cela permet d’arbitrer entre la durée de la sauvegarde et sa taille.

Récupération des journaux :

pg_basebackup sait récupérer les fichiers WAL nécessaires à la restauration de la sauvegarde sans passer par la commande d’archivage. Il connaît deux méthodes :

Avec l’option --wal-method fetch (ou -X), les WAL générés pendant la sauvegarde seront demandés une fois celle-ci terminée, à condition qu’ils n’aient pas été recyclés entre-temps (ce qui peut nécessiter un slot de réplication, ou éventuellement une configuration élevée du paramètre wal_keep_size/wal_keep_segments).

L’option par défaut est cependant -X stream : les WAL sont récupérés non pas en fin de sauvegarde, mais en streaming pendant celle-ci. Cela nécessite néanmoins l’utilisation d’un wal sender supplémentaire, le paramètre max_wal_senders doit parfois être augmenté en conséquence.

Rappelons que si l’archivage des WAL n’est pas actif, la sauvegarde effectuée ne sera utilisée que pour restaurer l’instance telle qu’elle était au moment de la fin de la sauvegarde : il ne sera pas possible de réaliser une restauration PITR.

À l’inverse, -X none peut être utile si la récupération des journaux est réalisée par ailleurs (généralement par archive_command ou archive_library). Attention, l’archive réalisée avec pg_basebackup ne sera alors pas « complète », et ne pourra pas être restaurée sans ces archives des journaux (il faudra indiquer où aller les chercher avec restore_command.)

Slots de réplication :

Par défaut, pg_basebackup va créer un slot de réplication temporaire sur le serveur pour sécuriser la sauvegarde. Il disparaîtra une fois celle-ci terminée.

Pour faciliter la mise en place d’une instance secondaire, et garantir que tous les journaux nécessaires seront encore sur le primaire à son démarrage, il est possible de créer un slot de réplication permanent, et de le fournir à pg_basebackup avec --slot nom_du_slot. pg_basebackup peut le créer lui-même avec --create. Si l’on préfère le créer préalablement, il suffit d’exécuter la requête suivante :

SELECT pg_create_physical_replication_slot ('nom_du_slot');

Rappelons qu’un slot initialisé mais inutilisé doit être rapidement supprimé pour ne pas mener à une dangereuse accumulation des journaux.

Sécurisation de la sauvegarde :

Par défaut, pg_basebackup crée un fichier manifeste (à partir de PostgreSQL 13). Ce fichier contient la liste des fichiers sauvegardés, leur taille et leur somme de contrôle. Cela permet après coup de vérifier l’intégrité de la sauvegarde à l’aide de l’outil pg_verifybackup.

L’algorithme par défaut de la somme de contrôle, CRC32, suffit pour détecter une erreur technique accidentelle ; d’autres algorithmes disponibles permettent de détecter une manipulation volontaire de la sauvegarde.

Vérification des sommes de contrôle :

Une sauvegarde avec pg_basebackup entraîne la vérification des sommes de contrôle de l’instance. Cela garantit que la sauvegarde n’héritera pas d’une corruption existante, sinon l’outil tombe en erreur.

L’option --no-verify-checksums autorise la sauvegarde d’une instance où une corruption est détectée (sauvegarde aussi problématique, certes, mais qui peut permettre de travailler sur la récupération, ou de sauver l’essentiel).

Emplacement de la sauvegarde

À partir de la version 15, l’option --target permet de spécifier où la sauvegarde doit être réalisée :

• sur le serveur où la commande est lancée (client) ;
• sur le serveur de base de données (server) ;
• dans le vide (blackhole).

Des destinations peuvent être ajoutées par des extensions, basebackup_to_shell est fournie à titre d’exemple et permet d’exécuter une commande à l’issue d’une sauvegarde.

Lorsque la destination server est choisie, plusieurs restrictions s’appliquent à la sauvegarde :

• le format doit être tar ;
• l’utilisateur employé pour la réaliser doit être membre du rôle pg_write_server_files ;
• la méthode de récupération des WAL doit être fetch ou none.

Compression de la sauvegarde :

À partir de la version 15, il est possible de demander la compression de la sauvegarde avec un grand niveau de personnalisation :

• algorithme de compression parmi gzip, lz4 et zstd ;
• rapidité de la compression hors parallélisme (lz4) ;
• niveau de compression (zstd) ;
• parallélisation de la compression (zstd) ;
• localisation de la compression (serveur ou client).

Cela permet de gérer différents scénarios et d’éviter certains goulets d’étranglement lors d’une sauvegarde.

Autres options :

Le débit de la sauvegarde est configurable avec l’option --max-rate= (-r) pour limiter l’impact sur l’instance ou le réseau. Cette restriction de débit ne concerne pas les journaux transférés en parallèle (-X stream).

Pour gagner un peu de temps, si l’instance n’est pas trop chargée, --checkpoint=fast accélère le checkpoint préalable à la sauvegarde.

Avec une sauvegarde plain, il est possible de modifier sur la cible les chemins des éventuels tablespaces avec l’option --tablespace-mapping=<vieuxrep>=<nouveaurep> (ou -T), et de relocaliser le répertoire des fichiers WAL avec l’option --waldir=<nouveau chemin>.

Depuis un secondaire :

pg_basebackup permet nativement de réaliser une sauvegarde à partir d’une instance secondaire. Le paramétrage nécessaire figure plus bas.

Suivi :

Pour suivre le déroulement de la sauvegarde depuis un terminal, il existe l’option --progress (-P).

À partir de PostgreSQL 13, il existe aussi une vue pour ce suivi : pg_stat_progress_basebackup.

Options complètes :

Pour mémoire, toutes les options disponibles sont celles-ci (en version 15) :

$ pg_basebackup --help

pg_basebackup prend une sauvegarde binaire d'un serveur PostgreSQL en cours
d'exécution.

Usage :
  pg_basebackup [OPTION]...

Options contrôlant la sortie :
  -D, --pgdata=RÉPERTOIRE        reçoit la sauvegarde de base dans ce répertoire
  -F, --format=p|t               format en sortie (plain (par défaut), tar)
  -r, --max-rate=TAUX            taux maximum de transfert du répertoire de
                                 données (en Ko/s, ou utiliser le suffixe « k »
                                 ou « M »)
  -R, --write-recovery-conf      écrit la configuration pour la réplication
  -t, --target=CIBLE[:DETAIL]    cible de sauvegarde (si autre que client)
  -T, --tablespace-mapping=ANCIENREP=NOUVEAUREP
                                 déplace le répertoire ANCIENREP en NOUVEAUREP
      --waldir=RÉP_WAL           emplacement du répertoire des journaux de
                                 transactions
  -X, --wal-method=none|fetch|stream
                                 inclut les journaux de transactions requis avec
                                 la méthode spécifiée
  -z, --gzip                     compresse la sortie tar
  -Z, --compress=[{client|server}-]METHODE[:DETAIL]
                                 compresse sur le client ou le serveur comme indiqué
  -Z, --compress=none            ne compresse pas la sortie tar

Options générales :
  -c, --checkpoint=fast|spread   exécute un CHECKPOINT rapide ou réparti
      --create-slot              crée un slot de réplication
  -l, --label=LABEL              configure le label de sauvegarde
  -n, --no-clean                 ne nettoie pas en cas d'erreur
  -N, --no-sync                  n'attend pas que les modifications soient
                                 proprement écrites sur disque
  -P, --progress                 affiche la progression de la sauvegarde
  -S, --slot=NOMREP              slot de réplication à utiliser
  -v, --verbose                  affiche des messages verbeux
  -V, --version                  affiche la version puis quitte
      --manifest-checksums=SHA{224,256,384,512}|CRC32C|NONE
                                 utilise cet algorithme pour les sommes de
                                 contrôle du manifeste
      --manifest-force-encode    encode tous les noms de fichier dans le
                                 manifeste en hexadécimal
      --no-estimate-size         ne réalise pas d'estimation sur la taille de la
                                 sauvegarde côté serveur
      --no-manifest              supprime la génération de manifeste de
                                 sauvegarde
      --no-slot                  empêche la création de slots de réplication
                                 temporaires
      --no-verify-checksums      ne vérifie pas les sommes de contrôle
  -?, --help                     affiche cette aide puis quitte

Options de connexion :
  -d, --dbname=CHAÎNE_CONNEX     chaîne de connexion
  -h, --host=HÔTE                hôte du serveur de bases de données ou
                                 répertoire des sockets
  -p, --port=PORT                numéro de port du serveur de bases de données
  -s, --status-interval=INTERVAL durée entre l'envoi de paquets de statut au
                                 serveur (en secondes)
  -U, --username=UTILISATEUR     se connecte avec cet utilisateur
  -w, --no-password              ne demande jamais le mot de passe
  -W, --password                 force la demande du mot de passe (devrait
                                 survenir automatiquement)

Rapporter les bogues à <pgsql-bugs@lists.postgresql.org>.
Page d'accueil de PostgreSQL : <https://www.postgresql.org/>

Configuration :

pg_basebackup étant conçu pour la mise en place d’une instance en réplication, l’instance principale nécessite d’être configurée en conséquence :

• max_wal_senders doit avoir une valeur supérieure à 0 pour permettre à pg_basebackup de se connecter (au moins 2 si on utilise le transfert des WAL par streaming) — c’est le cas par défaut ;
• le fichier pg_hba.conf de l’instance principale doit être configuré pour autoriser les connexions de type replication depuis la machine où la sauvegarde est déclenchée, par exemple ainsi :

host  replication  repli_user  192.168.0.100/32  scram-sha-256

Dans l’idéal, l’utilisateur employé est dédié à la réplication. Pour automatiser, stocker le mot de passe nécessaire dans un fichier .pgpass.

L’archivage n’est pas géré par pg_basebackup. Il ne récupère par streaming que les journaux nécessaires à la cohérence de sa sauvegarde. Il faudra paramétrer archive_command ou archive_library à la main pour une sauvegarde PITR.

Si la sauvegarde est effectuée à partir d’une instance secondaire :

• ces paramétrages sont nécessaires (et en place par défaut) :
  • instance secondaire ouverte en lecture (hot_standby à on) ;
  • max_wal_senders supérieur 0 et droits en place pour permettre à pg_basebackup de se connecter ;
  • écriture complète des pages dans les WAL activée (full_page_writes à on) ;
• pour du PITR :
  • l’archivage des fichiers WAL doit être configuré indépendamment.
  • une attention particulière doit être apportée au fait que tous les fichiers WAL nécessaires à la restauration ont bien été archivés.

Gestion des sauvegardes :

La gestion des sauvegardes (rétention, purge…) n’est pas prévue dans l’outil.

pg_basebackup n’effectue pas non plus de lien entre les WAL archivés et les sauvegardes effectuées (si pg_basebackup ne les sauvegarde pas lui-même avec l’option -X).

Restauration :

pg_basebackup n’offre pas d’outil ni d’option pour la restauration.

La copie est directement utilisable, éventuellement après déplacement et/ou décompression des .tar.gz. Mais, généralement, on ajoutera un fichier recovery.signal, et on définira la restore_command pour récupérer les archives. Dans l’idéal, restore_command sera déjà prête dans le postgresql.conf.

Si le but est de monter un serveur secondaire de l’instance copiée, il existe une option utile : --write-recovery-conf (ou -R), qui génère la configuration nécessaire dans le répertoire de la sauvegarde (postgresql.auto.conf et fichier vide standby.signal). avec les paramètres pour une réplication en streaming.

pgBackRest est un outil de gestion de sauvegardes PITR écrit en perl et en C, par David Steele de Crunchy Data.

Il met l’accent sur les performances avec de gros volumes et les fonctionnalités, au prix d’une complexité à la configuration :

• un protocole dédié pour le transfert et la compression des données ;
• des opérations parallélisables en multithread ;
• la possibilité de réaliser des sauvegardes complètes, différentielles et incrémentielles ;
• la possibilité d’archiver ou restaurer les WAL de façon asynchrone, et donc plus rapide ;
• la possibilité d’abandonner l’archivage en cas d’accumulation et de risque de saturation de pg_wal ;
• la gestion de dépôts de sauvegarde multiples (pour sécuriser, ou avoir plusieurs niveaux d’archives) ;
• le support intégré de dépôts S3 ou Azure ;
• le support d’un accès TLS géré par pgBackRest en alternative à SSH ;
• la sauvegarde depuis un serveur secondaire ;
• le chiffrement des sauvegardes ;
• la restauration en mode delta, très pratique pour restaurer un serveur qui a décroché mais n’a que peu divergé ;
• la reprise d’une sauvegarde échouée.

pgBackRest n’utilise pas pg_receivewal pour garantir la sauvegarde du dernier journal (non terminé) avant un sinistre. Les auteurs considèrent que dans ce cas un secondaire synchrone est plus adapté et plus fiable.

Le projet est très actif et considéré comme fiable, et les fonctionnalités proposées sont intéressantes.

Pour la supervision de l’outil, une sonde Nagios est fournie par un des développeurs : check_pgbackrest.

pgBackRest gère uniquement des sauvegardes physiques.

La sauvegarde s’effectue :

• soit en local (push, directement sur le serveur hébergeant l’instance à sauvegarder) pour un stockage local des sauvegardes, ou un stockage accessible par un montage NFS ou vers un dépôt S3, Azure… ;
• soit depuis un serveur distant (pull), déléguant ainsi l’ordonnancement et le stockage des données à celui-ci.

La planification des sauvegardes peut être faite par n’importe quel outil de planification de tâches, le plus connu étant cron.

La technique utilisée pour la prise de sauvegarde repose sur le mécanisme interne standard et historique : pg_backup_start(), copie des fichiers, pg_backup_stop().

L’archivage des journaux se fait bien sûr en permanence et utilise le classique archive_command.

La restauration d’une sauvegarde peut se faire soit localement, si les sauvegardes sont stockées en local, soit à distance. Dans ce dernier cas, les données à restaurer seront transférées via SSH.

Plusieurs types de point dans le temps peuvent être utilisés comme cible :

• la date ;
• un identifiant de transaction ;
• une timeline (en cas de divergence de timeline, pgBackRest peut restaurer les transactions issues d’une timeline précise) ;
• un point de restauration créé par un appel préalable à la fonction :
  • pg_create_restore_point().

pgBackRest est disponible sur le dépôt communautaire maintenu par la communauté PostgreSQL pour les systèmes d’exploitation disposant des gestionnaires de paquet au format deb (Debian, Ubuntu…) ou rpm (Red Hat, Rocky Linux, CentOS, Fedora…).

Il est recommandé de manière générale de privilégier une installation à partir de ces paquets plutôt que par les sources, essentiellement pour des raisons de maintenance.

pgBackRest propose différentes commandes pouvant être passées en argument afin de contrôler les actions.

L’usage de ces différentes commandes sera détaillé ultérieurement.

Le format de configuration INI permet de définir des sections, qui sont matérialisées sous la forme d’une ligne : [nomdesection].

pgBackRest s’attend à lire un fichier de configuration contenant la section [global], contenant les paramètres de configuration globaux, et une section par instance à sauvegarder.

pgBackRest utilise le terme stanza pour regrouper l’ensemble des configurations à appliquer pour une instance à sauvegarder.

Exemple de configuration :

[global]
repo1-path=/var/lib/pgsql/10/backups

[erp_prod]
pg1-path=/var/lib/pgsql/10/data

Il peut y avoir plusieurs stanzas déclarées dans le fichier, notamment s’il est situé sur le serveur où sont stockées les sauvegardes de plusieurs instances.

Pour des questions de lisibilité, il est possible de créer un fichier de configuration par instance à sauvegarder. Le nom du fichier doit se terminer par .conf pour être pris en compte. Les fichiers doivent être regroupés dans un répertoire référencé par le paramètre config-include-path.

Il est nécessaire d’activer l’archivage des journaux de transactions en positionnant le paramètre archive_mode à on et en définissant un niveau d’enregistrement d’informations dans les journaux de transactions (wal_level) supérieur ou égal à replica (ou archive avant la version 9.6).

pgBackRest fournit une commande permettant de simplifier la configuration de l’archivage. Pour l’utiliser, il faut configurer le paramètre archive_command pour qu’il utilise l’option archive-push de la commande pgbackrest. Il faut également fournir à cette commande le nom de la stanza à utiliser.

Comme pgBackRest n’archive que des journaux complets, il vaut mieux penser à mettre un archive_timeout adapté au RPO accepté. (S’il est nul, les auteurs recommandent plutôt un secondaire synchrone).

• process-max : nombre de processus maximum à utiliser pour la compression et le transfert des sauvegardes ;
• repo1-path : chemin où seront stockées les sauvegardes et les archives ;
• repo-cipher-pass : passphrase à utiliser pour chiffrer/déchiffrer le répertoire des sauvegardes ;
• log-level-console : par défaut à warn, définit le niveau de traces des commandes exécutées en console.

La politique de rétention des sauvegardes complètes peut être configurée avec l’option repo1-retention-full-type. Elle peut prendre deux valeurs :

• count : le nombre de sauvegardes à conserver, c’est la valeur par défaut ;
• time : un nombre de jours pendant lequel on doit pouvoir restaurer, c’est-à-dire que l’on doit avoir au moins une sauvegarde plus vieille que ce nombre de jours.

Voici un exemple pour illustrer le mode de rétention time, dont le fonctionnement n’est pas très intuitif. Si l’on dispose des trois sauvegardes complètes suivantes :

• F1 : 25 jours ;
• F2 : 20 jours ;
• F3 : 10 jours.

Avec une rétention de 15 jours, seule la sauvegarde F1 sera supprimée. F2 sera conservée, car il doit exister au moins une sauvegarde de plus de 15 jours pour garantir de pouvoir restaurer pendant cette période.

Il est possible de différencier le nombre de sauvegardes complètes et différentielles. La rétention pour les sauvegardes différentielles ne peut être définie qu’en nombre.

Lorsqu’une sauvegarde complète expire, toutes les sauvegardes différentielles et incrémentales qui lui sont associées expirent également.

Dans le cadre de la mise en place de sauvegardes avec un stockage des données sur un serveur tiers, pgBackRest fonctionnera par SSH.

Il est donc impératif d’autoriser l’authentification SSH par clé, et d’échanger les clés publiques entre les différents serveurs hébergeant les instances PostgreSQL et le serveur de sauvegarde.

Il faudra ensuite adapter les paramètres repo1-host* dans la configuration de pgBackRest.

• repo1-host : hôte à joindre par SSH ;
• repo1-host-user : utilisateur pour la connexion SSH ;
• …

Il existe une alternative à l’utilisation de SSH qui consiste à configurer un serveur TLS en valorisant le paramètre repo1-host-type et pg1-host-type à tls (défaut : ssh). La configuration du serveur se fait ensuite avec les paramètres :

• tls-server-address : adresse IP sur laquelle le serveur écoute pour servir des requêtes clients ;
• tls-server-auth : la liste des clients autorisés à se connecter sous la forme <client-cn>=<stanza> ;
• tls-server-ca-file : certificat de l’autorité ;
• tls-server-cert-file : certificat du serveur ;
• tls-server-key-file : clé du serveur.

Il faut ensuite configurer l’accès au dépôt de sauvegarde :

• repo1-host-type=tls : la connexion au dépôt utilise TLS ;
• repo1-host-cert-file : certificat pour se connecter au dépôt ;
• repo1-host-key-file : clé pour se connecter au dépôt ;
• repo1-host-ca-file : certificat de l’autorité.

Exemple de configuration :

[global]
repo1-host=backrest-srv
repo1-host-user=backrest
repo1-host-type=tls
repo1-host-cert-file=/etc/certs/srv1-cert.pem
repo1-host-key-file=/etc/certs/srv1-key.pem
repo1-host-ca-file=/etc/certs/CA-cert.pem

tls-server-address=*
tls-server-cert-file=/etc/certs/srv1-cert.pem
tls-server-key-file=/etc/certs/srv1-key.pem
tls-server-ca-file=/etc/certs/CA-cert.pem
tls-server-auth=backrest-srv=main

[main]
pg1-path=/var/lib/pgsql/14/data

Sur le serveur de sauvegarde, la configuration est similaire :

• pg1-host-type=tls : la connexion au serveur PostgreSQL utilise TLS ;
• pg1-host-cert-file : certificat pour se connecter au serveur de bases de données ;
• pg1-host-key-file : certificat pour se connecter au serveur de bases de données ;
• pg1-host-ca-file : certificat de l’autorité.

Exemple de configuration du serveur de sauvegarde :

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2

tls-server-address=*
tls-server-cert-file=/etc/certs/backrest-srv-cert.pem
tls-server-key-file=/etc/certs/backrest-srv-key.pem
tls-server-ca-file=/etc/certs/CA-cert.pem
tls-server-auth=srv1=main

[main]
pg1-host=srv1
pg1-port=5432
pg1-path=/var/lib/pgsql/14/data

pg1-host-type=tls
pg1-host-cert-file=/etc/certs/backrest-srv-cert.pem
pg1-host-key-file=/etc/certs/backrest-srv-key.pem
pg1-host-ca-file=/etc/certs/CA-cert.pem

Le serveur TLS doit ensuite être démarré avec la commande pgbackrest server. Un service est prévu à cet effet et installé automatiquement sur les distributions de type RedHat et Debian.

Un ping vers le serveur TLS peut être testé avec la commande pgbackrest server-ping <hote>. Suivant les distributions, il peut être nécessaire d’ouvrir le port 8432 (valeur par défaut de tls-server-port).

[postgres@backrest log]$ pgbackrest server-ping srv1
INFO: server-ping command begin 2.41: [srv1] --exec-id=7467-76e4b8cf
 --log-level-console=info --tls-server-address=*
INFO: server-ping command end: completed successfully (47ms)

Génération des clés et certificats auto-signés :

# Générer une clé privée et un certificat pour l'autorité de certification
openssl req -new -x509 \
                 -days 365 \ 
         -nodes \
         -out CA-cert.pem \
         -keyout CA-key.pem \
         -subj "/CN=root-ca"

# Générer une clé privée et demande de certificat (CSR)
openssl req -new -nodes \ 
                 -out backrest-srv-csr.pem \
         -keyout backrest-srv-key.pem \
         -subj "/CN=backrest-srv"
openssl req -new -nodes \
                 -out srv1-csr.pem \
         -keyout srv1-key.pem \ 
         -subj "/CN=srv1"

# Générer le certificat signé
openssl x509 -req -in backrest-srv-csr.pem \
                  -days 365 \
          -CA CA-cert.pem \
          -CAkey CA-key.pem \
          -CAcreateserial \
          -out backrest-srv-crt.pem
openssl x509 -req -in srv1.csr
                  -days 365 \
          -CA CA-cert.pem \
          -CAkey CA-key.pem \
          -CAcreateserial \
          -out srv1-crt.pem

Après avoir vu les options globales, nous allons voir à présent les options spécifiques à chaque instance à sauvegarder.

Une stanza définit l’ensemble des configurations de sauvegardes pour un cluster PostgreSQL spécifique. Chaque section stanza définit l’emplacement du répertoire de données ainsi que l’hôte/utilisateur si le cluster est distant. Chaque configuration de la partie globale peut être surchargée par stanza.

Le nom de la stanza est important et doit être significatif car il sera utilisé lors des tâches d’exploitation pour identifier l’instance cible.

Il est également possible d’ajouter ici des recovery-option afin de personnaliser les options du postgresql.auto.conf qui sera généré automatiquement à la restauration d’une sauvegarde.

La commande d’initialisation doit être lancée sur le serveur où se situe le répertoire de stockage après que la stanza ait été configurée dans pgbackrest.conf.

La commande check valide que pgBackRest et le paramètre archive_command soient correctement configurés. Les commandes pg_create_restore_point('pgBackRest Archive Check') et pg_switch_wal() sont appelées à cet effet pour forcer PostgreSQL à archiver un segment WAL.

La sauvegarde accepte de nombreux paramètres dont :

• --archive-copy : archive les WAL dans la sauvegarde en plus de les mettre dans le dépôt de WAL ;
• --backup-standby : déclenche la sauvegarde sur un serveur secondaire ;
• --no-online : fait une sauvegarde à froid ;
• --resume : reprend une sauvegarde précédemment échouée en conservant les fichiers qui n’ont pas changés ;
• --start-fast : exécuter le checkpoint immédiatement.

Exemple de sortie d’une sauvegarde complète :

$ sudo -u postgres pgbackrest --stanza=erp_prod --type=full backup |grep P00
P00   INFO: backup command begin 2.19: --log-level-console=info
--no-log-timestamp --pg1-path=/var/lib/pgsql/12/data --process-max=1
--repo1-path=/var/lib/pgsql/12/backups --repo1-retention-full=1
--stanza=erp_prod --type=full
P00   INFO: execute non-exclusive pg_start_backup() with label
"pgBackRest backup started at 2019-11-26 12:39:26":
backup begins after the next regular checkpoint completes
P00   INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028
P00   INFO: full backup size = 24.2MB
P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL
segments to archive
P00   INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000100
P00   INFO: new backup label = 20191126-123926F
P00   INFO: backup command end: completed successfully
P00   INFO: expire command begin 2.19: --log-level-console=info
--no-log-timestamp --pg1-path=/var/lib/pgsql/12/data --process-max=1
--repo1-path=/var/lib/pgsql/12/backups --repo1-retention-full=1
--stanza=erp_prod --type=full
P00   INFO: expire full backup 20191126-123848F
P00   INFO: remove expired backup 20191126-123848F
P00   INFO: expire command end: completed successfully

La commande se charge automatiquement de supprimer les sauvegardes devenues obsolètes.

Il est possible d’ajouter des annotations aux sauvegardes comme ceci :

$ sudo -u postgres pgbackrest
  --stanza=erp_prod
  --type=full
  --annotation=desc="Premier backup"
  backup

L’annotation peut être observé en affichant les informations du backup set.

Exemple de sortie des commandes :

$ sudo -u postgres pgbackrest --stanza=erp_prod info
stanza: erp_prod
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000030000000000000019/00000003000000000000001B

        full backup: 20221026-071751F
            timestamp start/stop: 2022-10-26 07:17:51 / 2022-10-26 07:17:57
            wal start/stop: 00000003000000000000001B / 00000003000000000000001B
            database size: 25.2MB, database backup size: 25.2MB
            repo1: backup set size: 3.2MB, backup size: 3.2MB

$ sudo -u postgres pgbackrest --stanza=erp_prod --set 20221026-071751F info
stanza: erp_prod
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000030000000000000019/00000003000000000000001B

        full backup: 20221026-071751F
            timestamp start/stop: 2022-10-26 07:17:51 / 2022-10-26 07:17:57
            wal start/stop: 00000003000000000000001B / 00000003000000000000001B
            lsn start/stop: 0/1B000028 / 0/1B000100
            database size: 25.2MB, database backup size: 25.2MB
            repo1: backup set size: 3.2MB, backup size: 3.2MB
            database list: postgres (13748)
            annotation(s)
                desc: Premier backup

pgBackRest permet de maintenir plusieurs dépôts de sauvegarde simultanément.

Un intérêt est de gérer des rétentions différentes. Par exemple un dépôt local contiendra juste les dernières sauvegardes et journaux, alors qu’un deuxième dépôt sera sur un autre site plus lointain, éventuellement moins cher, et/ou une rétention supérieure.

Les propriétés des différents dépôts (type, chemin, rétention…) se définissent avec les options repo1-path, repo2-path, etc. Désigner un dépôt particulier se fait avec --repo=1 par exemple.

Une sauvegarde se fait vers un seul dépôt donné en le désignant explicitement. Cependant, l’archivage des journaux est simultané sur tous les dépôts à la fois. L’archivage asynchrone est conseillé dans ce cas.

Les types de dépôts supportés sont ceux montés sur le serveur ou accessibles par ssh, NFS (avec la même attention aux options de montage que pour PostgreSQL), CIFS (avec des restrictions sur les liens symboliques ou le fsync), mais aussi ceux à base de buckets : S3 ou compatible, Google Cloud, et Azure Blob.

Pour les détails, voir la conférence de Stefan Fercot à la PGSession 16 de 2021 (slides.

« Bundling » des petits fichiers

Si une instances contient de nombreux petits fichiers (base aux nombreuses toutes petites tables, pg_commit_ts rempli à cause de track_commit_timestamp à on, très nombreuses petites partitions, chacune avec des fichiers annexes…), il est possible de les regrouper par paquets.

repo1-bundle=y
# défauts
repo1-bundle-limit=2MiB
repo-bundle-size=20MiB

Les bundles ne sont pas conservés en cas de backup interrompu puis redémarré. Les fichiers doivent être re-sauvegardés lors de la relance. Bundles et hard-links ne peuvent pas être utilisés ensemble.

Cette fonctionnalité est particulièrement utile avec un stockage comme S3 où le coût de création de fichier est prohibitif.

Sauvegarde incrémentale en mode bloc (2.46)

La sauvegarde incrémentale par bloc permet plus de granularité en divisant les fichiers en blocs qui peuvent être sauvegardés indépendamment. C’est particulièrement intéressant pour des fichiers avec peu de modifications, car pgBackRest ne sauvegardera que quelques blocs au lieu du fichier complet (les tables et index sont segmentés en fichiers de 1 Go). Cela permet donc d’économiser de l’espace dans le dépôt de sauvegarde et accélère les restaurations par delta.

La sauvegarde incrémentale par bloc doit être activée sur tous les types de sauvegardes : full, incrémentielle ou différentielle. Cela aura pour impact de rendre la sauvegarde full un peu plus grosse du fait de la création de fichier de cartographie des blocs. En revanche, les sauvegardes différentielles et incrémentielles suivantes pourront utiliser cette cartographie pour économiser de l’espace.

La taille du bloc pour un fichier donné est définie en fonction de l’âge et de la taille du fichier. Généralement, les fichiers les plus gros et/ou les plus anciens auront des tailles de bloc supérieures. Si un fichier est assez vieux, aucune cartographie ne sera crée.

Cette fonctionnalité nécessite le bundling et s’active ainsi :

repo1-block=y
repo1-bundle=y

Exemple de sortie de la commande :

$ sudo -u postgres pgbackrest --stanza=erp_prod restore |grep P00

P00   INFO: restore command begin 2.19: --log-level-console=info
--no-log-timestamp --pg1-path=/var/lib/pgsql/12/data
--process-max=1 --repo1-path=/var/lib/pgsql/12/backups --stanza=erp_prod
P00   INFO: restore backup set 20191126-123926F
P00   INFO: write updated /var/lib/pgsql/12/data/postgresql.auto.conf
P00   INFO: restore global/pg_control (performed last to ensure aborted
restores cannot be started)
P00   INFO: restore command end: completed successfully

L’option --delta permet de ne restaurer que les fichiers qui seraient différents entre la sauvegarde et le répertoire de données déjà présent sur le serveur. Elle permet de gagner beaucoup de temps pour reprendre une restauration qui a été interrompue pour une raison ou une autre, pour resynchroniser une instance qui a « décroché », pour restaurer une version légèrement antérieure ou postérieure dans du PITR.

La cible à restaurer peut être spécifiée avec --target, associé à --type. Par exemple, pour restaurer à une date précise sur une timeline précise :

pgbackrest --stanza=instance --delta \
  --type=time --target='2020-07-16 11:07:00' \
  --target-timeline=4 \
  --target-action=pause \
  --set=20200716-102845F \
  restore

barman est un outil développé avec le langage python, compatible uniquement avec les environnements Linux/Unix. Il a été développé par la société 2ndQuadrant Italia (à présent partie de EDB) et distribué sous license GPL3.

Le scénario évoqué ci-dessus est communément appelé streaming-only puisqu’il ne requiert pas de connexion SSH pour les opérations de sauvegardes et d’archivage. Il faudra quand même configurer le SSH pour rendre possible la restauration depuis un serveur dédié ou faciliter la restauration en local.

En effet, les outils pg_basebackup et pg_receivewal sont utilisés pour ces opérations et se basent donc uniquement sur le protocole de réplication. Cela a pour avantage que les améliorations faites aux outils dans le cadre des mises à jour majeures de PostgreSQL sont disponible directement dans Barman.

Par exemple :

• la possibilité d’utiliser pg_stat_progress_basebackup pour la supervision ;
• les fichiers manifestes de sauvegarde et la vérification des sauvegardes ;
• la compression des sauvegardes.

Afin de garantir que l’instance sauvegardée conserve bien les WAL nécessaires, Barman permet de créer automatiquement un slot de réplication. Il se chargera également de démarrer pg_receivewal grâce à sa tâche de maintenance programmée en crontab.

L’archivage peut être configuré à la place ou en plus du streaming des WAL.

Ce mode de sauvegarde permet de sauvegarder un serveur PostgreSQL installé sous Windows.

Ce deuxième scénario se base donc sur une connexion SSH afin de réaliser les sauvegardes et récupérer les archives des journaux WAL.

Cette méthode ne permet pas de compresser les sauvegardes mais permet de faire de la déduplication avec des hard links et de bénéficier de la parallélisation.

Barman gère uniquement des sauvegardes physiques.

Il peut fonctionner soit en local (directement sur le serveur hébergeant l’instance à sauvegarder) pour un stockage local des sauvegardes, et peut aussi être exécuté depuis un serveur distant, déléguant ainsi l’ordonnancement, la compression et le stockage des données.

La technique utilisée pour la prise de sauvegarde repose sur le mécanisme interne standard et historique : pg_backup_start(), copie des fichiers, pg_backup_stop().

Contrairement aux autres outils présentés, Barman peut également se servir de pg_basebackup et pg_receivewal pour récupérer les sauvegardes et les archives des journaux WAL.

Il est possible d’activer la dé-duplication de fichiers entre deux sauvegardes lorsque la méthode via rsync est employée.

Barman supporte la limitation du débit réseau lors du transfert des données sur un serveur tiers, ainsi que la compression des données à la volée le temps du transfert.

Quatre niveaux de scripts ancrés (hooks) sont possibles :

• avant la sauvegarde ;
• après la sauvegarde ;
• avant l’archivage d’un WAL ;
• après l’archivage d’un WAL.

Attention, l’opération d’archivage citée ici est celle effectuée par Barman lorsqu’il déplace et compresse un WAL à partir du répertoire incoming_wals/ vers le répertoire wals/, il ne s’agit pas de l’archivage au sens PostgreSQL.

La politique de rétention peut être exprimée soit en nombre de sauvegardes à conserver, soit en fenêtre de restauration : une semaine, deux mois, etc.

La restauration d’une sauvegarde peut se faire soit localement, si les sauvegardes sont stockées en local, soit à distance. Dans ce dernier cas, les données à restaurer seront transférées via SSH.

Plusieurs types de point dans le temps peuvent être utilisés comme cible :

• la date ;
• un identifiant de transaction ;
• une timeline (en cas de divergence de timeline, barman peut restaurer les transactions issues d’une timeline précise) ;
• un point de restauration créé par un appel préalable à la fonction :
  • pg_create_restore_point().

Barman est disponible sur le dépôt communautaire maintenu par la communauté PostgreSQL pour les systèmes d’exploitation disposant des gestionnaires de paquet au format deb (Debian, Ubuntu…) ou rpm (Red Hat, Rocky Linux, CentOS, Fedora…).

Il est recommandé de manière générale de privilégier une installation à partir des paquets issus du PGDG plutôt que par les sources, essentiellement pour des raisons de maintenance.

Barman propose différentes commandes pouvant être passées en argument afin de contrôler les actions.

L’usage de ces différentes commandes sera détaillé ultérieurement.

L’option -c (ou --config) permet d’indiquer l’emplacement du fichier de configuration. L’option -q (ou --quiet) désactive l’envoi de messages sur la sortie standard.

Le format de configuration INI permet de définir des sections, qui sont matérialisées sous la forme d’une ligne : [nomdesection].

Barman s’attend à lire un fichier de configuration contenant la section [barman], contenant les paramètres de configuration globaux, et une section par instance à sauvegarder, le nom de la section définissant ainsi le nom de l’instance.

Pour des questions de lisibilité, il est possible de créer un fichier de configuration par instance à sauvegarder. Ce fichier doit alors se trouver (par défaut) dans le dossier /etc/barman.d. Le nom du fichier doit se terminer par .conf pour être pris en compte.

L’utilisateur système barman est utilisé pour les connexions SSH. Il faut donc penser à générer ses clés RSA, les échanger et établir une première connexion avec les serveurs hébergeant les instances PostgreSQL à sauvegarder.

Dans le cadre de la mise en place de sauvegardes avec un stockage des données sur un serveur tiers, la plupart des outils et méthodes historiques de sauvegardes reposent sur le protocole SSH et des outils tels que rsync pour assurer les transferts au travers du réseau.

Afin d’automatiser ces transferts via le protocole SSH, il est impératif d’autoriser l’authentification SSH par clé, et d’échanger les clés publiques entre les différents serveurs hébergeant les instances PostgreSQL et le serveur de sauvegarde.

Le paramétrage de l’archivage des journaux de transactions reste classique. La directive archive_command doit faire appel directement à l’outil système en charge du transfert du fichier.

Le paramètre archive_mode peut prendre la valeur always pour permettre un archivage à partir des serveurs secondaires.

Depuis la version 2.6 de Barman, il est recommandé d’utiliser la commande barman-wal-archive intégrée (fournie par le paquet barman-cli) pour gérer l’archivage. Cette commande interagit directement avec Barman pour recevoir le fichier, écrire son contenu via fsync et l’envoyer dans le répertoire incomming adapté. Cela réduit donc le risque de corruption, perte de données ou simplement d’erreur de répertoire.

• barman_home : répertoire racine de travail de Barman, contenant les sauvegardes et les journaux de transactions archivés ;
• barman_user : utilisateur système ;
• log_file : fichier contenant les traces Barman ;
• configuration_files_directory: chemin vers le dossier d’inclusion des fichiers de configuration supplémentaires (défaut : /etc/barman.d) ;
• log_level : niveau de verbosité des traces, par défaut INFO.

• compression : méthode de compression des journaux de transaction - sont disponibles : gzip, bzip2, custom, laissant la possibilité d’utiliser l’utilitaire de compression de son choix (défaut : gzip) ;
• backup_compression : méthode utilisée par Barman pour compresser la sauvegarde, les options disponibles dépendent de la version de PostgreSQL utilisée (la version 15 apporte beaucoup de nouveautés à ce niveau) ;
• immediate_checkpoint : force la création immédiate d’un checkpoint impliquant une augmentation des écritures, le but étant de débuter la sauvegarde le plus rapidement possible (defaut : off) ;
• basebackup_retry_times : nombre de tentative d’écriture d’un fichier - utile pour relancer la copie d’un fichier en cas d’échec sans compromettre le déroulement global de la sauvegarde ;
• basebackup_retry_sleep : spécifié en secondes, il s’agit ici de l’intervalle de temps entre deux tentatives de copie d’un fichier en cas d’échec.

• bandwidth_limit : limitation de l’utilisation de la bande passante réseau lors du transfert de la sauvegarde, s’exprime en kbps (par défaut à 0, autrement dit pas de limitation) ;
• network_compression : activation de la compression à la volée des données lors du transfert réseau de la sauvegarde - utilisé à la sauvegarde ou lors d’une restauration (défaut : false).

• minimum_redundancy : nombre minimum de sauvegardes à conserver - si ce n’est pas respecté, Barman empêchera la suppression (défaut : 0) ;
• retention_policy : définit la politique de rétention en s’exprimant soit en nombre de sauvegarde via la syntaxe REDUNDANCY <valeur>, soit en fenêtre de restauration via la syntaxe RECOVERY OF <valeur> {DAYS | WEEKS | MONTHS} (défaut : aucune rétention appliquée) ;
• last_backup_maximum_age : expression sous la forme <value> {DAYS | WEEKS | MONTHS}, définit l’âge maximal de la dernière sauvegarde - si celui-ci n’est pas respecté, lors de l’utilisation de la commande barman check, une erreur sera levée.

Barman offre la possibilité d’exécuter des commandes externes (scripts) avant et/ou après les opérations de sauvegarde et les opérations d’archivage des journaux de transaction.

Attention, la notion d’archivage de journal de transactions dans ce contexte ne concerne pas l’archivage réalisé depuis l’instance PostgreSQL, qui copie les WAL dans un répertoire <incoming> sur le serveur Barman, mais bien l’opération de récupération du WAL depuis ce répertoire <incoming>.

Barman permet de créer une copie d’un dépôt barman pour répondre à des besoins de redondance géographique. Il suffit pour cela de valoriser le paramètre primary_ssh_command pour que le serveur barman client se connecte au serveur principal et duplique les sauvegardes et WAL.

La commande barman sync-info --primary <instance> <ID-sauvegarde> permet d’afficher les informations de synchronisation. Le processus de copie est lancé automatiquement par la tâche de maintenance automatisée. Il est aussi possible de lancer la synchronisation manuellement pour une sauvegarde en particulier avec barman sync-backup <instance> <ID-sauvegarde> ou pour les WAL avec barman sync-wal <instance>.

Après avoir vu les options globales, nous allons voir à présent les options spécifiques à chaque instance à sauvegarder.

Afin de conserver une certaine souplesse dans la gestion de la configuration Barman, il est recommandé de paramétrer la directive configuration_files_directory de la section [barman] afin de pouvoir charger d’autres fichiers de configuration, permettant ainsi d’isoler la section spécifique à chaque instance à sauvegarder dans son propre fichier de configuration.

La première ligne définit le nom de la section. Ce nom est important et doit être significatif car il sera utilisé lors des tâches d’exploitation pour identifier l’instance cible.

L’idéal est d’utiliser le nom d’hôte ou l’adresse IP du serveur si celui-ci n’héberge qu’une seule instance.

• description : chaîne de caractère servant de descriptif de l’instance ;
• ssh_command : commande shell utilisée pour établir la connexion ssh vers le serveur hébergeant l’instance à sauvegarder ;
• conninfo : chaîne de connexion PostgreSQL.

Tous les autres paramètres, à l’exception de log_file et log_level, peuvent être redéfinis pour chaque instance.

La commande barman replication-status permet d’afficher l’état de la réplication :

$ barman replication-status pgsrv
Status of streaming clients for server 'pgsrv':
  Current LSN on master: 0/140001B0
  Number of streaming clients: 1

  1. Async WAL streamer
     Application name: barman_receive_wal
     Sync stage      : 3/3 Remote write
     Communication   : Unix domain socket
     User name       : barman
     Current state   : streaming (async)
     Replication slot: barman
     WAL sender PID  : 29439
     Started at      : 2022-10-17 14:54:02.122742+00:00
     Sent LSN   : 0/140001B0 (diff: 0 B)
     Write LSN  : 0/140001B0 (diff: 0 B)
     Flush LSN  : 0/14000000 (diff: -432 B)

La commande show-server permet de visualiser la configuration de Barman pour l’instance spécifiée, ou pour toutes les instances si le mot-clé all est utilisé.

La commande check vérifie le bon paramétrage de Barman pour l’instance spécifiée, ou pour toutes les instances si le mot-clé all est utilisé.

Elle permet de s’assurer que les points clés sont fonctionnels, tels que l’accès SSH, l’archivage des journaux de transaction (archive_command, archive_mode…), la politique de rétention, la compression, etc.

Il est possible d’utiliser l’option --nagios qui permet de formater la sortie de la commande check et de l’utiliser en tant que sonde Nagios.

Exemple de sortie de la commande show-server :

$ barman show-server pgsrv
Server pgsrv:
  active: True
  archive_command: None
  archive_mode: None
  archiver: True
  archiver_batch_size: 0
  backup_directory: /var/lib/barman/pgsrv
  backup_method: rsync
  backup_options: BackupOptions(['exclusive_backup'])
  bandwidth_limit: None
  barman_home: /var/lib/barman
  barman_lock_directory: /var/lib/barman
  basebackup_retry_sleep: 30
  basebackup_retry_times: 0
  basebackups_directory: /var/lib/barman/pgsrv/base
  check_timeout: 30
  compression: None
  conninfo: host=pgsrv user=postgres dbname=postgres
  create_slot: manual
  current_xlog: None
  custom_compression_filter: None
  custom_decompression_filter: None
  data_directory: None
  description: PostgreSQL Instance pgsrv
  disabled: False
  errors_directory: /var/lib/barman/pgsrv/errors
  immediate_checkpoint: False
  incoming_wals_directory: /var/lib/barman/pgsrv/incoming
  is_in_recovery: None
  is_superuser: None
  last_backup_maximum_age: None
  max_incoming_wals_queue: None
  minimum_redundancy: 0
  msg_list: []
  name: pgsrv
  network_compression: False
  parallel_jobs: 1
  passive_node: False
  path_prefix: None
  pgespresso_installed: None
  post_archive_retry_script: None
  post_archive_script: None
  post_backup_retry_script: None
  post_backup_script: None
  post_delete_retry_script: None
  post_delete_script: None
  post_recovery_retry_script: None
  post_recovery_script: None
  post_wal_delete_retry_script: None
  post_wal_delete_script: None
  postgres_systemid: None
  pre_archive_retry_script: None
  pre_archive_script: None
  pre_backup_retry_script: None
  pre_backup_script: None
  pre_delete_retry_script: None
  pre_delete_script: None
  pre_recovery_retry_script: None
  pre_recovery_script: None
  pre_wal_delete_retry_script: None
  pre_wal_delete_script: None
  primary_ssh_command: None
  recovery_options: RecoveryOptions([])
  replication_slot: None
  replication_slot_support: None
  retention_policy: None
  retention_policy_mode: auto
  reuse_backup: link
  server_txt_version: None
  slot_name: None
  ssh_command: ssh postgres@pgsrv
  streaming_archiver: False
  streaming_archiver_batch_size: 0
  streaming_archiver_name: barman_receive_wal
  streaming_backup_name: barman_streaming_backup
  streaming_conninfo: host=pgsrv user=postgres dbname=postgres
  streaming_wals_directory: /var/lib/barman/pgsrv/streaming
  synchronous_standby_names: None
  tablespace_bandwidth_limit: None
  wal_retention_policy: main
  wals_directory: /var/lib/barman/pgsrv/wals

Exemple de sortie de la commande check :

$ barman check pgsrv
Server pgsrv:
  PostgreSQL: OK
  superuser or standard user with backup privileges: OK
  PostgreSQL streaming: OK
  wal_level: OK
  replication slot: OK
  directories: OK
  retention policy settings: OK
  backup maximum age: OK (no last_backup_maximum_age provided)
  backup minimum size: OK (33.6 MiB)
  wal maximum age: OK (no last_wal_maximum_age provided)
  wal size: OK (0 B)
  compression settings: OK
  failed backups: OK (there are 0 failed backups)
  minimum redundancy requirements: OK (have 2 backups, expected at least 0)
  pg_basebackup: OK
  pg_basebackup compatible: OK
  pg_basebackup supports tablespaces mapping: OK
  systemid coherence: OK
  pg_receivexlog: OK
  pg_receivexlog compatible: OK
  receive-wal running: OK
  archiver errors: OK

La commande status retourne de manière détaillée le statut de l’instance spécifiée, ou de toutes si le mot-clé all est utilisé.

Les informations renvoyées sont, entre autres :

• la description extraite du fichier de configuration de Barman ;
• la version de PostgreSQL ;
• si l’extension pgespresso est utilisée ;
• l’emplacement des données sur l’instance (PGDATA) ;
• la valeur de l’archive_command ;
• des informations sur les journaux de transactions :
  • position courante
  • dernier segment archivé
• des informations sur les sauvegardes :
  • nombre de sauvegarde
  • ID de la première sauvegarde
  • ID de la dernière sauvegarde
  • politique de rétention

Exemple de sortie de la commande :

$ barman status pgsrv
Server pgsrv:
  Description: PostgreSQL Instance pgsrv
  Active: True
  Disabled: False
  PostgreSQL version: 12.1
  Cluster state: in production
  pgespresso extension: Not available
  Current data size: 24.4 MiB
  PostgreSQL Data directory: /var/lib/pgsql/12/data
  Current WAL segment: 000000010000000000000004
  PostgreSQL 'archive_command' setting: barman-wal-archive localhost pgsrv %p
  Last archived WAL: 000000010000000000000003, at Wed Dec 11 11:44:12 2019
  Failures of WAL archiver: 52 (000000010000000000000001 at Wed Dec 11 11:44:04 2019)
  Server WAL archiving rate: 1.41/hour
  Passive node: False
  Retention policies: not enforced
  No. of available backups: 0
  First available backup: None
  Last available backup: None
  Minimum redundancy requirements: satisfied (0/0)

La commande diagnose retourne les informations importantes concernant toutes les instances à sauvegarder, en donnant par exemple les versions de chacun des composants utilisés.

Elle reprend également les informations retournées par la commande status, le tout au format JSON.

La commande backup lance immédiatement une nouvelle sauvegarde, pour une seule instance si un identifiant est passé en argument, ou pour toutes les instances configurées si le mot-clé all est utilisé.

L’option --wait permet d’attendre que les WAL soient archivés avant de rendre la main.

Exemple de sortie de la commande :

$ barman backup pgsrv
Starting backup using rsync-exclusive method for server pgsrv in
                                  /var/lib/barman/pgsrv/base/20191211T121244
Backup start at LSN: 0/5000028 (000000010000000000000005, 00000028)
This is the first backup for server pgsrv
WAL segments preceding the current backup have been found:
  000000010000000000000001 from server pgsrv has been removed
  000000010000000000000002 from server pgsrv has been removed
  000000010000000000000003 from server pgsrv has been removed
Starting backup copy via rsync/SSH for 20191211T121244
Copy done (time: 1 second)
This is the first backup for server pgsrv
Asking PostgreSQL server to finalize the backup.
Backup size: 24.3 MiB. Actual size on disk: 24.3 MiB (-0.00% deduplication ratio).
Backup end at LSN: 0/5000138 (000000010000000000000005, 00000138)
Backup completed (start time: 2019-12-11 12:12:44.788598, elapsed time: 5 seconds)
Processing xlog segments from file archival for pgsrv
  000000010000000000000004
  000000010000000000000005
  000000010000000000000005.00000028.backup

Liste les sauvegardes du catalogue, soit par instance, soit toutes si le mot-clé all est passé en argument.

Exemple de sortie de la commande :

$ barman list-backup pgsrv
pgsrv 20191211T121244 - Wed Dec 11 12:12:47 2019 - Size: 40.3 MiB -
                        WAL Size: 0 B

La commande show-backup affiche toutes les informations relatives à une sauvegarde en particulier, comme l’espace disque occupé, le nombre de journaux de transactions associés, etc.

La commande list-files permet quant à elle d’afficher la liste complète des fichiers contenus dans la sauvegarde.

Exemple de sortie de la commande show-backup :

$ barman show-backup pgsrv 20191211T121244
Backup 20191211T121244:
  Server Name            : pgsrv
  System Id              : 6769104211696624889
  Status                 : DONE
  PostgreSQL Version     : 120001
  PGDATA directory       : /var/lib/pgsql/12/data

  Base backup information:
    Disk usage           : 24.3 MiB (40.3 MiB with WALs)
    Incremental size     : 24.3 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 000000010000000000000005
    End WAL              : 000000010000000000000005
    WAL number           : 1
    Begin time           : 2019-12-11 12:12:44.526305+01:00
    End time             : 2019-12-11 12:12:47.794687+01:00
    Copy time            : 1 second + 1 second startup
    Estimated throughput : 14.3 MiB/s
    Begin Offset         : 40
    End Offset           : 312
    Begin LSN           : 0/5000028
    End LSN             : 0/5000138

  WAL information:
    No of files          : 0
    Disk usage           : 0 B
    Last available       : 000000010000000000000005

  Catalog information:
    Retention Policy     : not enforced
    Previous Backup      : - (this is the oldest base backup)
    Next Backup          : - (this is the latest base backup)

La suppression d’une sauvegarde nécessite de spécifier l’instance ciblée et l’identifiant de la sauvegarde à supprimer.

Cet identifiant peut être trouvé en utilisant la commande Barman list-backup.

Si le nombre de sauvegardes (après suppression) ne devait pas respecter le seuil défini par la directive minimum_redundancy, la suppression ne sera alors pas possible.

Il est possible de marquer une sauvegarde pour qu’elle soit conservée par barman quelle que soit la rétention configurée avec la commande barman keep <instance> <ID-sauvegarde>.

La sauvegarde peut être relâchée en ajoutant le paramètre --release.

La commande cron permet d’exécuter les tâches de maintenance qui doivent être exécutées périodiquement, telles que l’archivage des journaux de transactions (déplacement du dossier incoming_wals/ vers wals/), ou la compression.

L’application de la politique de rétention est également faite dans ce cadre.

Le démarrage de la commande pg_recievewal est aussi gérée par ce biais.

L’exécution de cette commande doit donc être planifiée via votre ordonnanceur préféré (cron d’Unix par exemple), par exemple toutes les minutes.

Si vous avez installé Barman via les paquets (rpm ou debian), une tâche cron exécutée toutes les minutes a été créée automatiquement.

Le processus de restauration géré par Barman reste classique, mais nécessite tout de même quelques points d’attention.

En particulier, les fichiers de configuration sauvegardés sont restaurés dans le dossier $PGDATA, or ce n’est potentiellement pas le bon emplacement selon le type d’installation / configuration de l’instance. Dans une installation basée sur les paquets Debian/Ubuntu par exemple, les fichiers de configuration se trouvent dans /etc/postgresql/<version>/<instance> et non dans le répertoire PGDATA. Il convient donc de penser à les supprimer du PGDATA s’ils n’ont rien à y faire avant de démarrer l’instance.

De même, la directive de configuration archive_command est passée à false par Barman. Une fois l’instance démarrée et fonctionnelle, il convient de modifier la valeur de ce paramètre pour réactiver l’archivage des journaux de transactions.

Au niveau de la restauration, Barman offre la possibilité de restaurer soit en local (sur le serveur où se trouvent les sauvegardes), soit à distance.

Le cas le plus commun est une restauration à distance, car les sauvegardes sont généralement centralisées sur le serveur de sauvegarde d’où Barman est exécuté.

Pour la restauration à distance, Barman s’appuie sur la couche SSH pour le transfert des données.

Barman supporte différents types de cibles dans le temps pour la restauration :

• timeline : via l’option --target-tli, lorsqu’une divergence de timeline a eu lieu, il est possible de restaurer et rejouer toutes les transactions d’une timeline particulière ;
• date : via l’option --target-time au format YYYY-MM-DD HH:MM:SS.mmm, spécifie une date limite précise dans le temps au delà de laquelle la procédure de restauration arrête de rejouer les transactions ;
• identifiant de transaction : via l’option --target-xid, restauration jusqu’à une transaction précise ;
• point de restauration : via l’option --target-name, restauration jusqu’à un point de restauration créé préalablement sur l’instance via l’appel à la fonction pg_create_restore_point(nom).

Barman permet également de relocaliser un tablespace lors de la restauration.

Ceci est utile lorsque l’on souhaite restaurer une sauvegarde sur un serveur différent, ne disposant pas des même points de montage des volumes que l’instance originelle.

Dans cet exemple, nous souhaitons effectuer une restauration à distance via l’option --remote-ssh-command, prenant en argument "ssh postgres@pgsrv" correspondant à la commande SSH pour se connecter au serveur à restaurer.

L’option --target-time définit ici le point de restauration dans le temps comme étant la date « 2019-12-11 14:00:00 ».

Les trois derniers arguments sont :

• l’identifiant de l’instance dans le fichier de configuration de Barman : pgsrv ;
• l’identifiant de la sauvegarde cible : 20191211T121244 ;
• et enfin le dossier PGDATA de l’instance à restaurer.

pitrery est un outil de gestion de sauvegarde physique et restauration PITR, écrit en bash, issu du labo R&D de Dalibo.

Il est compatible avec tous les environnements Unix/Linux disposant de l’interpréteur de shell bash, et supporte toutes les versions de PostgreSQL depuis la 8.2 jusqu’à la version 14, qui est la dernière version supportée.

Site Web de pitrery.

Du fait du dynamisme du projet, l’écosystème des outils autour de PostgreSQL est très changeant.

À côté des outils évoqués ci-dessus, que nous recommandons, on trouve de nombreux projets autour du thème de la gestion des sauvegardes.

Certains de ces projets répondent à des problématiques spécifiques, d’autres sont assez anciens et plus guère maintenus (comme WAL-E), rendus inutiles par l’évolution de PostgreSQL ces dernières années (comme walmgr, de la suite Skytools, ou OmniPITR) ou simplement peu actifs et peu rencontrés en production (par exemple pg_rman, développé par NTT).

Le plus intéressant et actif est sans doute WAL-G.

WAL-G est une réécriture d’un ancien outil assez populaire, WAL-E, par Citus et Yandex, et actif.

De par sa conception, il est optimisé pour l’archivage des journaux de transactions vers des stockages cloud (Amazon S3, Google, Yandex), la compression multiprocesseur par différents algorithmes et l’optimisation du temps de restauration. Il supporte aussi MySQL et SQL Server (et d’autres dans le futur).

Nous venons de vous présenter des outils qui vont vous permettre de vous simplifier la tâche dans la mise en place d’une solution de sauvegarde fiable et robuste de vos instance PostgreSQL.

Cependant, leur maîtrise passera par de la pratique, et en particulier, la pratique de la restauration.

Le jour où la restauration d’une instance de production se présente, ce n’est généralement pas une situation confortable à cause du stress lié à une perte/corruption de données, interruption du service, etc. Autant maîtriser les outils qui vous permettront de sortir de ce mauvais pas.

N’oubliez pas également l’importance de la supervision des sauvegardes !

Installer pgBackRest à partir des paquets du PGDG.

En vous aidant de https://pgbackrest.org/user-guide.html#quickstart : - configurer pgBackRest pour sauvegarder le serveur PostgreSQL en local dans /var/lib/pgsql/1/backups ; - le nom de la stanza sera instance_dev ; - prévoir de ne conserver qu’une seule sauvegarde complète.

Configurer l’archivage des journaux de transactions de PostgreSQL avec pgBackRest.

Initialiser le répertoire de stockage des sauvegardes et vérifier la configuration de l’archivage.

Lancer une sauvegarde complète. Afficher les détails de cette sauvegarde.

Ajouter des données :

• ajouter une table avec 1 million de lignes ;
• forcer la rotation du journal de transaction courant (pg_switch_wal) pour s’assurer que les dernières modifications sont archivées ;
• vérifier que le journal concerné est bien dans les archives.

Simulation d’un incident : supprimer tout le contenu de la table.

Restaurer les données avant l’incident à l’aide de pgBackRest.

Utilisation de barman (Optionnel)

Installer barman depuis les dépôts communautaires (la documentation est sur https://www.pgbarman.org/documentation/).

Configurer barman pour la sauvegarde du serveur via Streaming Replication (pg_basebackup et pg_receivewal).

Vérifier que l’archivage fonctionne et que la configuration de barman est correcte.

Faire une sauvegarde.

Ajouter des données :

• ajouter une table avec 1 million de lignes ;
• forcer la rotation du journal de transaction courant pour garantir que les dernières modifications sont archivées.

Vérifier que le journal concerné est bien dans les archives.