PostgreSQL: logische Sicherung und Wiederherstellung (pg_dump / pg_restore)

Logische Sicherung und Wiederherstellung mit pg_dump, pg_dumpall und pg_restore auf Ebene von Tabelle, Schema, Datenbank oder ganzem Cluster; nur Struktur (DDL) oder nur Daten, Umzug auf einen anderen Server bzw. eine andere Version, Wiederherstellung einer einzelnen Tabelle aus einem Dump sowie zeitgesteuerte, geprüfte Sicherungen mit Aufbewahrungsfrist über systemd/cron. Jeder Weg endet mit einer Prüfung, die Zeilenzahlen vergleicht. In Streaming-Replication- und Patroni-Clustern wird der Dump von einem Replica gezogen (HAProxy-Leseport oder Standby), die Wiederherstellung erfolgt nur auf dem Primary/Leader.

Eine logische Sicherung führt auf den Zeitpunkt des Dumps zurück und erlaubt keine Point-in-Time-Recovery (PITR); in der Produktion muss die eigentliche Sicherung physisch sein: pgBackRest (empfohlen) oder ohne Zusatzwerkzeuge pg_basebackup + WAL-Archiv. Um eine große Datenbank mit Sekunden Ausfallzeit auf eine neue Version zu bringen, nimm Logical Replication; für ein In-place-Upgrade auf demselben Server pg_upgrade.

Topologie: 1 Server, Streaming Replication (Primary + Standby) oder ein Patroni-Cluster; beim Umzug Quelle + Ziel, bei zeitgesteuerter Remote-Sicherung zusätzlich ein Backup-Server

Szenarien

• Einzelne Tabelle(n) exportieren / importieren — Struktur und Daten der ausgewählten Tabellen (inklusive Indizes, Constraints, Trigger) werden gesichert und in eine andere Datenbank geladen.
• Ein Schema exportieren / importieren — Alle Objekte des Schemas (Tabellen, Views, Funktionen, Sequenzen) werden gesichert.
• Einzelne Datenbank sichern / wiederherstellen — Eine ganze Datenbank (Struktur + Daten) wird in einen einzigen Dump geschrieben und in dieselbe oder eine andere Datenbank zurückgeladen. Rollen (globals) sind nicht enthalten.
• Ganzer Cluster (Rollen + Tablespaces + jede Datenbank) — Globals mit pg_dumpall -g, ein eigener pg_dump je Datenbank; die Wiederherstellung wird auf einem temporären Test-Cluster geprobt.
• Nur Schemastruktur (DDL) — Definitionen von Tabellen, Indizes, Views und Funktionen ohne Daten; zur Prüfung, für die Versionsverwaltung oder zum Aufbau einer leeren Umgebung.
• Nur Daten — Nur die Zeilen (COPY) werden übertragen, während die Struktur auf dem Ziel bereits vorhanden ist.
• Auf anderen Server / andere Version umziehen — Auf dem Zielserver verbindet sich der pg_dump der neuen Version über das Netz mit der Quelle; die Übertragung läuft über eine Pipe oder eine Datei.
• Einzelne Tabelle / einzelnes Objekt aus einem Dump wiederherstellen — Aus einem vorhandenen Custom- oder Directory-Dump wird mit einer Listendatei (-L) nur eine Tabelle samt ihren Indizes, Constraints und Daten wiederhergestellt.
• Zeitgesteuerte Sicherung (systemd timer / cron) — Tägliche Sicherung von Globals + Datenbanken, Lesbarkeitsprüfung, Aufbewahrungsfrist, Log und Fehlermeldung.

Vor dem Start

• …Der Patroni-Cluster läuft; auf diesem Server sind die Client-Werkzeuge von PostgreSQL … (…) installiert, und die Ports von HAProxy/VIP (…) zum Schreiben (…)… und Lesen (…)… sind erreichbar. Du hast das Passwort der Rolle postgres (bei Patroni PATRONI_SUPERUSER_PASSWORD in /etc/patroni/patroni.env).…Auf dem Primary läuft PostgreSQL …, und der Benutzer postgres kann sich lokal (peer) verbinden; der Standby … ist im Zustand streaming, und sein Port … ist vom Primary aus erreichbar. Da sich der Dump über das Netz mit dem Standby verbindet, braucht die Rolle postgres ein Passwort (falls nicht, auf dem Primary \password postgres; das Passwort gelangt per WAL auf den Standby).…PostgreSQL … läuft, und der Betriebssystembenutzer postgres kann sich lokal (peer) verbinden. Wurde mit dem DBAtlas-Guide PostgreSQL-Einzelserver-Installation installiert, sind die Pfade identisch.…
• …Der Cluster ist gesund: ein Leader, mindestens ein Replica im Zustand streaming, geringe Verzögerung. Prüfe das auf einem der Patroni-Nodes.…Der Standby … ist mit dem Primary verbunden, und seine Verzögerung ist gering (der Dump erfasst den Stand des Standby zu diesem Zeitpunkt).…
• Das Sicherungsverzeichnis … liegt auf einer von der Datenplatte getrennten Platte und hat genug freien Platz: Ein komprimierter Custom- oder Directory-Dump hat meist 10–30 % der Datenbankgröße (Indizes kommen nur als Definition in den Dump); Plain-Format und unkomprimierte Dumps sind größer.
• pg_dump hat dieselbe oder eine neuere Hauptversion als der zu sichernde Server (ein neuerer pg_dump kann einen älteren Server lesen, umgekehrt nicht). pg_restore muss dieselbe oder eine neuere Version haben als der pg_dump, der den Dump erstellt hat.
• pg_dump/pg_restore auf dem Ziel (…) haben dieselbe oder eine neuere Hauptversion als der Quellserver (…): Der Umzug läuft mit den neueren Werkzeugen des Ziels.
• Die Software der auf der Quelle verwendeten Extensions (postgis, pgvector, pg_partman…) ist auch auf dem Zielserver installiert. Eine logische Sicherung überträgt nur die Zeile CREATE EXTENSION, nicht die Extension selbst.
• Gibt es benutzerdefinierte Tablespaces (die folgende Abfrage liefert Zeilen), müssen auf dem Ziel dieselben Verzeichnisse vorbereitet werden, oder die Wiederherstellung erfolgt mit --no-tablespaces in den Standard-Tablespace.
• Auf dem Zielserver … ist PostgreSQL … installiert, läuft und ist leer (nur postgres, template0, template1). Das Ziel erreicht den Port … der Quelle.
• Der Backup-Server … erreicht …den Port … der HAProxy-/VIP-Adresse ……den Port … des Standby ……den Port … des Datenbankservers… und hat Zugriff auf das PGDG-Repository (oder einen internen Mirror).
• Der Server kann E-Mails versenden: Der lokale MTA (postfix) leitet an das SMTP-Relay des Unternehmens weiter.
• Für eine große Wiederherstellung oder einen Umzug ist ein Wartungsfenster eingeplant; wird das Ziel überschrieben, existiert eine Sicherung bzw. ein Snapshot des Ziels.

