Pg_dump ist im Lieferumfang von PostgreSQL enthalten und ermöglicht das Erstellen sogenannter logischer Backups. Dies bedeutet, dass Sicherungen der Datenbanken nicht mittels einer physischen Kopie der Datenbanken stattfinden, sondern die Informationen hinsichtlich Datenbankschema und Daten in SQL-Form extrahiert und gespeichert werden. pg_dump erzeugt einen konsistenten Schnappschuss einer Datenbank und unterstützt unterschiedliche Ausgabeformate für die Sicherung. Das Ausgabeformat bestimmen Sie mit dem Kommandozeilenschalter "-F <Format>". Die unterstützten Formate hierbei sind:

- Das sogenannte PLAIN-Format, das als Ergebnis die Sicherung als SQL-Skript erzeugt (Schalter "-Fp").

- Das TAR-Format (Schalter "-Ft"): Dieses schreibt eine Sicherung als TAR-Datei, die sich entsprechend mit TAR-kompatiblen Tools weiterverarbeiten lässt, um etwa auf ein externes Backupsystem geschrieben zu werden.

- Das PostgreSQL-spezifische CUSTOM-Format ("-Fc"): Ein eigens von pg_dump beziehungsweise PostgreSQL entwickeltes, flexibles Format, das ein Inhaltsverzeichnis (Table of Contents, TOC) sowie das Komprimieren von Dateninhalten der gesicherten Tabellen unterstützt.

- Das DIRECTORY-Format ("-Fd"): Dieses verfügt wie das CUSTOM-Format über eine TOC, schreibt aber die Dateninhalte von Tabellen als separate, komprimierte TAR-kompatible Dateien in ein spezifiziertes Zielverzeichnis.

Das Ausgabeformat TAR kommt heute relativ selten zum Einsatz, da es keine eingebaute Komprimierung unterstützt. Die flexibelsten Formate sind CUSTOM beziehungsweise DIRECTORY: Diese unterstützen eine Komprimierung, wobei standardmäßig gzip für die Datenbestände der Tabellen zum Einsatz kommt. Für die Wiederherstellung dieser Formate ist das Tool pg_restore gefragt, das gilt auch für das zuvor genannte TAR-Format. Das PLAIN-Format ist das Standardformat für die Datensicherung, falls kein expliziter Schalter "-F" mit entsprechendem Ausgabebezeichner angegeben wurde.

Das Ergebnis einer solchen Sicherung ist eine Plaintext-Datei, die ein SQL-Skript für die komplette Wiederherstellung der Quelldatenbank enthält. Dieses File kann beispielsweise einfach an psql übergeben werden, um so die Daten wiederherzustellen. Dies ermöglicht auch die sehr einfache Erstellung einer Kopie einer Datenbank, wie im nachfolgenden Listing ausgeführt. In diesem Beispiel kopieren wir die lokale Datenbank "db1" einfach auf eine leere Datenbank auf dem Server "test-db.internal.server":

Für eine komplette Datensicherung, aber auch bei der Wiederherstellung müssen Sie für pg_dump auf einen entsprechenden PostgreSQL-Superuser zurückgreifen oder eine umfassend privilegierte Datenbankrolle nutzen, wie es beispielsweise zwingend bei einigen Cloudanbietern nötig ist. Gängige PostgreSQL-Installationen nutzen den Betriebssystem-Benutzer postgres für den Betrieb der PostgreSQL-Instanz und konfigurieren diesen für lokalen Zugriff ohne zusätzliche Authentifizierung, wenn Sie sich lokal entsprechend auf Betriebssystemebene angemeldet haben.

Daher lässt sich in diesem Fall nach dem Wechsel in den Postgres-Benutzerkontext in der Regel die Benutzerkennung bei lokaler Verbindungsaufnahme ignorieren. Für die entfernte PostgreSQL-Instanz "test-db.internal.server" ist in unserem gezeigten Beispiel jedoch auf jeden Fall eine Authentifizierung erforderlich. Möchten Sie daher solche Prozesse automatisieren, bietet sich eine PGPASS-Datei an, die die erforderlichen Daten für die Authentifizierung beispielsweise sicher im Home-Verzeichnis des postgres-Benutzers abspeichert.

