Gestion d’un sinistre

Module I5

Dalibo SCOP

25.03

12 mars 2025

Sur ce document

Formation	Module I5
Titre	Gestion d’un sinistre
Révision	25.03
PDF	https://dali.bo/i5_pdf
EPUB	https://dali.bo/i5_epub
HTML	https://dali.bo/i5_html
Slides	https://dali.bo/i5_slides
TP	https://dali.bo/i5_tp
TP (solutions)	https://dali.bo/i5_solutions

Chers lectrices & lecteurs,

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

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

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

À propos de DALIBO

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

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

Remerciements

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

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

Forme de ce manuel

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

Licence Creative Commons CC-BY-NC-SA

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

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

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Marques déposées

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

Versions de PostgreSQL couvertes

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

PostgreSQL : Gestion d’un sinistre

Introduction

Une bonne politique de sauvegardes est cruciale
- mais elle n’empêche pas les incidents
Il faut être prêt à y faire face

Anticiper les désastres
Réagir aux désastres
Rechercher l’origine du problème
Outils utiles
Cas type de désastres

Anticiper les désastres

Un désastre peut toujours survenir
Il faut savoir le détecter le plus tôt possible
- et s’être préparé à y répondre

Documentation

Documentation complète et à jour
- emplacement et fréquence des sauvegardes
- emplacement des traces
- procédures et scripts d’exploitation
Sauvegarder et versionner la documentation

Par nature, les désastres arrivent de façon inattendue.

Il faut donc se préparer à devoir agir en urgence, sans préparation, dans un environnement perturbé et stressant — par exemple, en pleine nuit, la veille d’un jour particulièrement critique pour l’activité de la production.

Un des premiers points d’importance est donc de s’assurer de la présence d’une documentation claire, précise et à jour, afin de minimiser le risque d’erreurs humaines.

Cette documentation devrait détailler l’architecture dans son ensemble, et particulièrement la politique de sauvegarde choisie, l’emplacement de celles-ci, les procédures de restauration et éventuellement de bascule vers un environnement de secours.

Les procédures d’exploitation doivent y être expliquées, de façon détaillée mais claire, afin qu’il n’y ait pas de doute sur les actions à effectuer une fois la cause du problème identifié.

La méthode d’accès aux informations utiles (traces de l’instance, du système, supervision…) devrait également être soigneusement documentée afin que le diagnostic du problème soit aussi simple que possible.

Toutes ces informations doivent être organisées de façon claire, afin qu’elles soient immédiatement accessibles et exploitables aux intervenants lors d’un problème.

Il est évidemment tout aussi important de penser à versionner et sauvegarder cette documentation, afin que celle-ci soit toujours accessible même en cas de désastre majeur (perte d’un site).

Procédures et scripts

Procédures détaillées de restauration / PRA
- préparer des scripts / utiliser des outils
- minimiser le nombre d’actions manuelles
Tester les procédures régulièrement
- bases de test, développement…
- s’assurer que chacun les maîtrise
Sauvegarder et versionner les scripts

La gestion d’un désastre est une situation particulièrement stressante, le risque d’erreur humaine est donc accru.

Un DBA devant restaurer d’urgence l’instance de production en pleine nuit courera plus de risques de faire une fausse manipulation s’il doit taper une vingtaine de commandes en suivant une procédure dans une autre fenêtre (voire un autre poste) que s’il n’a qu’un script à exécuter.

En conséquence, il est important de minimiser le nombre d’actions manuelles à effectuer dans les procédures, en privilégiant l’usage de scripts d’exploitation ou d’outils dédiés (comme pgBackRest ou barman pour restaurer une instance PostgreSQL).

Néanmoins, même cette pratique ne suffit pas à exclure tout risque.

L’utilisation de ces scripts ou de ces outils doit également être comprise, correctement documentée, et les procédures régulièrement testées. Le test idéal consiste à remonter fréquemment des environnements de développement et de test ; vos développeurs vous en seront d’ailleurs reconnaissants.

Dans le cas contraire, l’utilisation d’un script ou d’un outil peut aggraver le problème, parfois de façon dramatique — par exemple, l’écrasement d’un environnement sain lors d’une restauration parce que la procédure ne mentionne pas que le script doit être lancé depuis un serveur particulier.

L’aspect le plus important est de s’assurer par des tests réguliers et manuels que les procédures sont à jour, n’ont pas de comportement inattendu, et sont maîtrisées par toute l’équipe d’exploitation.

Tout comme pour la documentation, les scripts d’exploitation doivent également être sauvegardés et versionnés.

Supervision et historisation

Tout doit être supervisé
- réseau, matériel, système, logiciels…
- les niveaux d’alerte doivent être significatifs
Les métriques importantes doivent être historisées
- cela permet de retrouver le moment où le problème est apparu
- quand cela a un sens, faire des graphes

La supervision est un sujet vaste, qui touche plus au domaine de la haute disponibilité.

Un désastre sera d’autant plus difficile à gérer qu’il est détecté tard. La supervision en place doit donc être pensée pour détecter tout type de défaillance (penser également à superviser la supervision !).

Attention à bien calibrer les niveaux d’alerte, la présence de trop de messages augmente le risque que l’un d’eux passe inaperçu, et donc que l’incident ne soit détecté que tardivement.

Pour aider la phase de diagnostic de l’origine du problème, il faut prévoir d’historiser un maximum d’informations.

La présentation de celles-ci est également importante : il est plus facile de distinguer un pic brutal du nombre de connexions sur un graphique que dans un fichier de traces de plusieurs Go !

Automatisation

Des outils existent
- Patroni, PAF (Pacemaker)
Automatiser une bascule est complexe
- cela peut mener à davantage d’incidents
- voire à des désastres (split brain)

Si on poursuit jusqu’au bout le raisonnement précédent sur le risque à faire effectuer de nombreuses opérations manuelles lors d’un incident, la conclusion logique est que la solution idéale serait de les éliminer complètement, et d’automatiser complètement le déclenchement et l’exécution de la procédure.

Un problème est que toute solution visant à automatiser une tâche se base sur un nombre limité de paramètres et sur une vision restreinte de l’architecture.

De plus, il est difficile à un outil de bascule automatique de diagnostiquer correctement certains types d’incident, par exemple une partition réseau. L’outil peut donc détecter à tort à un incident, surtout s’il est réglé de façon à être assez sensible, et ainsi provoquer lui-même une coupure de service inutile.

Dans le pire des cas, l’outil peut être amené à prendre une mauvaise décision amenant à une situation de désastre, comme un split brain (deux instances PostgreSQL se retrouvent ouvertes en écriture en même temps sur les mêmes données).

Il est donc fortement préférable de laisser un administrateur prendre les décisions potentiellement dangereuses, comme une bascule ou une restauration.

Réagir aux désastres

Savoir identifier un problème majeur
Bons réflexes
Mauvais réflexes

Symptômes d’un désastre

Crash de l’instance
Résultats de requêtes erronnés
Messages d’erreurs dans les traces
Dégradation importante des temps d’exécution
Processus manquants
- ou en court d’exécution depuis trop longtemps

De très nombreux éléments peuvent aider à identifier que l’on est en situation d’incident grave.

Le plus flagrant est évidemment le crash complet de l’instance PostgreSQL, ou du serveur l’hébergeant, et l’impossibilité pour PostgreSQL de redémarrer.

Les désastres les plus importants ne sont toutefois pas toujours aussi simples à détecter.

Les crash peuvent se produire uniquement de façon ponctuelle, et il existe des cas où l’instance redémarre immédiatement après (typiquement suite au kill -9 d’un processus backend PostgreSQL).

Cas encore plus délicat, il peut également arriver que les résultats de requêtes soient erronés (par exemple en cas de corruption de fichiers d’index) sans qu’aucune erreur n’apparaisse.

Les symptômes classiques permettant de détecter un problème majeur sont :

la présence de messages d’erreurs dans les traces de PostgreSQL (notamment des messages PANIC ou FATAL, mais les messages ERROR et WARNING sont également très significatifs, particulièrement s’ils apparaissent soudainement en très grand nombre) ;
la présence de messages d’erreurs dans les traces du système d’exploitation (notamment concernant la mémoire ou le système de stockage) ;
le constat d’une dégradation importante des temps d’exécution des requêtes sur l’instance ;
l’absence de certains processus critiques de PostgreSQL ;
la présence de processus présents depuis une durée inhabituelle (plusieurs semaines, mois…).

Bons réflexes 1

Garder la tête froide
Répartir les tâches clairement
Minimiser les canaux de communication
Garder des notes de chaque action entreprise

Une fois que l’incident est repéré, il est important de ne pas foncer tête baissée dans des manipulations.

Il faut bien sûr prendre en considération la criticité du problème, notamment pour définir la priorité des actions (par exemple, en cas de perte totale d’un site, quelles sont les applications à basculer en priorité ?), mais quelle que soit la criticité ou l’impact, il ne faut jamais effectuer une action sans en avoir parfaitement saisi l’impact et s’être assuré qu’elle répondait bien au problème rencontré.

Si le travail s’effectue en équipe, il faut bien faire attention à répartir les tâches clairement, afin d’éviter des manipulations concurrentes ou des oublis qui pourraient aggraver la situation.

Il faut également éviter de multiplier les canaux de communication, cela risque de favoriser la perte d’information, ce qui est critique dans une situation de crise.

Surtout, une règle majeure est de prendre le temps de noter systématiquement toutes les actions entreprises.

Les commandes passées, les options utilisées, l’heure d’exécution, toutes ces informations sont très importantes, déjà pour pouvoir agir efficacement en cas de fausse manipulation, mais également pour documenter la gestion de l’incident après coup, et ainsi en conserver une trace qui sera précieuse si celui-ci venait à se reproduire.

Bons réflexes 2

Se prémunir contre une aggravation du problème
- couper les accès applicatifs
Si une corruption est suspectée
- arrêter immédiatement l’instance
- faire une sauvegarde immédiate des fichiers
- travailler sur une copie

S’il y a suspicion de potentielle corruption de données, il est primordial de s’assurer au plus vite de couper tous les accès applicatifs vers l’instance afin de ne pas aggraver la situation.

Il est généralement préférable d’avoir une coupure de service plutôt qu’un grand volume de données irrécupérables.

Ensuite, il faut impérativement faire une sauvegarde complète de l’instance avant de procéder à toute manipulation. En fonction de la nature du problème rencontré, le type de sauvegarde pouvant être effectué peut varier (un export de données ne sera possible que si l’instance est démarrée et que les fichiers sont lisibles par exemple). En cas de doute, la sauvegarde la plus fiable qu’il est possible d’effectuer est une copie des fichiers à froid (instance arrêtée) - toute autre action (y compris un export de données) pourrait avoir des conséquences indésirables.

Si des manipulations doivent être tentées pour tenter de récupérer des données, il faut impérativement travailler sur une copie de l’instance, restaurée à partir de cette sauvegarde. Ne jamais travailler directement sur une instance de production corrompue, la moindre action (même en lecture) pourrait aggraver le problème !

Pour plus d’information, voir sur le wiki PostgreSQL.

Bons réflexes 3

Déterminer le moment de démarrage du désastre
Adopter une vision générale plutôt que focalisée sur un détail
Remettre en cause chaque élément de l’architecture
- aussi stable (et/ou coûteux/complexe) soit-il
Éliminer en priorité les causes possibles côté hardware, système
Isoler le comportement précis du problème
- identifier les requêtes / tables / index impliqués

