PostgreSQL Backup und Restore testen: Dumps, WAL und Recovery-Drills

Ein Backup, das nie getestet wurde, ist kein Backup – es ist ein Versprechen. In produktiven PostgreSQL-Umgebungen reicht es nicht, regelmäßige Dumps oder WAL-Archivierung einzurichten und darauf zu vertrauen, dass im Ernstfall alles funktioniert. Dieser Artikel zeigt, wie man Backups strukturiert testet, Wiederherstellungsabläufe automatisiert und Recovery-Drills in bestehende CI/CD-Pipelines integriert.

Backup-Strategien im Überblick

PostgreSQL bietet drei grundlegend unterschiedliche Backup-Mechanismen, die sich in Recovery-Granularität, Aufwand und Einsatzgebiet unterscheiden:

• Logische Backups mit pg_dump / pg_dumpall: Erzeugen SQL- oder Custom-Format-Dumps einzelner Datenbanken oder des gesamten Clusters. Portabel, aber nicht für Point-in-Time-Recovery geeignet.
• Physische Backups mit pg_basebackup: Kopieren den gesamten Datenbankcluster auf Dateisystemebene. Basis für WAL-basiertes Recovery.
• Kontinuierliche Archivierung (WAL-Archivierung): In Kombination mit einem Base-Backup ermöglicht sie Point-in-Time-Recovery (PITR) auf beliebige Zeitpunkte innerhalb der Aufbewahrungsfrist.

In der Praxis kombiniert man physische Base-Backups mit WAL-Archivierung für maximale Flexibilität. Logische Dumps dienen ergänzend zur selektiven Wiederherstellung einzelner Tabellen oder zur Migration zwischen Versionen.

pg_dump korrekt erstellen und validieren

Logische Dumps werden häufig unterschätzt, weil ihre Erstellung trivial wirkt. Entscheidend ist jedoch das Format und die anschließende Validierung. Das Custom-Format (-Fc) ist gegenüber Plain-SQL vorzuziehen, da es parallele Wiederherstellung und selektiven Restore erlaubt.

# Dump im Custom-Format mit Komprimierung
pg_dump -h localhost -U postgres -Fc -Z 6 \
  --exclude-table-data=audit_logs \
  myapp_production > /backups/myapp_$(date +%Y%m%d_%H%M%S).dump

# Strukturprüfung ohne Restore
pg_restore --list /backups/myapp_20260814_020000.dump | head -50

# Restore in eine Testdatenbank zur Validierung
createdb myapp_restore_test
pg_restore -h localhost -U postgres -d myapp_restore_test \
  --jobs=4 --verbose /backups/myapp_20260814_020000.dump 2>&1 | tee /logs/restore.log

# Zeilenanzahl kritischer Tabellen prüfen
psql -U postgres -d myapp_restore_test -c \
  "SELECT schemaname, tablename, n_live_tup
   FROM pg_stat_user_tables
   ORDER BY n_live_tup DESC LIMIT 20;"

Der letzte Schritt – das Zählen von Zeilen in kritischen Tabellen und der Vergleich mit der Produktionsdatenbank – ist entscheidend. Ein Dump kann formal korrekt sein, aber aufgrund eines unterbrochenen Exports weniger Daten enthalten als erwartet.

WAL-Archivierung einrichten und prüfen

Für Point-in-Time-Recovery muss PostgreSQL WAL-Segmente kontinuierlich archivieren. Die Konfiguration in postgresql.conf erfordert mindestens diese Parameter:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 300   # Maximal 5 Minuten bis zum nächsten WAL-Segment

# Sicherstellen, dass archive_command funktioniert
psql -U postgres -c "SELECT pg_switch_wal();"

# WAL-Archiv-Status überwachen
psql -U postgres -c "
  SELECT last_archived_wal,
         last_archived_time,
         last_failed_wal,
         last_failed_time,
         archived_count,
         failed_count
  FROM pg_stat_archiver;"

Ein häufiger Fehler: Der archive_command gibt 0 zurück, obwohl er nichts archiviert hat – etwa weil das Zielverzeichnis voll ist, aber der Befehl trotzdem erfolgreich exit. Daher sollte der Archivierungsbefehl immer mit expliziter Fehlerprüfung und Monitoring auf failed_count begleitet werden.

pgBackRest als professionelles Backup-Werkzeug

Während pg_dump und manuelle WAL-Archivierung für kleine Setups ausreichen, empfiehlt sich für produktive Umgebungen ein dediziertes Backup-Tool wie pgBackRest. Es übernimmt Base-Backups, WAL-Archivierung, Verschlüsselung, Delta-Backups und Verifizierung in einem einzigen Werkzeug.

Nach der Konfiguration einer Stanza (logische Gruppe für einen PostgreSQL-Cluster) kann ein Backup-Check wie folgt aussehen:

• pgbackrest --stanza=main check – Prüft Konfiguration und Archivverbindung
• pgbackrest --stanza=main info – Zeigt alle verfügbaren Backups und WAL-Abdeckung
• pgbackrest --stanza=main verify – Verifiziert Integrität aller gespeicherten Backups und WAL-Segmente

Das Verify-Kommando ist besonders wertvoll: Es prüft Checksummen aller archivierten Dateien und meldet defekte oder fehlende WAL-Segmente, bevor man sie im Ernstfall benötigt.

Recovery-Drill automatisieren

Ein Recovery-Drill ist die vollständige, dokumentierte Wiederherstellung eines Backups in einer isolierten Umgebung. Ohne Automatisierung werden diese Drills selten durchgeführt, weil der manuelle Aufwand zu hoch ist. Das Ziel ist, den Restore als automatisierten Job laufen zu lassen – täglich, wöchentlich oder bei jedem neuen Backup.

