PostgreSQL-Backup und -Wiederherstellung ist das Fundament jedes Disaster-Recovery-Plans für Datenbanken. Ob Sie eine einzelne Anwendungsdatenbank schützen oder Hunderte von Instanzen verwalten — wer pg_dump und pg_restore beherrscht, kann Hardware-Ausfälle, versehentliche Löschungen und fehlgeschlagene Migrationen beheben. Dieser Leitfaden deckt jeden praxisrelevanten Aspekt ab: von einfachen Dumps über automatisierte Zeitplanung und selektive Wiederherstellungen bis hin zum entscheidenden Schritt, den die meisten Teams überspringen — das tatsächliche Testen, ob Backups wiederhergestellt werden können.

Voraussetzungen

  • PostgreSQL 12 oder neuer installiert und in Betrieb
  • Zugang zu einer PostgreSQL-Datenbank mit ausreichenden Rechten (pg_dump benötigt mindestens Lesezugriff auf alle Objekte)
  • sudo- oder postgres-Benutzerzugang auf dem Datenbankserver
  • Grundkenntnisse in SQL und PostgreSQL-Konzepten (Datenbanken, Schemas, Tabellen)

Backups mit pg_dump erstellen

pg_dump erzeugt logische Backups — eine Darstellung Ihrer Datenbank als SQL-Anweisungen oder komprimiertes Archiv. Anders als Kopien auf Dateisystemebene sind diese Backups über PostgreSQL-Versionen und Plattformen hinweg portierbar.

Einfacher SQL-Dump (Standard)

Das einfachste Backup erstellt eine .sql-Datei mit allen SQL-Befehlen, die zur Wiederherstellung der Datenbank notwendig sind:

# Eine einzelne Datenbank in eine SQL-Datei sichern
pg_dump -U postgres -h localhost mydb > mydb_backup.sql

# Mit CREATE DATABASE-Anweisung
pg_dump -U postgres -h localhost -C mydb > mydb_backup_with_create.sql

# Dump mit Zeitstempel im Dateinamen
pg_dump -U postgres mydb > "mydb_$(date +%Y%m%d_%H%M%S).sql"

Das einfache SQL-Format ist menschenlesbar und lässt sich mit psql wiederherstellen. Der Nachteil: Es unterstützt weder parallele Wiederherstellung noch selektive Tabellenwiederherstellung.

Custom-Format (-Fc) — Empfohlen für Produktion

Das Custom-Format erstellt ein komprimiertes, nicht-textuelles Archiv, das die flexibelsten Wiederherstellungsoptionen bietet:

# Custom-Format-Backup (standardmäßig komprimiert)
pg_dump -U postgres -Fc mydb > mydb_backup.dump

# Custom-Format mit maximaler Komprimierung
pg_dump -U postgres -Fc -Z 9 mydb > mydb_backup.dump

# Custom-Format mit parallelem Dump (4 Jobs)
pg_dump -U postgres -Fc -j 4 mydb > mydb_backup.dump

Warum das Custom-Format bevorzugt wird:

  • Automatisch komprimiert (typischerweise 5–10-mal kleiner als einfaches SQL)
  • Unterstützt parallele Wiederherstellung mit pg_restore -j
  • Ermöglicht selektive Wiederherstellung (bestimmte Tabellen, Schemas oder nur Daten)
  • Kann Elemente während der Wiederherstellung neu anordnen, um die Ladegeschwindigkeit zu optimieren

Directory-Format (-Fd) — Optimal für große Datenbanken

Das Directory-Format erstellt ein Verzeichnis mit einer Datei pro Tabelle und ermöglicht echte parallele Dumps:

# Directory-Format mit parallelem Dump (8 Worker)
pg_dump -U postgres -Fd -j 8 -f /backup/mydb_dir mydb

Dabei entsteht ein Verzeichnis mit einer toc.dat-Datei (Inhaltsverzeichnis) und je einer komprimierten Datei pro Tabelle. Der parallele Dump verkürzt die Backup-Zeit bei großen Datenbanken erheblich.

Alle Datenbanken sichern (pg_dumpall)

Um jede Datenbank im PostgreSQL-Cluster sowie globale Objekte (Rollen, Tablespaces) zu sichern:

# Alle Datenbanken und globale Objekte sichern
pg_dumpall -U postgres > all_databases.sql

# Nur globale Objekte sichern (Rollen, Tablespaces)
pg_dumpall -U postgres --globals-only > globals.sql

pg_dumpall erzeugt immer einfaches SQL-Format. Bei großen Clustern empfiehlt es sich, Globals separat zu sichern und pg_dump pro Datenbank im Custom-Format einzusetzen.