La première chose à identifier est l’instant précis où le problème a commencé à se manifester. Cette information est en effet déterminante pour identifier la cause du problème, et le résoudre — notamment pour savoir à quel instant il faut restaurer l’instance si cela est nécessaire.

Il convient pour cela d’utiliser les outils de supervision et de traces (système, applicatif et PostgreSQL) pour remonter au moment d’apparition des premiers symptômes. Attention toutefois à ne pas confondre les symptômes avec le problème lui-même ! Les symptômes les plus visibles ne sont pas forcément apparus les premiers. Par exemple, la charge sur la machine est un symptôme, mais n’est jamais la cause du problème. Elle est liée à d’autres phénomènes, comme des problèmes avec les disques ou un grand nombre de connexions, qui peuvent avoir commencé à se manifester bien avant que la charge ne commence réellement à augmenter.

Si la nature du problème n’est pas évidente à ce stade, il faut examiner l’ensemble de l’architecture en cause, sans en exclure d’office certains composants (baie de stockage, progiciel…), quels que soient leur complexité / coût / stabilité supposés. Si le comportement observé côté PostgreSQL est difficile à expliquer (crashs plus ou moins aléatoires, nombreux messages d’erreur sans lien apparent…), il est préférable de commencer par s’assurer qu’il n’y a pas un problème de plus grande ampleur (système de stockage, virtualisation, réseau, système d’exploitation).

Un bon indicateur consiste à regarder si d’autres instances / applications / processus rencontrent des problèmes similaires.

Ensuite, une fois que l’ampleur du problème a été cernée, il faut procéder méthodiquement pour en déterminer la cause et les éléments affectés.

Pour cela, les informations les plus utiles se trouvent dans les traces, généralement de PostgreSQL ou du système, qui vont permettre d’identifier précisément les éventuels fichiers ou relations corrompus.

Bons réflexes 4

En cas de défaillance matérielle
- s’assurer de corriger sur du hardware sain et non affecté !
- baies partagées…

Bons réflexes 5

Communiquer, ne pas rester isolé
Demander de l’aide si le problème est trop complexe
- autres équipes
- support
- forums
- listes

La communication est très importante dans la gestion d’un désastre.

Il est préférable de minimiser le nombre de canaux de communication plutôt que de les multiplier (téléphone, e-mail, chat, ticket…), ce qui pourrait amener à une perte d’informations et à des délais indésirables.

Il est primordial de rapidement cerner l’ampleur du problème, et pour cela il est généralement nécessaire de demander l’expertise d’autres administrateurs / équipes (applicatif, système, réseau, virtualisation, SAN…). Il ne faut pas rester isolé et risquer que la vision étroite que l’on a des symptômes (notamment en terme de supervision / accès aux traces) empêche l’identification de la nature réelle du problème.

Si la situation semble échapper à tout contrôle, et dépasser les compétences de l’équipe en cours d’intervention, il faut chercher de l’aide auprès de personnes compétentes, par exemple auprès d’autres équipes, du support.

En aucun cas, il ne faut se mettre à suivre des recommandations glanées sur Internet, qui ne se rapporteraient que très approximativement au problème rencontré, voire pas du tout. Si nécessaire, on trouve en ligne des forums et des listes de discussions spécialisées sur lesquels il est également possible d’obtenir des conseils — il est néanmoins indispensable de prendre en compte que les personnes intervenant sur ces médias le font de manière bénévole. Il est déraisonnable de s’attendre à une réaction immédiate, aussi urgent le problème soit-il, et les suggestions effectuées le sont sans aucune garantie.

Bons réflexes 6

Dérouler les procédures comme prévu
En cas de situation non prévue, s’arrêter pour faire le point
- ne pas hésiter à remettre en cause l’analyse
- ou la procédure elle-même

Dans l’idéal, des procédures détaillant les actions à effectuer ont été écrites pour le cas de figure rencontré. Dans ce cas, une fois que l’on s’est assuré d’avoir identifié la procédure appropriée, il faut la dérouler méthodiquement, point par point, et valider à chaque étape que tout se déroule comme prévu.

Si une étape de la procédure ne se passe pas comme prévu, il ne faut pas tenter de poursuivre tout de même son exécution sans avoir compris ce qui s’est passé et les conséquences. Cela pourrait être dangereux.

Il faut au contraire prendre le temps de comprendre le problème en procédant comme décrit précédemment, quitte à remettre en cause toute l’analyse menée auparavant, et la procédure ou les scripts utilisés.

C’est également pour parer à ce type de cas de figure qu’il est important de travailler sur une copie et non sur l’environnement de production directement.

Bons réflexes 7

En cas de bug avéré
- tenter de le cerner et de le reproduire au mieux
- le signaler à la communauté de préférence (configuration, comment reproduire)

Ce n’est heureusement pas fréquent, mais il est possible que l’origine du problème soit liée à un bug de PostgreSQL lui-même.

Dans ce cas, la méthodologie appropriée consiste à essayer de reproduire le problème le plus fidèlement possible et de façon systématique, pour le cerner au mieux.

Il est ensuite très important de le signaler au plus vite à la communauté, généralement sur la liste pgsql-bugs@postgresql.org (cela nécessite une inscription préalable), en respectant les règles définies dans la documentation.

Notamment (liste non exhaustive) :

indiquer la version précise de PostgreSQL installée, et la méthode d’installation utilisée ;
préciser la plate-forme utilisée, notamment la version du système d’exploitation utilisé et la configuration des ressources du serveur ;
signaler uniquement les faits observés, éviter les spéculations sur l’origine du problème ;
joindre le détail des messages d’erreurs observés (augmenter la verbosité des erreurs avec le paramètre log_error_verbosity) ;
joindre un cas complet permettant de reproduire le problème de façon aussi simple que possible.

Pour les problèmes relevant du domaine de la sécurité (découverte d’une faille), la liste adéquate est security@postgresql.org.

Bons réflexes 8

Après correction
Tester complètement l’intégrité des données
- pour détecter tous les problèmes
Validation avec export logique complet

pg_dumpall > /dev/null

ou sauvegarde physique
- pg_basebackup…
Reconstruction dans une autre instance (vérification de cohérence)

pg_dumpall | psql -h autre serveur

Une fois les actions correctives réalisées (restauration, recréation d’objets, mise à jour des données…), il faut tester intensivement pour s’assurer que le problème est bien complètement résolu.

Il est donc extrêmement important d’avoir préparé des cas de tests permettant de reproduire le problème de façon certaine, afin de valider la solution appliquée.

En cas de suspicion de corruption de données, il est également important de tenter de procéder à la lecture de la totalité des données depuis PostgreSQL.

Un premier outil pour cela est une sauvegarde physique avec pg_basebackup ou un autre outil (voir plus loin).

Alternativement, la commande suivante, exécutée avec l’utilisateur système propriétaire de l’instance (généralement postgres) effectue une lecture complète de toutes les tables (et uniquement les tables), sans nécessiter de place sur disque supplémentaire :

$ pg_dumpall > /dev/null

Sous Windows Powershell, la commande est :

PS C:\ pg_dumpall > $null

Cette commande ne devrait renvoyer aucune erreur. En cas de problème, notamment une somme de contrôle qui échoue, une erreur apparaîtra :

pg_dump: WARNING:  page verification failed, calculated checksum 20565 but expected 17796
pg_dump: erreur : Sauvegarde du contenu de la table « corrompue » échouée :
                  échec de PQgetResult().
pg_dump: erreur : Message d'erreur du serveur :
         ERROR:  invalid page in block 0 of relation base/104818/104828
pg_dump: erreur : La commande était : COPY public.corrompue (i) TO stdout;
pg_dumpall: erreur : échec de pg_dump sur la base de données « corruption », quitte

Attention, les fichiers associés aux index ou aux vues matérialisées ne sont pas parcourus lors de la lecture des données par pg_dumpall ou pg_dump. Une corruption physique d’index ne sera donc pas remontée. Une vérification exhaustive implique d’autres outils comme pg_checksums ou pg_basebackup.

De plus, l’absence d’erreur physique ne garantit en aucun cas l’intégrité fonctionnelle des données : les corruptions peuvent très bien être logiques.

Dans les situations les plus extrêmes (problème de stockage, fichiers corrompus), il est important de tester la validité des données dans une nouvelle instance en effectuant un export/import complet des données.

Par exemple, initialiser une nouvelle instance avec initdb, sur un autre système de stockage, voire sur un autre serveur, puis lancer la commande suivante (l’application doit être coupée, ce qui est normalement le cas depuis la détection de l’incident si les conseils précédents ont été suivis) pour exporter et importer à la volée :

$ pg_dumpall -h <serveur_corrompu> -U postgres | psql -h <nouveau_serveur> \
                                                    -U postgres postgres
$ vacuumdb --analyze -h <nouveau_serveur> -U postgres postgres

D’éventuels problèmes peuvent être détectés lors de l’import des données, par exemple si des corruptions entraînent l’échec de la reconstruction de clés étrangères. Il faut alors procéder au cas par cas.

Enfin, même si cette étape s’est déroulée sans erreur, tout risque n’est pas écarté, il reste la possibilité de corruption de données silencieuses. Sauf si la fonctionnalité de checksum de PostgreSQL a été activée sur l’instance (ce n’est pas activé par défaut !), le seul moyen de détecter ce type de problème est de valider les données fonctionnellement.

Dans tous les cas, en cas de suspicion de corruption de données en profondeur, il est fortement préférable d’accepter une perte de données et de restaurer une sauvegarde d’avant le début de l’incident, plutôt que de continuer à travailler avec des données dont l’intégrité n’est pas assurée.

Mauvais réflexes 1

Paniquer
Prendre une décision hâtive
- exemple, supprimer des fichiers du répertoire pg_wal
Lancer une commande sans la comprendre, par exemple :
- pg_resetwal
- l’extension pg_surgery
- DANGER, dernier espoir

Quelle que soit la criticité du problème rencontré, la panique peut en faire quelque chose de pire.

Il faut impérativement garder son calme, et résister au mieux au stress et aux pressions qu’une situation de désastre ne manque pas de provoquer.

Il est également préférable d’éviter de sauter immédiatement à la conclusion la plus évidente. Il ne faut pas hésiter à retirer les mains du clavier pour prendre de la distance par rapport aux conséquences du problème, réfléchir aux causes possibles, prendre le temps d’aller chercher de l’information pour réévaluer l’ampleur réelle du problème.

La plus mauvaise décision que l’on peut être amenée à prendre lors de la gestion d’un incident est celle que l’on prend dans la précipitation, sans avoir bien réfléchi et mesuré son impact. Cela peut provoquer des dégâts irrécupérables, et transformer une situation d’incident en situation de crise majeure.

Un exemple classique de ce type de comportement est le cas où PostgreSQL est arrêté suite au remplissage du système de fichiers contenant les fichiers WAL, pg_wal.

Le réflexe immédiat d’un administrateur non averti pourrait être de supprimer les plus vieux fichiers dans ce répertoire, ce qui répond bien aux symptômes observés mais reste une erreur dramatique qui va rendre le démarrage de l’instance impossible.

Quoi qu’il arrive, ne jamais exécuter une commande sans être certain qu’elle correspond bien à la situation rencontrée, et sans en maîtriser complètement les impacts. Même si cette commande provient d’un document mentionnant les mêmes messages d’erreur que ceux rencontrés (et tout particulièrement si le document a été trouvé via une recherche hâtive sur Internet) !