Ein minimales Skript für einen automatisierten Drill könnte folgende Schritte umfassen:

1. Temporären PostgreSQL-Container oder separaten Host starten
2. Letztes Base-Backup wiederherstellen
3. WAL-Replay bis zu einem definierten Zeitpunkt durchführen
4. Sanity-Checks ausführen (Tabellenanzahl, FK-Integrität, Anwendungsschema-Version)
5. Ergebnis in Monitoring-System schreiben (Success/Failure + Restore-Dauer)
6. Testinstanz wieder bereinigen

Die Restore-Dauer ist dabei eine ebenso wichtige Metrik wie der Erfolg selbst. Wenn ein Restore bei 500 GB Daten sechs Stunden dauert, muss das im Incident-Plan berücksichtigt sein.

CI/CD-Integration von Backup-Tests

Recovery-Drills lassen sich in GitLab CI, GitHub Actions oder Jenkins als geplante Jobs integrieren. Der entscheidende Unterschied zu Ad-hoc-Tests: Die Ergebnisse werden versioniert, Regressionen fallen sofort auf, und das Team wird automatisch benachrichtigt, wenn ein Restore fehlschlägt.

Ein typischer Ansatz für GitHub Actions:

name: PostgreSQL Recovery Drill

on:
  schedule:
    - cron: '0 3 * * 1'  # Jeden Montag um 03:00 UTC
  workflow_dispatch:

jobs:
  restore-test:
    runs-on: ubuntu-latest
    services:
      postgres-restore:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: testpassword
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Download latest backup
        run: |
          aws s3 cp s3://myapp-backups/latest.dump /tmp/restore.dump

      - name: Restore dump
        run: |
          PGPASSWORD=testpassword pg_restore \
            -h localhost -U postgres \
            -d postgres --jobs=2 \
            /tmp/restore.dump

      - name: Run sanity checks
        run: |
          PGPASSWORD=testpassword psql -h localhost -U postgres \
            -f ./scripts/restore_sanity_check.sql

      - name: Report result to monitoring
        if: always()
        run: |
          curl -X POST "${{ secrets.MONITORING_WEBHOOK }}" \
            -d "{\"job\": \"restore-drill\", \"status\": \"${{ job.status }}\"}"

Das SQL-Skript restore_sanity_check.sql sollte tabellenspezifische Mindestanzahlen prüfen, Fremdschlüssel-Integrität validieren und die Schema-Version der Anwendung mit dem erwarteten Wert vergleichen.

Point-in-Time-Recovery testen

PITR ist das mächtigste Recovery-Werkzeug, wird aber selten geübt. Dabei ist gerade hier die Übung entscheidend: Der Recovery-Target muss korrekt angegeben werden, recovery.signal oder standby.signal muss vorhanden sein, und restore_command muss WAL-Segmente korrekt liefern.

Ein strukturierter PITR-Test sollte folgende Szenarien abdecken:

• Recovery auf exakten Zeitpunkt: Simulation eines Datenfehlers zu einem bekannten Zeitpunkt, Restore bis kurz davor
• Recovery auf benannte Transaktion: Verwendung von recovery_target_name mit vorher gesetzten Wiederherstellungspunkten (pg_create_restore_point())
• Abgebrochenes Recovery: Was passiert, wenn WAL-Segmente fehlen? Wird der Fehler klar gemeldet?

Das Ergebnis jedes PITR-Tests sollte die tatsächlich erreichte Recovery-Zeit (RTO) und den Datenverlust-Horizont (RPO) dokumentieren, damit diese Werte mit den SLA-Anforderungen abgeglichen werden können.

Monitoring und Alerting für Backup-Jobs

Backups, die still fehlschlagen, sind gefährlicher als fehlende Backups – weil man sie nicht kennt. Ein zuverlässiges Monitoring muss mindestens folgende Signale erfassen:

• Alter des letzten erfolgreichen Backups (Alert bei Überschreitung des RTO-Fensters)
• WAL-Archivierungsverzögerung (pg_stat_archiver.last_failed_time)
• Restore-Dauer im Drill (Trend über Zeit)
• Backup-Größe (plötzliche Einbrüche deuten auf unvollständige Exports hin)

Tools wie Prometheus mit dem postgres_exporter exportieren pg_stat_archiver-Metriken direkt. In Grafana lassen sich daraus Dashboards und Alerting-Regeln bauen, die das Backup-System kontinuierlich überwachen – ohne manuelle Prüfung.

Dokumentation des Recovery-Playbooks

Technisch ausgereifte Backup-Systeme versagen im Ernstfall, wenn das Wissen nur in den Köpfen einzelner Personen steckt. Ein Recovery-Playbook dokumentiert Schritt für Schritt, was im Datenverlustfall zu tun ist – einschließlich Zugangsdaten-Quellen, Tool-Versionen, Kontaktpersonen und erwarteten Laufzeiten.

Entscheidend ist, das Playbook mindestens einmal pro Quartal mit einem echten Drill zu validieren. Dabei wird nicht nur der technische Ablauf geprüft, sondern auch ob die Dokumentation verständlich genug ist, dass ein Teammitglied ohne Vorwissen den Restore eigenständig durchführen kann.

Backup-Tests sind keine einmalige Aufgabe, sondern ein kontinuierlicher Prozess. Die Kombination aus automatisierten Recovery-Drills in CI/CD, strukturiertem Monitoring und gepflegter Dokumentation ist das, was ein Backup-Konzept operativ belastbar macht.