Aufgabe 14 - Point-in-Time Recovery

Exercise 14 - Point-in-Time Recovery

Es ist Montag, 13:00 Uhr. Ihr Team führt routinemäßige Datenbankwartungen durch. Sie erstellen ein reguläres Base Backup der Northwind-Datenbank.

Am Dienstag um 15:30 Uhr meldet der Vertrieb einen kritischen Fehler: Ein Datenbankskript aus einem Entwickler-Repository wurde versehentlich in der Produktionsumgebung ausgeführt und hat die orders-Tabelle gelöscht.

Die Geschäftsleitung verlangt:

“Die Tabelle muss auf den Zustand von 15:29 Uhr wiederhergestellt werden. Alles, was danach passiert ist, muss wieder weg. Wir können den Zeitverlust durch die Neueingabe dieser 132 Orders akzeptieren, aber älter darf es nicht sein.”

Die Herausforderung: Können Sie das erreichen?

---

Ihr Ziel

Die Datenbank soll mittels Point-in-Time Recovery (PITR) auf einen exakten Zeitpunkt zurückgesetzt werden. Das ist nur möglich, wenn:

1. Ein Base Backup vor dem Fehler existiert
2. Alle WAL-Dateien (Write Ahead Logs) seit dem Backup archiviert wurden
3. Sie wissen, wann genau der Fehler auftrat
4. Sie die Recovery-Konfiguration richtig einrichten

Nutzen Sie das PostgreSQL PITR Tutorial als Referenz.

---

Auftrag 1 - Vorbereitung und Umgebungs-Analyse

1.1 Schaffen Sie eine sichere Testumgebung

Aufgaben

1. Klonen Sie die Northwind-Datenbank für experimentelle Zwecke.

   • Nutzen Sie createdb mit einer geeigneten Option – recherchieren Sie, wie diese funktioniert
   • Geben Sie der Kopie einen aussagekräftigen Namen (z.B. northwind_pitr_test)
   • Dokumentieren Sie den genauen Befehl in Ihrer Abgabe

2. Überprüfen Sie die geklonte Datenbank:

   SELECT COUNT(*) FROM orders;
   SELECT table_name FROM information_schema.tables
   WHERE table_schema='public';

   • Wie viele Orders sind vorhanden?
   • Welche Tabellen sind kopiert worden?

3. Verständnisfrage beantworten: Lesen Sie die Docker-Dokumentation Ihres Setup (schauen Sie sich docker-compose.yml an):

   • Welcher Benutzer ist der PostgreSQL-Administrator? Welcher Benutzer wird für PostgreSQL verwendet?
   • Wo befindet sich das Datenverzeichnis der Datenbank innerhalb des Containers?
   • Welches Volume wird dafür verwendet? Hinweis: Das data_directory liegt nicht unter /var/lib/postgresql/data, sondern unter /var/lib/postgresql/18/docker.

Reflexion

• Warum ist eine Test-Kopie notwendig? Nennen Sie Szenarien, in denen das Original beschädigt werden könnte.
• Wäre eine Kopie via pg_dump besser oder schlechter als eine geklonte Template-Kopie? Begründen Sie ihre Antwort.

---

Auftrag 2 - WAL-Archiving aktivieren und testen

2.1 Recherchieren Sie die notwendige Theorie aus dem Tutorial

Beantworten Sie schriftlich:

1. Wie funktioniert WAL (Write Ahead Log)?

   • Was wird protokolliert und warum vor jeder Änderung?
   • Warum reicht ein einfaches Backup nicht aus, um auf beliebige Zeitpunkte zurückzugehen?

2. Was ist der Unterschied zwischen:

   • archive_mode = on vs archive_mode = off
   • wal_level = 'minimal' vs wal_level = 'replica'
   • Warum ist archive_command so kritisch?

2.2 WAL-Archiving in Ihrem Docker-Setup aktivieren

Aufgaben

1. Prüfen Sie die aktuelle Konfiguration:

   docker compose exec postgres psql -U pgadmin -d postgres -c "SHOW wal_level;"
   docker compose exec postgres psql -U pgadmin -d postgres -c "SHOW archive_mode;"
   docker compose exec postgres psql -U pgadmin -d postgres -c "SHOW archive_command;"
   docker compose exec postgres psql -U pgadmin -d postgres -c "SHOW data_directory;"

   • Was ist der aktuelle Status?
   • Wenn alles off oder minimal ist: Sie müssen es aktivieren!

   Hinweis: Prüfen Sie zusätzlich data_directory, bevor Sie Pfade für Archiv und Recovery festlegen, damit später die richtigen archive_command- und restore_command-Pfade verwendet werden.

