6. Physische Organisation

Physische Organisation von Daten in Datenbankmanagementsystemen

Die physische Organisation bildet die Brücke zwischen der relationalen Welt von SQL und der mechanischen Realität der Speichermedien. Logische Objekte wie Tabellen und Spalten muss das Datenbankmanagementsystem (DBMS) effizient auf physische Laufwerke abbilden. Diese Schicht ist entscheidend für die Performance, denn Disk-I/O bleibt gegenüber dem schnellen RAM ein zentraler Engpass.

Um diese Abbildung zu optimieren, organisieren DBMS Daten meist in Einheiten fester Größe, sogenannte Pages oder Blocks, und nutzen so Caching-Strategien und Buffer-Management.

Anordnung der Daten auf dem Speichermedium

Der physische Speicher eines Datenbankmanagementsystems beginnt mit einem eigenen Data Directory im Dateisystem. Die meisten relationalen Datenbanken, darunter MariaDB und PostgreSQL, nutzen eine strukturierte Hierarchie aus Verzeichnissen, um systemweite Metadaten von den eigentlichen Benutzerdaten zu trennen. Während MariaDB oft ein Verzeichnis je Datenbankschema verwendet (mit .ibd-Dateien für InnoDB), verwaltet PostgreSQL seinen Cluster über die Umgebungsvariable PGDATA.

„… data directory, commonly referred to as PGDATA … A common location for PGDATA is /var/lib/pgsql/data.” Siehe PostgreSQL Storage Layout für weitere Details.

Ein typischer Aufbau des PostgreSQL-Datenverzeichnisses sieht so aus:

/var/lib/pgdata
├── base
│   ├── 1
│   ├── 4
│   ├── 5
│   └── 16384
├── global
│   ├── 1213
│   ├── 1213_fsm
│   ├── 1213_vm
│   ├── 1214
│   ├── 1232
│   ├── 1233
│   ├── 1260
│   ├── 1260_fsm
│   ├── 1260_vm
...
│   ├── 6303
│   ├── pg_control
│   ├── pg_filenode.map
│   └── pg_internal.init
├── pg_commit_ts
├── pg_dynshmem
├── pg_hba.conf
├── pg_ident.conf
├── pg_logical
│   ├── mappings
│   ├── replorigin_checkpoint
│   └── snapshots
├── pg_multixact
│   ├── members
│   └── offsets
├── pg_notify
├── pg_replslot
├── pg_serial
├── pg_snapshots
├── pg_stat
│   └── pgstat.stat
├── pg_stat_tmp
├── pg_subtrans
│   └── 0000
├── pg_tblspc
├── pg_twophase
├── PG_VERSION
├── pg_wal
│   ├── 000000010000000000000001
│   ├── archive_status
│   └── summaries
├── pg_xact
│   └── 0000
├── postgresql.auto.conf
├── postgresql.conf
└── postmaster.opts

Typische Merkmale des Aufbaus:

Verzeichnis je Datenbank: In PostgreSQL enthält das Verzeichnis base/ Unterordner, die nach der OID der Datenbank benannt sind (Object Identifier, ein eindeutiger numerischer Bezeichner, z. B. 23). MariaDB verwendet für seine Ordner dagegen meist den wörtlichen Datenbanknamen („northwind”).
Systemkataloge: Beide Systeme speichern globale Informationen (etwa Benutzer und Rechte) an zentraler Stelle. PostgreSQL nutzt dafür das Verzeichnis global/.
Transaktionsprotokolle: Um Dauerhaftigkeit zu sichern, führen Datenbanken Write-Ahead-Logs. PostgreSQL legt diese in pg_wal/ ab, ähnlich den ib_logfile- oder Redo-Logs von MariaDB.

Um den physischen Speicher von der logischen Datenbankstruktur zu entkoppeln, setzen viele Systeme Tablespaces ein. Diese Abstraktion erlaubt es Administratorinnen, Daten über verschiedene physische Speichergeräte zu verteilen und die I/O-Last auszubalancieren. Während MariaDB (über InnoDB) file-per-table oder allgemeine Tablespaces unterstützt, kann PostgreSQL Tabellen auf verschiedene Mount-Points oder Laufwerke abbilden.

„Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.” Siehe PostgreSQL Tablespaces für weitere Details.

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE fastspace;

Auf Dateisystemebene stellen Datenbanken Tabellen und Indizes als Relation Files dar. Eine häufige Herausforderung für ein DBMS ist der Umgang mit sehr großen Tabellen, die Grenzen des Dateisystems überschreiten könnten. Manche Systeme nutzen eine einzige große Datei, PostgreSQL und andere setzen dagegen auf einen segmentierten Ansatz.

PostgreSQL begrenzt jede physische Datei auf genau 1 GB, um die Kompatibilität über verschiedene Betriebssysteme hinweg und einfache Backups zu gewährleisten.

Datei-Segmentierung: Wächst eine Tabelle auf 10 GB, legt PostgreSQL 10 Dateien an (z. B. 16384, 16384.1 usw.). InnoDB in MariaDB lässt dagegen meist eine einzige .ibd-Datei dynamisch wachsen.
Bezeichner-Zuordnung: Die meisten Datenbanken benennen Dateien nicht nach dem Tabellennamen, um Namenskollisionen oder betriebssystemspezifische Zeichenprobleme zu vermeiden; stattdessen verwenden sie interne IDs (OIDs).
Hilfsdateien: Neben den eigentlichen Daten pflegen die Systeme Karten für freien Speicher. PostgreSQL nutzt _fsm-Dateien (Free Space Map) und _vm-Dateien (Visibility Map), um interne Abläufe zu optimieren.

Schließlich ist das physische Layout darauf ausgelegt, mit der I/O-Schicht des Betriebssystems zusammenzuarbeiten. Die meisten modernen Datenbanken sprechen die Hardware nicht direkt an (Raw Devices), sondern nutzen die Standard-Dateisystemaufrufe. Das DBMS verlässt sich also für Low-Level-Aufgaben wie das Disk-Scheduling auf den Kernel. Damit Daten tatsächlich „sicher” auf der Platte oder in den Flash-Zellen liegen, verwenden Datenbanken Synchronisationsbefehle.

Pages und Blocks

Um Daten effizient zu verwalten, arbeitet ein DBMS beim Zugriff auf den Speicher nicht mit einzelnen Zeilen oder Bytes, sondern organisiert alle Daten in Einheiten fester Größe, den Pages (oder Blocks).

„In postgresql, the block stored in the disk is called Page, and the block in memory is called Buffer. Tables and indexes are called Relation, and rows are called Tuple. When reading and writing data, the page is the smallest unit, each page has a default size of 8KB.” Siehe Architecture of PostgreSQL DB

Diese Granularität erlaubt es der Datenbank, die Zahl teurer I/O-Operationen zu senken, indem sie eine ganze Page in den Speicher liest, selbst wenn nur ein einzelner Datensatz angefragt wird. In PostgreSQL beträgt die Standard-Page-Größe 8 KB, ein Wert, der den Overhead des Page-Headers gegen die Effizienz moderner Dateisystem-Blockgrößen abwägt. Andere Systeme wie InnoDB in MariaDB nutzen standardmäßig 16 KB, aber das Prinzip bleibt gleich: Die Page ist die kleinste Dateneinheit, die zwischen Platte und internem Speicherpuffer der Datenbank übertragen wird.

In der Page steht ein Header mit Metadaten über die Page, etwa Anzahl der Datensätze, freier Platz und Informationen zur Transaktionssichtbarkeit. Der Rest der Page nimmt die eigentlichen Datenzeilen (Tuples) und ein Array von Zeilenzeigern auf, die auf die Position jedes Tuples innerhalb der Page verweisen.

PostgreSQL Page Layout — Abb. 6.1: Aufbau einer PostgreSQL-Page.

Quelle: [15]

Heap Files und Tuples