Là encore, nous disposons comme exemple d’une erreur malheureusement fréquente, l’exécution de la commande pg_resetwal sur une instance rencontrant un problème. Comme l’indique la documentation, « cette commande ne doit être utilisée qu’en dernier ressort quand le serveur ne démarre plus du fait d’une telle corruption_ » et « il ne faut pas perdre de vue que la base de données peut contenir des données incohérentes du fait de transactions partiellement validées » (documentation). Nous reviendrons ultérieurement sur les (rares) cas d’usage réels de cette commande, mais dans l’immense majorité des cas, l’utiliser va aggraver le problème, en ajoutant des problématiques de corruption logique des données !

Il convient donc de bien s’assurer de comprendre les conséquences de l’exécution de chaque action effectuée.

Mauvais réflexes 2

Arrêter le diagnostic quand les symptômes disparaissent
Ne pas pousser l’analyse jusqu’au bout

Mauvais réflexes 3

Ne pas documenter
- le résultat de l’investigation
- les actions effectuées

Rechercher l’origine du problème

Quelques pistes (liste non exhaustive)

Prérequis

Avant de commencer à creuser
- référencer les symptômes
- identifier au mieux l’instant de démarrage du problème

Recherche d’historique

Ces symptômes ont-ils déjà été rencontrés dans le passé ?
Ces symptômes ont-ils déjà été rencontrés par d’autres ?
Attention à ne pas prendre les informations trouvées pour argent comptant !

Une fois les principaux symptômes identifiés, il est utile de prendre un moment pour déterminer si ce problème est déjà connu.

Notamment, identifier dans la base de connaissances si ces symptômes ont déjà été rencontrés dans le passé (d’où l’importance de bien documenter les problèmes).

Au-delà de la documentation interne, il est également possible de rechercher si ces symptômes ont déjà été rencontrés par d’autres.

Pour ce type de recherche, il est préférable de privilégier les sources fiables (documentation officielle, listes de discussion, plate-forme de support…) plutôt qu’un quelconque document d’un auteur non identifié.

Dans tous les cas, il faut faire très attention à ne pas prendre les informations trouvées pour argent comptant, et ce même si elles proviennent de la documentation interne ou d’une source fiable !

Il est toujours possible que les symptômes soient similaires mais que la cause soit différente. Il s’agit donc ici de mettre en place une base de travail, qui doit être complétée par une observation directe et une analyse.

Matériel

Vérifier le système disque (SAN, carte RAID, disques)
Un fsync est-il bien honoré de l’OS au disque ? (batteries !)
Rechercher toute erreur matérielle
Firmwares pas à jour
- ou récemment mis à jour
Matériel récemment changé

Les défaillances du matériel, et notamment du système de stockage, sont de celles qui peuvent avoir les impacts les plus importants et les plus étendus sur une instance et sur les données qu’elle contient.

Ce type de problème peut également être difficile à diagnostiquer en se contentant d’observer les symptômes les plus visibles. Il est facile de sous-estimer l’ampleur des dégâts.

Parmi les bonnes pratiques, il convient de vérifier la configuration et l’état du système disque (SAN, carte RAID, disques).

Quelques éléments étant une source habituelle de problèmes :

le système disque n’honore pas les ordres fsync ? (SAN ? virtualisation ?) ;
quel est l’état de la batterie du cache en écriture ?

Il faut évidemment rechercher la présence de toute erreur matérielle, au niveau des disques, de la mémoire, des CPU…

Vérifier également la version des firmwares installés. Il est possible qu’une nouvelle version corrige le problème rencontré, ou à l’inverse que le déploiement d’une nouvelle version soit à l’origine du problème.

Dans le même esprit, il faut vérifier si du matériel a récemment été changé. Il arrive que de nouveaux éléments soient défaillants.

Il convient de noter que l’investigation à ce niveau peut être grandement complexifiée par l’utilisation de certaines technologies (virtualisation, baies de stockage), du fait de la mutualisation des ressources, et de la séparation des compétences et des informations de supervision entre différentes équipes.

Virtualisation

Mutualisation excessive
Configuration du stockage virtualisé
Rechercher les erreurs aussi niveau superviseur
Mises à jour non appliquées
- ou appliquées récemment
Modifications de configuration récentes

Tout comme pour les problèmes au niveau du matériel, les problèmes au niveau du système de virtualisation peuvent être complexes à détecter et à diagnostiquer correctement.

Le principal facteur de problème avec la virtualisation est lié à une mutualisation excessive des ressources.

Il est ainsi possible d’avoir un total de ressources allouées aux VM supérieur à celles disponibles sur l’hyperviseur, ce qui amène à des comportements de fort ralentissement, voire de blocage des systèmes virtualisés.

Si ce type d’architecture est couplé à un système de gestion de bascule automatique (Pacemaker, repmgr…), il est possible d’avoir des situations de bascules impromptues, voire des situations de split brain, qui peuvent provoquer des pertes de données importantes. Il est donc important de prêter une attention particulière à l’utilisation des ressources de l’hyperviseur, et d’éviter à tout prix la sur-allocation.

Par ailleurs, lorsque l’architecture inclut une brique de virtualisation, il est important de prendre en compte que certains problèmes ne peuvent être observés qu’à partir de l’hyperviseur, et pas à partir du système virtualisé. Par exemple, les erreurs matérielles ou système risquent d’être invisibles depuis une VM, il convient donc d’être vigilant, et de rechercher toute erreur sur l’hôte.

Il faut également vérifier si des modifications ont été effectuées peu avant l’incident, comme des modifications de configuration ou l’application de mises à jour.

Comme indiqué dans la partie traitant du matériel, l’investigation peut être grandement freinée par la séparation des compétences et des informations de supervision entre différentes équipes. Une bonne communication est alors la clé de la résolution rapide du problème.

Système d’exploitation 1

Erreurs dans les traces
Mises à jour système non appliquées
Modifications de configuration récentes

Après avoir vérifié les couches matérielles et la virtualisation, il faut ensuite s’assurer de l’intégrité du système d’exploitation.

La première des vérifications à effectuer est de consulter les traces du système pour en extraire les éventuels messages d’erreur :

sous Linux, on trouvera ce type d’informations en sortie de la commande dmesg, et dans les fichiers traces du système, généralement situés sous /var/log ;
sous Windows, on consultera à cet effet le journal des événements (les event logs).

Tout comme pour les autres briques, il faut également voir s’il existe des mises à jour des paquets qui n’auraient pas été appliquées, ou à l’inverse si des mises à jour, installations ou modifications de configuration ont été effectuées récemment.

Système d’exploitation 2

Opération d’IO impossible
- FS plein ?
- FS monté en lecture seule ?
Tester l’écriture sur PGDATA
Tester la lecture sur PGDATA

Parmi les problèmes fréquemment rencontrés se trouve l’impossibilité pour PostgreSQL d’accéder en lecture ou en écriture à un ou plusieurs fichiers.

La première chose à vérifier est de déterminer si le système de fichiers sous-jacent ne serait pas rempli à 100% (commande df sous Linux) ou monté en lecture seule (commande mount sous Linux).

On peut aussi tester les opérations d’écriture et de lecture sur le système de fichiers pour déterminer si le comportement y est global :

pour tester une écriture dans le répertoire PGDATA, sous Linux :

$ touch $PGDATA/test_write

pour tester une lecture dans le répertoire PGDATA, sous Linux :

$ cat $PGDATA/PGVERSION

Pour identifier précisément les fichiers présentant des problèmes, il est possible de tester la lecture complète des fichiers dans le point de montage :

$ tar cvf /dev/null $PGDATA

Système d’exploitation 3

Consommation excessive des ressources
- OOM killer (overcommit !)
Après un crash, vérifier les processus actifs
- ne pas tenter de redémarrer si des processus persistent
Outils : sar, atop…

Sous Linux, l’installation d’outils d’aide au diagnostic sur les serveurs est très important pour mener une analyse efficace, particulièrement le paquet systat qui permet d’utiliser la commande sar.

La lecture des traces système et des traces PostgreSQL permettent également d’avancer dans le diagnostic.

Un problème de consommation excessive des ressources peut généralement être anticipée grâce à une supervision sur l’utilisation des ressources et des seuils d’alerte appropriés. Il arrive néanmoins parfois que la consommation soit très rapide et qu’il ne soit pas possible de réagir suffisamment rapidement.

Dans le cas d’une consommation mémoire d’un serveur Linux qui menacerait de dépasser la quantité totale de mémoire allouable, le comportement par défaut de Linux est d’autoriser par défaut la tentative d’allocation.

Si l’allocation dépasse effectivement la mémoire disponible, alors le système va déclencher un processus Out Of Memory Killer (OOM Killer) qui va se charger de tuer les processus les plus consommateurs.

Dans le cas d’un serveur dédié à une instance PostgreSQL, il y a de grandes chances que le processus en question appartienne à l’instance.

S’il s’agit d’un OOM Killer effectuant un arrêt brutal (kill -9) sur un backend, l’instance PostgreSQL va arrêter immédiatement tous les processus afin de prévenir une corruption de la mémoire et les redémarrer.

S’il s’agit du processus principal de l’instance (postmaster), les conséquences peuvent être bien plus dramatiques, surtout si une tentative est faite de redémarrer l’instance sans vérifier si des processus actifs existent encore.

Pour un serveur dédié à PostgreSQL, la recommendation est habituellement de désactiver la sur-allocation de la mémoire, empêchant ainsi le déclenchement de ce phénomène.

