Datenbanken mit pg_dump und pg_restore sichern und wiederherstellen

In der Welt der Datenbankverwaltung sind robuste Backup- und Wiederherstellungsstrategien nicht nur empfehlenswert, sondern absolut unerlässlich. Datenverlust kann aus verschiedenen Quellen entstehen, darunter Hardwareausfälle, menschliches Versagen oder böswillige Angriffe. PostgreSQL, eine leistungsstarke Open-Source-Relationale Datenbank, bietet wesentliche Kommandozeilen-Dienstprogramme, pg_dump und pg_restore, um die Integrität und Wiederherstellbarkeit Ihrer Daten zu gewährleisten. Dieses Tutorial führt Sie durch die besten Vorgehensweisen bei der Nutzung dieser Tools, um zuverlässige Backups zu erstellen und sichere Wiederherstellungen Ihrer PostgreSQL-Datenbanken durchzuführen.

Das Verständnis dieser Dienstprogramme ist grundlegend für jeden PostgreSQL-Administrator oder -Entwickler, der für den Datenschutz verantwortlich ist. pg_dump wird verwendet, um eine PostgreSQL-Datenbank in eine Skriptdatei oder eine andere Archivdatei zu extrahieren, während pg_restore solche Dateien interpretieren kann, um die Datenbank zu rekonstruieren. Ihre Beherrschung versetzt Sie in die Lage, Ihre wertvollen Informationen zu schützen und die Geschäftskontinuität aufrechtzuerhalten.

pg_dump verstehen

pg_dump ist ein Dienstprogramm zur Erstellung von Sicherungskopien einer PostgreSQL-Datenbank. Es generiert eine Datei, die SQL-Befehle enthält, mit denen die Datenbankobjekte (Tabellen, Funktionen, Indizes usw.) neu erstellt und mit Daten gefüllt werden können. pg_dump funktioniert, indem es sich mit der Datenbank verbindet und dann die Ausgabe generiert. Es kann in verschiedenen Formaten ausgeben, die jeweils eigene Vorteile haben.

Ausgabeformate

pg_dump unterstützt mehrere Ausgabeformate:

Unformatiertes SQL-Skript (-Fp oder Standard): Dies ist das einfachste Format. Es gibt eine Datei aus, die eine Reihe von SQL-Befehlen enthält. Dieses Format ist menschenlesbar und einfach zu skripten, kann aber bei sehr großen Datenbanken groß und langsamer bei der Wiederherstellung sein.
Benutzerdefiniertes Archiv (-Fc): Dieses Format erstellt eine komprimierte, benutzerdefinierte Archivdatei. Es ist im Allgemeinen das empfohlene Format für Backups, da es standardmäßig komprimiert ist, die parallele Wiederherstellung (pg_restore) unterstützt und im Allgemeinen flexibler ist.
Verzeichnisformat (-Fd): Dies erstellt ein Verzeichnis, das mehrere Dateien enthält, eine für jede Tabelle und andere Objekte. Es unterstützt ebenfalls die parallele Wiederherstellung und ist nützlich für sehr große Datenbanken.
Tar-Format (-Ft): Dies erstellt ein Tar-Archiv. Es ähnelt dem Verzeichnisformat, ist aber eine einzelne Datei. Es ist nützlich für die Kompatibilität mit anderen Tools, die Tar-Archive verarbeiten.

Häufige pg_dump-Optionen

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