Um zu verstehen, wie eine Datenbank einen bestimmten Datensatz findet, muss man die Beziehung zwischen dem Index (B-Tree) und dem Heap (Pages) betrachten.

In der Datenbankterminologie heißt eine Tabelle oft Heap, weil die Daten in keiner bestimmten Reihenfolge liegen. Der Index ist die „Karte”, die der Datenbank genau sagt, welche Page und welcher Slot innerhalb dieser Page die Daten enthält.

Die nächsten Abschnitte zerlegen die physische Organisation der Daten in einer PostgreSQL-Datenbank und zeigen, wie der B-Tree-Index mit den Heap-Dateien zusammenspielt, um Datensätze effizient zu finden. Die InnoDB-Engine von MariaDB hat eine ähnliche Struktur, aber wir konzentrieren uns der Klarheit und Tiefe halber auf PostgreSQL.

Die CTID (Item Pointer)

Jeder Datensatz (Tuple) in einer PostgreSQL-Tabelle hat eine physische Adresse namens TID oder CTID. Das ist ein 6-Byte-Wert, der als direkter Zeiger auf den physischen Speicher dient. Er besteht aus zwei Zahlen:

Block Number: Welche 8-KB-Page in der Datei enthält den Datensatz?
Offset Number: Welcher „Line Pointer” (Index) innerhalb dieser Page zeigt auf die eigentlichen Daten?

Eine CTID von (42, 5) bedeutet zum Beispiel: 42. Block der Tabellendatei, und dort der 5. Line Pointer (innerhalb dieses Blocks).

Die Rolle des B-Tree

Ohne Index müsste die Datenbank einen Sequential Scan durchführen und jede einzelne 8-KB-Page von der Platte lesen, um einen Datensatz zu finden. Der B-Tree-Index ist eine eigene physische Datei, die eine sortierte Liste von Werten speichert (z. B. user_id oder ein beliebiger Primärschlüssel) samt der zugehörigen CTIDs.

Ein B-Tree-Beispiel. — Abb. 6.2: Beispiel einer B-Tree-Struktur.

Quelle: [13]

Bei einer Abfrage wie SELECT * FROM users WHERE id = 35:

Baum durchlaufen: Die Datenbank startet an der „Root” des B-Tree und steigt über „Internal Nodes” hinab, indem sie den Wert 35 vergleicht.
Blatt finden: Schließlich erreicht sie einen Leaf Node. Dieser enthält den Schlüssel 35 und dessen physische Adresse in der Tabelle, z. B. (Block 12, Offset 3).
Der „Pointer”-Sprung: Die Engine weiß nun genau, welche 8-KB-Page sie aus der Tabellendatei laden muss. Sie springt direkt zu Block 12 und holt den 3. Datensatz.

Entkopplung zugunsten der Performance

Ein wichtiges Detail der physischen Organisation ist, dass der B-Tree nicht direkt auf die Datenbytes zeigt. Er zeigt auf den Line Pointer (Item Identifier) am Anfang der Page.

Warum? Muss die Datenbank einen Datensatz innerhalb einer Page verschieben (etwa um Platz zurückzugewinnen), genügt es, den internen Line Pointer zu aktualisieren.
Der Vorteil: Der B-Tree-Index bleibt gültig, weil sich die „Offset Number” nicht geändert hat, selbst wenn die „Datenbytes” ein paar „Zentimeter” nach links oder rechts innerhalb des 8-KB-Blocks gewandert sind.

Zusammenfassung des Wegs

Benutzerabfrage ➡️ WHERE id = 35
B-Tree-Index ➡️ sucht 35, findet CTID (Block, Offset)
Buffer Manager ➡️ prüft, ob Block im RAM ist; falls nicht, liest er 8 KB von der Platte.
Page-Zugriff ➡️ schaut Offset im Line-Pointer-Array der Page nach.
Datenabruf ➡️ folgt dem Line Pointer zu den eigentlichen Tuple-Bytes.

MVCC und Versionierung