Voir pour cela les paramètres kernel vm.overcommit_memory et vm.overcommit_ratio (référence : https://kb.dalibo.com/overcommit_memory).

PostgreSQL

Relever les erreurs dans les traces
- ou messages inhabituels
Vérifier les mises à jour mineures

Tout comme pour l’analyse autour du système d’exploitation, la première chose à faire est rechercher toute erreur ou message inhabituel dans les traces de l’instance. Ces messages sont habituellement assez informatifs, et permettent de cerner la nature du problème. Par exemple, si PostgreSQL ne parvient pas à écrire dans un fichier, il indiquera précisément de quel fichier il s’agit.

Si l’instance est arrêtée suite à un crash, et que les tentatives de redémarrage échouent avant qu’un message puisse être écrit dans les traces, il est possible de tenter de démarrer l’instance en exécutant directement le binaire postgres afin que les premiers messages soient envoyés vers la sortie standard.

Il convient également de vérifier si des mises à jour qui n’auraient pas été appliquées ne corrigeraient pas un problème similaire à celui rencontré.

Identifier les mises à jours appliquées récemment et les modifications de configuration peut également aider à comprendre la nature du problème.

Paramétrage de PostgreSQL : écriture des fichiers

La désactivation de certains paramètres est dangereuse
- fsync
- full_page_write

Si des corruptions de données sont relevées suite à un crash de l’instance, il convient particulièrement de vérifier la valeur du paramètre fsync.

En effet, si celui-ci est désactivé, les écritures dans les journaux de transactions ne sont pas effectuées de façon synchrone, ce qui implique que l’ordre des écritures ne sera pas conservé en cas de crash. Le processus de recovery de PostgreSQL risque alors de provoquer des corruptions si l’instance est malgré tout redémarrée.

Ce paramètre ne devrait jamais être positionné à une autre valeur que on, sauf dans des cas extrêmement particuliers (en bref, si l’on peut se permettre de restaurer intégralement les données en cas de crash, par exemple dans un chargement de données initial).

Le paramètre full_page_write indique à PostgreSQL d’effectuer une écriture complète d’une page chaque fois qu’elle reçoit une nouvelle écriture après un checkpoint, pour éviter un éventuel mélange entre des anciennes et nouvelles données en cas d’écriture partielle.

La désactivation de full_page_write peut avoir le même type de conséquences catastrophiques que celle de fsync !

Paramétrage de PostgreSQL : les sommes de contrôle

Activez les checksums !
- pas (encore ?) par défaut
- initdb --data-checksums (création)
- pg_checksums --enable (à posteriori)
Détecte les corruptions silencieuses
Impact faible sur les performances
Vérification :
- pg_checksums --check
- outils de sauvegarde et lecture

PostgreSQL ne verrouille pas tous les fichiers dès son ouverture. Sans mécanisme de sécurité, il est donc possible de modifier un fichier sans que PostgreSQL s’en rende compte, ce qui aboutit à une corruption silencieuse.

Les sommes de contrôles (checksums) permettent de se prémunir contre des corruptions silencieuses de données. Hélas, elles ne sont pas actives par défaut (cela pourrait bientôt changer). Leur mise en place est donc fortement recommandée sur une nouvelle instance. L’activation se fait différemment suivant les environnements (voir les procédures pour les paquets RPM et Debian du PGDG mais revient toujours à transmettre le paramètre --data-checksums à initdb.

Il est possible de mettre en place les checksums sur une instance existante avec l’utilitaire pg_checksums. Les gros inconvénients d’une activation tardive sont :

tous les fichiers de données et d’index seront réécrits (puisqu’il faudra calculer le checksum de chaque bloc, écrit dans le bloc même) ;
l’instance doit être arrêtée pendant toute l’opération.

À titre d’exemple, créons une instance sans utiliser les checksums, et une autre qui les utilisera :

$ initdb -D /tmp/sans_checksums/
$ initdb -D /tmp/avec_checksums/ --data-checksums

Insérons une valeur de test, sur chacun des deux clusters :

CREATE TABLE test (name text);
INSERT INTO test (name) VALUES ('toto');

On récupère le chemin du fichier de la table pour aller le corrompre à la main (seul celui sans checksums est montré en exemple).

SELECT pg_relation_filepath('test');

 pg_relation_filepath
----------------------
 base/12036/16317

Instance arrêtée (pour ne pas être gêné par le cache), on va s’attacher à corrompre ce fichier, en remplaçant la valeur « toto » par « goto » avec un éditeur hexadécimal :

$ hexedit /tmp/sans_checksums/base/12036/16317
$ hexedit /tmp/avec_checksums/base/12036/16399

(On remarquera au passage que la ligne est à la fin du bloc de 8 ko.) Enfin, on peut ensuite exécuter des requêtes sur ces deux clusters.

Sans checksums :

TABLE test;

 name
------
 qoto

Avec checksums :

TABLE test;

WARNING:  page verification failed, calculated checksum 16321
          but expected 21348
ERROR:  invalid page in block 0 of relation base/12036/16387

Les sommes de contrôle sont vérifiées lors des lectures par les requêtes, mais aussi lors des sauvegardes, notamment lors d’un appel à pg_dump (données de la base concernée uniquement), pg_basebackup, pgbackrest… En cas de corruption des données, l’opération sera interrompue. pg_checksums --check peut vérifier complètement une instance arrêtée.

En pratique, avec PostgreSQL 9.5 au moins et des processeurs supportant les instructions SSE 4.2 (voir dans /proc/cpuinfo, mais cela concerne au moins tous les processeurs Intel depuis 15 ans), il n’y aura pas d’impact notable en performances. Par contre, il y a aura un peu plus de journaux générés, pour des raisons techniques.

L’activation ou non des sommes de contrôle peut se faire indépendamment sur un serveur primaire et son secondaire, mais il est fortement conseillé de les activer simultanément des deux côtés pour éviter de gros problèmes dans certains scénarios de restauration.

Erreur de manipulation

Traces système, traces PostgreSQL
Revue des dernières manipulations effectuées
Historique des commandes
Danger : kill -9, rm -rf, rsync, find … -exec …

Outils

Quelques outils peuvent aider
- à diagnostiquer la nature du problème
- à valider la correction apportée
- à appliquer un contournement
ATTENTION
- certains de ces outils peuvent corrompre les données !

Outils : pg_controldata

Fournit des informations de contrôle sur l’instance
Ne nécessite pas que l’instance soit démarrée

L’outil pg_controldata est livré avec PostgreSQL. Il lit les informations du fichier de contrôle d’une instance PostgreSQL. Cet outil ne se connecte pas à l’instance, il a juste besoin d’avoir un accès en lecture sur le répertoire PGDATA de l’instance.

Les informations qu’il récupère ne sont donc pas du temps réel, il s’agit d’une vision de l’instance telle qu’elle était la dernière fois que le fichier de contrôle a été mis à jour. L’avantage est qu’elle peut être utilisée même si l’instance est arrêtée.

pg_controldata affiche notamment les informations initialisées lors d’initdb, telles que la version du catalogue, ou la taille des blocs, qui peuvent être cruciales si l’on veut restaurer une instance sur un nouveau serveur à partir d’une copie des fichiers.

Il affiche également de nombreuses informations utiles sur le traitement des journaux de transactions et des checkpoints, par exemple :

positions de l’avant-dernier checkpoint et du dernier checkpoint dans les WAL ;
nom du WAL correspondant au dernier WAL ;
timeline sur laquelle se situe le dernier checkpoint ;
instant précis du dernier checkpoint.

Quelques informations de paramétrage sont également renvoyées, comme la configuration du niveau de WAL, ou le nombre maximal de connexions autorisées.

En complément, le dernier état connu de l’instance est également affiché. Les états potentiels sont :

in production : l’instance est démarrée et est ouverte en écriture ;
shut down : l’instance est arrêtée ;
in archive recovery : l’instance est démarrée et est en mode recovery (restauration, Warm ou Hot Standby) ;
shut down in recovery : l’instance s’est arrêtée alors qu’elle était en mode recovery ;
shutting down : état transitoire, l’instance est en cours d’arrêt ;
in crash recovery : état transitoire, l’instance est en cours de démarrage suite à un crash ;
starting up : état transitoire, concrètement jamais utilisé.

Bien entendu, comme ces informations ne sont pas mises à jour en temps réel, elles peuvent être erronées.

Cet asynchronisme est intéressant pour diagnostiquer un problème, par exemple si pg_controldata renvoie l’état in production mais que l’instance est arrêtée, cela signifie que l’arrêt n’a pas été effectué proprement (crash de l’instance, qui sera donc suivi d’un recovery au démarrage).

Exemple de sortie de la commande :

$ /usr/pgsql-10/bin/pg_controldata /var/lib/pgsql/10/data

pg_control version number:            1002
Catalog version number:               201707211
Database system identifier:           6451139765284827825
Database cluster state:               in production
pg_control last modified:             Mon 28 Aug 2017 03:40:30 PM CEST
Latest checkpoint location:           1/2B04EC0
Prior checkpoint location:            1/2B04DE8
Latest checkpoint's REDO location:    1/2B04E88
Latest checkpoint's REDO WAL file:    000000010000000100000002
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:1023
Latest checkpoint's NextOID:          41064
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        548
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  1022
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Mon 28 Aug 2017 03:40:30 PM CEST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            7fb23aca2465c69b2c0f54ccf03e0ece
                                      3c0933c5f0e5f2c096516099c9688173

Outils pour l’export/import de données

pg_dump
pg_dumpall
COPY
psql / pg_restore
- --section=pre-data / data / post-data

Les outils pg_dump et pg_dumpall permettent d’exporter des données à partir d’une instance démarrée. Dans le cadre d’un incident grave, il est possible de les utiliser pour :

extraire le contenu de l’instance suspecte ;
extraire le contenu des bases de données ;
tester si les données sont lisibles dans un format compréhensible par PostgreSQL.

Par exemple, un moyen rapide de s’assurer que tous les fichiers des tables de l’instance sont lisibles est de forcer leur lecture complète, notamment grâce à la commande suivante :

$ pg_dumpall > /dev/null

Sous Windows Powershell :

pg_dumpall > $null

Rappelons que les fichiers associés aux index et vues matérialisées ne sont pas parcourus pendant cette opération.

Si pg_dumpall ou pg_dump renvoient des messages d’erreur et ne parviennent pas à exporter certaines tables, il est possible de contourner le problème à l’aide de la commande COPY, en sélectionnant exclusivement les données lisibles autour du bloc corrompu. Exemples :

-- si accès par index possible
COPY (SELECT * FROM pgbench_accounts WHERE aid BETWEEN 50000 AND 60000)
TO 'backuppartiel.dmp';
-- accès par références de lignes
COPY (SELECT * FROM pgbench_accounts WHERE ctid BETWEEN '(0,1)' AND '(1639,20)')
TO 'backuppartiel.dmp';

Il convient ensuite d’utiliser psql ou pg_restore pour importer les données dans une nouvelle instance, probablement sur un nouveau serveur, dans un environnement non affecté par le problème. Pour parer au cas où le réimport échoue à cause de contraintes non respectées, il est souvent préférable de faire le réimport par étapes :

$ pg_restore -1 --section=pre-data --verbose -d cible base.dump
$ pg_restore -1 --section=data --verbose -d cible base.dump
$ pg_restore -1 --section=post-data --exit-on-error --verbose -d cible base.dump

En cas de problème, on verra les contraintes posant problème.

Il peut être utile de générer les scripts en pur SQL avant de les appliquer, éventuellement par étape :

$ pg_restore --section=post-data -f postdata.sql base.dump

Pour rappel, même après un export/import de données réalisé avec succès, des corruptions logiques peuvent encore être présentes. Il faut donc être particulièrement vigilant et prendre le temps de valider l’intégrité fonctionnelle des données.

Outils : pageinspect

Extension
Vision du contenu d’un bloc
Sans le dictionnaire, donc sans décodage des données
Affichage brut
Utilisé surtout en debug, ou dans les cas de corruption
Fonctions de décodage pour les tables, les index (B-tree, hash, GIN, GiST), FSM
Nécessite de connaître le code de PostgreSQL

Voici quelques exemples.

Contenu d’une page d’une table :

SELECT * FROM heap_page_items(get_raw_page('dspam_token_data',0)) LIMIT 2;

-[ RECORD 1 ]------
lp          | 1
lp_off      | 8152
lp_flags    | 1
lp_len      | 40
t_xmin      | 837
t_xmax      | 839
t_field3    | 0
t_ctid      | (0,7)
t_infomask2 | 3
t_infomask  | 1282
t_hoff      | 24
t_bits      |
t_oid       |
t_data      | \x01000000010000000100000001000000
-[ RECORD 2 ]------
lp          | 2
lp_off      | 8112
lp_flags    | 1
lp_len      | 40
t_xmin      | 837
t_xmax      | 839
t_field3    | 0
t_ctid      | (0,8)
t_infomask2 | 3
t_infomask  | 1282
t_hoff      | 24
t_bits      |
t_oid       |
t_data      | \x02000000010000000100000002000000

Et son entête :

SELECT * FROM page_header(get_raw_page('dspam_token_data',0));

-[ RECORD 1 ]--------
lsn       | F1A/5A6EAC40
checksum  | 0
flags     | 0
lower     | 56
upper     | 7872
special   | 8192
pagesize  | 8192
version   | 4
prune_xid | 839

Méta-données d’un index (contenu dans la première page) :

SELECT * FROM bt_metap('dspam_token_data_uid_key');

-[ RECORD 1 ]-----
magic     | 340322
version   | 2
root      | 243
level     | 2
fastroot  | 243
fastlevel | 2

La page racine est la 243. Allons la voir :

SELECT * FROM bt_page_items('dspam_token_data_uid_key',243) LIMIT 10;

 offset |   ctid    | len |nulls|vars|                data
--------+-----------+-----+-----+----+-------------------------------------
      1 | (3,1)     |   8 | f   | f  |
      2 | (44565,1) |  20 | f   | f  | f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00
      3 | (242,1)   |  20 | f   | f  | 77 c6 0d 6f a6 92 db 81 28 00 00 00
      4 | (43569,1) |  20 | f   | f  | 47 a6 aa be 29 e3 13 83 18 00 00 00
      5 | (481,1)   |  20 | f   | f  | 30 17 dd 8e d9 72 7d 84 0a 00 00 00
      6 | (43077,1) |  20 | f   | f  | 5c 3c 7b c5 5b 7a 4e 85 0a 00 00 00
      7 | (719,1)   |  20 | f   | f  | 0d 91 d5 78 a9 72 88 86 26 00 00 00
      8 | (41209,1) |  20 | f   | f  | a7 8a da 17 95 17 cd 87 0a 00 00 00
      9 | (957,1)   |  20 | f   | f  | 78 e9 64 e9 64 a9 52 89 26 00 00 00
     10 | (40849,1) |  20 | f   | f  | 53 11 e9 64 e9 1b c3 8a 26 00 00 00

La première entrée de la page 243, correspondant à la donnée f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00 est stockée dans la page 3 de notre index :

SELECT * FROM bt_page_stats('dspam_token_data_uid_key',3);

-[ RECORD 1 ]-+-----
blkno         | 3
type          | i
live_items    | 202
dead_items    | 0
avg_item_size | 19
page_size     | 8192
free_size     | 3312
btpo_prev     | 0
btpo_next     | 44565
btpo          | 1
btpo_flags    | 0

SELECT * FROM bt_page_items('dspam_token_data_uid_key',3) LIMIT 10;

 offset |   ctid    | len |nulls|vars|                data
--------+-----------+-----+-----+----+-------------------------------------
      1 | (38065,1) |  20 | f   | f  | f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00
      2 | (1,1)     |   8 | f   | f  |
      3 | (37361,1) |  20 | f   | f  | 30 fd 30 b8 70 c9 01 80 26 00 00 00
      4 | (2,1)     |  20 | f   | f  | 18 2c 37 36 27 03 03 80 27 00 00 00
      5 | (4,1)     |  20 | f   | f  | 36 61 f3 b6 c5 1b 03 80 0f 00 00 00
      6 | (43997,1) |  20 | f   | f  | 30 4a 32 58 c8 44 03 80 27 00 00 00
      7 | (5,1)     |  20 | f   | f  | 88 fe 97 6f 7e 5a 03 80 27 00 00 00
      8 | (51136,1) |  20 | f   | f  | 74 a8 5a 9b 15 5d 03 80 28 00 00 00
      9 | (6,1)     |  20 | f   | f  | 44 41 3c ee c8 fe 03 80 0a 00 00 00
     10 | (45317,1) |  20 | f   | f  | d4 b0 7c fd 5d 8d 05 80 26 00 00 00

Le type de la page est i, c’est-à-dire «internal», donc une page interne de l’arbre. Continuons notre descente, allons voir la page 38065 :

SELECT * FROM bt_page_stats('dspam_token_data_uid_key',38065);

-[ RECORD 1 ]-+-----
blkno         | 38065
type          | l
live_items    | 169
dead_items    | 21
avg_item_size | 20
page_size     | 8192
free_size     | 3588
btpo_prev     | 118
btpo_next     | 119
btpo          | 0
btpo_flags    | 65

SELECT * FROM bt_page_items('dspam_token_data_uid_key',38065) LIMIT 10;

 offset |    ctid     | len |nulls|vars|                data
--------+-------------+-----+-----+----+-------------------------------------
      1 | (11128,118) |  20 | f   | f  | 33 37 89 95 b9 23 cc 80 0a 00 00 00
      2 | (45713,181) |  20 | f   | f  | f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00
      3 | (45424,97)  |  20 | f   | f  | f3 4b 2e 8c 39 a3 cb 80 26 00 00 00
      4 | (45255,28)  |  20 | f   | f  | f3 4b 2e 8c 39 a3 cb 80 27 00 00 00
      5 | (15672,172) |  20 | f   | f  | f3 4b 2e 8c 39 a3 cb 80 28 00 00 00
      6 | (5456,118)  |  20 | f   | f  | f3 bf 29 a2 39 a3 cb 80 0f 00 00 00
      7 | (8356,206)  |  20 | f   | f  | f3 bf 29 a2 39 a3 cb 80 28 00 00 00
      8 | (33895,272) |  20 | f   | f  | f3 4b 8e 37 99 a3 cb 80 0a 00 00 00
      9 | (5176,108)  |  20 | f   | f  | f3 4b 8e 37 99 a3 cb 80 0f 00 00 00
     10 | (5466,41)   |  20 | f   | f  | f3 4b 8e 37 99 a3 cb 80 26 00 00 00

Nous avons trouvé une feuille (type l). Les ctid pointés sont maintenant les adresses dans la table :

SELECT * FROM dspam_token_data WHERE ctid = '(11128,118)';

 uid |        token         | spam_hits | innocent_hits |  last_hit
-----+----------------------+-----------+---------------+------------
  40 | -6317261189288392210 |         0 |             3 | 2014-11-10

Outils : pg_resetwal

Efface les WAL courants
Permet à l’instance de démarrer en cas de corruption d’un WAL
- comme si elle était dans un état cohérent
- …ce qui n’est pas le cas
Cet outil est dangereux et mène à des corruptions !!!
Pour récupérer ce qu’on peut, et réimporter ailleurs

pg_resetwal est un outil fourni avec PostgreSQL. Son objectif est de pouvoir démarrer une instance après un crash si des corruptions de fichiers (typiquement WAL ou fichier de contrôle) empêchent ce démarrage.

Cette action n’est pas une action de réparation ! La réinitialisation des journaux de transactions implique que des transactions qui n’étaient que partiellement validées ne seront pas détectées comme telles, et ne seront donc pas annulées lors du recovery.

La conséquence est que les données de l’instance ne sont plus cohérentes. Il est fort possible d’y trouver des violations de contraintes diverses (notamment clés étrangères), ou d’autres cas d’incohérences plus difficiles à détecter.

Il s’utilise manuellement, en ligne de commande. Sa fonctionnalité principale est d’effacer les fichiers WAL courants, et il se charge également de réinitialiser les informations correspondantes du fichier de contrôle. Il est possible de lui spécifier les valeurs à initialiser dans le fichier de contrôle si l’outil ne parvient pas à les déterminer (par exemple, si tous les WAL dans le répertoire pg_wal ont été supprimés).

Attention, pg_resetwal ne doit jamais être utilisé sur une instance démarrée. Avant d’exécuter l’outil, il faut toujours vérifier qu’il ne reste aucun processus de l’instance.

Après la réinitialisation des WAL, une fois que l’instance a démarré, il ne faut surtout pas ouvrir les accès à l’application ! Comme indiqué, les données présentent sans aucun doute des incohérences, et toute action en écriture à ce point ne ferait qu’aggraver le problème.

L’étape suivante est donc :

de faire un export immédiat des données ;
de les restaurer dans une nouvelle instance initialisée à cet effet (de préférence sur un nouveau serveur, surtout si l’origine de la corruption n’a pas été clairement identifiée) ;
ensuite de procéder à une validation méthodique des données.

Il est probable que certaines données incohérentes puissent être identifiées à l’import, lors de la phase de recréation des contraintes : celles-ci échoueront si les données ne les respectent pas, ce qui permettra de les identifier.

En ce qui concerne les incohérences qui passeront au travers de ces tests, il faudra les trouver et les corriger manuellement, en procédant à une validation fonctionnelle des données.

Il faut donc bien retenir les points suivants :

pg_resetwal n’est pas magique ;
pg_resetwal rend les données incohérentes (ce qui est souvent pire qu’une simple perte d’une partie des données, comme on aurait eu en restaurant une sauvegarde) ;
pg_resetwal n’est à utiliser qu’en dernier recours s’il n’y a aucun autre moyen de faire autrement pour récupérer les données ;
il ne faut pas l’utiliser sur l’instance ayant subi le problème, mais sur une copie complète effectuée à froid ;
il permet au mieux d’exporter les données pour les importer dans une nouvelle instance ;
la validation soigneuse des données de la nouvelle instance est capitale.

Outils : pg_surgery

Extension (v14+)
Collection de fonctions permettant de modifier le statut des tuples d’une relation
Extrêmement dangereuse

Outils pour vérifier les sommes de contrôle

Base arrêtée :
- pg_checksums --check
À la lecture
- dont pg_dump, pg_dumpall
Lors d’une sauvegarde physique :
- pg_basebackup, voire pg_basebackup --target=blackhole
- pgBackRest, barman (selon configuration)

Une fois les sommes de contrôle en place, elles sont revérifiées à chaque lecture du bloc, mais il n’y a pas d’automatisme pour une vérification globale.

pg_checksums :

pg_checksums --check est la commande dédiée pour vérifier les sommes de contrôles existantes sur les bases de données à froid : l’instance doit être arrêtée proprement auparavant.

Par exemple, suite à une modification de deux blocs dans une table avec l’outil hexedit, on peut rencontrer ceci :

$ /usr/pgsql-12/bin/pg_checksums -D /var/lib/pgsql/12/data -c

pg_checksums: error: checksum verification failed in file
  "/var/lib/pgsql/12/data/base/14187/16389", block 0:
  calculated checksum 5BF9 but block contains C55D
pg_checksums: error: checksum verification failed in file
  "/var/lib/pgsql/12/data/base/14187/16389", block 4438:
  calculated checksum A3 but block contains B8AE
Checksum operation completed
Files scanned:  1282
Blocks scanned: 28484
Bad checksums:  2
Data checksum version: 1

Rappelons que pg_checksums sait aussi ajouter ou supprimer les sommes de contrôle sur une instance existante arrêtée (donc après le initdb).

Sauvegarde logique :

pg_dumpall > /dev/null vérifie certaines sommes de contrôles car il lit tous les fichiers de données, mais pas les index. Si une sauvegarde logique fonctionne (et se restaure sans souci…), on a au moins sauvé l’essentiel.

pg_basebackup :

La meilleure alternative est d’effectuer une sauvegarde physique avec pg_basebackup. Par défaut, toutes les sommes de contrôle sont alors vérifiées. Cela n’oblige pas à arrêter la base. De plus, si l’outil est utilisé pour faire des sauvegardes, on obtient une vérification gratuite. S’il s’agit juste de forcer la lecture intégrale, il existe même une option dédiée --target=blackhole qui n’écrit pas la sauvegarde (à partir de PostgreSQL 15) :

$ pg_basebackup -h /var/run/postgresql -X none --target=blackhole
WARNING:  checksum verification failed in file "./base/5/1824979", block 1: calculated B15 but expected B41E
WARNING:  checksum verification failed in file "./base/5/1824979", block 3: calculated 601A but expected 4D7
WARNING:  checksum verification failed in file "./base/5/1824979", block 35: calculated 22A2 but expected 4DC
WARNING:  checksum verification failed in file "./base/5/1824979", block 67: calculated 633C but expected 4E5
WARNING:  checksum verification failed in file "./base/5/1824979", block 99: calculated 4CB9 but expected 4E2
WARNING:  further checksum verification failures in file "./base/5/1824979" will not be reported
WARNING:  file "./base/5/1824979" has a total of 142 checksum verification failures
WARNING:  could not verify checksum in file "./base/5/1824645", block 0: read buffer size 2 and page size 8192 differ
NOTICE:  all required WAL segments have been archived
WARNING:  142 total checksum verification failures
pg_basebackup: erreur : erreur de somme de contrôle

pg_basebackup peut désactiver la vérification des sommes de contrôle avec l’option --no-verify-checksums afin d’obtenir une copie, aussi corrompue que l’original, mais pouvant servir de base de travail.

Une sauvegarde physique périodique est un bon moyen de vérifier les sommes de contrôle régulièrement, tout en permettant de parer aux soucis qui pourraient survenir. L’outil barman, selon sa configuration, peut utiliser pg_basebackup. pgBackRest vérifie aussi les sommes de contrôle lors de ses sauvegardes.

Outils : amcheck & pg_amcheck

Pour les index :

amcheck : vérification uniquement
- fonction pour l’intégrité des tables
- vérification de la cohérence index/table (probabiliste)
- et de l’unicité
pg_amcheck (v14+)
- version ligne de commande
- en masse
Si problème : REINDEX

amcheck :

Plus finement, amcheck permet de vérifier la cohérence des tables et index et de leur structure interne, et ainsi détecter des corruptions. Il définit plusieurs fonctions :

verify_heapam (PostgreSQL 14 et suivants) vérifie que chaque bloc est bien lisible, correctement construit ; cette fonction peut couvrir les TOAST et peut se limiter à certains blocs ;
bt_index_check est destinée aux vérifications de routine des index, et ne pose qu’un verrou AccessShareLock peu gênant ;
bt_index_parent_check est plus minutieuse, mais son exécution gêne les modifications dans la table (verrou ShareLock sur la table et l’index) et elle ne peut pas être exécutée sur un serveur secondaire.

Avec le paramètre heapallindex à true, les deux dernières fonctions effectuent une vérification supplémentaire en recréant temporairement une structure d’index et en la comparant avec l’index original. bt_index_check vérifie alors que chaque entrée de la table possède une entrée dans l’index. bt_index_parent_check vérifie en plus qu’à chaque entrée de l’index correspond une entrée dans la table.

Les verrous posés par les fonctions ne changent pas. Néanmoins, l’utilisation de ce mode a un impact sur la durée d’exécution des vérifications. Pour limiter l’impact, l’opération n’a lieu qu’en mémoire, et dans la limite du paramètre maintenance_work_mem (soit entre 256 Mo et 1 Go, parfois plus, sur les serveurs récents). C’est cette restriction mémoire qui implique que la détection de problèmes est probabiliste pour les plus grosses tables : selon la documentation, la probabilité de rater une incohérence est de 2 % si l’on peut consacrer 2 octets de mémoire à chaque ligne. Mais rien n’empêche de relancer les vérifications régulièrement, diminuant ainsi les chances de rater une erreur.

À partir de PostgreSQL 17, le paramètre checkunique à true permet de détecter des violations de contrainte d’unicité.

amcheck ne fournit aucun moyen de corriger une erreur, puisqu’il détecte des choses qui ne devraient jamais arriver. REINDEX sera souvent la solution la plus sûre, simple et facile, mais tout dépend de la cause du problème.

Exemple :

Soit unetable_pkey, un index de 10 Go sur un entier :

CREATE EXTENSION amcheck ;

SELECT bt_index_check('unetable_pkey');
Durée : 63753,257 ms (01:03,753)

SELECT bt_index_check('unetable_pkey', true);
Durée : 234200,678 ms (03:54,201)

Ici, la vérification exhaustive multiplie le temps de vérification par un facteur 4.

Voir la documentation d’amcheck pour tous les détails et les options disponibles.

pg_amcheck :

À partir de la version 14, PostgreSQL dispose d’un nouvel outil en ligne de commande appelé pg_amcheck, basé sur amcheck.

Ses options de ligne de commande reprennent celles de l’extension, tout en permettant de lancer les vérifications en masse (choix des bases, des tables, parallélisation).

Voir sa documentation.

Exemple de recherche de corruption d’unicité :

Cet exemple, dérivé d’un autre de la liste pgsql-hackers, modifie les tables systèmes pour provoquer la corrution d’un index unique (à ne jamais faire en production, bien sûr !) :

CREATE TABLE junk (t text UNIQUE);
-- données
INSERT INTO junk (t) VALUES ('ba'), ('be'), ('bo'), ('by');
UPDATE pg_catalog.pg_index
   SET indisunique = false
 WHERE indrelid = 'junk'::regclass;
-- nouvelles données avec doublons
INSERT INTO junk (t) VALUES ('ba'), ('bi'), ('bu'), ('by');
-- remise en place de l'unicité
UPDATE pg_catalog.pg_index
   SET indisunique = true
 WHERE indrelid = 'junk'::regclass;
SELECT * FROM junk ORDER BY 1;

Tout semble fonctionner ensuite, si ce n’est que que l’unicité des données déjà présentes n’est pas là.

Détection avec l’extension amcheck :

CREATE EXTENSION amcheck ;
SELECT bt_index_check('junk_t_key', false, true);

ERROR:  index uniqueness is violated for index "junk_t_key"
DÉTAIL : Index tid=(1,1) and tid=(1,2) (point to heap tid=(0,1) and tid=(0,5)) page lsn=65/EB31C810.

Détection avec pg_amcheck (l’extension amcheck doit être installée) :

pg_amcheck --database=postgres --relation=junk --checkunique

vérification de l'index btree« postgres public.junk_t_key » :
    ERROR:  index uniqueness is violated for index "junk_t_key"
    DÉTAIL : Index tid=(1,1) and tid=(1,2) (point to heap tid=(0,1) and tid=(0,5)) page lsn=65/EB31C810.

Les données responsables peuvent être trouvées dans la table (heap) grâce aux tid indiqués :

SELECT * FROM junk WHERE ctid='(0,1)' or ctid='(0,5)';

 t  
----
 ba
 ba

Malheureusement, l’outil ne fournit que la première erreur rencontrée. Il faudra faire une requête pour les trouver :

SELECT t, count(*) FROM junk
GROUP BY 1  HAVING count(*) > 1  ORDER BY t ;

Sachant que cette requête risque de se baser sur l’index problématique, il faudra se méfier, ou plutôt agréger avec t||''.

Cas type de désastres

Les cas suivants sont assez rares
Ils nécessitent généralement une restauration
Certaines manipulations à haut risque sont possibles
- mais complètement déconseillées !
Généralement, il faudra restaurer une sauvegarde

Avertissement

Privilégier une solution fiable (restauration, bascule)
Les actions listées ici sont parfois destructrices
La plupart peuvent (et vont) provoquer des incohérences
Travailler sur une copie

La plupart des manipulations mentionnées dans cette partie sont destructives, et peuvent (et vont) provoquer des incohérences dans les données.

Tous les experts s’accordent pour dire que l’utilisation de telles méthodes pour récupérer une instance tend à aggraver le problème existant ou à en provoquer de nouveaux, plus graves. S’il est possible de l’éviter, ne pas les tenter (ie : préférer la restauration d’une sauvegarde) !

S’il n’est pas possible de faire autrement (ie : pas de sauvegarde utilisable, données vitales à extraire…), alors TRAVAILLER SUR UNE COPIE.

Il ne faut pas non plus oublier que chaque situation est unique, il faut prendre le temps de bien cerner l’origine du problème, documenter chaque action prise, s’assurer qu’un retour arrière est toujours possible.

Corruption de blocs dans des index

Messages d’erreur lors des accès par l’index
Requêtes incohérentes
Données différentes entre un indexscan et un seqscan
Supprimer et recréer l’index : REINDEX
Est-ce juste cet index ?

Les index sont des objets de structure complexe, ils sont donc particulièrement vulnérables aux corruptions.

Lorsqu’un index est corrompu, on aura généralement des messages d’erreur de ce type :

ERROR: invalid page header in block 5869177 of relation base/17291/17420

Il peut arriver qu’un bloc corrompu ne renvoie pas de message d’erreur à l’accès, mais que les données elles-mêmes soient altérées, ou que des filtres ne renvoient pas les données attendues. Ce cas est néanmoins très rare dans un bloc d’index.

Dans la plupart des cas, si les données de la table sous-jacente ne sont pas affectées, il est possible de réparer l’index en le reconstruisant intégralement grâce à la commande REINDEX. Une corruption d’index se corrige donc facilement, mais le plus inquiétant est la présence même d’une corruption. Le contrôle complet de la base est fortement conseillé.

Corruption de blocs dans des tables 1

ERROR: invalid page header in block 32570 of relation base/16390/2663
ERROR: could not read block 32570 of relation base/16390/2663:
       read only 0 of 8192 bytes

Cas plus problématique
Restauration probablement nécessaire

Les corruptions de blocs vont généralement déclencher des erreurs du type suivant :

ERROR: invalid page header in block 32570 of relation base/16390/2663
ERROR: could not read block 32570 of relation base/16390/2663:
       read only 0 of 8192 bytes

Si la relation concernée est une table, tout ou partie des données contenues dans ces blocs est perdu.

L’apparition de ce type d’erreur est un signal fort qu’une restauration est certainement nécessaire.

Corruption de blocs dans des tables 2

SET zero_damaged_pages = true ;
VACUUM FULL tablecorrompue ;

Des données vont certainement être perdues !

S’il est nécessaire de lire le maximum de données possibles de la table, il est possible d’utiliser l’option de PostgreSQL zero_damaged_pages pour demander au moteur de réinitialiser les blocs invalides à zéro lorsqu’ils sont lus au lieu de tomber en erreur. Il s’agit d’un des très rares paramètres absents de postgresql.conf.

Par exemple :

SET zero_damaged_pages = true ;
VACUUM FULL tablecorrompue ;

WARNING:  invalid page header in block 32570 of relation base/16390/2663; zeroing out page

Si l’opération continue et se termine sans erreur, les blocs invalides ont été réinitialisés.

Les données qu’ils contenaient sont évidemment perdues, mais la table peut désormais être accédée dans son intégralité en lecture, permettant ainsi par exemple de réaliser un export des données pour récupérer ce qui peut l’être.

Attention, du fait des données perdues, le résultat peut être incohérent (contraintes non respectées…).

Par ailleurs, sans les checksums, PostgreSQL ne détecte pas les corruptions logiques, c’est-à-dire n’affectant pas la structure des données mais corrompant le contenu. Il ne faut donc pas penser que la procédure d’export complet de données suivie d’un import sans erreur garantit l’absence de corruption.

Corruption de blocs dans des tables 3

Si la corruption est importante, l’accès au bloc peut faire crasher l’instance
Il est tout de même possible de réinitialiser le bloc
- identifier le fichier à l’aide de pg_relation_filepath()
- trouver le bloc avec ctid / pageinspect
- réinitialiser le bloc avec dd
- il faut vraiment ne pas avoir d’autre choix

Dans certains cas, il arrive que la corruption soit suffisamment importante pour que le simple accès au bloc fasse crasher l’instance. Dans ce cas, le seul moyen de réinitialiser le bloc est de le faire manuellement au niveau du fichier, instance arrêtée, par exemple avec la commande dd.

Pour identifier le fichier associé à la table corrompue, utiliser la fonction pg_relation_filepath() :

SELECT pg_relation_filepath('test_corruptindex') ;

 pg_relation_filepath
----------------------
 base/16390/40995

Le résultat donne le chemin vers le fichier principal de la table, relatif au PGDATA de l’instance.

Attention, une table peut contenir plusieurs fichiers. Par défaut une instance PostgreSQL sépare les fichiers en segments de 1 Go. Une table dépassant cette taille aura donc des fichiers supplémentaires (base/16390/40995.1, base/16390/40995.2 …).

Pour trouver le fichier contenant le bloc corrompu, il faudra donc prendre en compte le numéro du bloc trouvé dans le champ ctid, multiplier ce numéro par la taille du bloc (paramètre block_size, 8 ko par défaut), et diviser le tout par la taille du segment.

Cette manipulation est évidemment extrêmement risquée, la moindre erreur pouvant rendre irrécupérables de grandes portions de données. Il est donc fortement déconseillé de se lancer dans ce genre de manipulations à moins d’être absolument certain que c’est indispensable.

Encore une fois, ne pas oublier de travailler sur une copie, et pas directement sur l’instance de production.

Corruption des WAL 1

Situés dans le répertoire pg_wal
Les WAL sont nécessaires au recovery
Démarrage impossible s’ils sont corrompus ou manquants
Si les fichiers WAL ont été archivés, les récupérer
Sinon, la restauration est la seule solution viable

Les fichiers WAL sont les journaux de transactions de PostgreSQL. Leur fonction est d’assurer que les transactions qui ont été effectuées depuis le dernier checkpoint ne seront pas perdues en cas de crash de l’instance.

Si certains sont corrompus ou manquants, alors PostgreSQL ne pourra pas redémarrer.

Il ne faut jamais supprimer les fichiers WAL, même si le système de fichiers est plein ! (Dans ce dernier cas, chercher la source : il s’agit souvent d’un archivage qui traîne ou d’un slot de réplication bloqué.)

Si l’archivage était activé et que les fichiers WAL perdus ont été archivés, alors il est possible de les restaurer avant de tenter un nouveau démarrage (copie manuelle, utilisation de restore_command…).

Si ce n’est pas possible, ou si les archives ont également été corrompues ou supprimées, l’instance ne pourra pas redémarrer.

Dans cette situation, comme dans la plupart des autres évoquées ici, la seule solution permettant de s’assurer que les données ne seront pas corrompues est de procéder à une restauration de l’instance depuis la dernière sauvegarde complète disponible.

Corruption des WAL 2

pg_resetwal permet de forcer le démarrage
ATTENTION !!!
- cela va provoquer des pertes de données
- des corruptions de données sont également probables
- ce n’est pas une action corrective !

L’utilitaire pg_resetwal a comme fonction principale de supprimer les fichiers WAL courants et d’en créer un nouveau, avant de mettre à jour le fichier de contrôle pour permettre le redémarrage.

Au minimum, cette action va provoquer la perte de toutes les transactions validées effectuées depuis le dernier checkpoint. Il est également probable que des incohérences vont apparaître, certaines relativement simples à détecter via un export/import (incohérences dans les clés étrangères par exemple), certaines complètement invisibles.

L’utilisation de cet utilitaire est extrêmement dangereuse, n’est pas recommandée, et ne peut jamais être considérée comme une action corrective. Il faut toujours privilégier la restauration d’une sauvegarde plutôt que son exécution.

Si l’utilisation de pg_resetwal est néanmoins nécessaire (par exemple pour récupérer des données absentes de la sauvegarde), alors il faut travailler sur une copie des fichiers de l’instance, récupérer ce qui peut l’être à l’aide d’un export de données, et les importer dans une autre instance.

Les données récupérées de cette manière devraient également être soigneusement validées avant d’être importée de façon à s’assurer qu’il n’y a pas de corruption silencieuse.

Il ne faut en aucun cas remettre une instance en production après une réinitialisation des WAL.

Corruption du fichier de contrôle

Fichier global/pg_control
Contient les informations liées au dernier checkpoint
Sans lui, l’instance ne peut pas démarrer
Recréation avec pg_resetwal… parfois
Restauration nécessaire

Le fichier de contrôle de l’instance contient de nombreuses informations liées à l’activité et au statut de l’instance, notamment l’instant du dernier checkpoint, la position correspondante dans les WAL, le numéro de transaction courant et le prochain à venir…

Ce fichier est le premier lu par l’instance. S’il est corrompu ou supprimé, l’instance ne pourra pas démarrer.

Il est possible de forcer la réinitialisation de ce fichier à l’aide de la commande pg_resetwal, qui va se baser par défaut sur les informations contenues dans les fichiers WAL présents pour tenter de « deviner » le contenu du fichier de contrôle.

Ces informations seront très certainement erronées, potentiellement à tel point que même l’accès aux bases de données par leur nom ne sera pas possible :

$ pg_isready
/var/run/postgresql:5432 - accepting connections

$ psql postgres
psql: FATAL:  database "postgres" does not exist

Encore une fois, utiliser pg_resetwal n’est en aucun cas une solution, mais doit uniquement être considéré comme un contournement temporaire à une situation désastreuse.

Une instance altérée par cet outil ne doit pas être considérée comme saine.

Corruption du CLOG

Fichiers dans pg_xact
Statut des différentes transactions
Son altération risque de causer des incohérences majeures
Préférer la restauration

Corruption du catalogue système

Le catalogue contient la définition du schéma
Sans lui, les données sont inaccessibles
Situation très délicate…

Conclusion

Les désastres peuvent arriver
Il faut s’y être préparé
Faites des sauvegardes !
- et testez-les

Quiz

https://dali.bo/i5_quiz

Travaux pratiques

Corruption d’un bloc de données

But : Corrompre un bloc et voir certains impacts possibles.

Vérifier que l’instance utilise bien les checksums. Au besoin les ajouter avec pg_checksums.

Créer une base pgbench et la remplir avec l’outil de même, avec un facteur d’échelle 10 et avec les clés étrangères entre tables ainsi :
/usr/pgsql-17/bin/pgbench -i -s 10 -d pgbench --foreign-keys

Voir la taille de pgbench_accounts, les valeurs que prend sa clé primaire.

Retrouver le fichier associé à la table pgbench_accounts (par exemple avec pg_file_relationpath).

Arrêter PostgreSQL.

Avec un outil hexedit (à installer au besoin, l’aide s’obtient par F1), modifier une ligne dans le PREMIER bloc de la table.

Redémarrer PostgreSQL et lire le contenu de pgbench_accounts.

Tenter un pg_dumpall > /dev/null.

Arrêter PostgreSQL.
Voir ce que donne pg_checksums (pg_verify_checksums en v11).

Faire une copie de travail à froid du PGDATA.
Protéger en écriture le PGDATA original.
Dans la copie, supprimer la possibilité d’accès depuis l’extérieur.

Avant de redémarrer PostgreSQL, supprimer les sommes de contrôle dans la copie (en désespoir de cause).

Démarrer le cluster sur la copie avec pg_ctl.

Que renvoie ceci ?

SELECT * FROM pgbench_accounts LIMIT 100 ;

Tenter une récupération avec SET zero_damaged_pages. Quelles données ont pu être perdues ?

Corruption d’un bloc de données et incohérences

But : Corrompre une table portant une clé étrangère.

Nous continuons sur la copie de la base de travail, où les sommes de contrôle ont été désactivées.

Consulter le format et le contenu de la table pgbench_branches.

Retrouver les fichiers des tables pgbench_branches (par exemple avec pg_file_relationpath).

Pour corrompre la table :

Arrêter PostgreSQL.

Avec hexedit, dans le premier bloc en tête de fichier, remplacer les derniers caractères non nuls (C0 9E 40) par FF FF FF.

En toute fin de fichier, remplacer le dernier 01 par un FF.

Redémarrer PostgreSQL.

Compter le nombre de lignes dans pgbench_branches.
Recompter après SET enable_seqscan TO off ;.
Quelle est la bonne réponse ? Vérifier le contenu de la table.

Qu’affiche pageinspect pour cette table ?

Avec l’extension amcheck, essayer de voir si le problème peut être détecté. Si non, pourquoi ?

Pour voir ce que donnerait une restauration :

Exporter pgbench_accounts, définition des index comprise.

Supprimer la table (il faudra supprimer pgbench_history aussi).

Tenter de la réimporter.

Travaux pratiques (solutions)

Corruption d’un bloc de données

Vérifier que l’instance utilise bien les checksums. Au besoin les ajouter avec pg_checksums.

# SHOW data_checksums ;

 data_checksums
----------------
 on

Si la réponse est off, on peut (à partir de la v12) mettre les checksums en place :

$ /usr/pgsql-17/bin/pg_checksums -D /var/lib/pgsql/15/data.BACKUP/  --enable --progress
58/58 MB (100%) computed
Checksum operation completed
Files scanned:  964
Blocks scanned: 7524
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster

Créer une base pgbench et la remplir avec l’outil de même, avec un facteur d’échelle 10 et avec les clés étrangères entre tables ainsi :
/usr/pgsql-17/bin/pgbench -i -s 10 -d pgbench --foreign-keys

$ dropdb --if-exists pgbench ;
$ createdb pgbench ;

$ /usr/pgsql-17/bin/pgbench -i -s 10 -d pgbench --foreign-keys
...
creating tables...
generating data...
100000 of 1000000 tuples (10%) done (elapsed 0.15 s, remaining 1.31 s)
200000 of 1000000 tuples (20%) done (elapsed 0.35 s, remaining 1.39 s)
..
1000000 of 1000000 tuples (100%) done (elapsed 2.16 s, remaining 0.00 s)
vacuuming...
creating primary keys...
creating foreign keys...
done.

Voir la taille de pgbench_accounts, les valeurs que prend sa clé primaire.

La table fait 128 Mo selon un \d+.

La clé aid va de 1 à 100000 :

# SELECT min(aid), max(aid) FROM pgbench_accounts ;

 min |   max
-----+---------
   1 | 1000000

Un SELECT montre que les valeurs sont triées mais c’est dû à l’initialisation.

Retrouver le fichier associé à la table pgbench_accounts (par exemple avec pg_file_relationpath).

 SELECT pg_relation_filepath('pgbench_accounts') ;

 pg_relation_filepath
----------------------
 base/16454/16489

Arrêter PostgreSQL.

 # systemctl stop postgresql-15

Cela permet d’être sûr qu’il ne va pas écraser nos modifications lors d’un checkpoint.

Avec un outil hexedit (à installer au besoin, l’aide s’obtient par F1), modifier une ligne dans le PREMIER bloc de la table.

 # dnf install hexedit

postgres$ hexedit /var/lib/pgsql/15/data/base/16454/16489

Aller par exemple sur la 2è ligne, modifier 80 9F en FF FF. Sortir avec Ctrl-X, confirmer la sauvegarde.

Redémarrer PostgreSQL et lire le contenu de pgbench_accounts.

 # systemctl start postgresql-15

# SELECT * FROM pgbench_accounts ;

WARNING:  page verification failed, calculated checksum 62947 but expected 57715
ERROR:  invalid page in block 0 of relation base/16454/16489

Tenter un pg_dumpall > /dev/null.

$ pg_dumpall > /dev/null
pg_dump: WARNING:  page verification failed, calculated checksum 62947 but expected 57715
pg_dump: error: Dumping the contents of table "pgbench_accounts" failed: PQgetResult() failed.
pg_dump: error: Error message from server:
         ERROR:  invalid page in block 0 of relation base/16454/16489
pg_dump: error: The command was:
         COPY public.pgbench_accounts (aid, bid, abalance, filler) TO stdout;
pg_dumpall: error: pg_dump failed on database "pgbench", exiting

Arrêter PostgreSQL.
Voir ce que donne pg_checksums (pg_verify_checksums en v11).

 # systemctl stop postgresql-15

$ /usr/pgsql-17/bin/pg_checksums -D /var/lib/pgsql/15/data/  --check --progress
pg_checksums: error: checksum verification failed in file
"/var/lib/pgsql/15/data//base/16454/16489", block 0:
                                calculated checksum F5E3 but block contains E173
216/216 MB (100%) computed
Checksum operation completed
Files scanned:  1280
Blocks scanned: 27699
Bad checksums:  1
Data checksum version: 1

Faire une copie de travail à froid du PGDATA.
Protéger en écriture le PGDATA original.
Dans la copie, supprimer la possibilité d’accès depuis l’extérieur.

Dans l’idéal, la copie devrait se faire vers un autre support, une corruption rend celui-ci suspect. Dans le cadre du TP, ceci suffira :

$ cp -upR /var/lib/pgsql/15/data/  /var/lib/pgsql/15/data.BACKUP/
$ chmod -R -w /var/lib/pgsql/15/data/

Dans /var/lib/pgsql/15/data.BACKUP/pg_hba.conf ne doit plus subsister que :

local   all             all                                     trust

Avant de redémarrer PostgreSQL, supprimer les sommes de contrôle dans la copie (en désespoir de cause).

$ /usr/pgsql-17/bin/pg_checksums -D /var/lib/pgsql/15/data.BACKUP/  --disable
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums disabled in cluster

Démarrer le cluster sur la copie avec pg_ctl.

/usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/15/data.BACKUP/ start

Que renvoie ceci ?

SELECT * FROM pgbench_accounts LIMIT 100 ;

# SELECT *  FROM pgbench_accounts LIMIT 10;

ERROR:  out of memory
DÉTAIL : Failed on request of size 536888061 in memory context "printtup".

Ce ne sera pas forcément cette erreur, plus rien n’est sûr en cas de corruption. L’avantage des sommes de contrôle est justement d’avoir une erreur moins grave et plus ciblée.

Un pg_dumpall renverra le même message.

Tenter une récupération avec SET zero_damaged_pages. Quelles données ont pu être perdues ?

pgbench=# SET zero_damaged_pages TO on ;
SET
pgbench=# VACUUM FULL pgbench_accounts ;
VACUUM

pgbench=# SELECT *  FROM pgbench_accounts LIMIT 100;

 aid | bid | abalance |                                        filler
-----+-----+----------+--------------------------------------------------------------------------------------
   2 |   1 |        0 |
   3 |   1 |        0 |
   4 |   1 |        0 |
[...]

pgbench=# SELECT min(aid), max(aid), count(aid) FROM pgbench_accounts ;

 min |   max   | count
-----+---------+--------
   2 | 1000000 | 999999

Apparemment une ligne a disparu, celle portant la valeur 1 pour la clé. Il est rare que la perte soit aussi évidente !

Corruption d’un bloc de données et incohérences

Consulter le format et le contenu de la table pgbench_branches.

Cette petite table ne contient que 10 valeurs :

# SELECT * FROM pgbench_branches ;

 bid | bbalance | filler
-----+----------+--------
 255 |        0 |
   2 |        0 |
   3 |        0 |
   4 |        0 |
   5 |        0 |
   6 |        0 |
   7 |        0 |
   8 |        0 |
   9 |        0 |
  10 |        0 |
(10 lignes)

Retrouver les fichiers des tables pgbench_branches (par exemple avec pg_file_relationpath).

# SELECT pg_relation_filepath('pgbench_branches') ;

 pg_relation_filepath
----------------------
 base/16454/16490

Pour corrompre la table :

Arrêter PostgreSQL.

Avec hexedit, dans le premier bloc en tête de fichier, remplacer les derniers caractères non nuls (C0 9E 40) par FF FF FF.

En toute fin de fichier, remplacer le dernier 01 par un FF.

Redémarrer PostgreSQL.

$ /usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/15/data.BACKUP/ stop

$ hexedit /var/lib/pgsql/15/data.BACKUP/base/16454/16490

$ /usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/15/data.BACKUP/ start

Compter le nombre de lignes dans pgbench_branches.
Recompter après SET enable_seqscan TO off ;.
Quelle est la bonne réponse ? Vérifier le contenu de la table.

Les deux décomptes sont contradictoires :

pgbench=# SELECT count(*) FROM pgbench_branches ;
 count
-------
     9

pgbench=# SET enable_seqscan TO off ;
SET

pgbench=# SELECT count(*) FROM pgbench_branches ;
 count
-------
    10

En effet, le premier lit la (petite) table directement, le second passe par l’index, comme un EXPLAIN le montrerait. Les deux objets diffèrent.

Et le contenu de la table est devenu :

# SELECT * FROM pgbench_branches ;

 bid | bbalance | filler
-----+----------+--------
 255 |        0 |
   2 |        0 |
   3 |        0 |
   4 |        0 |
   5 |        0 |
   6 |        0 |
   7 |        0 |
   8 |        0 |
   9 |        0 |
(9 lignes)

Le 1 est devenu 255 (c’est notre première modification) mais la ligne 10 a disparu !

Les requêtes peuvent renvoyer un résultat incohérent avec leur critère :

pgbench=# SET enable_seqscan TO off;
SET
pgbench=# SELECT * FROM pgbench_branches
          WHERE    bid = 1 ;

 bid | bbalance | filler
-----+----------+--------
 255 |        0 |

Qu’affiche pageinspect pour cette table ?

pgbench=# CREATE EXTENSION pageinspect ;

pgbench=# SELECT t_ctid, lp_off, lp_len, t_xmin, t_xmax, t_data
          FROM heap_page_items(get_raw_page('pgbench_branches',0));

 t_ctid | lp_off | lp_len | t_xmin | t_xmax |       t_data
--------+--------+--------+--------+--------+--------------------
 (0,1)  |   8160 |     32 |  63726 |      0 | \xff00000000000000
 (0,2)  |   8128 |     32 |  63726 |      0 | \x0200000000000000
 (0,3)  |   8096 |     32 |  63726 |      0 | \x0300000000000000
 (0,4)  |   8064 |     32 |  63726 |      0 | \x0400000000000000
 (0,5)  |   8032 |     32 |  63726 |      0 | \x0500000000000000
 (0,6)  |   8000 |     32 |  63726 |      0 | \x0600000000000000
 (0,7)  |   7968 |     32 |  63726 |      0 | \x0700000000000000
 (0,8)  |   7936 |     32 |  63726 |      0 | \x0800000000000000
 (0,9)  |   7904 |     32 |  63726 |      0 | \x0900000000000000
        |  32767 |    127 |        |        |
(10 lignes)

La première ligne indique bien que le 1 est devenu un 255.

La dernière ligne porte sur la première modification, qui a détruit les informations sur le ctid. Celle-ci est à présent inaccessible.

Avec l’extension amcheck, essayer de voir si le problème peut être détecté. Si non, pourquoi ?

La documentation est sur https://docs.postgresql.fr/current/amcheck.html.

Une vérification complète se fait ainsi :

pgbench=# CREATE EXTENSTION amcheck ;

pgbench=# SELECT bt_index_check (index => 'pgbench_branches_pkey',
                                 heapallindexed => true);
 bt_index_check
----------------

(1 ligne)

pgbench=# SELECT bt_index_parent_check (index => 'pgbench_branches_pkey',
                                        heapallindexed => true, rootdescend => true);