Phasen der Anleitung

• Vorbereitung — Sicherungsverzeichnis anlegen · Verbindungspasswort in .pgpass hinterlegen · Zugriff in pg_hba.conf auf dem Standby erlauben · Prüfen, bei welcher Rolle die Verbindungen ankommen · Datenbankgrößen und lang laufende Transaktionen prüfen
• Wiederherstellung (Import) — Zieldatenbank vorbereiten · Ziel vorbereiten: Extensions, Schemas und Trigger-Funktionen · Wiederherstellen · Unter Überschreiben des Ziels wiederherstellen · Statistiken sammeln (ANALYZE) · Zeilenzahlen vergleichen
• Nur Schemastruktur (DDL) — DDL-Dump erstellen · Zieldatenbank anlegen · DDL im Ziel anwenden · Prüfen, ob die Strukturen identisch sind
• Nur Daten — Daten-Dump erstellen · Zieldatenbank anlegen · Tabellenstruktur im Ziel anlegen · Zieltabellen leeren · Daten laden · Statistiken sammeln und Zeilenzahlen vergleichen
• Sicherung des ganzen Clusters — Globals und alle Datenbanken sichern · Lesbarkeit der Sicherung prüfen
• Wiederherstellungsprobe (temporärer Test-Cluster) — Testverzeichnis vorbereiten · Temporären Cluster anlegen und starten · Globals laden · Datenbanken laden · ANALYZE und Vergleich der Zeilenzahlen · Temporären Cluster stoppen und entfernen
• Zugriffsrecht auf dem Quellserver — Zielserver in pg_hba.conf freigeben · Zielserver in pg_hba freigeben (Patroni) · Port in der Firewall für das Ziel öffnen · Anwendung stoppen (Umschaltzeitpunkt)
• Übertragung auf dem Zielserver — Verbindungspasswort der Quelle in .pgpass hinterlegen · Versions- und Extension-Kompatibilität prüfen · Rollen übertragen (globals) · Datenbanken übertragen (über Dateien, parallel) · Datenbanken übertragen (über eine Pipe) · Statistiken sammeln (ANALYZE) …
• Nach der Umschaltung — Temporären Zugriff auf der Quelle entfernen · Temporären Zugriff auf der Quelle entfernen (Patroni) · Anwendung auf das Ziel umstellen
• Einzelne Tabelle aus einem Dump wiederherstellen — Inhalt des Dumps prüfen · Listendatei erstellen (-L) · Zieldatenbank vorbereiten · Tabelle wiederherstellen · Tabelle in die eigentliche Datenbank wiederherstellen · Zeilenzahl vergleichen …
• Backup-Server und Backup-Rolle — Backup-Rolle anlegen · Leserecht auf Large Objects erteilen · Passwort für die Backup-Rolle setzen · Backup-Server in pg_hba.conf freigeben · Backup-Server in pg_hba freigeben (Patroni) · PostgreSQL-Client-Werkzeuge installieren (Backup-Server) …
• Sicherungsskript — Volle Pfade der Werkzeuge prüfen · Benachrichtigungswerkzeug installieren · Sicherungsskript schreiben · Skript ausführbar machen · Erste Sicherung von Hand ausführen
• Zeitplanung (systemd timer) — systemd-Service schreiben · systemd-Timer schreiben · Timer aktivieren und Service von Hand auslösen
• Zeitplanung (cron) — cron-Dienst vorbereiten · cron-Job schreiben · Rechte der cron-Datei setzen und in einer cron-Umgebung testen · Prüfen, ob cron den Job ausführt
• Prüfung und Monitoring — Letzte Sicherung in eine Testdatenbank wiederherstellen · Alter der letzten Sicherung überwachen
• Nächster Schritt: physische Sicherung — Lücken der logischen Sicherung schließen