PostgreSQL PITR einrichten – Base Backup und WAL erklärt

Ein Datenbank-Backup ist nur dann wirklich gut, wenn du nicht nur den gestrigen Stand wiederherstellen kannst, sondern auf den exakten Zeitpunkt vor einem Fehler zurückspringen kannst. Genau dafür gibt es Point in Time Recovery (PITR) in PostgreSQL. Mit Base Backups und den Write Ahead Logs (WAL) lässt du die Datenbank bis zu einem gewünschten Zielzeitpunkt nachlaufen.

Die gute Nachricht: PITR klingt komplex, ist aber mit einem klaren Setup gut beherrschbar. Wenn du WAL-Archivierung aktivierst, regelmäßig Base Backups erstellst und die Recovery-Parameter kennst, bist du in der Lage, versehentliche DROP TABLEs, fehlerhafte Deploys oder Datenfehler zielgenau rückgängig zu machen.

Was PITR ist – und warum WAL dafür entscheidend ist

PostgreSQL schreibt jede Änderung zuerst ins WAL. Dieses Write Ahead Log ist ein Anhangsjournal, das sicherstellt, dass Transaktionen konsistent sind. Bei PITR kombinierst du ein Base Backup (Abbild des Datenverzeichnisses) mit WAL-Segmenten, um den Stand bis zu einem Zeitpunkt wiederherzustellen. Kurz: Base Backup liefert den Startpunkt, WAL füllt die Lücke bis zum Zielzeitpunkt.

Die Bausteine von PITR

Base Backup

Ein Base Backup ist ein konsistentes Abbild des Datenverzeichnisses. Es bildet die Grundlage jeder Wiederherstellung.

WAL-Archivierung

Mit archive_mode und archive_command kopierst du fertige WAL-Segmente zuverlässig in ein langfristiges Archiv. Ohne archivierte WAL-Dateien ist kein PITR möglich.

Recovery Target

Über recovery_target_time (oder recovery_target_lsn, recovery_target_xid) bestimmst du exakt, wie weit PostgreSQL beim Restore abspielen soll.

Umgebung vorbereiten

Bevor du startest, prüfe die PostgreSQL-Version. Seit v12 wurde recovery.conf in die postgresql.conf integriert, und das Aktivieren der Wiederherstellung erfolgt per recovery.signal. Lege feste Verzeichnisse für Base Backups und WAL-Archiv an, z. B. /backups/base/ und /backups/wal/. Achte auf Zugriffsrechte und Verschlüsselung, wenn du auf S3 oder Netzlaufwerke schreibst.

WAL-Archivierung einschalten

Aktiviere in der postgresql.conf die relevanten Optionen und starte PostgreSQL neu.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'
# Optional sinnvoll:
# max_wal_size, archive_timeout

Erklärung: wal_level=replica erzeugt genügend Informationen für Backups und Replikation. archive_mode=on erlaubt das Kopieren abgeschlossener WAL-Segmente über archive_command. Der Test vermeidet Überschreiben. Überwache pg_stat_archiver, um Fehler früh zu sehen.

Base Backup erstellen

Lege einen Replikations-User an und erzeuge ein Base Backup mit pg_basebackup.

-- einmalig auf der DB
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'starkes-passwort';

# pg_hba.conf ergänzen
host    replication   replicator   127.0.0.1/32    md5

# Base Backup ziehen (tar, komprimiert, inkl. WAL-Stream zur Konsistenz)
pg_basebackup -h 127.0.0.1 -U replicator \
  -D /backups/base/2025-08-19 -Ft -z -X stream -P

Wichtig: -X stream stellt sicher, dass WAL für den Backup-Zeitpunkt mitkommt. Alternativ kannst du -X none nutzen, wenn du dich vollständig auf die Archivierung verlässt.

Sicher speichern und rotieren

Lege Aufbewahrungsregeln fest: Wie lange Base Backups und WAL vorgehalten werden, ergibt sich aus deinem RPO und RTO. Komprimiere und verschlüssele die Archive, etwa mit gzip und GPG, und prüfe die Integrität regelmäßig. Eine Lifecycle Policy in S3 reduziert Kosten.

