Aufgabe 14 - Point-in-Time Recovery

Aufgabe 14 - Point-in-Time Recovery

Worum geht es?

In dieser Übung wird ein realistischer Notfall geübt: Ein versehentlich ausgeführtes Skript hat die orders-Tabelle gelöscht, und die Datenbank soll per Point-in-Time Recovery (PITR) auf den Zustand exakt vor dem Fehler zurückgeholt werden (siehe Kapitel 6 - Physische Organisation). Diese Übung ist durchgehend anspruchsvoll; Teil D ist der eigentliche Recovery-Kern.

Was Sie dafür brauchen

• Kapitel 6 - Physische Organisation, Abschnitte WAL und PITR.
• Das PostgreSQL-Docker-Setup mit Northwind und das PITR-Tutorial.

Welche Kompetenzen Sie erwerben und zeigen

• Sie aktivieren und verifizieren WAL-Archivierung.
• Sie erstellen ein konsistentes physisches Base Backup.
• Sie dokumentieren einen exakten Recovery-Zeitpunkt.
• Sie führen ein Point-in-Time Recovery durch und verifizieren es kritisch.

Pädagogische Einordnung

• Reproduktion: die Umgebung vorbereiten und WAL-Archivierung aktivieren (Teil A).
• Reorganisation und Transfer: Base Backup erstellen und einen Fehler simulieren (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: das Recovery durchführen und beurteilen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Teil A - Umgebung und WAL-Archivierung

1. Eine sichere Test-Kopie der Northwind-Datenbank anlegen (z. B. mit createdb --template) und mit SELECT COUNT(*) FROM orders; prüfen. Im docker-compose.yml das Datenverzeichnis im Container feststellen (Hinweis: liegt unter /var/lib/postgresql/18/docker).

2. Die aktuelle Konfiguration prüfen (SHOW wal_level; SHOW archive_mode; SHOW archive_command;) und den Unterschied zwischen archive_mode on/off sowie wal_level minimal/replica erklären.

3. WAL-Archivierung aktivieren: ein persistentes Archivverzeichnis anlegen und

   ALTER SYSTEM SET wal_level = 'replica';
   ALTER SYSTEM SET archive_mode = on;
   ALTER SYSTEM SET archive_command = 'test ! -f /var/lib/postgresql/18/archive/%f && cp %p /var/lib/postgresql/18/archive/%f';

   setzen, den Container neu starten und mit SELECT pg_switch_wal(); sowie einem ls im Archiv verifizieren, dass eine Datei erscheint. Erklären, welche Probleme entstehen, wenn archive_command fehlschlägt.

Teil B - Base Backup

1. Den Ausgangszustand dokumentieren (SELECT COUNT(*) FROM orders;, SELECT MAX(order_date) FROM orders;) und die Werte notieren.

2. Ein physisches Base Backup erstellen und die Parameter verstehen:

   docker compose exec postgres pg_basebackup -U pgadmin \
     -D /var/lib/postgresql/backups/base_monday_13_00 -Ft -z -P -X stream -c fast

   Prüfen, dass base.tar.gz, pg_wal.tar.gz und ein backup_manifest entstanden sind. Begründen, warum ein Base Backup konsistent sein muss und warum pg_dump für PITR ungeeignet ist.

Teil C - Fehler simulieren

1. Den exakten UTC-Zeitpunkt vor dem Fehler notieren (SELECT clock_timestamp() AT TIME ZONE 'UTC';).
2. Eine Test-Order einfügen (damit ein klar identifizierbarer Datensatz kurz vor dem Fehler existiert) und erneut den Zeitpunkt notieren.
3. Den Fehler auslösen: DROP TABLE orders CASCADE; (wegen des Fremdschlüssels von order_details), danach den Schaden prüfen (SELECT COUNT(*) FROM orders; endet mit Fehler). Als Recovery-Target einen exakt dokumentierten UTC-Zeitpunkt nach dem Test-Insert und vor dem DROP festhalten. Zusätzlich: mindestens drei Maßnahmen nennen, die diesen Fehler verhindert hätten (nicht nur technische).

Teil D - Expertenteil: Recovery durchführen und verifizieren

1. Recovery vorbereiten: Das Base Backup in ein separates Recovery-Verzeichnis entpacken, Eigentümer/Rechte auf postgres und 700 setzen und eine recovery.signal-Datei anlegen (ohne diese Datei startet kein Recovery).
2. Recovery konfigurieren: In postgresql.auto.conf restore_command (Kopie aus dem Archiv), recovery_target_time (der dokumentierte UTC-Zeitpunkt) und recovery_target_action = 'promote' setzen.
3. Recovery starten: Einen zweiten PostgreSQL-Container derselben Major-Version auf einem anderen Port (5433) mit dem Recovery-Datenverzeichnis starten, ohne die Originalinstanz zu stoppen. Die Logs verfolgen (starting point-in-time recovery ..., recovery stopping before ..., ready to accept connections). Erklären, was intern passiert (Replay der WAL-Records) und warum das Target nicht nach dem Fehler liegen darf.
4. Verifizieren: Auf Port 5433 prüfen, dass orders wieder existiert und die Test-Order aus Teil C enthält, aber nichts, was nach dem Fehler passierte. Mit dem dokumentierten Ausgangszustand aus Teil B vergleichen und einen etwaigen Datenverlust beziffern. Begründen, warum MAX(order_date) kein guter Beweis ist (Geschäftsdatum, nicht Commit-Zeitpunkt).
5. Reflexion: Welche Daten gingen verloren und ist das akzeptabel? Wie nah könnte man an den Fehlerzeitpunkt herangehen, und wo ist die Grenze? Warum ist PITR besser als nur tägliche Backups?

Wissenscheck

1. Warum reicht ein einfaches Backup nicht für die Wiederherstellung auf einen beliebigen Zeitpunkt?
2. Wozu dient archive_command, und was passiert, wenn es fehlschlägt?
3. Warum darf recovery_target_time nicht nach dem Fehler liegen?
4. Welche Rolle spielt die Datei recovery.signal?
5. Woran erkennt man ein erfolgreiches Recovery über die reine Existenz der Tabelle hinaus?

Abgabe

• Ein Word- oder PDF-Dokument mit Screenshots der SQL-Ausgaben, den relevanten Konfigurationsausschnitten, dem dokumentierten Recovery-Target und den Begründungen der Entscheidungen.

---

HTL Villach, 2025-2026,
https://www.htl-villach.at