ERROR:  heap tuple (0,1) from table "pgbench_branches"
        lacks matching index tuple within index "pgbench_branches_pkey"

Un seul des problèmes a été repéré.

Un REINDEX serait ici une mauvaise idée : c’est la table qui est corrompue ! Les sommes de contrôle, là encore, auraient permis de cibler le problème très tôt.

Pour voir ce que donnerait une restauration :

Exporter pgbench_accounts, définition des index comprise.

Supprimer la table (il faudra supprimer pgbench_history aussi).

Tenter de la réimporter.

$ pg_dump -d pgbench  -t pgbench_accounts -f /tmp/pgbench_accounts.dmp

$ psql pgbench -c 'DROP TABLE pgbench_accounts CASCADE'
NOTICE:  drop cascades to constraint pgbench_history_aid_fkey on table pgbench_history
DROP TABLE

$ psql pgbench < /tmp/pgbench_accounts.dmp
SET
SET
SET
SET
SET
 set_config
------------

(1 ligne)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 999999
ALTER TABLE
CREATE INDEX
ERROR:  insert or update on table "pgbench_accounts"
        violates foreign key constraint "pgbench_accounts_bid_fkey"
DÉTAIL : Key (bid)=(1) is not present in table "pgbench_branches".

La contrainte de clé étrangère entre les deux tables ne peut être respectée : bid est à 1 sur de nombreuses lignes de pgbench_accounts mais n’existe plus dans la table pgbench_branches ! Ce genre d’incohérence doit être recherchée très tôt pour ne pas surgir bien plus tard, quand on doit restaurer pour d’autres raisons.

