TL;DR — Kurzzusammenfassung

PostgreSQL-Backups in der Produktion: pg_dump, pg_dumpall, pg_basebackup, WAL-Archivierung, PITR und automatisierte Skripte mit Rotation und S3-Upload.

PostgreSQL ist das Rückgrat unzähliger Produktionsanwendungen — Datenverlust ohne einen getesteten Wiederherstellungsplan ist katastrophal. Dieser Leitfaden deckt alle wichtigen PostgreSQL-Backup- und Wiederherstellungsstrategien ab: logische Backups mit pg_dump und pg_dumpall, physische Backups mit pg_basebackup, kontinuierliche WAL-Archivierung für Point-in-Time Recovery (PITR), automatisierte Skripte, Verschlüsselung und Cloud-Upload.

Voraussetzungen

  • PostgreSQL 14 oder neuer (Befehle funktionieren ab Version 12).
  • Der postgres-Systembenutzer oder eine Rolle mit pg_read_all_data / SUPERUSER-Rechten.
  • Ausreichend Speicherplatz (mindestens 1,5× die Größe Ihrer Datenbanken).
  • aws-cli oder rclone installiert bei Verwendung von Cloud-Speicher.
  • gpg installiert für verschlüsselte Backups.

Logische Backups mit pg_dump

pg_dump exportiert eine einzelne Datenbank in SQL oder ein benutzerdefiniertes Binärformat — die portabelste Backup-Methode.

Vollständiges Datenbank-Backup

# Custom Format (empfohlen) — komprimiert, selektive Wiederherstellung möglich
pg_dump -U postgres -Fc -d meindb -f /backup/meindb_$(date +%Y%m%d).pgdump

# Reines SQL — lesbar, Wiederherstellung mit psql
pg_dump -U postgres -Fp -d meindb -f /backup/meindb_$(date +%Y%m%d).sql

Backup einer einzelnen Tabelle

pg_dump -U postgres -Fc -d meindb -t public.bestellungen -f /backup/bestellungen_$(date +%Y%m%d).pgdump

Custom Format vs Reines SQL

FunktionCustom Format (-Fc)Reines SQL (-Fp)
KomprimierungIntegriert (zlib)Nein (gzip separat)
Selektive WiederherstellungJa (-t, -n, -T)Nein
Parallele WiederherstellungJa (pg_restore -j)Nein
MenschenlesbarNeinJa
Wiederherstellungstoolpg_restorepsql

Verwenden Sie Custom Format für Produktions-Backups.

Cluster-weite Backups mit pg_dumpall

pg_dumpall sichert den gesamten PostgreSQL-Cluster: alle Datenbanken, Rollen und Tablespaces.

pg_dumpall -U postgres -f /backup/cluster_$(date +%Y%m%d).sql
pg_dumpall -U postgres --globals-only -f /backup/globals_$(date +%Y%m%d).sql

Wiederherstellung:

psql -U postgres -f /backup/cluster_20260322.sql

Physische Backups mit pg_basebackup

pg_basebackup erstellt eine binäre Kopie des PostgreSQL-Datenverzeichnisses — die Grundlage für PITR und Standby-Server.

pg_basebackup -U replicator -h localhost -D /backup/base \
  -Ft -z -P --wal-method=stream

Voraussetzungen:

  • Rolle mit REPLICATION-Recht: CREATE ROLE replicator REPLICATION LOGIN PASSWORD '...';
  • max_wal_senders >= 2 in postgresql.conf.
  • Eintrag in pg_hba.conf: host replication replicator 127.0.0.1/32 md5

WAL-Archivierung und Point-in-Time Recovery

WAL-Archivierung kombiniert mit einem Basis-Backup ermöglicht PITR — Wiederherstellung zu jedem beliebigen vergangenen Zeitpunkt.

WAL-Archivierung aktivieren

Bearbeiten Sie /etc/postgresql/16/main/postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'
archive_timeout = 300

Für S3:

archive_command = 'aws s3 cp %p s3://meine-pg-backups/wal/%f'
sudo systemctl reload postgresql