2. Falls nötig: WAL-Archiving aktivieren

   Hinweis: Das WAL-Archiv muss sich in einem persistenten Verzeichnis befinden (z. B. innerhalb des gemounteten Datenverzeichnisses).

   a) Erstellen Sie ein WAL-Archive-Verzeichnis im Container:

   docker compose exec postgres mkdir -p /var/lib/postgresql/18/archive
   docker compose exec -u root postgres chown postgres:postgres /var/lib/postgresql/18/archive
   docker compose exec -u root postgres chmod 700 /var/lib/postgresql/18/archive

   b) Ändern Sie die PostgreSQL-Konfiguration (im Container oder via ALTER SYSTEM):

   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';

   Hinweise:

   • Das archive_command muss die WAL-Dateien aus dem Datenverzeichnis in das Archiv kopieren. Platzhalter werden dabei verwendet.
   • Verwenden Sie das Command test ! -f ... && cp ... , da es robuster ist als nur ein cp ....
   • Achten Sie darauf, dass das Archiv-Verzheichnis dem Benutzer postgres gehört.

   c) Restart PostgreSQL (Container):

   docker compose restart postgres

3. Verifizieren Sie, dass es funktioniert:

   SELECT pg_switch_wal();  -- Erzeugt einen WAL-Switch

   Überprüfen Sie, ob die Datei im Archiv auftaucht:

   docker compose exec postgres ls -la /var/lib/postgresql/18/archive/

Hinweise:

• Die PostgreSQL-Konfiguration befindet sich im Datenverzeichnis
• Änderungen erfordern in der Regel einen Neustart
• Das Archiv muss in einem persistenten Verzeichnis gespeichert werden
• Wenn nach pg_switch_wal() keine Datei im Archiv erscheint, prüfen Sie sofort die Container-Logs. Ein sichtbarer WAL-Switch allein beweist noch nicht, dass archive_command erfolgreich war.

Kritische Reflexion

• Welche Probleme könnten entstehen, wenn archive_command fehlschlägt? (Tipp: Überprüfen Sie die Logs)

---

Auftrag 3 - Base Backup erstellen und dokumentieren

3.1 Aufgaben

1. Dokumentieren Sie den aktuellen Datenbankzustand (vor dem Backup):

   SELECT COUNT(*) as order_count FROM orders;
   SELECT MAX(order_date) as latest_order FROM orders;

   Notieren Sie diese Werte – Sie brauchen sie später!

   Hinweis: Im bereitgestellten Northwind-Schema werden snake_case-Spaltennamen verwendet, also z. B. order_date statt OrderDate.

2. Wählen Sie ein geeignetes Zielverzeichnis

   • Muss persistent sein
   • Muss im Container erreichbar sein

3. Erstellen Sie ein physisches Base Backup:

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

   • Verstehen Sie jeden Parameter: Was bewirken -Ft, -z, -X stream?
   • Wo landet das Backup? (im Container oder auf dem Host-System?)
   • Größe des Backups? Führen Sie aus:

     docker compose exec postgres du -sh /var/lib/postgresql/backups/base_monday_13_00/

4. Überprüfen Sie, welche Dateien erstellt wurden:

   docker compose exec postgres tar -tzf /var/lib/postgresql/backups/base_monday_13_00/base.tar.gz | head -20

Wichtige Hinweise:

• Kontrollieren Sie nach dem Backup nicht nur base.tar.gz, sondern auch, ob zusätzlich pg_wal.tar.gz und ein backup_manifest erzeugt wurden. Diese Dateien sind ein guter Plausibilitätscheck für ein physisches Base Backup.
• Das Backup sollte vollständig und konsistent sein
• Achten Sie auf Benutzerrechte und Zugriff

3.2 Reflexion

• Warum ist es wichtig, dass das Backup “konsistent” ist? Was könnte schieflaufen, wenn während des Backups noch Schreibvorgänge stattfinden?
• Könnten Sie dieses Backup mit pg_dump alternativ erstellen? Warum ist oder ist das nicht für PITR geeignet?

---

Auftrag 4 - Fehler-Simulation

4.1 Aufgaben

1. Notieren Sie den genauen Zeitpunkt vor dem verursachten “Fehler”:

   SELECT clock_timestamp() AT TIME ZONE 'UTC' as before_change_utc;