Gestion d’un sinistre

Sur ce document

Licence Creative Commons CC-BY-NC-SA

Marques déposées

Versions de PostgreSQL couvertes

PostgreSQL : Gestion d’un sinistre

Introduction

Au menu

Anticiper les désastres

Documentation

Procédures et scripts

Supervision et historisation

Automatisation

Réagir aux désastres

Symptômes d’un désastre

Bons réflexes 1

Bons réflexes 2

Bons réflexes 3

Bons réflexes 4

Bons réflexes 5

Bons réflexes 6

Bons réflexes 7

Bons réflexes 8

Mauvais réflexes 1

Mauvais réflexes 2

Mauvais réflexes 3

Rechercher l’origine du problème

Prérequis

Recherche d’historique

Matériel

Virtualisation

Système d’exploitation 1

Système d’exploitation 2

Système d’exploitation 3

PostgreSQL

Paramétrage de PostgreSQL : écriture des fichiers

Paramétrage de PostgreSQL : les sommes de contrôle

Erreur de manipulation

Outils

Outils : pg_controldata

Outils pour l’export/import de données

Outils : pageinspect

Outils : pg_resetwal

Outils : pg_surgery

Outils pour vérifier les sommes de contrôle

Outils : amcheck & pg_amcheck

Cas type de désastres

Cas type de désastres

Avertissement

Corruption de blocs dans des index

Corruption de blocs dans des tables 1

Corruption de blocs dans des tables 2

Corruption de blocs dans des tables 3

Corruption des WAL 1

Corruption des WAL 2

Corruption du fichier de contrôle

Corruption du CLOG

Corruption du catalogue système

Conclusion

Quiz

Travaux pratiques

Corruption d’un bloc de données

Corruption d’un bloc de données et incohérences

Travaux pratiques (solutions)