pg_dump beziehungsweise pg_restore können sich dann transparent an der entfernten Instanz anmelden. Darüber hinaus zeigt sich PLAIN gerade beim Sichern von großen Datenbanken relativ unflexibel. Es gibt keine eingebaute Komprimierung sowie keine Möglichkeit, Tabellen oder andere Datenbankobjekte aus der Sicherung im Nachhinein zu extrahieren. Hier helfen die Ausgabeformate CUSTOM und DIRECTORY: Beide verfügen über eine TOC, die es ermöglicht, Objekte aus einer Sicherung gezielt herauszugreifen und gesondert wiederherzustellen.

Das DIRECTORY-Format unterstützt darüber hinaus die Datenbanksicherung mit mehreren parallelen Prozessen gleichzeitig (Schalter "-j <Anzahl paralleler Prozesse>"). Die Angabe paralleler Prozesse ermöglicht das schnellere Sichern der Tabellen durch entsprechende Parallelität. Dies ist besonders interessant, wenn in der Datenbank mehrere große Tabellen vorhanden sind und sich so die Laufzeit der Sicherung entsprechend verkürzen lässt. Die Komprimierung beider Formate sehen Sie im folgenden Listing. Dieses zeigt das DIRECTORY-Format nach einer Sicherung in das Verzeichnis "/var/lib/ pgsql/backup":

Das Listing erzeugt eine Datenbanksicherung im DIRECTORY, wobei vier Tabellen gleichzeitig parallel gesichert werden. Das CUSTOM- wie auch DIRECTORY-Format lassen unterschiedliche Komprimierungsalgorithmen zu, neben gzip stehen lz4 sowie zstd zur Auswahl. Bild 1 zeigt einen Vergleich der verfügbaren Kompressionsalgorithmen und der resultierenden Größe der Sicherung anhand einer rund 5706 MByte großen Datenbank. Zu sehen ist der klare Größenvorteil für zstd. Der zu erzielende Kompressionsgrad ist abhängig von der Art der Daten, die in den Tabellen gespeichert sind. Es lohnt sich also, bei eigenen Sicherungen die verwendete Komprimierung anzupassen und damit zu experimentieren.

pg_dump verfügt ebenfalls über umfangreiche Filter, um Objekte oder Klassen aus der Sicherung auszuschließen oder spezifisch nur bestimmte Objekte zu sichern. Die wichtigsten sind im Einzelnen:

- "-n": Nur angegebene Schema(s) und darin enthaltene Objekte sichern.

-  "-t": Nur angegebene Tabelle(n), Sequenzen, Views, Materialized Views oder Foreign Tables sichern.

- "-e": Nur die angegebene(n) Extension(s) sichern.

- "-s": Nur das Datenbankschema sichern. Dies lässt alle Daten einfach weg.

Wenn Sie die Option in Großbuchstaben verwenden (-N, -T, -E), funktioniert der Schalter jeweils als explizites Ausschlusskriterium. Die angegebenen Objekte sind dann nicht in der Sicherung enthalten. Als Argument können Sie für jede dieser Optionen ein Pattern anstatt eines absoluten Objektnamens angeben, sodass auch Filter auf mehrere Objekte möglich sind. Das folgende Listing sichert alle Tabellen der Datenbank "db1" im Schema "bernd" die mit "t" beginnen:

In PostgreSQL 17 debütiert ferner der Schalter "--filter". Diese neue Option akzeptiert als Argument einen Dateinamen mit umfangreichen Filterausdrücken auf Objektklassen, die in- oder exkludiert werden sollen. Eine solche Filterdatei kann analog zum eben gezeigten Beispiel so aussehen:

Für pg_dump stehen als Filterklassen die Objekttypen "extension", "foreign_data", "table", "table_data", "table_and_children", "table_data_and_children" sowie "schema" zur Verfügung. Darüber hinaus darf der Filterausdruck auch Suchausdrücke enthalten.

