PostgreSQL-Backups mit pg_dump automatisieren

pg_dump erzeugt eine konsistente Sicherung einer laufenden PostgreSQL-Datenbank, ohne Schreibzugriffe zu blockieren. Manuell abgesetzt wird der Befehl aber genau dann vergessen, wenn der Betrieb dicht wird. Diese Anleitung baut aus pg_dump, pg_dumpall, einem Shell-Skript und einem systemd-Timer eine Backup-Routine mit Aufbewahrungsfrist und geprüftem Restore.

Was macht pg_dump genau?

pg_dump ist das Client-Werkzeug von PostgreSQL, das eine einzelne Datenbank in eine Dump-Datei schreibt und dabei über einen MVCC-Snapshot einen in sich konsistenten Stand zum Startzeitpunkt sichert, ohne Lese- oder Schreibzugriffe zu sperren.

Der Dump enthält Schema und Daten genau dieser einen Datenbank. Nicht enthalten sind clusterweite Objekte wie Rollen, Passwort-Hashes und Tablespace-Definitionen. Dafür ist pg_dumpall --globals-only zuständig. Ein Dump ersetzt außerdem kein Point-in-Time-Recovery: Du stellst den Stand des Dump-Zeitpunkts wieder her, nicht den Zustand eine Sekunde vor einem fehlerhaften DELETE. Wer minutengenau zurückspulen muss, braucht zusätzlich WAL-Archivierung.

Voraussetzungen

• Ein Linux-Host mit PostgreSQL 14 oder neuer, zum Beispiel eine skalierbare Cloud-VM für Datenbank-Workloads mit Debian 12 oder Ubuntu 24.04
• Die Client-Tools pg_dump, pg_dumpall, pg_restore und psql in derselben oder einer neueren Hauptversion als der Server
• Ein Konto mit sudo-Rechten sowie Zugriff auf den Systembenutzer postgres
• Freier Speicherplatz für mindestens zwei volle Dump-Generationen

Prüfe zuerst, welche Versionen tatsächlich installiert sind:

$ psql --version
psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)
$ sudo -u postgres psql -Atc "SHOW server_version;"
16.3

Welches Dump-Format solltest du wählen?

Für automatisierte Backups ist das Custom-Format (-Fc) die richtige Wahl, weil es komprimiert, das selektive Zurückspielen einzelner Tabellen erlaubt und von pg_restore parallel eingelesen wird; nur bei sehr großen Datenbanken lohnt das Directory-Format.

Paralleles Dumpen mit -j funktioniert ausschließlich mit -Fd. Beim Zurückspielen dagegen beschleunigt pg_restore -j sowohl Custom- als auch Directory-Dumps. Ab PostgreSQL 16 akzeptiert -Z neben gzip auch lz4 und zstd, etwa --compress=zstd:3 für deutlich schnellere Läufe bei ähnlicher Größe.

Backup-Rolle und .pgpass einrichten

Backups laufen nicht als Superuser. Seit PostgreSQL 14 gibt es dafür die vordefinierte Rolle pg_read_all_data, die Lesezugriff auf alle Tabellen, Views und Sequenzen gewährt:

CREATE ROLE backup WITH LOGIN PASSWORD '<starkes-passwort>';
GRANT pg_read_all_data TO backup;

Damit pg_dump unbeaufsichtigt läuft, gehört das Passwort in die .pgpass des Systembenutzers postgres. PostgreSQL ignoriert die Datei, wenn die Rechte zu offen sind:

$ sudo -u postgres tee /var/lib/postgresql/.pgpass > /dev/null <<'EOF'
127.0.0.1:5432:*:backup:<starkes-passwort>
EOF
$ sudo chmod 0600 /var/lib/postgresql/.pgpass

Läuft die Datenbank auf demselben Host und ist in der /etc/postgresql/16/main/pg_hba.conf für den Unix-Socket peer eingetragen, kannst du das Skript auch ohne Passwort direkt als Benutzer postgres fahren. Die .pgpass-Variante hat den Vorteil, dass sie unverändert funktioniert, sobald die Datenbank auf einen eigenen Host umzieht.

Backup-Skript schreiben

