J'aimerais partager avec vous ma première expérience réussie de restauration d'une base de données Postgres avec toutes ses fonctionnalités. J'ai découvert le SGBD Postgres il y a six mois ; auparavant, je n'avais aucune expérience en administration de bases de données.
Je travaille en tant qu'ingénieur semi-DevOps dans une grande entreprise informatique. Notre société développe des logiciels pour les services à forte charge, et je suis responsable de la performance, de la maintenance et du déploiement. On m'a confié une tâche standard : mettre à jour une application sur un serveur. L'application est écrite en Django, lors de la mise à jour, des migrations sont effectuées (modifications dans la structure de la base de données), et avant ce processus, nous effectuons un vidage complet de la base de données via le programme standard pg_dump, juste au cas où.
Une erreur inattendue s'est produite lors de la réalisation d'un dump (Postgres version 9.5) :
pg_dump: Oumping the contents of table “ws_log_smevlog” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
pg_dump: The command was: COPY public.ws_log_smevlog [...]
pg_dunp: [parallel archtver] a worker process dled unexpectedly
Punaise "page invalide dans le bloc" parle de problèmes au niveau du système de fichiers, ce qui est très grave. Sur divers forums, il a été suggéré de faire VIDE COMPLET avec possibilité zero_damaged_pages pour résoudre ce problème. Eh bien, essayons...
Préparer la récupération
ATTENTION! Assurez-vous d'effectuer une sauvegarde Postgres avant toute tentative de restauration de votre base de données. Si vous disposez d'une machine virtuelle, arrêtez la base de données et prenez un instantané. S'il n'est pas possible de prendre un instantané, arrêtez la base de données et copiez le contenu du répertoire Postgres (y compris les fichiers wal) dans un endroit sûr. L’essentiel dans notre métier n’est pas d’aggraver les choses. Lire .
Étant donné que la base de données fonctionnait généralement pour moi, je me suis limité à un vidage régulier de la base de données, mais j'ai exclu la table contenant les données endommagées (option -T, --exclude-table=TABLE dans pg_dump).
Le serveur était physique, il était impossible de prendre un instantané. La sauvegarde a été supprimée, passons à autre chose.
Vérification du système de fichiers
Avant de tenter de restaurer la base de données, nous devons nous assurer que tout est en ordre avec le système de fichiers lui-même. Et en cas d’erreurs, corrigez-les, car sinon vous ne pourrez qu’empirer les choses.
Dans mon cas, le système de fichiers avec la base de données a été monté dans "/srv" et le type était ext4.
Arrêt de la base de données : arrêt systemctl [email protected] et vérifiez que le système de fichiers n'est utilisé par personne et peut être démonté à l'aide de la commande lsof:
lsof +D /srv
J'ai également dû arrêter la base de données Redis, car elle utilisait également "/srv". Ensuite j'ai démonté / srv (monter).
Le système de fichiers a été vérifié à l'aide de l'utilitaire e2fsck avec le commutateur -f (Forcer la vérification même si le système de fichiers est marqué comme propre):
Ensuite, en utilisant l'utilitaire dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep vérifié), vous pouvez vérifier que le contrôle a bien été effectué :
e2fsck dit qu'aucun problème n'a été trouvé au niveau du système de fichiers ext4, ce qui signifie que vous pouvez continuer à essayer de restaurer la base de données, ou plutôt revenir à vide plein (bien sûr, vous devez remonter le système de fichiers et démarrer la base de données).
Si vous disposez d'un serveur physique, veillez à vérifier l'état des disques (via smartctl -a /dev/XXX) ou un contrôleur RAID pour vous assurer que le problème ne vient pas du niveau matériel. Dans mon cas, le RAID s'est avéré être « matériel », j'ai donc demandé à l'administrateur local de vérifier l'état du RAID (le serveur était à plusieurs centaines de kilomètres de moi). Il a dit qu’il n’y avait aucune erreur, ce qui signifie que nous pouvons définitivement commencer la restauration.
Tentative 1 : zero_damaged_pages
Nous nous connectons à la base de données via psql avec un compte disposant des droits de superutilisateur. Nous avons besoin d'un superutilisateur, parce que... option zero_damaged_pages lui seul peut changer. Dans mon cas, c'est postgres :
psql -h 127.0.0.1 -U postgres -s [nom_base de données]
Option zero_damaged_pages nécessaire pour ignorer les erreurs de lecture (à partir du site Web de postgrespro) :
Lorsque PostgreSQL détecte un en-tête de page corrompu, il signale généralement une erreur et abandonne la transaction en cours. Si zero_damaged_pages est activé, le système émet un avertissement, remet à zéro la page endommagée en mémoire et poursuit le traitement. Ce comportement détruit les données, à savoir toutes les lignes de la page endommagée.
Nous activons l'option et essayons de faire un vide complet des tables :
VACUUM FULL VERBOSE
Malheureusement, pas de chance.
Nous avons rencontré une erreur similaire :
INFO: vacuuming "“public.ws_log_smevlog”
WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page
ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070– un mécanisme de stockage des « données longues » dans Poetgres si elles ne tiennent pas sur une seule page (8 Ko par défaut).
Tentative 2 : réindexer
Les premiers conseils de Google n’ont pas aidé. Après quelques minutes de recherche, j'ai trouvé le deuxième conseil : faire réindexer tableau endommagé. J'ai vu ce conseil à de nombreux endroits, mais il n'inspire pas confiance. Réindexons :
reindex table ws_log_smevlog
réindexer terminé sans problèmes.
Cependant, cela n'a pas aidé, VIDE PLEIN s'est écrasé avec une erreur similaire. Comme je suis habitué aux échecs, j'ai commencé à chercher des conseils sur Internet et je suis tombé sur un sujet plutôt intéressant. .
Tentative 3 : SÉLECTION, LIMITE, DÉCALAGE
L'article ci-dessus suggérait d'examiner le tableau ligne par ligne et de supprimer les données problématiques. Nous devions d’abord examiner toutes les lignes :
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; doneDans mon cas, le tableau contenait +1 (628)991 lignes! Il fallait bien prendre soin de , mais c'est un sujet pour une discussion distincte. C'était samedi, j'ai exécuté cette commande dans tmux et je me suis couché :
for ((i=0; i<1628991; i++ )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog LIMIT 1 offset $i" >/dev/null || echo $i; doneLe matin, j'ai décidé de vérifier comment les choses se passaient. À ma grande surprise, j'ai découvert qu'au bout de 20 heures, seulement 2% des données avaient été scannées ! Je ne voulais pas attendre 50 jours. Encore un échec complet.
Mais je n'ai pas abandonné. Je me demandais pourquoi la numérisation prenait autant de temps. À partir de la documentation (encore une fois sur Postgrespro), j'ai découvert :
OFFSET spécifie d'ignorer le nombre de lignes spécifié avant de commencer à afficher les lignes.
Si OFFSET et LIMIT sont spécifiés, le système ignore d'abord les lignes OFFSET, puis commence à compter les lignes pour la contrainte LIMIT.Lors de l'utilisation de LIMIT, il est important d'utiliser également une clause ORDER BY afin que les lignes de résultats soient renvoyées dans un ordre spécifique. Sinon, des sous-ensembles de lignes imprévisibles seront renvoyés.
Évidemment, la commande ci-dessus était fausse : premièrement, il n'y avait pas de commandé par, le résultat pourrait être erroné. Deuxièmement, Postgres a d'abord dû analyser et ignorer les lignes OFFSET, et avec l'augmentation OFFSET la productivité diminuerait encore davantage.
Tentative 4 : effectuer un dump sous forme de texte
Puis une idée apparemment géniale m'est venue à l'esprit : faire un dump sous forme de texte et analyser la dernière ligne enregistrée.
Mais d’abord, examinons la structure du tableau. ws_log_smevlog:
Dans notre cas nous avons une colonne "Identifiant", qui contenait l'identifiant unique (compteur) de la ligne. Le plan était le suivant :
- Nous commençons à faire un dump sous forme de texte (sous forme de commandes SQL)
- À un moment donné, le dump serait interrompu en raison d'une erreur, mais le fichier texte serait toujours enregistré sur le disque.
- Nous regardons la fin du fichier texte, nous trouvons ainsi l'identifiant (id) de la dernière ligne qui a été supprimée avec succès
J'ai commencé à faire un dump sous forme de texte :
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpLe dump, comme prévu, a été interrompu avec la même erreur :
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
Plus loin queue J'ai regardé la fin du dump (queue -5 ./my_dump.dump) a découvert que le dump avait été interrompu sur la ligne avec l'identifiant +186 (525)XNUMX XNUMX. "Le problème vient donc de la ligne avec l'identifiant 186 526, elle est cassée et doit être supprimée !" - Je pensais. Mais en faisant une requête à la base de données :
«sélectionnez * dans ws_log_smevlog où id=186529"Il s'est avéré que tout allait bien avec cette ligne... Les lignes avec les indices 186 530 - 186 540 fonctionnaient également sans problème. Une autre « idée brillante » a échoué. Plus tard, j'ai compris pourquoi cela se produisait : lors de la suppression et de la modification des données d'une table, elles ne sont pas physiquement supprimées, mais sont marquées comme « tuples morts », puis vient aspirateur automatique et marque ces lignes comme supprimées et permet à ces lignes d'être réutilisées. Pour comprendre, si les données du tableau changent et que le vide automatique est activé, ils ne sont pas stockés de manière séquentielle.
Tentative 5 : SELECT, FROM, WHERE id=
Les échecs nous rendent plus forts. Il ne faut jamais abandonner, il faut aller jusqu'au bout et croire en soi et en ses capacités. J'ai donc décidé d'essayer une autre option : il suffit de parcourir tous les enregistrements de la base de données un par un. Connaissant la structure de ma table (voir ci-dessus), nous disposons d'un champ id qui est unique (clé primaire). Nous avons 1 628 991 lignes dans le tableau et id sont dans l'ordre, ce qui signifie que nous pouvons les parcourir un par un :
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneSi quelqu'un ne comprend pas, la commande fonctionne comme suit : elle analyse le tableau ligne par ligne et envoie stdout à / dev / null, mais si la commande SELECT échoue, alors le texte d'erreur est imprimé (stderr est envoyé à la console) et une ligne contenant l'erreur est imprimée (grâce à ||, ce qui signifie que la sélection a eu des problèmes (le code retour de la commande n'est pas 0)).
J'ai eu de la chance, j'avais des index créés sur le terrain id:
Cela signifie que trouver une ligne avec l’identifiant souhaité ne devrait pas prendre beaucoup de temps. En théorie cela devrait fonctionner. Eh bien, exécutons la commande dans tmux et allons nous coucher.
Au matin, j'ai constaté qu'environ 90 000 entrées avaient été consultées, soit un peu plus de 5 %. Un excellent résultat par rapport à la méthode précédente (2%) ! Mais je ne voulais pas attendre 20 jours...
Tentative 6 : SELECT, FROM, WHERE id >= et id
Le client disposait d'un excellent serveur dédié à la base de données : bi-processeur Intel Xeon E5-2697 v2, il y avait jusqu'à 48 discussions dans notre région ! La charge sur le serveur était moyenne, nous avons pu télécharger une vingtaine de threads sans aucun problème. Il y avait aussi assez de RAM : jusqu'à 20 gigaoctets !
Par conséquent, la commande devait être parallélisée :
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneIci, il était possible d'écrire un script beau et élégant, mais j'ai choisi la méthode de parallélisation la plus rapide : diviser manuellement la plage 0-1628991 en intervalles de 100 000 enregistrements et exécuter séparément 16 commandes du formulaire :
for ((i=N; i<M; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneMais ce n'est pas tout. En théorie, la connexion à une base de données prend également du temps et des ressources système. Connecter 1 628 991 n’était pas très intelligent, vous en conviendrez. Par conséquent, récupérons 1000 XNUMX lignes au lieu d’une connexion sur une. En conséquence, l’équipe s’est transformée en ceci :
for ((i=N; i<M; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; doneOuvrez 16 fenêtres dans une session tmux et exécutez les commandes :
1) for ((i=0; i<100000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 2) for ((i=100000; i<200000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done … 15) for ((i=1400000; i<1500000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 16) for ((i=1500000; i<1628991; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done
Un jour plus tard, j'ai reçu les premiers résultats ! A savoir (les valeurs XXX et ZZZ ne sont plus conservées) :
ERROR: missing chunk number 0 for toast value 37837571 in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value XXX in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value ZZZ in pg_toast_106070
146000Cela signifie que trois lignes contiennent une erreur. Les identifiants du premier et du deuxième enregistrement problématique étaient compris entre 829 000 et 830 000, les identifiants du troisième étaient compris entre 146 000 et 147 000. Ensuite, nous devions simplement trouver la valeur exacte de l'identifiant des enregistrements problématiques. Pour ce faire, nous parcourons notre gamme d'enregistrements problématiques avec un pas de 1 et identifions l'identifiant :
for ((i=829000; i<830000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number 2 (expected 0) for toast value 37837843 in pg_toast_106070 829449 for ((i=146000; i<147000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number ZZZ (expected 0) for toast value XXX in pg_toast_106070 146911
Fin heureuse
Nous avons trouvé les lignes problématiques. On va dans la base de données via psql et on essaie de les supprimer :
my_database=# delete from ws_log_smevlog where id=829417;
DELETE 1
my_database=# delete from ws_log_smevlog where id=829449;
DELETE 1
my_database=# delete from ws_log_smevlog where id=146911;
DELETE 1À ma grande surprise, les entrées ont été supprimées sans aucun problème, même sans l'option zero_damaged_pages.
Ensuite, je me suis connecté à la base de données, j'ai fait VIDE PLEIN (Je pense que ce n'était pas nécessaire de faire cela), et finalement j'ai réussi à supprimer la sauvegarde en utilisant pg_dump. Le dump a été effectué sans aucune erreur ! Le problème a été résolu d’une manière tellement stupide. La joie n'avait pas de limites, après tant d'échecs nous avons réussi à trouver une solution !
Remerciements et conclusion
C'est ainsi que s'est déroulée ma première expérience de restauration d'une véritable base de données Postgres. Je me souviendrai longtemps de cette expérience.
Et enfin, je voudrais remercier PostgresPro pour la traduction de la documentation en russe et pour , ce qui a beaucoup aidé lors de l'analyse du problème.
Source: habr.com