Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 8: Logische Backups

Ziel: Du sicherst Datenbanken mit pg_dump in 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_dump sichert 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.dir

Erwartet:

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.sql

Erwartet (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_dump 18 nur mit --statistics. Ohne sie läuft nach jedem Restore zuerst ANALYZE, 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 tag08

Erwartet (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 -2

Erwartet:

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-80

Erwartet:

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 PGPASSWORD

Erwartet:

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

  1. Sichere die Datenbank tag08 im Directory-Format mit 4 Jobs nach /tmp/t8. Zeitziel: 30 s
  2. Zeige nur die Tabellendaten-Einträge im Archiv tag08.dump. Zeitziel: 20 s
  3. Sichere nur die Rollen des Clusters ohne Passwörter. Zeitziel: 20 s
  4. Spiele tag08.sql so 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.sql

Erwartet: 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.sql

Auf 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 ~/tag08

Interview-Notiz

  • „pg_dump oder pg_basebackup?“ – pg_dump für einzelne Datenbanken, Migrationen, Versionswechsel und selektive Restores; pg_basebackup plus 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-only einspielen 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.sql gehört dazu.
  • Eigene Backup-Rolle mit pg_read_all_data statt 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 N mit zstd; ab einigen Hundert GB eher physische Backups (Tag 9), weil der Restore sonst Stunden dauert.
  • pg_dump sperrt Tabellen mit ACCESS SHARE: Ein ALTER TABLE wartet dann bis zum Ende des Dumps – Wartungsfenster und lock_timeout beachten.