Ein zentrales Merkmal moderner RDBMS, besonders PostgreSQL, ist der Umgang mit gleichzeitigen Transaktionen. Der zeitgleiche Zugriff auf dieselben Daten kann zu Konflikten führen, und verschiedene Datenbanken gehen unterschiedlich damit um.

Statt klassisches Row-Level-Locking zu verwenden, das Leser auf Schreiber warten lässt, nutzt PostgreSQL Multi-Version Concurrency Control (MVCC). In diesem Modell überschreibt die Datenbank Daten nicht an Ort und Stelle. Stattdessen hält sie mehrere Versionen derselben Zeile gleichzeitig vor.

Die physische Wirkung von Updates und Deletes

In vielen anderen Datenbanken (etwa MariaDB mit InnoDB) werden Updates oft über „Undo-Logs” abgewickelt. In PostgreSQL wird dagegen jedes UPDATE physisch als DELETE gefolgt von einem INSERT behandelt.

INSERT: Ein neues Tuple wird einer Page hinzugefügt.
DELETE: Das bestehende Tuple wird nicht von der Platte entfernt; es wird lediglich als „abgelaufen” markiert.
UPDATE: Die alte Version der Zeile wird als abgelaufen markiert, und eine ganz neue Version (mit den aktualisierten Werten) wird anderswo im Heap gespeichert, unter Umständen sogar auf einer anderen Page.

Das heißt: Zu jedem Zeitpunkt kann eine einzelne 8-KB-Page mehrere „tote” Tuples enthalten, die für keine aktive Transaktion mehr sichtbar sind, aber weiterhin physischen Platz belegen.

Table Bloat und VACUUM

Die physische Folge dieser Architektur ist ein Phänomen namens Table Bloat. Da aktualisierte und gelöschte Zeilen auf der Platte bleiben, wachsen die Datenbankdateien weiter, selbst wenn die Menge der „lebenden” Daten konstant bleibt. Um diesen Platz zurückzugewinnen, nutzt PostgreSQL einen Hintergrundprozess namens VACUUM.

Der Ablauf: VACUUM scannt die Pages, erkennt Tuples und markiert Platz als „verfügbar” für künftige Inserts.
Autovacuum: In modernen Installationen stößt ein Daemon diesen Prozess automatisch an, abhängig vom Anteil geänderter Zeilen.
Grenzen: Ein normales VACUUM gibt keinen Platz an das Betriebssystem zurück; es macht ihn nur innerhalb der bestehenden 1-GB-Relation-Files für neue Daten verfügbar. Nur ein VACUUM FULL (das die ganze Tabelle neu schreibt) kann die Dateigröße auf der Platte verkleinern, benötigt dafür aber eine exklusive Sperre auf der Tabelle.

TOAST (The Oversized-Attribute Storage Technique)

Da PostgreSQL eine feste Page-Größe von 8 KB nutzt, entsteht ein physisches Problem, wenn eine einzelne Zeile viele Daten enthält, etwa einen langen Textblock, ein hochauflösendes Bild oder ein komplexes JSONB-Objekt. Weil ein Tuple vollständig in eine einzelne Page passen muss, setzt PostgreSQL einen Mechanismus namens TOAST ein. Diese „Out-of-line”-Speichertechnik sorgt dafür, dass große Attribute den Haupt-Heap nicht aufblähen und die physischen Grenzen eines Blocks nicht sprengen.

Wie TOAST funktioniert

Beim Einfügen oder Aktualisieren einer Zeile prüft PostgreSQL, ob die Gesamtgröße einen bestimmten Schwellenwert (meist 2 KB) überschreitet. Sind die Daten zu groß, startet der TOAST-Prozess:

Kompression: Zuerst versucht PostgreSQL, den großen Wert zu komprimieren, damit er doch in die 8-KB-Page passt.
Out-of-line-Speicherung: Ist der komprimierte Wert immer noch zu groß, wird er in eine separate, interne TOAST-Tabelle ausgelagert, die zur Haupttabelle gehört.
Der Zeiger: In der ursprünglichen Heap-Page werden die eigentlichen Daten durch einen kleinen Zeiger ersetzt (einen „Toast Pointer”). Dieser enthält die OID der TOAST-Tabelle und die genaue Stelle, an der der große Wert liegt.

