PostgreSQL 10: Backups erstellen

Nov 22, 2019 von Frank Stüber

Schlagwörter: Windows PostgreSQL Backups

Wer das Datenbank-Management-System PostgreSQL produktiv im Einsatz hat, muss regelmäßig Backups erstellen und diese auch wiederherstellen können. Dieser Blog-Artikel stellt die wichtigsten Optionen kurz vor. Dabei konzentrieren wir uns auf den Einsatz von PostgreSQL 10 in einer Windows-Umgebung.

Einführung

Von Hause aus bietet PostgreSQL unterschiedliche Optionen zum Erstellen von Backups an. Die drei wichtigsten sind:

Kopie auf Dateisystemebene
Dump
Continuous Archiving und Point-in-Time Recovery (PITR)

Alle drei Optionen stelle ich im Folgenden vor. Dabei setze ich folgendes Testszenario voraus:

Auf einem Windows Server 2019 ist PostgreSQL 10 installiert. Der Installationsordner lautet C:\Program Files\PostgreSQL\10.
Alle Backup-Prozesse werden lokal, also direkt auf dem Windows Server ausgeführt.
Alle Backups sollen im Ordner C:\Backups abgelegt werden.

Kopie auf Dateisystemebene

Dies ist die einfachste Form des Backups, einfach das Datenverzeichnis kopieren, fertig.

Vorteil: Im Prinzip einfach.
Nachteile: Der PostgreSQL-Server muss zwingend gestoppt werden, dies führt zu Ausfallzeiten, die bei einem Produktivbetrieb nicht akzeptabel sind. Zudem kann nur der gesamte Cluster (also alle Datenbanken auf einmal) so gesichert werden. Je nach Größe des Clusters geht das ordentlich in die Zeit.

Diese Form des Backups ist also nur in begründeten Ausnahmefällen zu empfehlen.

Backup

Öffne die Windows-Kommandozeile als Administrator und stoppe den PostgreSQL-Server:

net stop "PostgreSQL-10"

Jetzt kopiere den Ordner C:\Program Files\PostgreSQL\10\data samt Inhalt nach C:\Backups\Cluster.backup.

Anschließend kannst Du den PostgreSQL-Server wieder starten.

net start "PostgreSQL-10"

Restore

Öffne die Windows-Kommandozeile als Administrator und stoppe den PostgreSQL-Server:

net stop "PostgreSQL-10"

Jetzt gehe wie folgt vor:

Lösche den Inhalt des Ordners C:\Program Files\PostgreSQL\10\data.
Kopiere den Inhalt des Ordners C:\Backups\Cluster.backup nach C:\Program Files\PostgreSQL\10\data.

Anschließend kannst Du den PostgreSQL-Server wieder starten.

net start "PostgreSQL-10"

Dump

Bei einem Dump wird mit Hilfe des Werkzeugs pg_dump ein logisch konsistenter Auszug (also ein Snapshot) aus einer Datenbank erstellt.

Vorteile: PostgreSQL muss nicht gestoppt werden, alle Benutzer können parallel weiterarbeiten. Zudem kann ich ein Backup von einer einzelnen Datenbank erstellen, muss also nicht den gesamten Cluster sichern.
Nachteile: Ein Dump kann je nach Größe der Datenbank dauern. Wenn alle Datenbanken eines Clusters per Dump gespeichert werden sollen und der Cluster sehr datenintensiv ist, dann kann es zu Zeitproblemen kommen. Nämlich dann, wenn das Zeitfenster zwischen zwei sich wiederholenden Dumps zu gering ist.

Diese Form des Backups ist unter Berücksichtigung von Cluster-Größe und Zeitintervallen eine saubere und sichere Vorgehensweise und ist für die Erstellung von Archiv-Backups unverzichtbar. Ein Archiv-Backup ist ein Backup, auf das zurückgegriffen werden muss, wenn man versehentlich gelöschte Daten wiederherstellen möchte.