pg_dump setzt (falls nicht anders per Schalter "--inserts" angegeben) auf den COPY-Befehl für den Datenexport. Hierbei verwendet pg_dump eine READ-ONLY-Transaktion im Isolationsgrad REPEATABLE READ. Dies garantiert einen konsistenten Snapshot (und damit einen absolut konsistenten Dump) der Datenbank für die Dauer der Sicherung. Dies sorgt allerdings dafür, dass pg_dump schlecht mit konkurrierenden Schemaänderungen während der Sicherung umgehen kann. Zu Beginn der Transaktion fordert pg_dump auf jede Tabelle, die gesichert werden muss, einen AccessShareLock an.

Dies ist zwar die niedrigste Sperre auf Tabellen- beziehungsweise Objektebene, allerdings steht diese logischerweise in Konflikt mit Transaktionen, die Änderungen an Tabellen oder anderen Objekten vornehmen wollen. Aus diesem Grund ist es geboten, entsprechende Änderungen des Datenmodells mit der Datensicherung zu koordinieren. Normale Änderungen an den Datenbeständen sind jedoch davon nicht betroffen, daher bekommen normale Transaktionen nichts von pg_dump mit. Sind jedoch sehr viele Tabellen in einer Datenbank enthalten, ist eventuell eine Anpassung des Parameters "max_locks_per_transaction" nötig. Die Sicherung bricht ab, wenn die AccessShareLock-Sperren nicht in den zur Verfügung stehenden Speicher passen. Leider erfordert dies auf jeden Fall einen Neustart der Instanz.

Die Geschwindigkeit von pg_dump hängt primär von den Ressourcen des Storage-Systems und, falls über das Netzwerk von einem entfernten Server gesichert wird, von der Bandbreite der Netzwerkverbindung ab. Allerdings führt die Nutzung des alten Large-Object-Interfaces (LO) mitunter zu Problemen. Befinden sich sehr viele derartige Large Objects in einer Datenbank, kann sich der Export stark verzögern. Logische Sicherungen bieten sich daher vor allem in einer eher ruhigen Auslastungsphase der Quelldatenbank an.

pg_dump sichert nur jeweils den Inhalt einer PostgreSQL-Datenbank, jedoch nicht alle auf einen Streich. Hierfür muss pg_dump jede Datenbank separat durchlaufen. Es gibt das Programm pg_dumpall im Lieferumfang von PostgreSQL, das im Wesentlichen einen Wrapper um pg_dump implementiert und alle Datenbanken einer PostgreSQL-Instanz sichert. Es hat allerdings einen entscheidenden Nachteil und bietet keine Unterstützung für die flexiblen DIRECTORY- und CUSTOM-Formate.

Vielmehr sichert das Tool alles in eine große Sicherungsdatei als SQL-Skript. Für eine sehr große Instanz oder Datenbank erweist sich dies schnell als äußerst unhandlich und die vielen Vorteile wie eingebaute Komprimierung oder parallelisierte Sicherungen kommen nicht zum Zug. pg_dump dagegen kann globale Objekte wie Tablespaces oder Rollendefinitionen nicht in die Sicherung aufnehmen.

Es ist jedoch möglich, beides zu kombinieren: pg_dumpall bietet den Schalter "-g", der ausschließlich globale Objektdefinitionen einer PostgreSQL-Instanz sichert. Das nebenstehende Listing zeigt beispielhaft, wie das aussehen könnte. Das Bash-Skript verwendet dabei temporäre Dateien, sodass bei einem Fehler oder Abbruch keine Dateileichen zurückbleiben. Ferner schützt es sich mit dem Tool flock vor paralleler Ausführung.

