Tag 8: Logische Backups
Ziel: Du sicherst Datenbanken mit
pg_dumpin allen Formaten, sicherst Rollen separat, stellst ganz oder selektiv wieder her und beweist mit einem Restore-Test, dass das Backup taugt. · Dauer: ca. 60 min · Voraussetzung: Tag 2 und 3
Briefing
pg_dumpsichert eine Datenbank als SQL bzw. Archiv – konsistent zu einem Zeitpunkt, im laufenden Betrieb, ohne Schreibsperre.- Rollen und Tablespaces gehören dem Cluster, nicht der Datenbank:
dafür
pg_dumpall --globals-only. - Formate:
plain(SQL-Text,psql),custom(eine Datei,pg_restore, selektiv),directory(ein Verzeichnis, parallel sichern und wiederherstellen). - Logisch heißt: kein Point-in-Time-Recovery, Restore dauert so lange wie das Einspielen plus Indexbau. Für große Datenbanken kommt Tag 9.
Alle Befehle laufen auf pg1, außer es steht
pg2 dran.
Drill 1: Testdatenbank mit Fallen
Eine Datenbank mit eigenem Owner, zwei Schemas, Rechten und einer Erweiterung – genau die Dinge, an denen Restores scheitern:
psql -c 'CREATE ROLE tag08_owner' -c 'CREATE ROLE tag08_reader'
createdb -O tag08_owner tag08
psql -d tag08 <<'EOF'
SET ROLE tag08_owner;
CREATE SCHEMA app;
CREATE SCHEMA audit;
CREATE TABLE app.notes (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
INSERT INTO app.notes (text) SELECT 'Notiz ' || i FROM generate_series(1, 200000) i;
CREATE TABLE app.tags (note_id bigint REFERENCES app.notes, tag text);
INSERT INTO app.tags SELECT i, 'tag' || (i % 10) FROM generate_series(1, 200000) i;
CREATE TABLE audit.log (at timestamptz DEFAULT now(), msg text);
INSERT INTO audit.log (msg) SELECT 'Ereignis ' || i FROM generate_series(1, 50000) i;
GRANT USAGE ON SCHEMA app TO tag08_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO tag08_reader;
RESET ROLE;
CREATE EXTENSION pgcrypto;
EOF
psql -d tag08 -c '\dt (app|audit).*'Erwartet (gekürzt):
Schema | Name | Type | Owner
--------+-------+-------+-------------
app | notes | table | tag08_owner
app | tags | table | tag08_owner
audit | log | table | tag08_owner
(3 rows)
Diese Abfrage dient heute als Fingerabdruck – sie muss nach jedem Restore dasselbe liefern:
Q="SELECT (SELECT count(*) FROM app.notes) AS notes,
(SELECT count(*) FROM app.tags) AS tags, (SELECT count(*) FROM audit.log) AS log"
psql -d tag08 -c "$Q"Erwartet:
notes | tags | log
--------+--------+-------
200000 | 200000 | 50000
(1 row)
Fortschritt speichern? Kostenlos registrieren
Drill 2: Drei Formate
mkdir -p ~/tag08 && cd ~/tag08
pg_dump -d tag08 -f tag08.sql
pg_dump -d tag08 -Fc -f tag08.dump
pg_dump -d tag08 -Fd -j 4 -f tag08.dir
pg_dump -d tag08 -Fc -Z zstd -f tag08-zstd.dump
du -sh tag08.sql tag08.dump tag08.dir tag08-zstd.dump
ls tag08.dirErwartet:
14M tag08.sql
1.6M tag08.dump
1.6M tag08.dir
336K tag08-zstd.dump
3472.dat.gz
3473.dat.gz
3474.dat.gz
toc.dat
custom und directory komprimieren
standardmäßig mit gzip, -Z zstd ist schneller und kleiner.
-j (parallel) geht beim Sichern nur mit
directory: Jede Tabelle wird eine eigene Datei. Ein Blick
in die SQL-Datei:
grep -vE '^(--|$)' tag08.sql | head -4
grep -nE '^\\(un)?restrict' tag08.sqlErwartet (gekürzt):
\restrict piY0FMd7hZhUG8rgI1XZ9SBmmdcc46cGhausxDDS9tEir0l9SJjNneYsXyPyyZR
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
5:\restrict piY0FMd7hZhUG8rgI1XZ9SBmmdcc46cGhausxDDS9tEir0l9SJjNneYsXyPyyZR
450181:\unrestrict piY0FMd7hZhUG8rgI1XZ9SBmmdcc46cGhausxDDS9tEir0l9SJjNneYsXyPyyZR
\restrict gibt es seit den Minor-Releases vom August
2025 (18.0 hat es schon): Es sperrt psql-Meta-Befehle innerhalb des
Dumps, damit eine manipulierte Datenbank beim Einspielen keine
Shell-Befehle ausführen kann. Die SQL-Datei braucht psql
mit einem Client ab diesem Stand.
Tipp: Statistiken sichert
pg_dump18 nur mit--statistics. Ohne sie läuft nach jedem Restore zuerstANALYZE, sonst plant der Server blind.
Fortschritt speichern? Kostenlos registrieren
Drill 3: pg_restore – Inhalt und neue Datenbank
Ein Archiv hat ein Inhaltsverzeichnis (TOC):
pg_restore -l tag08.dump | grep -v '^;'Erwartet:
8; 2615 16621 SCHEMA - app tag08_owner
3481; 0 0 ACL - SCHEMA app tag08_owner
7; 2615 16622 SCHEMA - audit tag08_owner
2; 3079 16651 EXTENSION - pgcrypto
3482; 0 0 COMMENT - EXTENSION pgcrypto
223; 1259 16624 TABLE app notes tag08_owner
3483; 0 0 ACL app TABLE notes tag08_owner
222; 1259 16623 SEQUENCE app notes_id_seq tag08_owner
224; 1259 16635 TABLE app tags tag08_owner
3484; 0 0 ACL app TABLE tags tag08_owner
225; 1259 16645 TABLE audit log tag08_owner
3472; 0 16624 TABLE DATA app notes tag08_owner
3473; 0 16635 TABLE DATA app tags tag08_owner
3474; 0 16645 TABLE DATA audit log tag08_owner
3485; 0 0 SEQUENCE SET app notes_id_seq tag08_owner
3322; 2606 16634 CONSTRAINT app notes notes_pkey tag08_owner
3323; 2606 16640 FK CONSTRAINT app tags tags_note_id_fkey tag08_owner
Reihenfolge: erst Struktur, dann Daten, dann Indizes und Constraints
– so lädt COPY ohne Indexpflege. Wiederherstellen in eine
neue Datenbank, parallel. Die Zieldatenbank entsteht
aus template0 (Tag 2) – so bringt keine Änderung an
template1 Objekte mit, die mit dem Dump kollidieren:
createdb -T template0 tag08_restore
pg_restore -d tag08_restore -j 4 tag08.dump; echo rc=$?
psql -d tag08_restore -c "$Q"
psql -l | grep tag08Erwartet (gekürzt):
rc=0
notes | tags | log
--------+--------+-------
200000 | 200000 | 50000
(1 row)
tag08 | tag08_owner | UTF8 | libc | C.UTF-8 | C.UTF-8 | ...
tag08_restore | ubuntu | UTF8 | libc | C.UTF-8 | C.UTF-8 | ...
Die Daten stimmen, aber die neue Datenbank gehört
ubuntu: Eigenschaften der Datenbank selbst (Owner,
ALTER DATABASE … SET, Rechte darauf) landen nur mit
pg_dump -C bzw. pg_restore -C im Restore. Die
SQL-Datei spielt psql ein:
createdb -T template0 tag08_plain
psql -X -q -v ON_ERROR_STOP=1 -d tag08_plain -f tag08.sql >/dev/null; echo rc=$?
createdb -T template0 tag08_dir
pg_restore -d tag08_dir -j 4 tag08.dir; echo rc=$?Erwartet:
rc=0
rc=0
ON_ERROR_STOP=1 ist Pflicht: Ohne bricht
psql bei Fehlern nicht ab und meldet am Ende trotzdem
Erfolg.
Fortschritt speichern? Kostenlos registrieren
Drill 4: Selektiv wiederherstellen
Jemand hat in tag08_restore eine Tabelle gelöscht. Nur
diese eine zurückholen:
psql -d tag08_restore -c 'DROP TABLE audit.log'
pg_restore -d tag08_restore -n audit -t log tag08.dump; echo rc=$?
psql -d tag08_restore -Atc 'SELECT count(*) FROM audit.log'Erwartet:
DROP TABLE
rc=0
50000
-t allein trifft jede Tabelle log in jedem
Schema, deshalb -n dazu. Feiner geht es mit einer
bearbeiteten TOC-Liste (-L) – hier mit Absicht inklusive
der Schema-Zeile:
psql -d tag08_restore -c 'DROP TABLE audit.log'
pg_restore -l tag08.dump | grep -E 'SCHEMA - audit|audit log' > audit.list
cat audit.list
pg_restore -d tag08_restore -L audit.list tag08.dump; echo rc=$?
psql -d tag08_restore -Atc 'SELECT count(*) FROM audit.log'Erwartet (gekürzt):
7; 2615 16622 SCHEMA - audit tag08_owner
225; 1259 16645 TABLE audit log tag08_owner
3474; 0 16645 TABLE DATA audit log tag08_owner
pg_restore: error: could not execute query: ERROR: schema "audit" already exists
Command was: CREATE SCHEMA audit;
pg_restore: warning: errors ignored on restore: 1
rc=1
50000
pg_restore macht nach Fehlern standardmäßig weiter und
meldet am Ende rc=1. Die Tabelle ist da, das Schema gab es
schon. Für ernste Restores: -e
(--exit-on-error) bricht beim ersten Fehler ab,
-1 (--single-transaction) macht alles oder
nichts.
Fortschritt speichern? Kostenlos registrieren
Drill 5: Überschreiben mit –clean
Nach dem Backup wurde in tag08_restore gelöscht und
eingefügt. Ein Restore in die bestehende Datenbank scheitert an den
vorhandenen Objekten:
psql -d tag08_restore -c 'DELETE FROM app.tags' \
-c "INSERT INTO app.notes (text) VALUES ('nach dem Backup')"
pg_restore -d tag08_restore tag08.dump 2>&1 | head -2Erwartet:
DELETE 200000
INSERT 0 1
pg_restore: error: could not execute query: ERROR: schema "app" already exists
Command was: CREATE SCHEMA app;
--clean löscht jedes Objekt vor dem Neuanlegen,
--if-exists verhindert Fehler bei Objekten, die fehlen:
pg_restore -d tag08_restore --clean --if-exists -j 4 tag08.dump; echo rc=$?
psql -d tag08_restore -c "$Q"
psql -d tag08_restore -Atc "SELECT count(*) FROM app.notes
WHERE text = 'nach dem Backup'"Erwartet:
rc=0
notes | tags | log
--------+--------+-------
200000 | 200000 | 50000
(1 row)
0
Der Stand nach dem Backup ist weg – genau das, was ein Restore tut.
Objekte, die es im Backup nicht gibt, lässt --clean
stehen.
Fortschritt speichern? Kostenlos registrieren
Drill 6: Rollen sichern und die Owner-Falle
pg_dumpall --globals-only -f globals.sql
grep -E '^(CREATE|ALTER) ROLE (notes_app|tag08)' globals.sql | cut -c1-80Erwartet:
CREATE ROLE notes_app;
ALTER ROLE notes_app WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLI
CREATE ROLE tag08_owner;
ALTER ROLE tag08_owner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOR
CREATE ROLE tag08_reader;
ALTER ROLE tag08_reader WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NO
Die Datei enthält die SCRAM-Hashes der Passwörter – sie ist so
schutzwürdig wie die Passwörter selbst (--no-role-passwords
lässt sie weg). Jetzt der Umzug auf pg2, wo es diese
Rollen nicht gibt. Von deinem Rechner aus kopieren:
ssh [email protected] 'cat tag08/tag08.dump' \
| ssh [email protected] 'mkdir -p ~/tag08 && cat > ~/tag08/tag08.dump'
ssh [email protected] 'cat tag08/globals.sql' \
| ssh [email protected] 'cat > ~/tag08/globals.sql'Auf pg2 (dort gibt es nur die Rolle
postgres). pg_restore liest das Archiv von
stdin – so braucht postgres kein Leserecht auf dein
Home:
sudo -u postgres createdb tag08
sudo -u postgres pg_restore -d tag08 < ~/tag08/tag08.dump 2>&1 | grep '^pg_restore' \
| sort | uniq -c
sudo -u postgres psql -d tag08 -c '\dn'Erwartet (gekürzt):
5 pg_restore: error: could not execute query: ERROR: role "tag08_owner" ...
3 pg_restore: error: could not execute query: ERROR: role "tag08_reader" ...
1 pg_restore: warning: errors ignored on restore: 8
Name | Owner
--------+-------------------
app | postgres
audit | postgres
public | pg_database_owner
Die Daten sind da, aber alles gehört postgres und die
Leserechte fehlen. Zwei Wege: --no-owner --no-acl (bewusst
ohne Rechte) oder vorher die Rollen anlegen. Hier nur die beiden, die
wir brauchen:
sudo -u postgres dropdb tag08
grep -E 'ROLE tag08_' ~/tag08/globals.sql | sudo -u postgres psql -X
sudo -u postgres createdb -O tag08_owner tag08
sudo -u postgres pg_restore -d tag08 < ~/tag08/tag08.dump; echo rc=$?
sudo -u postgres psql -d tag08 -c '\dn' -c '\dp app.notes'Erwartet (gekürzt):
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
rc=0
Name | Owner
--------+-------------------
app | tag08_owner
audit | tag08_owner
public | pg_database_owner
Schema | Name | Type | Access privileges | ...
--------+-------+-------+----------------------------------+----
app | notes | table | tag08_owner=arwdDxtm/tag08_owner+| ...
| | | tag08_reader=r/tag08_owner | ...
Die Erweiterung pgcrypto kam mit, weil
postgresql-18 auf pg2 installiert ist. Fehlt
das Paket einer Erweiterung (etwa PostGIS) auf dem Ziel, scheitert der
Restore an CREATE EXTENSION – Pakete vorher
installieren.
Fortschritt speichern? Kostenlos registrieren
Drill 7: Backup übers Netz und Restore-Test
Auf pg2: pg_dump läuft auf dem
Backup-Server und holt die Daten über eine normale Verbindung – mit der
Lab-Rolle aus Tag 0 und geprüftem TLS aus Tag 3:
cd ~/tag08
pg_dump --version
export PGPASSWORD=lab-notes-123
C="host=172.16.0.182 dbname=notes user=notes_app sslmode=verify-full"
pg_dump "$C" -Fc -f notes.dump; echo rc=$?
pg_restore -l notes.dump | grep -v '^;'Erwartet:
pg_dump (PostgreSQL) 18.7 (Ubuntu 18.7-1.pgdg24.04+2)
rc=0
219; 1259 16388 TABLE public notes ubuntu
3428; 0 0 ACL public TABLE notes ubuntu
3421; 0 16388 TABLE DATA public notes ubuntu
3273; 2606 16399 CONSTRAINT public notes notes_pkey ubuntu
notes_app darf alles lesen, was in notes
liegt – das reicht hier. pg_dump muss mindestens die
Major-Version des Servers haben; ein älterer Client verweigert die
Arbeit. Ein Backup ohne Restore-Test ist eine Hoffnung. Also
wiederherstellen und den Fingerabdruck vergleichen:
sudo -u postgres createdb tag08_notes
sudo -u postgres pg_restore -d tag08_notes --no-owner --no-acl < notes.dump
echo rc=$?
sudo -u postgres psql -d tag08_notes -Atc 'SELECT count(*), max(created_at) FROM notes'
psql "$C" -Atc 'SELECT count(*), max(created_at) FROM notes'
unset PGPASSWORDErwartet:
rc=0
10004|2026-11-19 04:40:32.889006+00
10004|2026-11-19 04:40:32.889006+00
Rückgabewert 0, gleiche Zeilenzahl, gleicher jüngster Zeitstempel. In Produktion läuft genau das automatisch nach jedem Backup, und ein Alarm geht los, wenn es scheitert.
Fortschritt speichern? Kostenlos registrieren
Übung: Speed-Drills
- Sichere die Datenbank
tag08im Directory-Format mit 4 Jobs nach/tmp/t8. Zeitziel: 30 s - Zeige nur die Tabellendaten-Einträge im Archiv
tag08.dump. Zeitziel: 20 s - Sichere nur die Rollen des Clusters ohne Passwörter. Zeitziel: 20 s
- Spiele
tag08.sqlso ein, dass beim ersten Fehler abgebrochen wird. Zeitziel: 30 s
Lösung
pg_dump -d tag08 -Fd -j 4 -f /tmp/t8
pg_restore -l ~/tag08/tag08.dump | grep 'TABLE DATA'
pg_dumpall --roles-only --no-role-passwords -f /tmp/roles.sql
createdb tag08_speed
psql -X -v ON_ERROR_STOP=1 -d tag08_speed -f ~/tag08/tag08.sql >/dev/null; echo rc=$?Erwartet (gekürzt):
3472; 0 16624 TABLE DATA app notes tag08_owner
3473; 0 16635 TABLE DATA app tags tag08_owner
3474; 0 16645 TABLE DATA audit log tag08_owner
rc=0
Fortschritt speichern? Kostenlos registrieren
Kontrollpunkt
pg_restore -l ~/tag08/tag08.dump | grep -c 'TABLE DATA'Erwartet: 3
psql -d tag08_restore -c "$Q"Erwartet: 200000 | 200000 | 50000
grep -c 'ROLE tag08_' ~/tag08/globals.sqlErwartet: 4 (je ein CREATE
und ein ALTER für beide Rollen).
Auf pg2:
sudo -u postgres psql -d tag08_notes -Atc 'SELECT count(*) FROM notes'Erwartet: dieselbe Zahl wie
SELECT count(*) FROM notes auf pg1 (hier
10004).
Challenge
Stelle aus tag08.dump nur die Struktur
des Schemas app (ohne Daten, ohne audit) in
einer leeren Datenbank tag08_schema wieder her.
Hinweis: pg_restore -n app legt zwar
Tabellen an, aber nicht das Schema selbst – probiere es aus und lies die
Fehlermeldung.
Lösung
createdb tag08_schema
pg_restore -d tag08_schema -s -n app tag08.dump 2>&1 | grep -m1 ERROR
pg_restore -l tag08.dump | grep -E ' (SCHEMA )?app ' > app.list
pg_restore -d tag08_schema -s -L app.list tag08.dump; echo rc=$?
psql -d tag08_schema -Atc '\dt app.*' -c 'SELECT count(*) FROM app.notes'Erwartet:
pg_restore: error: could not execute query: ERROR: schema "app" does not exist
rc=0
app|notes|table|tag08_owner
app|tags|table|tag08_owner
0
Anders als pg_dump -n nimmt pg_restore -n
den SCHEMA-Eintrag nicht mit. Die TOC-Liste enthält ihn,
und -s filtert die TABLE DATA-Zeilen trotzdem
heraus.
Fortschritt speichern? Kostenlos registrieren
Aufräumen
Auf pg1:
dropdb --if-exists tag08
dropdb --if-exists tag08_restore
dropdb --if-exists tag08_plain
dropdb --if-exists tag08_dir
dropdb --if-exists tag08_schema
dropdb --if-exists tag08_speed
psql -c 'DROP ROLE tag08_owner' -c 'DROP ROLE tag08_reader'
rm -rf ~/tag08 /tmp/t8 /tmp/roles.sqlAuf pg2:
sudo -u postgres dropdb tag08
sudo -u postgres dropdb tag08_notes
sudo -u postgres psql -c 'DROP ROLE tag08_owner' -c 'DROP ROLE tag08_reader'
rm -rf ~/tag08Interview-Notiz
- „pg_dump oder pg_basebackup?“ –
pg_dumpfür einzelne Datenbanken, Migrationen, Versionswechsel und selektive Restores;pg_basebackupplus WAL für den ganzen Cluster mit Point-in-Time-Recovery und schnellem Restore großer Datenmengen. - „Warum fehlen nach dem Restore Rechte?“ – Rollen sind global und
nicht im Dump; vorher
pg_dumpall --globals-onlyeinspielen oder bewusst--no-owner --no-acl. - „Ist ein pg_dump konsistent?“ – Ja, er läuft in einer Transaktion
mit einem Snapshot (
REPEATABLE READ); parallele Jobs teilen sich diesen Snapshot.
Enterprise-Notiz
- Dumps verschlüsselt und außerhalb des Servers ablegen
(Objektspeicher mit Aufbewahrungsregeln und Object Lock),
globals.sqlgehört dazu. - Eigene Backup-Rolle mit
pg_read_all_datastatt Superuser oder Anwendungsrolle. - Automatischer Restore-Test in eine Wegwerf-Instanz mit Prüfabfragen und Alarm – ohne ihn ist ein Backup nicht bewiesen.
- Große Datenbanken:
-Fd -j Nmit zstd; ab einigen Hundert GB eher physische Backups (Tag 9), weil der Restore sonst Stunden dauert. pg_dumpsperrt Tabellen mitACCESS SHARE: EinALTER TABLEwartet dann bis zum Ende des Dumps – Wartungsfenster undlock_timeoutbeachten.