Aufgabe 13 - Physische Organisation, Backup und Restore

Exercise 13 - Physische Organisation und Datensicherung

Szenario: Vom SQL-Befehl zur Festplatte

Nachdem Sie die Zugriffskontrolle implementiert haben, überträgt Ihnen die IT-Leitung eine neue Verantwortung: die physische Integrität und Performance der Northwind-Datenbank.

Sie müssen verstehen, wie Daten auf der Festplatte landen, wie Indizes den Zugriff beschleunigen und - am wichtigsten - wie Sie das Unternehmen vor totalem Datenverlust schützen.

Beantworten Sie die folgenden Fragen und führen Sie die Aufgaben durch, um ein umfassendes Verständnis der physischen Organisation von PostgreSQL sowie der Backup- und Restore-Strategien zu entwickeln.

Halten Sie Ihre Antworten und Ergebnisse in einem Dokument fest, das Sie am Ende der Übung abgeben.

---

Auftrag 1 - Die Anatomie einer Tabelle

Daten in PostgreSQL werden nicht einfach als Text gespeichert, sondern in festen Einheiten organisiert.

Aufgaben

1. Finden Sie heraus, in welchem Verzeichnis auf Ihrem System die PostgreSQL-Daten (das PGDATA-Verzeichnis) liegen.
2. Identifizieren Sie die Standard-Page Size (Blockgröße) von PostgreSQL. Kann man diese konfigurieren? Wenn ja, wie?
3. Jede Zeile in einer Tabelle hat eine versteckte physische Adresse. Führen Sie folgende Abfrage auf der Northwind-Datenbank aus:

SELECT ctid, customer_id, company_name FROM customers LIMIT 5;

Reflexionsfragen

• Was gibt der ctid-Wert (z. B. (0,1)) über den physischen Speicherort einer Zeile an?
• Warum nutzt PostgreSQL feste Blockgrößen (meist 8 KB) statt die Dateien einfach zeilenweise zu schreiben?

---

Auftrag 2 - MVCC

In PostgreSQL führt ein UPDATE physisch gesehen nicht zu einer Änderung der bestehenden Zeile.

Aufgaben

1. Ändern Sie den Namen eines Kunden in der Tabelle customers.
2. Führen Sie erneut SELECT ctid, ... für diesen Kunden aus.
3. Vergleichen Sie den alten ctid mit dem neuen.

Reflexionsfragen

• Was passiert mit der “alten” Version der Zeile auf der Festplatte nach einem Update?
• Erklären Sie den Begriff Table Bloat. Was hat das mit dem Befehl VACUUM zu tun?
• Welcher Hintergrundprozess sorgt dafür, dass dieser “Müll” (tote Tupel) automatisch entfernt wird?

---

Auftrag 3 - Logisches vs. Physisches Backup

Ein Junior-Admin fragt Sie, ob er die Datenbank einfach mit zip sichern kann, während der Server läuft.

Aufgaben

1. Erstellen Sie ein logisches Backup der Datenbank northwind_permissions mit dem Tool pg_dump.
2. Öffnen Sie die erstellte .sql-Datei mit einem Texteditor und überprüfen Sie den Inhalt: Welche Informationen werden dort gespeichert? (z.B. Tabellenstruktur, Daten, Indizes).
3. Erstellen Sie ein physisches Backup der Datenbank mit pg_basebackup.
4. Vergleichen Sie die beiden Backup-Methoden hinsichtlich ihrer Anwendungsfälle, Vor- und Nachteile.

Reflexionsfragen

• Warum ist ein einfacher Datei-Kopiervorgang (cp oder zip) des laufenden Datenverzeichnisses gefährlich?
• Was ist der Hauptvorteil eines logischen Backups (SQL-Format), wenn Sie die Daten auf ein System mit einer neueren PostgreSQL-Version übertragen müssen?
• Warum bevorzugen Administratoren bei extrem großen Datenbanken (mehrere Terabyte) eher physische Backups?

---

Auftrag 4 - Notfallplan: Point-in-Time Recovery

Ein Mitarbeiter hat versehentlich um 14:05 Uhr alle Bestelldaten gelöscht. Das letzte Full-Backup ist von Mitternacht.

Aufgaben

1. Recherchieren Sie das Konzept des Write Ahead Log (WAL).
2. Skizzieren Sie den Ablauf eines Point-in-Time Recovery (PITR). Welche zwei Komponenten benötigen Sie zwingend dafür?

Reflexionsfragen

• Warum ist das WAL (Write Ahead Logging) schneller, als bei jeder Änderung sofort die gesamte Tabellendatei zu aktualisieren?
• Wie ermöglicht das WAL, den Zustand der Datenbank auf eine exakte Sekunde (z. B. 14:04:59 Uhr) wiederherzustellen?
• Was ist der Unterschied zwischen einem einfachen Restore und einem “Forward-Replay” von Log-Dateien?

---

Abgabe

Word oder PDF Dokument Ihrer Antworten und Ergebnisse. Bitte fügen Sie Screenshots der SQL-Ausgaben bei, um Ihre Ergebnisse zu belegen.