Meine erste Erfahrung mit der Wiederherstellung einer Postgres-Datenbank nach einem Ausfall (ungültige Seite im Block 4123007 der Relation base/16490)

Ich möchte meine erste erfolgreiche Erfahrung mit der Wiederherstellung der vollen Funktionsfähigkeit einer Postgres-Datenbank mit Ihnen teilen. Ich habe vor sechs Monaten mit der Datenbankmanagementsoftware Postgres begonnen, zuvor hatte ich keinerlei Erfahrung in der Datenbankadministration.

Ich arbeite als Teilzeit-DevOps-Ingenieur in einem großen IT-Unternehmen. Unser Unternehmen entwickelt Software für hochbelastete Dienste, und ich bin verantwortlich für die Betriebssicherheit, Wartung und das Deployment. Mir wurde die Standardaufgabe übertragen, eine Anwendung auf einem Server zu aktualisieren. Die Anwendung ist in Django geschrieben, und während des Updates werden Migrationen (Änderungen an der Datenbankstruktur) durchgeführt. Vor diesem Prozess erstellen wir zur Sicherheit ein vollständiges Datenbank-Backup mit dem Standardprogramm pg_dump.

Während des Backup-Vorgangs trat ein unerwarteter Fehler auf (Postgres-Version – 9.5):

pg_dump: Das Sichern des Inhalts der Tabelle "ws_log_smevlog" ist fehlgeschlagen: PQgetResult() fehlgeschlagen.
pg_dump: Fehlermeldung des Servers: FEHLER: Ungültige Seite im Block 4123007 der Relation base/16490/21396989
pg_dump: Der Befehl lautete: COPY public.ws_log_smevlog [...]
pg_dump: [parallele Archivierung] Ein Arbeitsprozess wurde unerwartet beendet.

Fehler "ungültige Seite im Block" weist auf Probleme auf der Ebene des Dateisystems hin, was sehr ungünstig ist. In verschiedenen Foren wurde vorgeschlagen, FULL VACUUM mit der Option zero_damaged_pages um dieses Problem zu lösen. Also, lasst uns versuchen…

Vorbereitung zur Wiederherstellung

ACHTUNG! Stellen Sie sicher, dass Sie ein Backup von Postgres machen, bevor Sie versuchen, die Datenbank wiederherzustellen. Falls Sie eine virtuelle Maschine haben, stoppen Sie die Datenbank und erstellen Sie einen Snapshot. Wenn das Erstellen eines Snapshots nicht möglich ist, stoppen Sie die Datenbank und kopieren Sie den Inhalt des Postgres-Verzeichnisses (einschließlich der WAL-Dateien) an einen sicheren Ort. Das Wichtigste ist, es nicht schlimmer zu machen. Lesen Sie dies.

Da die Datenbank insgesamt bei mir funktionierte, beschränkte ich mich auf ein reguläres Datenbank-Dump, schloss jedoch die Tabelle mit den beschädigten Daten aus (Option -T, —exclude-table=TABLE in pg_dump).

Der Server war physisch, einen Snapshot zu erstellen war nicht möglich. Backup erstellt, weiter geht's.

Überprüfung des Dateisystems

Bevor Sie versuchen, die Datenbank wiederherzustellen, müssen Sie sicherstellen, dass mit dem Dateisystem alles in Ordnung ist. Sollten Fehler auftreten, bitte beheben, da sonst möglicherweise nur noch schlimmer wird.

In meinem Fall war das Dateisystem mit der Datenbank unter «/srv» und der Typ war ext4.

Datenbank wird gestoppt: systemctl stop postgresql@9.5-main.service und wir überprüfen, ob das Dateisystem nicht verwendet wird und mit dem Befehl abgemeldet werden kann lsof:
lsof +D /srv

Ich musste auch die Redis-Datenbank anhalten, da sie ebenfalls verwendet wurde «/srv». Danach habe ich das Dateisystem abgemeldet /srv (umount).

Die Überprüfung des Dateisystems wurde mit dem Tool e2fsck mit dem Schalter -f (Force checking even if filesystem is marked clean):

Dann kann man mit dem Tool dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep checked) sicherstellen, dass die Überprüfung tatsächlich durchgeführt wurde:

e2fsck sagt, dass keine Probleme im ext4-Dateisystem gefunden wurden, was bedeutet, dass man die Versuche zur Wiederherstellung der Datenbank fortsetzen kann, genauer gesagt zurückkehren zu vacuum full (natürlich muss das Dateisystem wieder eingehängt und die Datenbank gestartet werden).