Globale Objekte wie Rollen finden sich nach erfolgreicher Ausführung in einem SQL-Skript globals.sql wieder. Die einzelnen Datenbanken werden in einer Schleife per Abfrage des Systemkatalogs pg_database in ein DIRECTORY-Format gesichert und mit zstd komprimiert. Für die Sicherungen kommt das Format "DBNAME-<Jahr><Monat><Tag>-<Stunde><Minute><Sekunde>" zum Einsatz. Das Skript läuft lokal auf dem Datenbankserver und benötigt unbedingt den entsprechenden PostgreSQL-Superuser. Es lässt sich aber natürlich beliebig adaptieren und anpassen.

Die Wiederherstellung der Formate TAR, CUSTOM und DIRECTORY erfolgt mithilfe des Tools pg_restore. Nachfolgend ein Beispiel, wie Sie eine Sicherung im DIRECTORY auf eine entfernte Datenbank direkt mit pg_restore wiederherstellen:

pg_restore kann auch anstatt direkt in eine Datenbank die Ausgabe per SQL Skript auf die Standardausgabe (Schalteroption "-f -") oder in eine Datei ausgeben (Schalter "-f <Datei>"). Der Schalter "-d" ist zusammen mit "-f" nicht erlaubt.

Leider ist es nicht möglich, die Standardausgabe von pg_dump und die Eingabe von pg_restore mit der Verwendung von Parallel Dump und Restore zu kombinieren. Da Parallel Dump nur mit dem DIRECTORY-Format arbeitet und Sie nicht ohne Weiteres diese Ausgabe an die Standardausgabe senden können, muss in diesem Fall die Sicherung zunächst auf die Platte geschrieben werden. Erst danach lässt sich mit pg_restore die Wiederherstellung parallelisieren.

Die Wiederherstellung verwendet hierfür vier parallele Prozesse (Schalter "-j", gilt für Tabellendaten und Indexerzeugung). Das Ermitteln des Formats erfolgt automatisch aus der Quelldatei beziehungsweise dem Quellverzeichnis. Inhalte der Sicherung können Sie jederzeit mit pg_ restore mit dem Schalter "-l" begutachten. Diese TOC lässt sich auch bearbeiten, wie das nachfolgende Listing zeigt. Hierzu wird die TOC in eine Datei umgeleitet, die Sie dann mit einem Editor Ihrer Wahl bearbeiten. Nach Abschluss lässt sich die editierte TOC (hier "toc.txt" genannt) per Schalter "-L <Dateiname>" wieder an pg_restore übergeben:

Vorsicht ist jedoch geboten, wenn Sie Elemente entfernen, von denen andere Objekte wie Views abhängig sind: Diese werden nicht implizit wiederhergestellt, wenn Sie diese aus der editierten TOC ausgeschlossen haben. Auch pg_restore unterstützt in PostgreSQL 17 den neuen Schalter "--filter", sodass Sie auch für die Wiederherstellung entsprechende Filterbedingungen übergeben können. Allerdings unterscheiden sich hier die unterstützten Filterklassen. Es stehen "function", "index", "schema", "table" und "trigger" mit Patternmatching für Filterausdrücke zur Verfügung. Das Format der Filterdatei ist identisch und lässt sich wie auch bei pg_dump mehrfach als Schalter mit unterschiedlichen Filterdateien auf der Kommandozeile angegeben.

Mittels des Schalters "-s" extrahieren Sie allein das Datenbankschema per SQL aus der Sicherung. Haben Sie die Rollen und Berechtigungen auf dem Zielsystem beispielsweise aus Testzwecken nicht zur Verfügung, helfen darüber hinaus die Schalter "-O" und "-x" weiter: Diese lassen dann die SQL-Anweisungen zum Anpassen der Objektbesitzer beziehungsweise -berechtigungen einfach weg. In diesem Fall ist der Benutzer, der die Wiederherstellung durchführt, der entsprechende neue Objektbesitzer.

Generell besitzen die Sicherungsformate TAR, DIRECTORY und CUSTOM jeweils drei Sektionen:

- Die "pre-data"-Sektion, mit Schemadefinitionen wie Tabellen, Funktionen oder Extensions, aber noch keine Indexe oder Ähnliches.

