So sichern und stellen Sie Datenbanken mit pg_dump und pg_restore wieder her

Sie benötigen eine PostgreSQL-Sicherung, die Sie wiederherstellen können, nicht nur eine Datei, die wie eine Sicherung aussieht. pg_dump und pg_restore sind die Standardwerkzeuge für logische Sicherungen: Sie kopieren Datenbankobjekte und -daten in einen portablen Dump und bauen sie später auf demselben Server, einem Testserver oder einer neuen PostgreSQL-Instanz wieder auf.

Dieser Leitfaden konzentriert sich auf logische Sicherungen einzelner Datenbanken. Für die Notfallwiederherstellung ganzer Cluster, Point-in-Time-Recovery oder sehr große Installationen kombinieren Sie dies mit physischen Sicherungen und WAL-Archivierung.

Grundlegendes zu pg_dump

pg_dump verbindet sich mit einer Datenbank und exportiert deren Schema und Daten. Es sichert keine Rollen, Tablespaces oder globalen Cluster-Objekte; verwenden Sie pg_dumpall --globals-only, wenn Sie diese benötigen.

Ausgabeformate

pg_dump unterstützt mehrere Ausgabeformate:

Klartext (-Fp, Standard): Schreibt SQL, das Sie mit psql wiederherstellen. Es ist einfach zu überprüfen und zu bearbeiten, aber große Wiederherstellungen können langsamer und weniger flexibel sein.
Benutzerdefiniertes Archiv (-Fc): Schreibt ein komprimiertes Archiv für pg_restore. Es unterstützt selektive Wiederherstellung und parallele Wiederherstellung.
Verzeichnisformat (-Fd): Schreibt ein Verzeichnis mit Dateien. Es unterstützt parallelen Dump und parallele Wiederherstellung, was bei großen Datenbanken hilfreich ist.
Tar-Format (-Ft): Schreibt ein Tar-Archiv für pg_restore. Es ist eine einzelne Datei, unterstützt aber keine Komprimierung innerhalb des Formats wie benutzerdefinierte Archive.

Häufige pg_dump-Optionen

Hier sind einige der am häufigsten verwendeten Optionen für pg_dump:

-h <hostname>: Verbindung zu einem Host herstellen.
-p <port>: Verbindung zu einem Port herstellen.
-U <benutzername>: Als Benutzer verbinden.
-W: Vor dem Verbinden zur Eingabe des Passworts auffordern.
-f <dateiname>: In eine Datei oder ein Verzeichnis statt in die Standardausgabe schreiben.
-F <format>: Wählen Sie p, c, d oder t.
-Z <stufe>: Komprimierungsstufe für Formate festlegen, die Komprimierung unterstützen.
--exclude-table=<muster>: Passende Tabellen ausschließen.
-j <anzahl> oder --jobs=<anzahl>: Parallele Jobs beim Dump im Verzeichnisformat verwenden.
--clean: Löschbefehle vor Erstellungsbefehlen in der reinen SQL-Ausgabe einfügen.
--create: Befehle zum Erstellen der Datenbank selbst einfügen.

Beispiel: Einfache Klartext-Sicherung

Um eine Klartext-Sicherung einer Datenbank namens mydatabase zu erstellen, die dem Benutzer myuser gehört, und sie in mydatabase_backup.sql zu speichern:

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

Wenn Sie einen Host und Port angeben müssen:

pg_dump -h localhost -p 5432 -U myuser -f mydatabase_backup.sql mydatabase

Abhängig von Ihrer Authentifizierungsmethode werden Sie möglicherweise nach dem Passwort von myuser gefragt.

Beispiel: Benutzerdefinierte Formatsicherung (empfohlen)

Das benutzerdefinierte Format ist eine gute Standardeinstellung für viele Produktionssicherungen, da es mit pg_restore funktioniert und selektive Wiederherstellung unterstützt.

pg_dump -U myuser -Fc -f mydatabase_backup.dump mydatabase

Dies erstellt mydatabase_backup.dump in einem Format, das für pg_restore geeignet ist.

Beispiel: Verzeichnisformat-Sicherung mit parallelen Jobs

Bei großen Datenbanken kann das parallele Dumping die Sicherungszeit verkürzen. Es erfordert das Verzeichnisformat:

pg_dump -U myuser -Fd -j 4 -f mydatabase_backup_dir mydatabase

Dieser Befehl erstellt ein Verzeichnis namens mydatabase_backup_dir, das mehrere Dateien enthält, und verwendet 4 parallele Jobs.

Grundlegendes zu pg_restore

pg_restore stellt benutzerdefinierte, Verzeichnis- oder Tar-Archive wieder her, die von pg_dump erstellt wurden. Reine SQL-Dateien werden mit psql wiederhergestellt, nicht mit pg_restore.

Häufige pg_restore-Optionen