Recovery konfigurieren – Zielzeitpunkt setzen

Für ein PITR spielst du das Base Backup zurück und lieferst die WAL-Dateien an restore_command aus. Ab v12 nutzt du recovery.signal, um den Wiederherstellungsmodus zu aktivieren.

# postgresql.conf auf dem Zielsystem
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2025-08-19 14:19:59+02'
recovery_target_action = promote
# Alternativen:
# recovery_target = 'immediate'
# recovery_target_lsn = '0/70001D0'

# Schritte im Überblick
1) Datenverzeichnis durch Base Backup ersetzen (entpacken, Besitzer postgres)
2) leere Datei recovery.signal im Datenverzeichnis anlegen
3) postgresql.conf mit restore_command und Target versehen
4) PostgreSQL starten - es werden WALs bis zum Ziel eingespielt
5) nach Erreichen des Targets: automatische Promote (oder manuell)

Tipp: Nutze recovery_target_time ein paar Sekunden vor dem Ereignis. Achte auf Zeitzonen. Nach erfolgreichem PITR erzeugt PostgreSQL eine neue Timeline, damit alte WAL-Ketten nicht verwechselt werden.

PITR einmal komplett – ein kleines Beispiel

Angenommen, um 14:20:10 wurde eine Tabelle versehentlich gelöscht. Dein Ziel ist 14:19:59.

1. Wähle das Base Backup vom Vormittag aus /backups/base/.
2. Entpacke es ins Datenverzeichnis der Zielinstanz, setze Besitzrechte.
3. Lege recovery.signal an, trage restore_command und recovery_target_time ein.
4. Starte PostgreSQL. In den Logs siehst du, wie WAL-Segmente aus /backups/wal/ wiedergegeben werden.
5. Nach Erreichen des Targets wird die Instanz promoted. Prüfe mit psql, ob die Daten vor dem DROP vorhanden sind.

Monitoring und Verifikation

Überwache pg_stat_archiver (Anzahl erfolgreicher und fehlgeschlagener Archivierungen) und die Logs auf archive_command failed. Prüfe regelmäßig, ob neue WAL-Dateien im Archiv landen und ob Base Backups lesbar sind. Eine Wiederherstellungsprobe auf einer Testinstanz ist der beste Beweis, dass dein Backup funktioniert.

Aufbewahrung, Sicherheit, Kosten

Definiere eine Retention-Policy: z. B. tägliche Base Backups 7 Tage, wöchentliche 4 Wochen, monatliche 6 Monate. Halte WAL mindestens so lange vor, wie du zurückspringen können willst. Schütze dein Archiv mit Zugriffsrechten, Verschlüsselung und Versionierung. Denke an Offsite-Kopien für Desasterfälle.

Häufige Fehler und schnelle Abhilfe

• archive_mode nicht aktiv – kein WAL im Archiv, PITR unmöglich.
• archive_command schlägt leise fehl – pg_stat_archiver prüfen und Logs alarmieren.
• SPÄTERES Base Backup genutzt – Timeline passt nicht, nimm das richtige Backup.
• Zeitzone verwechselt – UTC vs lokale Zeit sauber dokumentieren.
• WAL-Ordner gelöscht – ohne WAL kannst du nur auf den Backup-Zeitpunkt zurück.

Mini-Blueprint für deinen Start

Starte mit wal_level=replica, archive_mode=on, einem robusten archive_command und täglichen Base Backups per pg_basebackup. Lagere WAL und Backups getrennt, automatisiere Rotation und teste vierteljährlich einen Restore inklusive PITR. Dokumentiere restore_command und recovery_target in einer Runbook-Checkliste.

Fazit

Mit PostgreSQL PITR sicherst du dir die Feinsteuerung im Ernstfall: Ein Base Backup liefert den sicheren Stand, WAL bringt dich sekundengenau an dein Ziel. Wenn du Archivierung, Retention und Recovery-Prozess sauber aufsetzt und regelmäßig testest, erhältst du verlässliche Backups, die dich tatsächlich retten, wenn es darauf ankommt.