Das Skript sichert zuerst die clusterweiten Objekte, dumpt anschließend jede Datenbank einzeln und räumt erst danach alte Dateien weg. Die Reihenfolge ist wichtig: Durch set -euo pipefail bricht der Lauf bei einem Fehler ab, bevor die Aufbewahrungsfrist greift. So löscht ein fehlgeschlagenes Backup nicht die letzten funktionierenden Dumps.

graph TD
  A["systemd-Timer pg-backup.timer"] --> B["pg-backup.sh"]
  B --> C["pg_dumpall --globals-only"]
  B --> D["pg_dump -Fc je Datenbank"]
  C --> E{"Alle Läufe fehlerfrei?"}
  D --> E
  E -- ja --> F["Dumps älter als 14 Tage löschen"]
  E -- nein --> G["Abbruch, Fehler ins Journal, alte Dumps behalten"]
  F --> H["Kopie auf zweites Ziel und Restore-Test"]

Lege das Skript unter /usr/local/sbin/pg-backup.sh an:

#!/usr/bin/env bash
# /usr/local/sbin/pg-backup.sh
set -euo pipefail
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=14
export PGHOST="127.0.0.1"
export PGUSER="backup"
STAMP="$(date +%Y-%m-%d_%H%M)"
mkdir -p "$BACKUP_DIR"
# Clusterweite Objekte: Rollen, Passwort-Hashes, Tablespaces
pg_dumpall --globals-only | gzip -c > "$BACKUP_DIR/globals_${STAMP}.sql.gz"
# Alle verbindbaren Datenbanken ohne Templates ermitteln
mapfile -t DBS < <(psql -Atqc "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate ORDER BY datname;")
for db in "${DBS[@]}"; do
  pg_dump --format=custom --compress=6 --file="$BACKUP_DIR/${db}_${STAMP}.dump" "$db"
  echo "Dump geschrieben: ${db}_${STAMP}.dump"
done
# Aufräumen erst nach fehlerfreiem Durchlauf
find "$BACKUP_DIR" -type f -name '*.dump' -mtime +"$RETENTION_DAYS" -delete
find "$BACKUP_DIR" -type f -name '*.sql.gz' -mtime +"$RETENTION_DAYS" -delete
echo "Backup-Lauf abgeschlossen: ${#DBS[@]} Datenbanken"

Rechte und Zielverzeichnis setzen, danach einmal von Hand starten:

$ sudo install -o postgres -g postgres -m 0750 -d /var/backups/postgresql
$ sudo chmod 0755 /usr/local/sbin/pg-backup.sh
$ sudo -u postgres /usr/local/sbin/pg-backup.sh
Dump geschrieben: appdb_2026-08-17_1042.dump
Backup-Lauf abgeschlossen: 1 Datenbanken

RETENTION_DAYS steuert die Aufbewahrungsfrist in Tagen. --compress=6 ist der gzip-Standard des Custom-Formats; auf CPU-knappen Systemen ist --compress=1 sinnvoller, ab PostgreSQL 16 alternativ --compress=zstd:3.

Backup per systemd-Timer automatisieren

Ein systemd-Timer ist gegenüber einem Cron-Eintrag im Vorteil, weil Ausgabe und Exit-Code im Journal landen und Persistent=true verpasste Läufe nach einem Neustart nachholt. Zuerst die Service-Unit:

# /etc/systemd/system/pg-backup.service
[Unit]
Description=PostgreSQL-Backup mit pg_dump
After=postgresql.service
[Service]
Type=oneshot
User=postgres
Group=postgres
Environment=PGPASSFILE=/var/lib/postgresql/.pgpass
ExecStart=/usr/local/sbin/pg-backup.sh
Nice=10
IOSchedulingClass=idle

Dann der Timer:

# /etc/systemd/system/pg-backup.timer
[Unit]
Description=Täglicher PostgreSQL-Backup-Lauf um 02:30 Uhr
[Timer]
OnCalendar=*-*-* 02:30:00
RandomizedDelaySec=300
Persistent=true
[Install]
WantedBy=timers.target

Aktivieren und einmal prüfen:

$ sudo systemctl daemon-reload
$ sudo systemctl enable --now pg-backup.timer
$ systemctl list-timers pg-backup.timer
NEXT                        LEFT       LAST  PASSED  UNIT             ACTIVATES
Tue 2026-08-18 02:30:00 UTC 15h left   -     -       pg-backup.timer  pg-backup.service