-h <hostname>: Verbindung zu einem Host herstellen.
-p <port>: Verbindung zu einem Port herstellen.
-U <benutzername>: Als Benutzer verbinden.
-W: Vor dem Verbinden zur Eingabe des Passworts auffordern.
-d <datenbank>: Für die Wiederherstellung mit dieser Datenbank verbinden.
-v: Ausführliche Wiederherstellungsausgabe drucken.
-c oder --clean: Datenbankobjekte vor dem Neuerstellen löschen.
--create: Die gesicherte Datenbank vor der Wiederherstellung erstellen. Mit -d mit einer vorhandenen Wartungsdatenbank verbinden, üblicherweise postgres.
-j <anzahl>: Parallele Jobs für die Wiederherstellung verwenden, wo unterstützt.
-F <format>: Archivformat angeben, wenn die automatische Erkennung nicht ausreicht.

Wiederherstellen aus Klartext-SQL-Sicherungen

Wenn Sie eine Klartext-SQL-Sicherung mit pg_dump erstellt haben (z. B. mydatabase_backup.sql), stellen Sie sie mit psql wieder her:

psql -U myuser -d mydatabase -f mydatabase_backup.sql

Wenn die Sicherungsdatei CREATE DATABASE- und \connect-Befehle von pg_dump --create enthält, verbinden Sie sich mit einer Wartungsdatenbank wie postgres:

psql -U myuser -f mydatabase_backup.sql postgres

Beispiel: Wiederherstellen aus einer benutzerdefinierten Formatsicherung

Um die benutzerdefinierte Formatsicherung mydatabase_backup.dump in einer Datenbank namens restored_db wiederherzustellen:

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Wenn das Archiv mit pg_dump --create erstellt wurde, können Sie pg_restore bitten, die gesicherte Datenbank zu erstellen. Der -d-Wert muss eine vorhandene Datenbank für die anfängliche Verbindung sein, nicht die neue Datenbank, die erstellt wird:

pg_restore -U myuser --create -d postgres mydatabase_backup.dump

Dies erstellt die im Dump benannte Datenbank und stellt sie wieder her. Wenn Sie in eine anders benannte Datenbank wiederherstellen möchten, erstellen Sie diese Datenbank zuerst und stellen Sie ohne --create wieder her:

createdb -U myuser restored_db
pg_restore -U myuser -d restored_db mydatabase_backup.dump

Beispiel: Parallele Wiederherstellung

Für eine schnellere Wiederherstellung, insbesondere bei großen Datensätzen, verwenden Sie die Option -j:

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup.dump

Dies stellt mit 4 Worker-Jobs wieder her. Die parallele Wiederherstellung ist am nützlichsten, wenn der Dump mehrere unabhängige Objekte zum Laden und Indizieren enthält.

Beispiel: Wiederherstellen aus einer Verzeichnisformat-Sicherung

Um eine Sicherung wiederherzustellen, die im Verzeichnisformat erstellt wurde (z. B. mydatabase_backup_dir):

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

Beachten Sie, dass das letzte Argument der Verzeichnispfad ist.

Best Practices für Sicherungen

Sicherungen planen: Verwenden Sie cron, systemd-Timer oder Ihre Orchestrierungsplattform. Wählen Sie einen Zeitplan basierend darauf, wie viele Daten Sie sich leisten können zu verlieren.
Das richtige Format verwenden: Das benutzerdefinierte Format ist eine gute Standardeinstellung. Das Verzeichnisformat ist besser, wenn Sie einen parallelen Dump für eine große Datenbank benötigen.
Wiederherstellungen testen: Stellen Sie regelmäßig in einer Staging-Datenbank wieder her. Eine Sicherung, die Sie nie testen, ist nur eine Vermutung.
Globale Objekte erfassen: Sichern Sie Rollen und Tablespaces separat mit pg_dumpall --globals-only, wenn Sie einen Server von Grund auf neu aufbauen.
Kopien extern speichern: Halten Sie Sicherungen vom primären Server fern und schützen Sie sie mit Verschlüsselung und Zugriffskontrollen.
Jobs überwachen: Alarmieren Sie bei fehlgeschlagenen Dumps, ungewöhnlich kleinen Sicherungsdateien und alten Sicherungszeitstempeln.
Aufbewahrung planen: Entscheiden Sie, wie viele stündliche, tägliche, wöchentliche und monatliche Sicherungen Sie aufbewahren, und automatisieren Sie dann die Bereinigung.
Vorsicht mit --clean: Es löscht Objekte in der Zieldatenbank. Verwenden Sie es nur, wenn Sie vorhandene Objekte ersetzen möchten.

Fazit

Verwenden Sie pg_dump -Fc für eine flexible Einzeldatei-Sicherung, pg_dump -Fd -j, wenn Sie paralleles Dumping benötigen, psql für reine SQL-Wiederherstellungen und pg_restore für Archiv-Wiederherstellungen. Beweisen Sie dann den Prozess, indem Sie in einer Testdatenbank wiederherstellen, bevor Sie ihn im Notfall benötigen.