Backup

Öffne die Windows-Kommandozeile als Administrator und tippe folgendes ein:

"C:\Program Files\PostgreSQL\10\bin\pg_dump" -f "C:\Backups\MeineDB.dump" -U postgres -F c "MeineDB"

Dieser Befehl erzeugt ein Dump der Datenbank MeineDB in einem speziellen komprimierten Format. Du wirst bei der Ausführung nach dem Kennwort für den Superuser postgres gefragt.

Restore

Öffne die Windows-Kommandozeile als Administrator und tippe folgendes ein:

"C:\Program Files\PostgreSQL\10\bin\pg_restore" -d "MeineDB" -U postgres -c --if-exists "C:\Backups\MeineDB.dump"

Dieser Befehl löscht eine eventuell bereits vorhandene Datenbank MeineDB, erstellt sie neu und füllt sie mit allen Daten aus dem Backup auf. Du wirst bei der Ausführung nach dem Kennwort für den Superuser postgres gefragt. Der PostgreSQL-Server muss nicht gestoppt werden.

Anmerkungen

pg_dump und pg_restore besitzen noch sehr viel mehr Optionen (siehe die jeweiligen Verweise).

Wichtig: Ein Dump und Restore zwischen Major-Versionen von PostgreSQL (z.B. zwischen Version 10 und 11) ist in der soeben beschriebenen Konstellation nicht möglich. Auch sollte man darauf achten, dass die Dateiversion von pg_dump nicht älter ist als die Dateiversion von pg_restore.

Die Funktionalitäten von pg_dump und pg_restore werden auch von pgAdmin unterstützt. Wer also keine Lust auf die Kommandozeile hat, kann dort für manuelle Backups und Restores die GUI von pgAdmin nutzen. Mehr Infos zum Thema findest Du in der pgAdmin-Dokumentation unter Backup and Restore.

Ebenfalls wichtig: pgAdmin installiert eigene Versionen von pg_dump und pg_restore, die in der Regel neuer sind als jene der PostgreSQL-Installationen.

Continuous Archiving und Point-in-Time Recovery (PITR)

Das Problem der beiden vorherigen Optionen ist, dass es sich um Snapshot-Verfahren handeln. Die Zeit zwischen zwei sich wiederholenden Backups bleibt ungeschützt. Wenn ich also beispielsweise jede Stunde ein Backup mittels pg_dump mache, die Festplatte aber um 12:15 das Zeitliche segnet, dann sind alle Dateneingaben der letzten 15 Minuten unwiderruflich verloren.

Die Lösung hier ist die Nutzbarmachung des sogenannten Write Ahead Log (WAL). Dies ist ein Mechanismus, der PostgreSQL vor Datenverlust bei einem plötzlichen Ausfall des Dienstes oder des Dateisystems schützen soll. Das WAL zeichnet alle Datenänderungen unmittelbar nach Eintreffen auf. Fällt PostgreSQL während einer Datenmutation aus, dann können nicht rechtzeitig ausgeführte Operationen nach einem Neustart des Dienstes aus dem WAL wiederhergestellt und nachträglich ausgeführt werden. PostgreSQL garantiert damit jederzeit einen konsistenten Zustand.

Technisch gesehen produziert PostgreSQL eine nicht enden wollende Sequenz von WAL-Datensätzen. Diese Sequenz wird in Form von WAL-Segmentdateien gespeichert, deren Größe standardmäßig 16 MB ist. Die Idee ist es nun, nach einem einmaligen Basis-Backup mittels pg_basebackup diese WAL-Segmentdateien unmittelbar nach ihrem Entstehen (also kontinuierlich) in ein Archiv zu kopieren, um sie bei Bedarf zusammen mit dem Basis-Backup als Grundlage für ein Recovery ohne Datenverlust zu nutzen.