- Dann die "data"-Sektion als Abschnitt mit den komprimierten Daten.

- Und als Letztes die "post-data"-Sektion, die alle Definition für Constraints, Indexe, Trigger et cetera enthält.

Die Sektionen aus einer entsprechenden Sicherung lassen sich mit dem Schalter "--section" und dem entsprechenden Bezeichner direkt ansprechen. Das DIRECTOR-Format schreibt die Sicherung in eine Verzeichnisstruktur, wie Bild 2 an einem Beispiel zeigt. Die numerisch benannten Dateien sind die komprimierten Tabellendaten im COPY-Format, die Datei "toc.dat" enthält das Inhaltsverzeichnis sowie die relevanten Schemadefinitionen. Möchten Sie wissen, zu welcher Tabelle die jeweiligen Dateien mit numerischen Bezeichnern gehören, werfen Sie einen Blick in die TOC. Hier kommt wieder der bereits vorgestellte "--list"-Schalter von pg_restore zum Zug. Es handelt sich also um die Daten für die Tabelle "mytable" im Schema "bernd":

Anders als pg_dump lässt sich die Wiederherstellung durch Konfigurationsparameter seitens der PostgreSQL-Zielinstanz massiv beeinflussen. Da pg_restore je nach Größe der Ursprungsdatenbank sehr viele Daten schreibt, profitieren Optimierungen im Hinblick auf die Schreibgeschwindigkeit in PostgreSQL. Die wichtigsten Parameter hier sind:

- max_wal_size: Da unter Umständen sehr viele Daten geschrieben werden, erreicht das Transaktionslog in seiner Größe sehr schnell den konfigurierten Schwellenwert. Daher empfiehlt es sich, diesen Parameter zumindest für die Wiederherstellung deutlich großzügiger zu bemessen. Das funktioniert auch zur Laufzeit einer PostgreSQL durch einen einfachen Reload.

- maintenance_work_mem: pg_restore erstellt alle Indexe bei der Wiederherstellung neu. Hier lohnt es sich auf jeden Fall, mit großzügigeren Werten zu experimentieren, zumindest im Rahmen des zur Verfügung stehenden Hauptspeichers. In der Regel sind, sofern es die Ressourcen dafür gibt, 1 bis 2 GByte für größere Datenbanken ein guter Wert.

- max_parallel_maintenance_workers: erlaubt die Verwendung mehrerer Worker für die parallelisierte Indexerzeugung und beschleunigt das Erzeugen von Indexen bei der Wiederherstellung. Allerdings ist dies abhängig von der maximal verfügbaren Anzahl an "max_parallel_ workers" und indirekt von "max_worker_processes". Letzteres lässt sich nur durch einen Neustart der PostgreSQL-Instanz ändern. Natürlich sollten Sie auch auf die Anzahl der verfügbaren CPU-Kerne achten und nicht zu viele Worker per "max_parallel_maintenance_workers" zuteilen. Bringen Sie nicht die Parallelität hier und die mit dem Schalter "-j" durcheinander: Der Schalter "-j" definiert die Anzahl der gleichzeitig zu erstellenden Objekte, "max_parallel_ maintenance_workers" die Parallelität der Objekterzeugung selbst. Bei der Wiederherstellung ist dies primär der CREATE INDEX. Daher sollten Sie vorsichtig sein und die Anzahl der Prozesse per Schalter "-j" sowie die Wiederherstellung an sich ausbalancieren, um die CPU-Kerne nicht zu stark zu provisionieren.

- synchronous_commit=off: Das Abschalten des synchronen COMMIT kann helfen, wenn sehr viele Objekte wie Tabellen während der Wiederherstellung erzeugt werden müssen.

Wird die Zielinstanz für die Wiederherstellung per Streaming Replication repliziert, ist ebenfalls hinsichtlich der maximalen Parallelität Vorsicht geboten: Bei entsprechend leistungsstarken Instanzen ist es möglich, bei entsprechenden großen Datenmengen unter gewissen Umständen den Standby in Bedrängnis zu bringen.