Wenn Sie einen physischen Server haben, überprüfen Sie unbedingt den Zustand der Festplatten (über smartctl -a /dev/XXX) oder RAID-Controller, um sicherzustellen, dass das Problem nicht auf Hardwareebene liegt. In meinem Fall stellte sich der RAID als „Hardware“ heraus, also bat ich einen lokalen Administrator, den Zustand des RAID zu überprüfen (der Server war mehrere hundert Kilometer entfernt von mir). Er sagte, dass es keine Fehler gäbe, was bedeutet, dass wir das Wiederherstellungsverfahren definitiv einleiten können.

Versuch 1: zero_damaged_pages

Wir verbinden uns über psql mit der Datenbank, unter Verwendung eines Kontos mit Superuser-Rechten. Wir benötigen unbedingt einen Superuser, da nur dieser die Option zero_damaged_pages ändern kann. In meinem Fall ist das postgres:

psql -h 127.0.0.1 -U postgres -s [database_name]

Option zero_damaged_pages wird benötigt, um Lese-Fehler zu ignorieren (von der Website postgrespro):

Wenn ein beschädigter Seitenkopf erkannt wird, gibt Postgres Pro normalerweise einen Fehler aus und bricht die aktuelle Transaktion ab. Wenn die Option zero_damaged_pages aktiviert ist, gibt das System stattdessen eine Warnung aus, setzt die beschädigte Seite im Speicher auf Null und setzt die Verarbeitung fort. Dieses Verhalten führt zu Datenverlust, da alle Zeilen in der beschädigten Seite betroffen sind.

Wir aktivieren die Option und versuchen, ein vollständiges VACUUM der Tabelle durchzuführen:

VACUUM FULL VERBOSE

Leider ist es fehlgeschlagen.

Wir sind auf einen ähnlichen Fehler gestoßen:

INFO: Leeren von "public.ws_log_smevlog"
WARNUNG: Ungültige Seite im Block 4123007 der Relation base/16400/21396989; Seite wird auf null gesetzt
FEHLER: Unerwartete Chunk-Nummer 573 (erwartet 565) für Toast-Wert 21648541 in pg_toast_106070

pg_toast – Mechanismus zur Speicherung von «langen Daten» in PostgreSQL, wenn sie nicht in eine Seite passen (standardmäßig 8kB).

Versuch 2: Reindexierung

Der erste Rat aus Google hat nicht geholfen. Nach ein paar Minuten Suche fand ich den zweiten Rat – machen wir eine Reindexierung der beschädigten Tabelle. Diesen Rat habe ich an vielen Orten gesehen, aber er schien mir nicht vertrauenswürdig. Lassen Sie uns die Reindexierung durchführen:

Reindexierung der Tabelle ws_log_smevlog

eine Reindexierung wurde problemlos abgeschlossen.

Allerdings hat das nicht geholfen, VACUUM FULL schloss mit einem ähnlichen Fehler ab. Da ich an Misserfolg gewöhnt bin, begann ich weiter im Internet nach Ratschlägen zu suchen und stieß auf einen recht interessanten den Artikel.

Versuch 3: SELECT, LIMIT, OFFSET

Im obigen Artikel wurde vorgeschlagen, die Tabelle zeilenweise zu betrachten und problematische Daten zu löschen. Zunächst musste ich alle Zeilen durchsehen:

for ((i=0; i/dev/null || echo $i; done

In meinem Fall enthielt die Tabelle 1 628 991 Zeilen! Im Grunde genommen hätte man sich um die Partitionierung der Daten kümmern müssen., aber das ist ein Thema für eine separate Diskussion. Es war Samstag, ich habe diesen Befehl in tmux ausgeführt und bin schlafen gegangen:

for ((i=0; i/dev/null || echo $i; done

Am Morgen wollte ich nachsehen, wie es steht. Zu meinem Erstaunen stellte ich fest, dass in 20 Stunden nur 2% der Daten gescannt wurden! 50 Tage wollte ich nicht warten. Ein weiterer totaler Fehlschlag.

Aber ich wollte nicht aufgeben. Mich interessierte, warum das Scannen so lange dauerte. Aus der Dokumentation (wieder auf postgrespro) erfuhr ich:

OFFSET gibt an, wie viele Zeilen übersprungen werden sollen, bevor die Ausgabe der Zeilen beginnt.
Wenn sowohl OFFSET als auch LIMIT angegeben sind, überspringt das System zunächst die OFFSET-Zeilen und beginnt dann, die Zeilen für die LIMIT-Beschränkung zu zählen.

Bei der Anwendung von LIMIT ist es wichtig, auch die ORDER BY-Klausel zu verwenden, damit die Ergebnissätze in einer bestimmten Reihenfolge ausgegeben werden. Andernfalls werden unvorhersehbare Teilmengen von Zeilen zurückgegeben.

Es ist offensichtlich, dass der oben angegebene Befehl fehlerhaft war: Erstens gab es kein order by, das Ergebnis hätte fehlerhaft sein können. Zweitens musste Postgres zuerst die OFFSET-Zeilen scannen und überspringen, und mit zunehmendem OFFSET Die Leistung wäre noch weiter gesunken.

Versuch 4: Erstellen eines Dumps im Textformat

Dann kam mir, scheinbar genial, die Idee: einen Dump im Textformat zu erstellen und die letzte erfolgreich aufgezeichnete Zeile zu analysieren.

Aber zuerst lassen Sie uns die Struktur der Tabelle kennenlernen. ws_log_smevlog:

In unserem Fall gibt es eine Spalte „id“, die die eindeutige Identifikationsnummer (Zähler) der Zeile enthielt. Der Plan war folgender:

1. Wir beginnen mit dem Erstellen eines Dumps im Textformat (in Form von SQL-Befehlen)
2. Zu einem bestimmten Zeitpunkt würde das Erstellen des Dumps aufgrund eines Fehlers unterbrochen, aber die Textdatei würde dennoch auf der Festplatte gespeichert.
3. Wir schauen uns das Ende der Textdatei an und finden damit die Identifikation (id) der letzten Zeile, die erfolgreich erfasst wurde.

Ich begann, den Dump im Textformat zu erstellen:

pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dump

Das Erstellen des Dumps wurde, wie erwartet, mit demselben Fehler unterbrochen:

pg_dump: Fehlermeldung vom Server: FEHLER: ungültige Seite im Block 4123007 der Relation base/16490/21396989

Dann durch tail überschlug ich das Ende des Dumps (tail -5 ./my_dump.dump) und stellte fest, dass der Dump bei der Zeile mit der id 186 525unterbrochen wurde. "Das heißt, das Problem liegt in der Zeile mit der id 186 526, sie ist beschädigt und muss gelöscht werden!" – dachte ich. Aber nachdem ich eine Anfrage an die Datenbank geschickt hatte:
«SELECT * FROM ws_log_smevlog WHERE id=186529» Es stellte sich heraus, dass mit dieser Zeile alles in Ordnung war… Die Zeilen mit den Indizes 186530 – 186540 funktionierten ebenfalls einwandfrei. Eine weitere „geniale Idee“ scheiterte. Später verstand ich, warum das so war: Beim Löschen oder Ändern von Daten in der Tabelle werden diese nicht physisch entfernt, sondern als „tote Tupel“ markiert. autovacuum Daraufhin markiert das System diese Zeilen als gelöscht und erlaubt, sie erneut zu verwenden. Das ist wichtig zu verstehen: Wenn die Daten in der Tabelle geändert werden und autovacuum aktiviert ist, werden sie nicht sequenziell gespeichert.

Versuch 5: SELECT, FROM, WHERE id=

Rückschläge machen uns stärker. Man sollte niemals aufgeben, man muss bis zum Ende gehen und an sich und seine Fähigkeiten glauben. Daher beschloss ich, noch einen weiteren Ansatz zu versuchen: einfach alle Einträge in der Datenbank einzeln durchzusehen. Ich kenne die Struktur meiner Tabelle (siehe oben), wir haben ein Feld id, das einzigartig (Primärschlüssel) ist. In der Tabelle sind 1.628.991 Zeilen und id sie sind der Reihe nach angeordnet, was bedeutet, dass wir sie einfach einzeln durchgehen können:

for ((i=1; i /dev/null || echo $i; done

Falls jemand nicht versteht, funktioniert der Befehl wie folgt: Er durchläuft die Tabelle zeilenweise und sendet stdout an /dev/null, aber wenn der SELECT-Befehl fehlschlägt, erscheint eine Fehlermeldung (stderr wird in die Konsole ausgegeben), und eine Zeile mit dem Fehler wird angezeigt (dank ||, was bedeutet, dass es beim Select Probleme gab (der Rückgabewert des Befehls ist nicht 0)).

Ich hatte Glück, ich hatte Indizes für das Feld erstellt id:

Das bedeutet, dass das Finden einer Zeile mit der benötigten ID nicht viel Zeit in Anspruch nehmen sollte. Theoretisch sollte es funktionieren. Also, lassen Sie uns den Befehl in tmux ausführen und gehen schlafen.

Am Morgen stellte ich fest, dass etwa 90.000 Datensätze durchgesehen wurden, was etwas mehr als 5 % entspricht. Ein hervorragendes Ergebnis im Vergleich zur vorherigen Methode (2 %)! Aber 20 Tage warten wollte ich nicht…

Versuch 6: SELECT, FROM, WHERE id >= and id <

Für die Datenbank hatte der Kunde einen hervorragenden Server reserviert: Zweikernprozessor Intel Xeon E5-2697 v2, und in unserer Konfiguration standen ganze 48 Threads zur Verfügung! Die Serverauslastung war durchschnittlich, wir konnten ohne größere Probleme etwa 20 Threads abholen. Auch der Arbeitsspeicher war ausreichend: ganze 384 Gigabyte!

Daher musste der Befehl parallelisiert werden:

for ((i=1; i /dev/null || echo $i; done

Hier hätte ich ein schönes und elegantes Skript schreiben können, aber ich wählte den schnellsten Weg zur Parallelisierung: den Bereich 0-1628991 manuell in Intervalle von 100.000 Datensätzen aufzuteilen und 16 separate Befehle zu starten.

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; done

Aber das ist noch nicht alles. Das Herstellen einer Verbindung zur Datenbank benötigt ebenfalls Zeit und Systemressourcen. Es wäre nicht sinnvoll, 1.628.991 zu verbinden, das würden Sie doch auch nicht sagen. Daher extrahieren wir bei einer Verbindung 1000 Zeilen anstelle von einer. Schließlich wurde der Befehl so umgestaltet:

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; done

Öffnen Sie 16 Fenster in einer tmux-Session und führen Sie die Befehle aus:

1) for ((i=0; i=$i and id/dev/null || echo $i; done
2) for ((i=100000; i=$i and id/dev/null || echo $i; done
…
15) for ((i=1400000; i=$i and id/dev/null || echo $i; done
16) for ((i=1500000; i=$i and id/dev/null || echo $i; done

Einen Tag später erhielt ich die ersten Ergebnisse! Genauer gesagt (die Werte XXX und ZZZ wurden nicht mehr gespeichert):

FEHLER: fehlende Chunk-Nummer 0 für Toast-Wert 37837571 in pg_toast_106070
829000
FEHLER: fehlende Chunk-Nummer 0 für Toast-Wert XXX in pg_toast_106070
829000
FEHLER: fehlende Chunk-Nummer 0 für Toast-Wert ZZZ in pg_toast_106070
146000

Das bedeutet, dass wir drei Zeilen mit einem Fehler haben. Die ID des ersten und zweiten problematischen Eintrags lag zwischen 829000 und 830000, die ID des dritten zwischen 146000 und 147000. Als Nächstes mussten wir nur den genauen Wert der ID der problematischen Einträge finden. Dazu durchsuchen wir den Bereich mit den problematischen Einträgen schrittweise und identifizieren die ID:

for ((i=829000; i/dev/null || echo $i; done
829417
ERROR:  unerwartete Chunk-Nummer 2 (erwartet 0) für Toast-Wert 37837843 in pg_toast_106070
829449
for ((i=146000; i/dev/null || echo $i; done
829417
ERROR:  unerwartete Chunk-Nummer ZZZ (erwartet 0) für Toast-Wert XXX in pg_toast_106070
146911

Happy End

Wir haben problematische Zeilen gefunden. Wir greifen über psql auf die Datenbank zu und versuchen, sie zu löschen:

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

Zu meinem Erstaunen wurden die Einträge problemlos gelöscht, sogar ohne die Option zero_damaged_pages.

Dann habe ich mich mit der Datenbank verbunden, habe VACUUM FULL (ich denke, das war nicht nötig), und schließlich konnte ich erfolgreich ein Backup mit Hilfe von pg_dump. Das Dump wurde ohne Fehler erstellt! Das Problem konnte auf so eine einfache Weise gelöst werden. Die Freude war riesig, nach so vielen Misserfolgen endlich eine Lösung gefunden zu haben!

Danksagungen und Fazit

So war meine erste Erfahrung mit der Wiederherstellung einer echten Postgres-Datenbank. Diese Erfahrung werde ich lange in Erinnerung behalten.

Und schließlich möchte ich PostgresPro für die übersetzte Dokumentation ins Russische und für vollständig kostenlose Online-Kurse, die während der Problemanalyse sehr hilfreich waren.

Quelle: habr.com