Selektive Dumps

# Eine einzelne Tabelle sichern
pg_dump -U postgres -t users mydb > users_table.sql

# Mehrere bestimmte Tabellen sichern
pg_dump -U postgres -t users -t orders -t products mydb > selected_tables.sql

# Ein bestimmtes Schema sichern
pg_dump -U postgres -n public mydb > public_schema.sql

# Nur Schema sichern (ohne Daten)
pg_dump -U postgres -s mydb > schema_only.sql

# Nur Daten sichern (ohne Schema)
pg_dump -U postgres -a mydb > data_only.sql

# Eine große Tabelle vom Dump ausschließen
pg_dump -U postgres -T audit_log mydb > mydb_no_audit.sql

Backups wiederherstellen

Wiederherstellung aus einfachem SQL

# In eine vorhandene Datenbank wiederherstellen
psql -U postgres -h localhost mydb < mydb_backup.sql

# Datenbank anlegen und wiederherstellen (wenn Dump -C enthält)
psql -U postgres -h localhost < mydb_backup_with_create.sql

# Wiederherstellung mit ausführlicher Ausgabe
psql -U postgres -v ON_ERROR_STOP=1 mydb < mydb_backup.sql

Das Flag ON_ERROR_STOP=1 bewirkt, dass psql beim ersten Fehler abbricht, anstatt stillschweigend fortzufahren. Verwenden Sie es immer beim Wiederherstellen von Produktionsdatenbanken.

Wiederherstellung aus Custom-Format

# Einfache Wiederherstellung
pg_restore -U postgres -d mydb mydb_backup.dump

# Parallele Wiederherstellung (8 Jobs — deutlich schneller bei großen Datenbanken)
pg_restore -U postgres -d mydb -j 8 mydb_backup.dump

# In eine neue Datenbank wiederherstellen
createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored mydb_backup.dump

# Vorhandene Objekte vor der Wiederherstellung löschen (clean)
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dump

Bestimmte Tabellen wiederherstellen

# Eine einzelne Tabelle aus einem Custom-Format-Dump wiederherstellen
pg_restore -U postgres -d mydb -t users mydb_backup.dump

# Wiederherstellung anhand einer Listendatei (feingranulare Kontrolle)
pg_restore -l mydb_backup.dump > restore_list.txt
# restore_list.txt bearbeiten — unerwünschte Einträge auskommentieren
pg_restore -U postgres -d mydb -L restore_list.txt mydb_backup.dump

Bestimmtes Schema wiederherstellen

# Nur das Schema 'public' wiederherstellen
pg_restore -U postgres -d mydb -n public mydb_backup.dump

# Nur Daten wiederherstellen (Schema existiert bereits)
pg_restore -U postgres -d mydb -a mydb_backup.dump

Vergleich der PostgreSQL-Backup-Methoden

Merkmalpg_dump (Logisch)pg_basebackup (Physisch)Dateisystem-Snapshot
Backup-UmfangEinzelne DatenbankGesamter ClusterGesamter Cluster
Versionsübergreifende WiederherstellungJaNein (gleiche Hauptversion)Nein
Selektive TabellenwiederherstellungJa (Flag -t)NeinNein
Point-in-Time-RecoveryNeinJa (mit WAL)Ja (mit WAL)
Backup während BetriebJa (konsistenter Snapshot)JaErfordert fsync/freeze
Backup-GrößeKleiner (komprimierte Daten)Größer (volles Datenverzeichnis)Größte (volle Festplatte)
Backup-GeschwindigkeitLangsamer (Lesen per SQL)Schneller (Streaming)Am schnellsten (Block-Ebene)
Am besten fürEinzelne Datenbanken, MigrationenVollständiges Cluster-DR, PITRVM-/Cloud-Snapshots

pg_dump empfiehlt sich für tägliche Backups einzelner Datenbanken und für Migrationen zwischen PostgreSQL-Versionen. pg_basebackup ist die richtige Wahl, wenn Sie Point-in-Time-Recovery benötigen oder Streaming-Replikation einrichten. Dateisystem-Snapshots ergänzen die anderen Methoden — sie sind schnell, erfordern aber sorgfältigen Umgang mit WAL-Dateien und sind kein vollständiger Ersatz.

Backups mit Cron automatisieren

Backup-Skript

#!/bin/bash
# /usr/local/bin/pg_backup.sh
# Automatisiertes PostgreSQL-Backup-Skript

set -euo pipefail

# Konfiguration
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=30
PG_USER="postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"