2. Führen Sie eine generische Änderung durch (um WAL-Archivierung zu initialisieren):

   INSERT INTO orders (
     order_id,
     customer_id,
     employee_id,
     order_date,
     required_date,
     shipped_date,
     ship_via,
     freight,
     ship_name,
     ship_address,
     ship_city,
     ship_region,
     ship_postal_code,
     ship_country
   )
   VALUES (
     (SELECT MAX(order_id) + 1 FROM orders),
     'VINET',
     1,
     CURRENT_DATE,
     CURRENT_DATE + 30,
     NULL,
     1,
     0,
     'PITR Test Order',
     'Test Address',
     'Test City',
     NULL,
     '0000',
     'Austria'
   );
   SELECT clock_timestamp() AT TIME ZONE 'UTC' as insertion_time_utc;

3. Jetzt passiert der Fehler (DROP TABLE):

   DROP TABLE orders CASCADE;

   Hinweis: Im Northwind-Schema hat order_details einen Fremdschlüssel auf orders. Deshalb funktioniert DROP TABLE orders; dort nicht ohne CASCADE.

4. Überprüfen Sie den Schaden:

   SELECT COUNT(*) FROM orders;  -- Sollte mit ERROR enden!

   Hinweis: Notieren Sie als Recovery-Target nicht nur “kurz vor dem Fehler”, sondern einen exakt dokumentierten UTC-Zeitpunkt nach dem Test-Insert und vor dem DROP TABLE. Nur dann können Sie hinterher belastbar beurteilen, ob die Wiederherstellung korrekt war.

4.2 Reflexion

• Wie hätten Sie diesen Fehler verhindern können? Nennen Sie mindestens 3 Maßnahmen (nicht nur technisch!).

---

Auftrag 5 - Wiederherstellung durchführen

5.1 Recovery-Strategie planen

Bevor Sie hier Schritte ausführen, dokumentieren Sie schriftlich:

1. Was ist Ihr Recovery-Target? (die exakte Zeit aus Auftrag 4)
2. Welche WAL-Dateien brauchen Sie? (vom Backup bis zum Target)
3. Wo liegen diese im Filesystem?
4. Was werden Sie mit dem aktuellen Data-Verzeichnis machen?

5.2 Recovery durchführen

Wichtige Hinweise:

• Das ist ein Docker-Setup! Sie werden NICHT das Host-Filesystem direkt manipulieren!

• Nutzen Sie Ihre Erkenntnisse aus Auftrag 1 zum Datenverzeichnis

Schritt 1: Backup in Recovery-Verzeichnis entpacken

# Im Container-Shell ein neues Verzeichnis vorbereiten
docker compose exec postgres mkdir -p /var/lib/postgresql/recovery_test
docker compose exec postgres tar -xzf /var/lib/postgresql/backups/base_monday_13_00/base.tar.gz \
  -C /var/lib/postgresql/recovery_test
# Eigentümer und Rechte setzen
docker compose exec -u root postgres chown -R postgres:postgres /var/lib/postgresql/recovery_test
docker compose exec -u root postgres chmod 700 /var/lib/postgresql/recovery_test

Hinweis: Für ein separates Recovery-Datenverzeichnis müssen Eigentümer und Rechte stimmen. PostgreSQL startet nicht, wenn das Verzeichnis nicht postgres gehört oder zu offene Rechte hat.

Schritt 2: Recovery-Signaldatei erstellen

docker compose exec postgres touch /var/lib/postgresql/recovery_test/recovery.signal

Wichtiger Hinweis: Ohne diese Datei startet PostgreSQL KEIN Recovery, selbst wenn alle anderen Einstellungen korrekt sind.

Schritt 3: Recovery-Konfiguration setzen

Analysieren Sie die folgenden Zeilen und fügen Sie diese ggf. angepasst in /var/lib/postgresql/recovery_test/postgresql.auto.conf ein:

Hinweis: Ersetzen Sie die Zeit durch Ihren exakt dokumentierten UTC-Zeitpunkt aus Auftrag 4.

docker compose exec postgres bash -c "printf '%s\n' \
\"restore_command = 'cp /var/lib/postgresql/18/archive/%f %p'\" \
\"recovery_target_time = '2026-04-21 19:36:58.228586+00'\" \
\"recovery_target_action = 'promote'\" \
>> /var/lib/postgresql/recovery_test/postgresql.auto.conf"

Hinweis: restore_command muss auf Ihr echtes Archivverzeichnis zeigen, sonst führt das zu einem scheinbar korrekt konfigurierten, aber nicht startfähigen Recovery.

Schritt 4: PostgreSQL im Recovery-Mode starten

Starten Sie nun einen zweiten PostgreSQL-Container ohne den existierenden zu stoppen!

Editieren Sie docker-compose.yml und fügen Sie einen neuen Service hinzu:

postgres_recovery:
   image: postgres:18  # Gleiche Major-Version wie original!
   environment:
      POSTGRES_USER: pgadmin
      POSTGRES_PASSWORD: postgres-root-password
   ports:
      - "5433:5432"  # Anderer Port
   volumes:
      - ./recovery_test_volume:/var/lib/postgresql/recovery_test
      - postgres-data:/var/lib/postgresql/source_data:ro
   command: >
      postgres
      -D /var/lib/postgresql/recovery_test
      -c restore_command='cp /var/lib/postgresql/source_data/18/archive/%f %p'

Starten Sie: docker compose up postgres_recovery

Wichtige Hinweise:

• Verwenden Sie für den Recovery-Container dieselbe PostgreSQL-Major-Version wie für die Quellinstanz.
• Wenn Sie statt eines Host-Verzeichnisses ein Docker-Volume verwenden, müssen Sie Ownership und Rechte auch dort prüfen. Das Problem verschwindet nicht nur deshalb, weil kein lokaler Ordner sichtbar ist.

Schritt 5: Überwachen Sie den Recovery-Prozess

docker compose logs -f postgres_recovery

Sie sollten Log-Meldungen sehen wie:

LOG: starting point-in-time recovery to ...
LOG: restored log file "..." from archive
LOG: redo starts at ...
LOG: recovery stopping before commit of ...
LOG: database system is ready to accept connections

Wichtige Hinweise:

• Wenn Meldungen wie recovery ended before configured recovery target was reached erscheinen, fehlt Ihnen mindestens ein benötigtes WAL-Segment bis zum Zielzeitpunkt. Dann müssen Sie zuerst die Archivierung auf der Quellinstanz vervollständigen.
• Nach Schreibaktivität kurz vor dem Fehler kann ein zusätzlicher SELECT pg_switch_wal(); nötig sein, damit das relevante WAL-Segment tatsächlich im Archiv landet und für die Recovery verfügbar ist.

Kritische Reflexion

• Was passiert intern während des Recovery-Prozesses? (Tipp: Replay of WAL records)
• Warum darf die Recovery-Target-Time nicht NACH dem Fehler liegen?
• Woran erkennen Sie, dass ein Recovery erfolgreich war? (Nicht nur: “Tabelle existiert!”)

---

Auftrag 6 - Verifizierung und Vergleich

Überprüfen Sie, dass der Recovery funktioniert hat, und analysieren mögliche Datenverluste.

6.1 Aufgaben

1. Verbinden Sie sich mit der wiederhergestellten Datenbank (auf Port 5433):

   docker compose exec postgres_recovery psql -U pgadmin -d northwind -c \
     "SELECT COUNT(*) as order_count FROM orders;"

   • Existiert die Tabelle?
   • Wie viele Orders sind vorhanden?

2. Vergleichen Sie mit dem Original-Backup-Status (aus Auftrag 3):

   # Führen Sie die gleichen Queries wie aus 3.1 aus und vergleichen Sie die Zahlen

3. Hat ein Datenverlust stattgefunden?

   SELECT ORDER_COUNT_BACKUP - ORDER_COUNT_RECOVERED as lost_orders;

4. War irgendetwas “zu viel” wiederhergestellt? (z.B. Daten NACH dem Fehler)

   SELECT order_id, customer_id, order_date
   FROM orders
   WHERE order_id = <ID_IHRER_TEST_ORDER>;

Wichtige Hinweise:

• MAX(order_date) beweist keinen korrekten PITR-Zeitpunkt, weil order_date ein Geschäftsdatum ist und nicht der Commit-Zeitpunkt. Die gezielte Kontrolle der unmittelbar vor dem Fehler eingefügten Test-Order ist aussagekräftiger.
• Vergleichen Sie die Recovery-Instanz immer auch mit der beschädigten Quellinstanz. Wenn orders in der Quelle noch fehlt, in der Recovery aber wieder vorhanden ist, sehen Sie den Unterschied zwischen Fehlerzustand und Wiederherstellung direkt.

6.2 Kritische Reflexion (schriftlich beantworten)

• Welche Daten gingen verloren und warum? Ist das akzeptabel?
• Hätten Sie näher an den Fehler-Zeitpunkt herangehen können? Was wäre die Grenze?
• Welche Business-Entscheidung müsste jetzt getroffen werden? (z.B. manuelle Nachbearbeitung?)
• Vergleich mit Regular Backup:
  • Warum ist PITR besser als nur tägliche Backups?

---

Abgabe

Erstellen Sie ein Word- oder PDF-Dokument mit:

• Screenshots der SQL-Abfragen
• ggf. Konfigurationsausschnitte
• kurze Begründungen Ihrer Entscheidungen