PostgreSQL-Backup mit pg_dump und pg_restore
Die Kommandozeilen-Tools pg_dump und pg_restore dienen zum Exportieren bzw. Importieren einer PostgreSQL-Datenbank. Zum Einsatz kommen sie beim Erstellen eines PostgreSQL-Backups sowie beim Migrieren von PostgreSQL-Datenbanken zwischen Servern.
Worum handelt es sich bei einem PostgreSQL-Dump?
Unter einem PostgreSQL-Dump versteht man die Ausgabe-Datei, die beim Export einer PostgreSQL-Datenbank erzeugt wird. PostgreSQL ist ein ausgefeiltes Datenbank-Management-System, das Daten in optimierten Datenstrukturen speichert. Das Extrahieren strukturierter Daten aus einer PostgreSQL-Datenbank erfordert daher ein spezielles Verfahren.
Was ist ein Backup? Diese grundsätzliche Frage beantworten wir in einem anderen Ratgeber.
Ähnlich wie beim MySQL-Backup via MySQL-Dump erzeugt das pg_dump-Tool eine Textdatei mit SQL-Anweisungen. Das Ausführen der Anweisungen stellt die Datenbank zum Zeitpunkt des Dumps wieder her. In den Worten der offiziellen PostgreSQL-Dokumentation:
„The idea behind this dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump.“
„Die Idee hinter dieser Dump-Methode besteht darin, eine Textdatei mit SQL-Anweisungen zu generieren, die, wenn sie am Server wieder eingespielt wird, die Datenbank zu dem Zustand wiederherstellt, an dem sie sich zum Zeitpunkt des Dumps befand.“
(https://www.postgresql.org/docs/9.1/backup-dump.html, übersetzt von IONOS)
Es ist wichtig, zu verstehen, was mit „PostgreSQL-Datenbank“ gemeint ist. Der Begriff wird oft verwechselt mit dem PostgreSQL-Server. In der Tat ist es nicht ungewöhnlich, dass ein einzelner PostgreSQL-Server mehrere Datenbanken enthält. Hier die Hierarchie der Objekte einer PostgreSQL-Installation im Überblick:
| PostgreSQL-Objekt | Enthält |
|---|---|
| Server | Datenbanken |
| Datenbank | Tabellen |
| Tabelle | Datensätze |
| Datensatz | Felder |
Wie funktionieren pg_dump und pg_restore?
Die Kommandozeilen-Tools pg_dump und pg_restore werden für gewöhnlich als Teil der PostgreSQL-Client-Anwendungen mitsamt der PostgreSQL-Kommandozeilen-Schnittstelle psql installiert. Die Tools folgen der Unix-Philosophie und nutzen Textströme für Ein- und Ausgabe. Dies ermöglicht, sie durch sogenannte Pipes mit anderen Programmen zu verknüpfen. Ferner lassen sich Ein- und Ausgabe mittels Umleitungen aus Dateien lesen bzw. an Dateien ausgeben.
Wir zeigen das generelle Muster bei der Nutzung von pg_dump zum Erzeugen eines PostgreSQL-Dumps:
pg_dump dbname > db.dumpWir nutzen eine Umleitung der Ausgabe ('>') an eine Datei. Der dabei erzeugte PostgreSQL-Dump enthält SQL-Anweisungen. Diese lassen sich mit dem psql-Tool ausführen. Wir zeigen das generelle Muster bei der Nutzung von psql zum Einlesen eines PostgreSQL-Dumps:
psql dbname < db.dumpWie Sie sehen, erfolgt der Aufruf analog zum Aufruf von pg_dump. Jedoch nutzen wir eine Umleitung der Eingabe ('<') aus der PostgreSQL-Dump-Datei.
Das pg_dump-Tool kann noch einiges mehr. So ist es möglich, Datenbanken in speziellen Dump-Formaten auszugeben. Je nach Format des erzeugten PostgreSQL-Dumps wird das pg_restore- oder psql-Tool zum Wiederherstellen benötigt.
Hier ein Überblick der beim Erstellen und Wiederherstellen von PostgreSQL-Backups zum Einsatz kommenden Kommandozeilen-Tools:
| Tool | Erklärung |
|---|---|
| pg_dump | Kommandozeilen-Tool zum Erstellen eines PostgreSQL-Dumps |
| pg_restore | Kommandozeilen-Tool zum Wiederherstellen einer PostgreSQL-Datenbank aus einem PostgreSQL-Dump; erlaubt ggf. spezielle Operationen wie partielle Imports, Umsortieren der Import-Daten, parallelen Import mehrerer Tabellen etc. |
| psql | PostgreSQL-Kommandozeilen-Schnittstelle; nimmt SQL-Anweisungen von der Kommandozeile oder aus einer PostgreSQL-Dump-Datei entgegen und führt diese aus |
Betrachten wir das generelle Muster bei der Nutzung von pg_restore zum Wiederherstellen eines PostgreSQL-Dumps:
pg_restore --dbname=dbname db.dumpDie Nutzung von pg_restore erfordert das Erzeugen des PostgreSQL-Dumps in einem speziellen Format. Schauen wir uns die möglichem Ausgabeformate von pg_dump an:
| pg_dump-Ausgabeformat | Erklärung | Import via |
|---|---|---|
| Plain | Plain-Text-Datei mit SQL-Anweisungen; Komprimieren erfordert weiteres Tool wie Gzip | psql |
| Custom | Komprimiertes Dump-Format; Import lässt sich im Detail steuern | pg_restore |
| Directory | Erstellt Verzeichnis mit je einer Datei pro Tabelle/Blob; zusätzlich Inhaltsverzeichnis; Llsst sich mit Standard-Unix-Tools bearbeiten; erlaubt parallelen Export mehrerer Tabellen | pg_restore |
| Tar | Archivierungsformat „Tape Archive“; lässt sich in Directory-Format umwandeln; Komprimieren erfordert weiteres Tool wie Gzip; Abfolge der Imports nicht steuerbar | pg_restore |
Abschließend das generelle Muster beim Aufruf von pg_dump zur Erstellung eines PostgreSQL-Dumps im „Custom“-Dump-Format:
pg_dump --format=custom dbname > db.dumpWenn Ihre PostgreSQL-Installation in einem Docker-Container läuft, können Sie pg_dump innerhalb des Containers verwenden, um ein PostgreSQL-Backup anzulegen. Es gibt auch die Möglichkeit, den gesamten Container als Docker-Backup zu speichern. Wie das geht, erklären wir in unserem Detailartikel.
Schritt-für-Schritt-Anleitung zum Erstellen und Wiederherstellen eines PostgreSQL-Backups
Es gibt unterschiedliche Wege, PostgreSQL-Backups anzulegen und wiederherzustellen. Je nach Einsatzszenario und Anforderungen bieten die Methoden verschiedene Vor- und Nachteile. Das Vorgehen beim Erstellen des PostgreSQL-Dumps bestimmt darüber, welche Methode beim Import zum Einsatz kommt.
Bevor wir uns mit der spezifischen Methode zum Anlegen und Wiederherstellen von PostgreSQL-Dumps beschäftigen, ein kleiner Hinweis: Die nachfolgenden Beispiele referenzieren lediglich den Namen der zu nutzenden Datenbank. Sie enthalten jedoch keine Datenbank-Nutzernamen oder -Passwörter. Diese sind bei PostgreSQL der Konvention folgend in der Passwort-Datei .pgpass abgelegt. Die Datei befindet sich im Heimverzeichnis des Nutzers und enthält PostgreSQL-Verbindungsdaten. Dabei kommt das folgende Format zum Einsatz:
hostname:port:database:username:passwordDie in der Passwort-Datei enthaltenen Informationen werden automatisch bei Aufruf der Kommandozeilen-Tools genutzt. So entfällt das riskante Angeben sensibler Daten auf der Kommandozeile.
Überprüfen, ob Tools vorhanden sind, und diese ggf. installieren
Vorab überprüfen wir, ob pg_dump und pg_restore installiert sind. Wir versuchen, die Tools aufzurufen und deren Version auszugeben. Schlägt dies fehl, dann fehlt das Tool und muss installiert werden.
- Überprüfen, ob pg_dump installiert ist:
pg_dump --version- Überprüfen, ob pg_restore installiert ist:
pg_restore --version- Ferner überprüfen wir, ob die PostgreSQL-Kommandozeilenschnittstelle psql installiert ist:
psql --versionSollten die Tools nicht gefunden werden, lassen sie sich leicht installieren.
- Auf dem Mac installieren wir die PostgreSQL-Client-Anwendungen mit Homebrew:
brew install libpq
brew link --force libpq- Unter Ubuntu-Linux nutzen wir die eingebaute Paketverwaltung:
sudo apt-get install postgresql-client- Folgen Sie unter Windows unserer Anleitung, um PostgreSQL auf Windows Server 2016 zu installieren.
PostgreSQL-Backup erstellen und wiederherstellen
Schauen wir uns zunächst das einfachste Szenario zum Erstellen eines PostgreSQL-Backups an. Dabei extrahieren wir eine einzelne Datenbank von einem PostgreSQL-Server. Struktur und Inhalte der Datenbank werden dabei in Form von SQL-Anweisungen in eine Datei geschrieben. Der folgende Aufruf des pg_dump-Tools leistet genau das:
pg_dump dbname > db.dumpWie sieht es nun aus, wenn man den erzeugten PostgreSQL-Dump auf einem anderen Server wiederherstellen möchte? Zu diesem Zweck kommt die PostgreSQL-Kommandozeilen-Schnittstelle psql zum Einsatz. Der Befehl ist denkbar einfach:
psql dbname < db.dumpNeben der Ausgabe eines PostgreSQL-Dumps als Textdatei mit SQL-Anweisungen erlaubt das pg_dump-Tool das Erzeugen spezialisierter PostgreSQL-Dump-Formate. Diese werden beim Aufruf über Optionen gesteuert. Hier ein Überblick der beiden nützlichsten Dump-Formate:
| PostgreSQL-Dump-Format | Ausführliche Options-Syntax | Kurze Options-Syntax |
|---|---|---|
| Custom | pg_dump --format=custom | pg_dump -Fc |
| Directory | pg_dump --format=directory | pg_dump -Fd |
Wir erstellen einen PostgreSQL-Dump im Custom-Format:
pg_dump --format=custom dbname > db.dumpZum Wiederherstellen an einem anderen Server nutzt man das pg_restore-Tool:
pg_restore --dbname=dbname db.dumpMöchte man den PostgreSQL-Dump am selben Server wiederherstellen, erfordert dies einen zusätzlichen Schritt. Denn Datenbank und Tabellen sind auf dem Server bereits vorhanden und müssen vor dem Import entfernt werden. Konzeptuell ist das ähnlich wie '--add-drop-tables' bei MySQL-Dump. Praktischerweise lässt sich bei PostgreSQL die Funktionalität beim Importieren hinzuschalten:
pg_restore --clean --create --dbname=dbname db.dumpDie Option '--clean' entfernt eine existierende Datenbank vor dem Import; mit der Option '--create' erstellen wir die Datenbank unter dem angegebenen Namen. So läuft der Import ohne Kollisionen ab.
PostgreSQL-Datenbank zwischen entfernten Servern migrieren
Export und Import eines PostgreSQL-Dumps lassen sich über eine Pipe ('|') verbinden. So werden die exportierten Daten direkt in den Import eingespeist. Da pg_dump, pg_restore und psql bei Bedarf auf einem entfernten Host operieren, ist es möglich, den Export von einem Server direkt auf einem anderen Server zu importieren. Schauen wir uns den dafür verwendeten Befehlsaufruf an. Wir nutzen die Option '--host', um die Host-Namen anzugeben:
pg_dump --host=export_host dbname | psql --host=import_host dbnamePostgreSQL-Backup großer Datenbanken erstellen
Bei PostgreSQL handelt es sich um ein professionelles Datenbank-Management-System. Um Backups umfangreicher Datenbanken anzulegen, wird ein spezielles Vorgehen benötigt. Denn erzeugte PostgreSQL-Dumps können sehr groß werden. Zunächst bietet sich an, Komprimierung zu nutzen. Als Textdatei exportierte PostgreSQL-Dumps enthalten für gewöhnlich große Mengen redundanter SQL-Anweisungen. Diese lassen sich gut komprimieren.
Wir leiten die Ausgabe von pg_dump per Pipe an das Gzip Kompressions-Tool weiter und schreiben anschließend in eine komprimierte .gz-Datei:
pg_dump dbname | gzip > db.dump.gzZum Wiederherstellen aus einem komprimierten PostgreSQL-Dump drehen wir den Prozess um. Das Gunzip-Tool entpackt die komprimierten Daten und gibt diese an die Standardausgabe aus. Wir leiten die Ausgabe per Pipe an das psql-Tool weiter. Beim Entpacken nutzen wir die Option '-c', um die Eingabedatei unberührt zu lassen:
gunzip -c db.dump.gz | psql dbnameDie 3-2-1-Backup-Regel erfordert mindestens ein Backup in der Cloud. Der Upload sehr großer Dateien kann unter gewissen Umständen problematisch sein. Ggf. ist es sinnvoll, das PostgreSQL-Dump mit dem split-Tool in mehrere Dateien zu zerlegen. Wiederum nutzen wir eine Pipe, um die Ausgabe von pg_dump an split weiterzuleiten. In unserem Beispiel spalten wir den PostgreSQL-Dump in einzelne Dateien mit einer maximalen Größe von 1 GB:
pg_dump dbname | split -b 1G - db.dumpWie lassen sich die beim Import durch split erzeugten partiellen Dateien wieder zusammenführen? Hierfür wird keine spezielle Software benötigt; stattdessen nutzen wir das Standard-cat-Tool. Wir übergeben eine Liste der partiellen PostgreSQL-Dumps, die vom cat-Tool zu einem zusammenhängenden Datenstrom kombiniert werden. Diesen leiten wir wie gehabt per Pipe an psql weiter:
cat db.dump* | psql dbnameBei Verwendung des Directory-Dump-Formats ist es möglich, mehrere Tabellen parallel zu dumpen. Das geht schneller, führt jedoch auch zu einer höheren Last am Datenbank-Server. Über die Option '--jobs' steuern wir die Anzahl parallel exportierter Tabellen. In unserem Beispiel exportieren wir drei Tabellen parallel. Da wir ein Verzeichnis schreiben, ist es nicht möglich, die Ausgabe an eine Datei umzuleiten. Stattdessen nutzen wir die Option '--file' mit Angabe des Verzeichnisnamens:
pg_dump --jobs=3 --format=directory --file=dump.dir dbname