Aufgabe 13 - Physische Organisation, Backup und Restore

Aufgabe 13 - Physische Organisation und Datensicherung

Worum geht es?

In dieser Übung wird untersucht, wie Daten in PostgreSQL physisch landen, wie MVCC und VACUUM zusammenhängen und wie man vor Datenverlust schützt (siehe Kapitel 6 - Physische Organisation). Im Expertenteil wird Table Bloat an echten Werten gemessen und Point-in-Time Recovery konzeptionell skizziert.

Was Sie dafür brauchen

• Kapitel 6 - Physische Organisation.
• Ein PostgreSQL-Server mit der Northwind-Datenbank; pg_dump, pg_basebackup.

Welche Kompetenzen Sie erwerben und zeigen

• Sie erklären Pages, ctid und das PGDATA-Verzeichnis.
• Sie verstehen MVCC, Table Bloat und die Rolle von VACUUM.
• Sie vergleichen logische und physische Backups.
• Sie messen Bloat und skizzieren einen PITR-Ablauf.

Pädagogische Einordnung

• Reproduktion: die physische Struktur untersuchen (Teil A).
• Reorganisation und Transfer: MVCC und Backups nachvollziehen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: Bloat messen und PITR skizzieren (Teil D).

Arbeitsaufträge

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

Teil A - Anatomie einer Tabelle

1. Herausfinden, in welchem Verzeichnis die PostgreSQL-Daten (PGDATA) liegen.
2. Die Standard-Page-Size bestimmen und angeben, ob und wie sie konfigurierbar ist.
3. SELECT ctid, customer_id, company_name FROM customers LIMIT 5; ausführen. Erklären, was ctid (z. B. (0,1)) über den physischen Ort einer Zeile aussagt und warum PostgreSQL feste 8-KB-Blöcke statt zeilenweiser Dateien nutzt.

Teil B - MVCC

1. Den Namen eines Kunden ändern und den ctid vorher und nachher vergleichen.
2. Erklären, was mit der alten Zeilenversion nach einem Update geschieht, was Table Bloat ist, wie VACUUM damit zusammenhängt und welcher Hintergrundprozess tote Tupel automatisch entfernt.

Teil C - Logisches vs. physisches Backup

1. Ein logisches Backup von northwind_permissions mit pg_dump erstellen und in der .sql-Datei prüfen, welche Informationen (Struktur, Daten, Indizes) enthalten sind.
2. Ein physisches Backup mit pg_basebackup erstellen. Beide Methoden nach Anwendungsfall, Vor- und Nachteilen vergleichen. Begründen: Warum ist ein cp/zip des laufenden Datenverzeichnisses gefährlich? Warum ist ein logisches Backup beim Wechsel auf eine neuere PostgreSQL-Version im Vorteil? Warum bevorzugt man bei mehreren Terabyte physische Backups?

Teil D - Expertenteil: Bloat messen und PITR skizzieren

1. Bloat messen: Eine Testtabelle mit vielen Zeilen anlegen (z. B. generate_series), ihre Größe mit pg_total_relation_size bzw. \dt+ notieren, dann alle Zeilen per UPDATE einmal ändern und die Größe erneut messen. Erklären, warum die Tabelle gewachsen ist, obwohl sich die Zeilenzahl nicht geändert hat.
2. VACUUM anwenden: VACUUM ausführen und die Größe erneut prüfen; dann VACUUM FULL ausführen und die Größe ein drittes Mal messen. Den Unterschied erklären: VACUUM gibt Platz nur intern frei, VACUUM FULL gibt ihn an das Betriebssystem zurück (mit exklusiver Sperre).
3. PITR skizzieren: Angenommen, ein Mitarbeiter löscht um 14:05 Uhr alle Bestelldaten, das letzte Voll-Backup ist von Mitternacht. Skizzieren Sie den Ablauf eines Point-in-Time Recovery und nennen Sie die zwei zwingend nötigen Komponenten (Base Backup und archivierte WAL-Segmente). Erklären Sie in drei bis vier Sätzen, warum das WAL das Zurückspielen auf eine exakte Sekunde erlaubt und worin sich ein einfacher Restore von einem Forward-Replay der Logs unterscheidet.

Wissenscheck

1. Was sagt der ctid über eine Zeile aus?
2. Warum erzeugt ein UPDATE in PostgreSQL eine neue Zeilenversion?
3. Worin unterscheiden sich VACUUM und VACUUM FULL?
4. Warum ist ein logisches Backup portabler als ein physisches?
5. Welche zwei Komponenten braucht ein Point-in-Time Recovery?

Abgabe

• Ein Dokument mit den Antworten, den Screenshots der SQL-Ausgaben (ctid, Größenmessungen vor/nach VACUUM) und der PITR-Skizze aus Teil D.

---

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