-h <hostname>: Gibt den Hostnamen der Maschine an, auf der der Server läuft.
-p <port>: Gibt den TCP-Port an, auf dem der Server auf Verbindungen wartet.
-U <username>: Verbindet sich als der angegebene Benutzer.
-W: Erzwingt, dass pg_dump vor der Verbindung nach einem Passwort fragt.
-f <filename>: Gibt den Namen der Ausgabedatei an. Wenn weggelassen, schreibt pg_dump in die Standardausgabe.
-F <format>: Gibt das Ausgabeformat der Datei an (p, c, d, t).
-Z <0-9>: Gibt die Komprimierungsstufe für komprimierte Formate an (z. B. Fc).
--exclude-table=<table_name>: Schließt die angegebene Tabelle vom Dump aus.
--jobs=<number>: Anzahl der parallelen Jobs, die beim Sichern verwendet werden sollen (nur für das Verzeichnisformat).
--clean: Schließt Befehle ein, um Datenbankobjekte zu löschen, bevor sie erstellt werden. Dies ist nützlich für vollständige Wiederherstellungen, um einen sauberen Zustand zu gewährleisten.
--create: Schließt einen Befehl ein, um die Datenbank selbst zu erstellen. Nützlich, wenn auf einem neuen Server wiederhergestellt wird.

Beispiel: Einfaches Backup im Klartextformat

Um ein Backup der Datenbank mydatabase im Klartextformat zu erstellen, die dem Benutzer myuser gehört, und es in mydatabase_backup.sql zu speichern:

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

Wenn Sie Host und Port angeben müssen:

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

Dieser Befehl fragt nach dem Passwort für myuser.

Beispiel: Backup im benutzerdefinierten Format (Empfohlen)

Die Verwendung des benutzerdefinierten Archivformats wird aufgrund seiner Flexibilität und Komprimierung im Allgemeinen bevorzugt. Um ein komprimiertes benutzerdefiniertes Backup von mydatabase zu erstellen:

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

Dies erstellt eine Datei namens mydatabase_backup.dump, die komprimiert und in einem Format vorliegt, das für pg_restore geeignet ist.

Beispiel: Backup im Verzeichnisformat mit parallelen Jobs

Bei sehr großen Datenbanken kann das parallele Sichern den Backup-Prozess erheblich beschleunigen. Dies 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, wobei 4 parallele Jobs verwendet werden.

pg_restore verstehen

pg_restore ist ein Dienstprogramm zur Wiederherstellung einer PostgreSQL-Datenbank aus einem Archiv, das von pg_dump im benutzerdefinierten, Verzeichnis- oder Tar-Format erstellt wurde. Es wird nicht für SQL-Dumps im Klartextformat verwendet (diese werden mit dem psql-Befehl wiederhergestellt).

Häufige pg_restore-Optionen

-h <hostname>: Gibt den Hostnamen der Maschine an, auf der der Server läuft.
-p <port>: Gibt den TCP-Port an, auf dem der Server auf Verbindungen wartet.
-U <username>: Verbindet sich als der angegebene Benutzer.
-W: Erzwingt, dass pg_restore vor der Verbindung nach einem Passwort fragt.
-d <database>: Gibt den Namen der Datenbank an, in die wiederhergestellt werden soll. Dies ist obligatorisch.
-v: Ausführlicher Modus (Verbose).
-c oder --clean: Datenbankobjekte vor der Neuerstellung löschen.
--create: Die Datenbank erstellen, bevor darin wiederhergestellt wird. Erfordert die anfängliche Verbindung zu einer anderen Datenbank (z. B. postgres).
-j <number>: Anzahl der parallelen Jobs, die für die Wiederherstellung verwendet werden sollen. Dies beschleunigt die Wiederherstellung erheblich, insbesondere bei benutzerdefinierten Formaten und Verzeichnisformaten.
-F <format>: Gibt das Archivformat an (c, d, t). Normalerweise kann pg_restore dies automatisch erkennen.

Wiederherstellung aus Backups im Klartext-SQL-Format

Wenn Sie ein Backup im Klartext-SQL-Format mit pg_dump erstellt haben (z. B. mydatabase_backup.sql), stellen Sie es mit psql wieder her:

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

Wenn die Sicherungsdatei CREATE DATABASE- und \connect-Befehle enthält, müssen Sie sich möglicherweise mit einer anderen Datenbank (wie postgres) verbinden und das Argument -d mydatabase weglassen:

psql -U myuser -f mydatabase_backup.sql postgres

Beispiel: Wiederherstellung aus einem Backup im benutzerdefinierten Format