# Backup-Verzeichnis anlegen, falls nicht vorhanden
mkdir -p "$BACKUP_DIR"

# Log-Funktion
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}

log "Starte PostgreSQL-Backup"

# Globals sichern (Rollen, Tablespaces)
pg_dumpall -U "$PG_USER" --globals-only > "$BACKUP_DIR/globals_${TIMESTAMP}.sql" 2>> "$LOG_FILE"
log "Globals gesichert"

# Jede Datenbank im Custom-Format sichern
for DB in $(psql -U "$PG_USER" -At -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    DUMP_FILE="$BACKUP_DIR/${DB}_${TIMESTAMP}.dump"
    pg_dump -U "$PG_USER" -Fc -Z 6 "$DB" > "$DUMP_FILE" 2>> "$LOG_FILE"
    SIZE=$(du -h "$DUMP_FILE" | cut -f1)
    log "Gesichert $DB ($SIZE)"
done

# Backups älter als Aufbewahrungszeitraum löschen
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete
find "$BACKUP_DIR" -name "globals_*.sql" -mtime +${RETENTION_DAYS} -delete
log "Backups älter als $RETENTION_DAYS Tage bereinigt"

log "Backup abgeschlossen"
# Ausführbar machen
chmod +x /usr/local/bin/pg_backup.sh

# Zuerst manuell testen
sudo -u postgres /usr/local/bin/pg_backup.sh

Cron-Zeitplan

# Crontab des postgres-Benutzers bearbeiten
sudo crontab -u postgres -e
# Tägliches Backup um 2:00 Uhr
0 2 * * * /usr/local/bin/pg_backup.sh

# Wöchentliches Vollbackup sonntags um 1:00 Uhr (längere Aufbewahrung)
0 1 * * 0 /usr/local/bin/pg_backup.sh

Passwortlose Authentifizierung mit .pgpass

Für Cron-Jobs benötigen Sie passwortlose Authentifizierung. Erstellen Sie eine .pgpass-Datei:

# .pgpass für den postgres-Benutzer erstellen
sudo -u postgres bash -c 'cat > ~/.pgpass << EOF
localhost:5432:*:postgres:your_secure_password
EOF'

# Erforderliche Berechtigungen setzen (pg_dump verweigert world-readable .pgpass)
sudo -u postgres chmod 600 ~/.pgpass

Format: hostname:port:database:username:password. Verwenden Sie * als Platzhalter für die Datenbank, um alle Datenbanken abzudecken.

Backup-Integrität testen

Ein Backup, das sich nicht wiederherstellen lässt, ist kein Backup. Planen Sie regelmäßige Wiederherstellungstests ein:

#!/bin/bash
# /usr/local/bin/pg_restore_test.sh
# Backup-Integrität durch Wiederherstellung in eine temporäre Datenbank prüfen

set -euo pipefail

BACKUP_FILE="$1"
TEST_DB="restore_test_$(date +%s)"
PG_USER="postgres"

# Temporäre Datenbank anlegen
createdb -U "$PG_USER" "$TEST_DB"

# Wiederherstellung versuchen
if pg_restore -U "$PG_USER" -d "$TEST_DB" "$BACKUP_FILE" 2>/dev/null; then
    # Daten prüfen
    TABLES=$(psql -U "$PG_USER" -At -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'" "$TEST_DB")
    ROWS=$(psql -U "$PG_USER" -At -c "SELECT sum(n_live_tup) FROM pg_stat_user_tables" "$TEST_DB")
    echo "BESTANDEN: $TABLES Tabellen mit $ROWS Gesamtzeilen wiederhergestellt"
else
    echo "FEHLGESCHLAGEN: Wiederherstellung hat Fehler erzeugt"
fi

# Aufräumen
dropdb -U "$PG_USER" "$TEST_DB"
# Das aktuellste Backup testen
sudo -u postgres /usr/local/bin/pg_restore_test.sh /var/backups/postgresql/mydb_latest.dump

Fehlerbehebung bei häufigen Problemen

„pg_dump: error: connection to server failed”

# Prüfen, ob PostgreSQL läuft
sudo systemctl status postgresql

# Verbindung testen
psql -U postgres -h localhost -l

# pg_hba.conf auf Authentifizierungsregeln prüfen
sudo cat /etc/postgresql/16/main/pg_hba.conf | grep -v '^#' | grep -v '^$'

„pg_restore: error: could not execute query: ERROR: relation already exists”

Die Zieldatenbank enthält bereits Objekte. Verwenden Sie --clean --if-exists, um vorhandene Objekte vor der Wiederherstellung zu löschen:

pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dump

Oder stellen Sie in einer leeren Datenbank wieder her:

dropdb -U postgres mydb
createdb -U postgres mydb
pg_restore -U postgres -d mydb mydb_backup.dump

Backup ist zu langsam

# Parallelen Dump verwenden (nur Custom- oder Directory-Format)
pg_dump -U postgres -Fd -j $(nproc) -f /backup/mydb_dir mydb

# Große, regenerierbare Tabellen ausschließen
pg_dump -U postgres -Fc -T large_cache_table -T session_data mydb > mydb.dump

# Weniger stark komprimieren für mehr Geschwindigkeit
pg_dump -U postgres -Fc -Z 1 mydb > mydb_fast.dump

Wiederherstellung ist zu langsam

# Parallele Wiederherstellung verwenden (Custom- oder Directory-Format)
pg_restore -U postgres -d mydb -j $(nproc) mydb_backup.dump

# Trigger während des Datenladens deaktivieren (deutlich schneller)
pg_restore -U postgres -d mydb --disable-triggers mydb_backup.dump

# maintenance_work_mem für die Wiederherstellungssitzung erhöhen
psql -U postgres -d mydb -c "SET maintenance_work_mem = '1GB';"
pg_restore -U postgres -d mydb mydb_backup.dump

.pgpass-Datei wird ignoriert

# Berechtigungen prüfen (muss 600 sein)
ls -la ~/.pgpass

# Berechtigungen korrigieren
chmod 600 ~/.pgpass

# Format prüfen (keine zusätzlichen Leerzeichen)
cat ~/.pgpass
# Sollte lauten: hostname:port:database:username:password

Besonderheiten und Randfälle

Large Objects (BLOBs): pg_dump schließt Large Objects standardmäßig ein, aber pg_restore -t stellt sie NICHT wieder her. Verwenden Sie pg_restore -L mit einer benutzerdefinierten Liste, um Large Objects bei selektiven Wiederherstellungen einzuschließen.

Extensions: pg_dump enthält CREATE EXTENSION-Anweisungen, aber die Wiederherstellung schlägt fehl, wenn die Extension auf dem Zielserver nicht installiert ist. Installieren Sie Extensions vor der Wiederherstellung.

Eigentümerschaft und Berechtigungen: pg_dump erfasst die Objekteigentümerschaft. Wenn die Eigentümerrolle auf dem Zielserver nicht existiert, schlägt die Wiederherstellung fehl. Verwenden Sie --no-owner, um Eigentümerzuweisungen zu überspringen, oder legen Sie die Rollen zuerst über den Globals-Dump an.

Gleichzeitiger Zugriff während des Dumps: pg_dump erstellt zu Beginn einen Snapshot und liest konsistent daraus. Andere Transaktionen laufen normal weiter. DDL-Operationen (ALTER TABLE, DROP), die AccessExclusiveLock halten, blockieren jedoch den Dump. Planen Sie Dumps in Zeiten geringer Aktivität.

Zeichensatz-Inkompatibilitäten: Wenn Quell- und Zieldatenbank unterschiedliche Zeichensätze verwenden, kann es zu Datenverfälschungen kommen. Überprüfen Sie immer, ob die Kodierung übereinstimmt:

psql -U postgres -c "SHOW server_encoding;"

Zusammenfassung

  • pg_dump mit Custom-Format (-Fc) ist der empfohlene Ansatz für Produktions-Backups — komprimiert Daten, unterstützt parallele Wiederherstellung und ermöglicht selektive Tabellenwiederherstellung
  • pg_dumpall erfasst globale Objekte (Rollen, Tablespaces), die pg_dump übersieht — sichern Sie Globals stets separat zusammen mit Ihren Datenbank-Backups
  • Mit cron und einem Backup-Skript automatisieren, das Rotation, Logging und Fehlerbehandlung umfasst — verlassen Sie sich niemals auf manuelle Backup-Verfahren
  • Wiederherstellungen regelmäßig testen, indem Sie in eine temporäre Datenbank wiederherstellen und Zeilenzahlen validieren — ein nie getestetes Backup könnte im Ernstfall versagen
  • .pgpass für passwortlose Authentifizierung in Cron-Jobs mit chmod 600-Berechtigungen verwenden — pg_dump verweigert das Lesen world-lesbarer Credential-Dateien
  • Paralleler Dump und parallele Wiederherstellung (Flag -j) reduzieren die Zeit für große Datenbanken drastisch — verwenden Sie das Directory-Format (-Fd) für beste Parallel-Performance

Verwandte Artikel