Nice=10 und IOSchedulingClass=idle halten die Last des Dumps von der produktiven Abfragelast fern. Auf stark ausgelasteten Datenbanken ist das der Unterschied zwischen einem unauffälligen Backup und spürbar längeren Antwortzeiten um 02:30 Uhr.

Dumps auf ein zweites Ziel kopieren

Ein Dump, der nur auf derselben Maschine liegt, überlebt den Ausfall dieser Maschine nicht. Ergänze das Skript deshalb um einen Transfer auf einen zweiten Host oder in einen Objektspeicher, zum Beispiel per rsync über SSH mit einem eigenen Schlüssel ohne Passphrase:

rsync -a --delete-after "$BACKUP_DIR/" backup@<zielhost>:/srv/pg-dumps/<quellhost>/

Wer Verschlüsselung und Deduplizierung braucht, nutzt statt rsync besser restic backup "$BACKUP_DIR" gegen ein Repository auf einem separaten System. Wichtig ist in beiden Fällen, dass der Zielspeicher nicht mit denselben Zugangsdaten beschreibbar ist wie der Quellhost.

Restore prüfen

Ein Backup gilt erst als funktionsfähig, wenn es einmal zurückgespielt wurde. Prüfe zunächst das Inhaltsverzeichnis des Dumps, danach den vollständigen Restore in eine Wegwerf-Datenbank:

$ sudo -u postgres pg_restore --list /var/backups/postgresql/appdb_2026-08-17_1042.dump | head -n 5
;
; Archive created at 2026-08-17 10:42:11 UTC
;     dbname: appdb
;     TOC Entries: 74
;     Compression: gzip
$ sudo -u postgres createdb -T template0 restore_test
$ sudo -u postgres pg_restore -d restore_test -j 4 /var/backups/postgresql/appdb_2026-08-17_1042.dump
$ sudo -u postgres psql -d restore_test -Atc "SELECT count(*) FROM information_schema.tables WHERE table_schema='public';"
23
$ sudo -u postgres dropdb restore_test

Gibt pg_restore keine Fehler aus und stimmt die Tabellenzahl mit der Quelle überein, ist der Dump brauchbar. Plane diesen Test monatlich ein. Für die Rollen aus globals_*.sql.gz gilt dasselbe: zcat globals_2026-08-17_1042.sql.gz | grep CREATE ROLE zeigt in Sekunden, ob die Datei sinnvolle Inhalte hat.

Troubleshooting

pg_dump: error: aborting because of server version mismatch Der Client ist älter als der Server. Installiere die Client-Tools aus dem PGDG-Repository und rufe den passenden Binärpfad direkt auf, etwa /usr/lib/postgresql/16/bin/pg_dump.

FATAL: Peer-Authentifizierung für Benutzer „backup" fehlgeschlagen Die Verbindung läuft über den Unix-Socket, in der pg_hba.conf steht dort peer. Setze PGHOST=127.0.0.1, damit die host-Zeile mit scram-sha-256 greift, und lade die Konfiguration mit sudo systemctl reload postgresql neu.

pg_dump: error: permission denied for table ... Auf PostgreSQL 13 und älter existiert pg_read_all_data noch nicht. Vergib dort GRANT USAGE ON SCHEMA public TO backup; und GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; plus ALTER DEFAULT PRIVILEGES für neue Tabellen.

Der Timer läuft nie Meist wurde nur der Service, nicht der Timer aktiviert. systemctl is-enabled pg-backup.timer muss enabled melden; journalctl -u pg-backup.service -n 50 --no-pager zeigt den letzten Lauf samt Exit-Code.

Fazit

Mit pg_dump im Custom-Format, pg_dumpall --globals-only für die Rollen und einem systemd-Timer steht eine Backup-Routine, die ohne manuelle Eingriffe läuft und im Journal nachvollziehbar bleibt. Zwei Punkte entscheiden über den Ernstfall: die Kopie auf ein zweites Ziel und der regelmäßige Restore-Test in eine Wegwerf-Datenbank. Wer minutengenau wiederherstellen muss, ergänzt die Dumps um WAL-Archivierung mit archive_command oder ein Werkzeug wie pgBackRest.