Um das Backup im benutzerdefinierten Format mydatabase_backup.dump in eine Datenbank namens restored_db wiederherzustellen:

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Wenn die Zieldatenbank restored_db nicht existiert, können Sie die Option --create verwenden. In diesem Fall müssen Sie sich mit einer anderen Datenbank (wie postgres) verbinden, da die Zieldatenbank erstellt wird.

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

Dieser Befehl erstellt die Datenbank restored_db und stellt dann die Daten darin wieder her.

Beispiel: Parallele Wiederherstellung

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

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

Dies versucht, die Datenbank mithilfe von 4 parallelen Jobs wiederherzustellen.

Beispiel: Wiederherstellung aus einem Backup im Verzeichnisformat

Um ein im Verzeichnisformat erstelltes Backup (z. B. mydatabase_backup_dir) wiederherzustellen:

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

Beachten Sie, dass das letzte Argument der Verzeichnispfad ist.

Empfohlene Vorgehensweisen für Backups

Regelmäßige Planung: Automatisieren Sie Ihre Backups mithilfe von cron (Linux/macOS) oder Task Scheduler (Windows), damit sie in regelmäßigen Abständen (z. B. täglich, stündlich) ausgeführt werden.
Wählen Sie das richtige Format: Für die meisten Anwendungsfälle wird das benutzerdefinierte Format (-Fc) aufgrund seiner Komprimierung und Kompatibilität mit den parallelen Funktionen von pg_restore empfohlen. Verwenden Sie das Verzeichnisformat (-Fd) für extrem große Datenbanken, bei denen das parallele Sichern/Wiederherstellen von entscheidender Bedeutung ist.
Testen Sie Ihre Backups: Ein Backup ist nur dann nützlich, wenn es wiederhergestellt werden kann. Testen Sie Ihren Wiederherstellungsprozess regelmäßig, indem Sie auf einer Staging- oder Entwicklungsumgebung wiederherstellen, um die Datenintegrität und die Gültigkeit Ihrer Sicherungsdateien zu gewährleisten.
Bewahren Sie Backups extern auf: Speichern Sie Kopien Ihrer Backups an einem anderen physischen Ort als Ihrem primären Datenbankserver, um sich vor katastrophalen Ausfällen des gesamten Standorts zu schützen.
Überwachen Sie Backup-Jobs: Implementieren Sie eine Überwachung, um sicherzustellen, dass Ihre geplanten Backup-Jobs erfolgreich ausgeführt werden, und um eine Benachrichtigung zu erhalten, falls sie fehlschlagen.
Berücksichtigen Sie die Komprimierung: Verwenden Sie die Komprimierungsoptionen von pg_dump oder leiten Sie die Ausgabe durch externe Komprimierungstools (gzip, zstd), wenn Sie keine Formate verwenden, die automatisch komprimieren.
Verwenden Sie --clean und --create mit Bedacht: Bei einer vollständigen Wiederherstellung einer bestehenden Umgebung kann --clean sehr nützlich sein. Wenn Sie auf eine neue Umgebung umziehen oder einen vollständig neuen Zustand sicherstellen möchten, ist --create unerlässlich. Seien Sie bei diesen Optionen für Produktionssysteme vorsichtig.
Versionieren Sie Backups: Implementieren Sie eine Strategie zur Verwaltung der Backup-Aufbewahrung, einschließlich der Speicherdauer von Backups und der Bereinigung alter Kopien.

Fazit

pg_dump und pg_restore sind unverzichtbare Werkzeuge zum Schutz Ihrer PostgreSQL-Daten. Indem Sie deren verschiedene Optionen, Ausgabeformate verstehen und eine konsistente Backup- und Wiederherstellungsstrategie mit regelmäßigen Tests implementieren, können Sie das Risiko von Datenverlust erheblich mindern und die Widerstandsfähigkeit Ihrer Datenbanksysteme gewährleisten. Machen Sie diese Dienstprogramme zu einem Eckpfeiler Ihrer Datenbankadministrationspraktiken.