Archivierung überprüfen

SELECT last_archived_wal, last_archived_time, last_failed_wal
FROM pg_stat_archiver;

Wiederherstellung zu einem bestimmten Zeitpunkt

  1. Stoppen Sie PostgreSQL und ersetzen Sie das Datenverzeichnis durch das Basis-Backup.
  2. Erstellen Sie recovery.signal im Datenverzeichnis (PostgreSQL 12+).
  3. Konfigurieren Sie Recovery-Parameter in postgresql.conf:
restore_command = 'cp /backup/wal_archive/%f %p'
recovery_target_time = '2026-03-22 14:30:00'
recovery_target_action = 'promote'
  1. Starten Sie PostgreSQL — es wird WAL bis zum angegebenen Zeitpunkt wiedergeben.

Wiederherstellung aus Backups

Wiederherstellen mit pg_restore (Custom Format)

createdb -U postgres meindb_wiederhergestellt
pg_restore -U postgres -d meindb_wiederhergestellt /backup/meindb_20260322.pgdump

# Parallele Wiederherstellung
pg_restore -U postgres -j 4 -d meindb_wiederhergestellt /backup/meindb_20260322.pgdump

# Nur eine Tabelle wiederherstellen
pg_restore -U postgres -d meindb_wiederhergestellt -t public.bestellungen /backup/meindb_20260322.pgdump

Wiederherstellen mit psql (Reines SQL)

psql -U postgres -d meindb_wiederhergestellt -f /backup/meindb_20260322.sql

Inhalt eines Backups anzeigen

pg_restore --list /backup/meindb_20260322.pgdump | grep TABLE

Automatisierte Backup-Skripte

Tägliches Skript mit Rotation

#!/bin/bash
# /usr/local/bin/pg-backup.sh

set -euo pipefail

BACKUP_DIR="/backup/postgresql"
TAGE_BEHALTEN=7
PG_USER="postgres"
DATUM=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR/daily"