Ein TOAST-Beispiel. — Abb. 6.3: Beispiel für TOAST in PostgreSQL. Die Page (links) im Haupt-Heap enthält einen Zeiger auf den großen Wert, der in der TOAST-Tabelle (rechts) liegt.

Quelle: [14]

Vorteile für die Performance

Diese physische Trennung bringt einen deutlichen Performancevorteil für Standardabfragen. Wird ein SELECT * ausgeführt, aber nur nach einer kleinen Spalte gefiltert (etwa user_id oder status), muss die Datenbank nur die 8-KB-Pages des Haupt-Heaps lesen. Die riesigen „getoasteten” Daten werden nur dann in den Speicher geladen, wenn die betreffende große Spalte tatsächlich angefragt wird. Das hält den Buffer Cache sauber und lässt mehr „kleine” Zeilen in eine einzelne Page passen, was Sequential Scans und Index-Lookups drastisch beschleunigt.

Indizes auf physischer Ebene

Zwar werden in PostgreSQL sowohl Tabellen als auch Indizes als Relation Files in 8-KB-Pages gespeichert, doch ihre interne physische Struktur unterscheidet sich deutlich. In einer Heap-Datei landen Daten auf jeder Page mit genug freiem Platz, es entsteht eine ungeordnete Sammlung. Eine Indexdatei dagegen pflegt eine streng organisierte, hierarchische Struktur.

Bei einem B-Tree-Index werden die physischen Pages in Meta-Pages, Internal Nodes und Leaf Nodes unterteilt. Die Meta-Page am Anfang der Datei enthält globale Informationen über den Index, während die Internal Nodes Schlüssel speichern, die als Wegweiser durch die Suche führen. Die Leaf Nodes am unteren Ende der Hierarchie speichern die eigentlichen indizierten Werte samt den CTIDs, die zurück auf den Heap zeigen.

Write Ahead Logging (WAL)

Das Write Ahead Log (WAL) ist der grundlegende Mechanismus, der Datenintegrität und Wiederherstellung bei einem Systemabsturz sichert. Statt jede Änderung sofort in die eigentlichen 8-KB-Heap- und Index-Pages zu schreiben, was teures Random-I/O erfordern würde, protokolliert PostgreSQL die Änderung zuerst als sequenziellen Eintrag im WAL.

Dieses Log, gespeichert im Verzeichnis pg_wal, dient als chronologisches Tagebuch aller Änderungen. Das „Write Ahead”-Prinzip verlangt, dass keine Daten-Page in die dauerhaften Relation-Files auf der Platte geschrieben werden darf, bevor der zugehörige Log-Eintrag sicher ins WAL committet wurde.

Dieses sequenzielle Schreiben ist deutlich schneller als das Aktualisieren vieler verstreuter Datendateien und erlaubt der Datenbank hohe Performance bei gleichzeitiger Garantie, dass keine committete Transaktion verloren geht.

Backup und Wiederherstellung

Strategien für Backup und Wiederherstellung sind entscheidend, um Datenbeständigkeit und Verfügbarkeit zu sichern. Die physische Organisation der Daten, also der Aufbau von Heap-Dateien, Indizes und WAL, beeinflusst direkt, wie Backups erstellt werden und wie die Wiederherstellung abläuft.

Gründe für Backup und Wiederherstellung

Trotz der robusten physischen Architektur moderner DBMS wie PostgreSQL bleiben Daten anfällig für verschiedene äußere und innere Bedrohungen. Backups sind nicht nur ein Sicherheitsnetz, sondern ein kritischer Teil des Daten-Lebenszyklus, aus folgenden Gründen:

Hardware- und Systemausfälle: Selbst mit hochwertigem Speicher können physische Medien ausfallen. Ein „Head Crash” auf einer klassischen HDD oder ein Controller-Defekt auf einer SSD kann zu nicht wiederherstellbarer Datenkorruption in den Relation-Files führen.
Menschliches Versagen und böswillige Aktivität: Versehentliche DROP TABLE- oder DELETE-Befehle gehören zu den häufigsten Ursachen für Datenverlust. Da diese Operationen technisch „gültige” Transaktionen sind, führt die Datenbank sie treu aus und schreibt sie ins WAL. In solchen Fällen kann nur ein Backup, das vor dem Fehler erstellt wurde (kombiniert mit Point-in-Time Recovery), die verlorenen Informationen wiederherstellen. Das gilt auch für Angriffe wie Ransomware, die das physische Datenverzeichnis verschlüsseln kann.
Softwarefehler und fehlerhafte Updates: Bugs im DBMS selbst, eine schlecht geschriebene Extension oder ein abgebrochenes Major-Upgrade einer Anwendung können zu Datenkorruption führen.
Compliance und rechtliche Vorgaben: Viele Branchen sind gesetzlich verpflichtet, Momentaufnahmen ihrer Daten über mehrere Jahre aufzubewahren. Physische Backups dienen als unveränderliche Aufzeichnungen des Datenbankzustands zu einem bestimmten Zeitpunkt und erfüllen Prüfanforderungen, die ein lebender, sich ständig ändernder Cluster nicht erfüllen kann.

Backups erstellen

In vielen DBMS wie PostgreSQL und MariaDB gibt es zwei grundsätzlich verschiedene Wege, ein Backup zu erstellen, die jeweils anders mit der physischen Speicherschicht umgehen. Die richtige Methode hängt von der Größe der Datenbank, der geforderten Wiederherstellungsgeschwindigkeit und davon ab, ob das Backup zwischen verschiedenen Versionen oder Betriebssystemen portabel sein muss.

Logische Backups (`pg_dump`)

Ein logisches Backup extrahiert die Datenbankstruktur und ihren Inhalt in eine Reihe von SQL-Befehlen oder ein eigenes Archivformat. Wird pg_dump (oder mysqldump für MariaDB) ausgeführt, verbindet sich das Werkzeug wie ein gewöhnlicher Client mit der Datenbank und liest die Daten über die SQL-Schicht.

Mechanismus: Es führt einen sequenziellen Scan aller Tabellen durch, umgeht dabei die physische 8-KB-Page-Struktur und konzentriert sich auf die Datenwerte selbst.
Portabilität: Da die Daten als SQL gespeichert werden (etwa INSERT-Anweisungen), lässt sich ein logisches Backup auf einer anderen CPU-Architektur oder einer neueren PostgreSQL-Version einspielen.
Granularität: Man kann problemlos eine einzelne Tabelle, ein bestimmtes Schema oder die ganze Datenbank sichern.
Nachteil: Bei sehr großen Datenbanken (Terabyte) sind logische Backups langsam, weil die Datenbank jede physische Zeile in Text/SQL umwandeln und beim Wiederherstellen erneut parsen und indizieren muss.

Ein logisches Backup der Datenbank „my_database” als Benutzer „db_admin” mit pg_dump in PostgreSQL erstellen:

pg_dump -U db_admin -d my_database &> BACKUP_2026-03-18.sql

Die Größe der Backup-Datei prüfen (hier 15 KB):

ls -hl BACKUP_2026-03-18.sql
-rw-r--r-- 1 root root 15K Mar 18 08:12 BACKUP_2026-03-18.sql

Die ersten Zeilen der Backup-Datei prüfen, um zu bestätigen, dass sie SQL-Befehle enthält (Leerzeilen ausgelassen):