Vor allem wenn Sie keine Replication Slots verwenden, erzeugt die Wiederherstellung auf der primären Instanz unter Umständen derart große Datenmengen im Transaktionslog, dass der Standby regelrecht abgehängt wird und eventuell abbricht. Auch mit Replication Slots laufen Sie Gefahr, dass der Standby sehr schnell ins Hintertreffen beim Abarbeiten der replizierten Transaktionslog-Information gerät. Dies sorgt dann für ein sehr schnell wachsendes Transaktionslog auf der primären Instanz. Geht hier dann der Speicherplatz aus, steht womöglich sogar der Primary vor großen Problemen.

pgcopydb nutzt die Möglichkeiten von pg_dump und pg_restore und baut auf diesen ein sehr mächtiges Werkzeug zum Kopieren/Klonen von Datenbanken auf. Es ist in allen gängigen Community Repositories für Debian/Ubuntu oder RHEL-basierten Systemen verfügbar. pgcopy nutzt eigene Optimierungen, um die Wiederherstellung zu beschleunigen. Vor allem ist es in der Lage, die Kombination aus parallelisiertem Dump und Restore ohne Zwischenspeicherung zu nutzen, um eine Datenbank schneller von einem Quell- zum Zielserver zu übertragen.

Darüber hinaus bietet es mit seiner eingebauten Unterstützung von Logical Replication die Möglichkeit, das Delta zwischen Dump und aktuellem Stand der Quelldatenbank zu replizieren (Change Data Capture Feature). Dazu gehört auch das Nachziehen von Sequenzen und deren aktuellen Zählerständen, da diese nicht direkt von Logical Replication unterstützt werden.

Sicherungen sehr großer Datenbanken und entsprechend strenge Service Level Agreements (SLAs) überfordern Sicherungsstrategien auf Basis logischer Sicherungen sehr schnell. Da eine logische Sicherung mittels pg_dump immer nur einen bestimmten Zeitpunkt einer Datenbank repräsentiert und auch trotz verfügbarer Möglichkeiten, diese über mehrere CPUs zu parallelisieren, entsprechend lange Laufzeit benötigt, ist es in der Regel sehr schwer, die heute üblichen Sicherungsanforderungen zu erfüllen. PostgreSQL bietet daher auch die Möglichkeit, physische Sicherungen einer laufenden Instanz durchzuführen.

Durch Kombination mit dem Archivieren der Transaktionslogs (quasi eine laufende Sicherung der Datenbankänderung "nebenher") ermöglicht dies auch, eine Instanz auf jeden beliebigen Zeitpunkt wiederherzustellen (Point In Time Recovery, PITR). Die offene Architektur erlaubt das Einbetten dieser Sicherungsansätze in beliebige Backuptools, es haben sich aber im Laufe der Zeit auch einige Standardwerkzeuge in der PostgreSQL-Community etabliert. Hierzu gehören pgbackrest und barman.

Physische Sicherungen sichern immer eine komplette PostgreSQL-Instanz. Das Extrahieren von Datenbanken oder gar Objekten ist nicht möglich. Auch ist der Platzbedarf trotz Komprimierung nicht zu vernachlässigen, da physische Sicherung immer auch die kompletten Indexe oder Materialized Views enthalten.

Logische Sicherungen sind auch heute im Zeitalter großer Datenbestände noch ein nützliches Werkzeug für die Datensicherung. Passen die Rahmenbedingungen entsprechend, erweisen sich pg_dump und sein Partner pg_restore als mächtige Verbündete. Aber auch beim Deployment von Testsystemen oder dem Extrahieren von Teilbeständen aus Datenbanken oder der Migration auf neue PostgreSQL-Hauptversionen spielen beide Tools in Verbindung ihre Stärken aus.

Wer viel damit zu tun hat, Datenbanken zu klonen, sollte sich unbedingt auch pgcopydb ansehen. Das gilt auch im Hinblick auf Migrationen auf neue PostgreSQL-Hauptversionen.