Vorteile: PostgreSQL muss nicht gestoppt werden, alle Benutzer können parallel weiterarbeiten. Alle Datenänderungen, die den PostgreSQL-Server erreichen, werden unmittelbar gespeichert. Ein möglicher Datenverlust ist also minimal.
Nachteile: Dieses Verfahren bringt den größten Konfigurationsaufwand mit sich. Zudem kann nur der gesamte Cluster (also alle Datenbanken auf einmal) so gesichert werden.

Diese Form des Backups schützt am besten vor möglichen Datenverlusten. In Kombination mit einem z.B. täglichen Dump ist dies die aus meiner Sicht beste Backup-Strategie.

Backup

Vorbereitung

Erstelle zunächst die folgenden zwei Ordner:

C:\Backups\Base
C:\Backups\Wal

Konfiguration

Öffne die Datei C:\Program Files\PostgreSQL\10\data\postgresql.conf in einem Texteditor Deiner Wahl und ändere die folgenden Parameter wie folgt ab:

archive_mode = on
archive_command = 'copy "%p" "C:\\Backups\\Wal\\%f"'

Speichere die Datei und starte den PostgreSQL-Server neu.

PostgreSQL erstellt nun fortwährend eine Archivkopie des Write Ahead Log (WAL) im Ordner C:\Backups\Wal.

Basis-Backup

Öffne die Windows-Kommandozeile als Administrator und tippe folgendes ein:

"C:\Program Files\PostgreSQL\10\bin\pg_basebackup" -U postgres -D "C:\Backups\Base"

Dieser Befehl erzeugt ein Snapshot des Ordners C:\Program Files\PostgreSQL\10\data. Du wirst bei der Ausführung nach dem Kennwort für den Superuser postgres gefragt. Der PostgreSQL-Server muss nicht gestoppt werden.

Restore

Gehe wie folgt vor:

Stoppe den PostgreSQL-Server.
Lösche den Inhalt des Ordners C:\Program Files\PostgreSQL\10\data.
Kopiere den Inhalt des Ordners C:\Backups\Base nach C:\Program Files\PostgreSQL\10\data.
Erstelle eine neue Textdatei C:\Program Files\PostgreSQL\10\data\recovery.conf, öffne diese in einem Texteditor Deiner Wahl und trage folgende Parameter ein:
```
restore_command = 'copy "C:\\Backups\\Wal\\%f" "%p"'
recovery_target_timeline = 'latest'
```
Starte den PostgreSQL-Server wieder.

Der PostgreSQL-Server geht gleich nach dem Start in den Recovery-Modus und spielt die aufgelaufenen Änderungen seit dem Basis-Backup aus der Archivkopie des Write Ahead Log (WAL) ein.

Resultat ist eine Rekonstruktion des Datenbank-Clusters bis zum Zeitpunkt des Ausfalls bzw. des Beendens des PostgreSQL-Servers. Die Datei recovery.conf wird nach Beendigung des Vorgangs in recovery.done umbenannt und kann bei Bedarf gelöscht werden.

Anmerkungen

Ich habe hier nur ein absolut minimales Beispiel beschrieben. Wer tiefer einsteigen möchte, sollte sich unbedingt die ausführliche Dokumentation in PostgreSQL unter Continuous Archiving and Point-in-Time Recovery (PITR) reinziehen.

Fazit

Das Testszenario für alle drei Backup-Optionen ist nur sehr rudimentär gewählt. In der Praxis müssen Backups natürlich automatisiert und auf einem oder mehreren externen Datenspeichern abgelegt werden. Zudem gibt es weitere Optionen, Backups zu erstellen. So macht es beispielsweise Sinn, in regelmäßigen Abständen den gesamten Windows-Server als Snapshot-Image zu sichern. Für einen ersten Überblick sollten diese Infos hier aber reichen.

Wir nutzen Cookies, um die Diskussionsplattform Disqus zu integrieren. Mit Klicken auf Kommentare anzeigen stimmen Sie dem zu und Disqus wird geladen.