grep -v '^$' BACKUP_2026-03-18.sql | head -n 20
--
-- PostgreSQL database dump
--
\restrict eDhVav5z1NQbVObw4zGJcnWnIu3FzAe9rR56JvPg1jp3F7SpZW7rGGULIzvwEAs
-- Dumped from database version 17.6 (Debian 17.6-2.pgdg13+1)
-- Dumped by pg_dump version 17.6 (Debian 17.6-2.pgdg13+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: RoleEnum; Type: TYPE; Schema: public; Owner: digidog
--

Physische Backups (`pg_basebackup`)

Ein physisches Backup, oft „Binary Backup” genannt, erstellt eine bitgenaue Kopie der Dateien im PGDATA-Verzeichnis. Dazu gehören alle 1-GB-Relation-Segmente, die WAL-Dateien und die internen Metadaten.

Wichtig: Ein physisches Backup ist nicht einfach eine Dateikopie. Die Datenbank muss sicherstellen, dass das Backup konsistent ist, dass also alle Dateien in einem Zustand sind, mit dem sich die Datenbank ohne Korruption starten lässt.

⚠️ Das ist heikel, weil die Datenbank ständig in diese Dateien schreibt, und ein einfaches cp oder tar des Datenverzeichnisses würde wahrscheinlich ein korruptes Backup erzeugen. ⚠️

Mechanismus: Werkzeuge wie pg_basebackup kopieren die eigentlichen 8-KB-Pages direkt aus dem Dateisystem. Für große Datenmengen ist das viel schneller, weil der Overhead der SQL-Verarbeitung entfällt.
Konsistenz: pg_basebackup nutzt ein spezielles Replikationsprotokoll, das sicherstellt, dass die kopierten Dateien zusammen mit den während des Backups erzeugten WAL-Segmenten einen konsistenten Zustand ergeben.
Wiederherstellungsgeschwindigkeit: Die Wiederherstellung ist deutlich schneller als bei logischen Backups, weil die Datenbank-Engine mit den kopierten Dateien einfach „hochfährt” und nur wenige WAL-Einträge nachspielen muss, statt alle Indizes neu aufzubauen.

Inkrementelle und differentielle Backups

Während ein physisches „Voll”-Backup (pg_basebackup) jede einzelne 8-KB-Page der gesamten Datenbank kopiert, speichern inkrementelle Backups nur die Pages, die sich seit dem letzten Backup tatsächlich geändert haben.

Dieser Ansatz reduziert die übertragene und gespeicherte Datenmenge erheblich, besonders bei mehrere Terabyte großen Datenbanken, bei denen täglich nur ein kleiner Teil der Zeilen verändert wird. In PostgreSQL (ab Version 17) wird das über einen Mechanismus auf Blockebene umgesetzt. Statt den gesamten Heap nach Änderungen zu durchsuchen, kann die Datenbank eine zusammengefasste Datei geänderter Blöcke pflegen, sodass das Backup-Werkzeug gezielt nur die nötigen 8-KB-Einheiten herauszieht.

Schritte für ein inkrementelles Backup:

Die Datenbank so konfigurieren, dass sie Blockänderungen verfolgt. Dazu ist diese Funktion eventuell in der PostgreSQL-Konfiguration (postgresql.conf) zu aktivieren und der Server neu zu starten.
Erstes Voll-Backup: Mit einem vollständigen physischen Backup über pg_basebackup beginnen.

pg_basebackup -D /path/to/full_backup -Fp -P -U db_admin

Änderungen verfolgen: Die Engine verfolgt, welche Blöcke seit dem letzten Backup verändert wurden.
Inkrementelles Backup erstellen: Ein Werkzeug verwenden, das die Datei mit den Blockänderungen liest und ein Backup nur mit den geänderten Blöcken erstellt.

pg_basebackup -D /path/to/incremental_1 --incremental=/path/to/full_backup backup_manifest -Fp -P -U db_admin

Restore

Ein Backup ist nur so gut wie die Fähigkeit, es wiederherzustellen. Der Restore ist die betriebliche Umkehrung des Backups: Daten wandern aus dem Backup-Speicher zurück in das physische Layout der Engine. Je nachdem, ob ein logischer oder physischer Ansatz gewählt wurde, greift der Restore unterschiedlich in Pages, Indizes und Write-Ahead-Logs ein.

Ein logisches Backup wiederherstellen

Ein logisches Backup (mit pg_dump oder mysqldump erstellt) wiederherzustellen bedeutet im Kern, ein großes SQL-Skript auszuführen. Da das Backup aus Standard-DDL (CREATE TABLE) und -DML (INSERT) besteht, muss die Engine jede Zeile wie eine neue Transaktion verarbeiten.

Der Ablauf: Verwendet wird das Werkzeug psql (oder mariadb).
Physische Ausführung: Während die Befehle laufen, muss das DBMS neue 8-KB-Pages anlegen, Tuples in den Heap einfügen und – am wichtigsten – jeden Index von Grund auf neu aufbauen.
Performance: Das ist CPU- und I/O-intensiv. Weil die Indizes während des Imports neu berechnet werden, kann das logische Wiederherstellen einer mehrere Terabyte großen Datenbank deutlich länger dauern als das ursprüngliche Backup.

Eine einfache SQL-Backup-Datei in eine Datenbank zurückspielen:

psql -U db_admin -d my_database -f BACKUP_2026-03-18.sql

Ein physisches Backup wiederherstellen

Ein physisches Backup (mit pg_basebackup erstellt) wiederherzustellen ähnelt eher einer Dateisystem- als einer Datenbankoperation. Statt SQL auszuführen, wird der Inhalt des PGDATA-Verzeichnisses in PostgreSQL ersetzt.

Der Ablauf:

Der Datenbankserver muss gestoppt werden.
Das bestehende (korrupte oder alte) Datenverzeichnis wird geleert.
Die Backup-Dateien (die 8-KB-Pages und Steuerdateien) werden in den PGDATA-Ort kopiert.
Der Server wird gestartet.

Physische Ausführung: Beim Start geht die PostgreSQL-Engine in den „Recovery Mode”. Sie prüft die Datei pg_control und nutzt die archivierten WAL-Segmente, um alle Transaktionen „nachzuspielen”, die während oder nach dem Backup auftraten.
Performance: Das ist extrem schnell, weil Indizes und Tabellen bereits in ihrer endgültigen Binärform vorliegen. Es braucht kein SQL-Parsing und keine Neuberechnung der Indizes.

Point-in-Time Recovery (PITR)

Point-in-Time Recovery (PITR) ist eine fortgeschrittene Form des physischen Restore. Damit lässt sich eine Datenbank auf einen bestimmten Zeitpunkt zurücksetzen (etwa „letzten Dienstag, 10:14 Uhr”), was Gold wert ist, wenn ein Fehler – wie das versehentliche Löschen einer Produktionstabelle – um 10:15 Uhr passiert ist.

PITR braucht zwei Bestandteile:

Ein Base Backup: eine physische Momentaufnahme des Datenverzeichnisses.
WAL-Archivierung: einen kontinuierlichen Strom von WAL-Segmenten, die seit diesem Base Backup gesichert wurden.

Bei der Wiederherstellung wird PostgreSQL die „recovery target time” vorgegeben. Die Engine startet mit dem Base Backup und „spult” durch die WAL-Logs vor, indem sie jede physische Änderung Block für Block erneut anwendet, bis sie exakt die Mikrosekunde vor dem Fehler erreicht. Das bietet eine Ausfallsicherheit, die mit logischen Backups allein kaum erreichbar ist.

Die Datei „recovery.conf” konfiguriert den Wiederherstellungsprozess und gibt Ort des Base Backups, WAL-Archiv und Zielzeitpunkt an.

-- specify the recovery target point in time
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-01-25 14:34:00'
recovery_target_action = 'promote'

-- alternative targets:
-- recovery_target_xid = '12345'        # up to a specific transaction
-- recovery_target_lsn = '0/15D68C50'   # up to a specific WAL position
-- recovery_target_name = 'before_migration'  # up to a named restore point

Siehe How to Recover Data with Point-in-Time Recovery in PostgreSQL für eine ausführliche Anleitung zum PITR-Ablauf. [16]