for DB in $(psql -U "$PG_USER" -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    DB=$(echo "$DB" | xargs)
    pg_dump -U "$PG_USER" -Fc -d "$DB" \
        -f "$BACKUP_DIR/daily/${DB}_${DATUM}.pgdump"
done

pg_dumpall -U "$PG_USER" --globals-only \
    -f "$BACKUP_DIR/daily/globals_${DATUM}.sql"

find "$BACKUP_DIR/daily" -type f -mtime +"$TAGE_BEHALTEN" -delete
0 2 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Backup-Sicherheit — Komprimierung, Verschlüsselung und S3

Gzip-Komprimierung

pg_dump -U postgres -Fp -d meindb | gzip > /backup/meindb_$(date +%Y%m%d).sql.gz
gunzip -c /backup/meindb_20260322.sql.gz | psql -U postgres -d meindb_wiederhergestellt

GPG-Verschlüsselung

pg_dump -U postgres -Fc -d meindb | \
    gpg --encrypt --recipient admin@beispiel.de \
    -o /backup/meindb_$(date +%Y%m%d).pgdump.gpg

gpg --decrypt /backup/meindb_20260322.pgdump.gpg | \
    pg_restore -U postgres -d meindb_wiederhergestellt

Upload zu S3

aws s3 cp /backup/meindb_20260322.pgdump \
    s3://meine-pg-backups/daily/ --storage-class STANDARD_IA

aws s3 sync /backup/postgresql/ s3://meine-pg-backups/ --storage-class STANDARD_IA

Vergleich der Backup-Methoden

MethodeGeschwindigkeitGrößeSelektive WiederherstellungPITR-UnterstützungAnwendungsfall
pg_dump (custom)MittelKleinJaNeinTägliches Backup pro DB
pg_dump (reines SQL)MittelGroßNeinNeinKleine DBs, Portabilität
pg_dumpallLangsamGroßNeinNeinVollständiger Cluster + Rollen
pg_basebackupSchnellVollst. Verz.NeinJa (mit WAL)PITR-Basis, Standby
WAL-ArchivierungKontinuierlichVariabelNeinJaKontinuierlicher Schutz

Praxisbeispiel: Disaster-Recovery-Durchführung

Situation: Ihr PostgreSQL-16-Produktionsserver fällt um 15:47 Uhr an einem Dienstag aus. Das letzte pg_basebackup lief Sonntag um 01:00 Uhr. Die WAL-Archivierung zu S3 lief kontinuierlich.

Ziel: Wiederherstellung zum Stand von 15:45 Uhr.

Schritt 1 — Neuen Server bereitstellen

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

Schritt 2 — Basis-Backup wiederherstellen

cd /var/lib/postgresql/16/main
sudo -u postgres tar -xzf /backup/base/20260320/base.tar.gz .
sudo -u postgres tar -xzf /backup/base/20260320/pg_wal.tar.gz pg_wal/

Schritt 3 — Recovery konfigurieren

restore_command = 'aws s3 cp s3://meine-pg-backups/wal/%f %p'
recovery_target_time = '2026-03-22 15:45:00'
recovery_target_action = 'promote'
sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
sudo systemctl start postgresql
sudo tail -f /var/log/postgresql/postgresql-16-main.log

Schritt 4 — Daten prüfen und Betrieb wiederaufnehmen

SELECT COUNT(*) FROM bestellungen WHERE created_at < '2026-03-22 15:46:00';

Sonderfälle und häufige Fallstricke

  • Große Datenbanken (>100 GB): Verwenden Sie pg_dump -j und pg_restore -j für parallele Dumps und Wiederherstellungen.
  • Sperren während des Backups: pg_dump hält eine ACCESS SHARE-Sperre. Führen Sie Backups in Zeiten geringer Last durch.
  • Kodierungsunterschiede: Erstellen Sie die Zieldatenbank mit der gleichen Kodierung und Locale wie die Quelle.
  • Sequenzen: Das Custom Format erfasst Sequenzwerte zum Zeitpunkt des Dumps.
  • Erweiterungen: Stellen Sie sicher, dass die gleichen Erweiterungen auf dem Wiederherstellungsserver verfügbar sind.
  • Benutzerdefinierte Tablespaces: Sie müssen mit den richtigen Pfaden vorhanden sein, bevor pg_restore ausgeführt wird.

Fehlerbehebung

ProblemWahrscheinliche UrsacheLösung
ERROR: permission denied bei pg_dumpRolle fehlen LeserechteErteilen Sie pg_read_all_data oder nutzen Sie den Superuser postgres
ERROR: relation already exists bei pg_restoreWiederherstellung in nicht leere DatenbankZiel-DB löschen und neu erstellen oder Flag --clean verwenden
archive_command schlägt lautlos fehlFalscher Pfad oder Berechtigungenpg_stat_archiver.last_failed_wal prüfen und Befehl manuell testen
PITR hängt bei WAL-Wiedergaberecovery_target_time in der Zukunft oder falsche ZeitzoneUTC-Zeitstempel verwenden; show timezone in psql prüfen
pg_basebackup: error: could not connectKein Eintrag in pg_hba.confhost replication replicator 127.0.0.1/32 md5 zu pg_hba.conf hinzufügen
Wiederherstellung sehr langsamSingle-threaded Wiederherstellung eines großen Dumpspg_restore -j N verwenden, N = Anzahl CPU-Kerne

Zusammenfassung

  • Verwenden Sie pg_dump (Custom Format) täglich für logische Backups mit selektiver Wiederherstellung.
  • Verwenden Sie pg_dumpall —globals-only täglich um Rollen und Tablespaces zu sichern.
  • Verwenden Sie pg_basebackup wöchentlich als binäre Basis für schnelle Vollwiederherstellung.
  • Aktivieren Sie WAL-Archivierung für kontinuierlichen Schutz und PITR.
  • Verschlüsseln Sie Backups mit GPG und lagern Sie sie extern in S3.
  • Testen Sie Wiederherstellungen regelmäßig — ein ungetestetes Backup ist kein Backup.
  • Verwenden Sie Cron + Rotationsskripte um Backups zu automatisieren und alte zu löschen.

Verwandte Artikel