Logische vs. physische Backups
pg_dump und Basis-Backups
Logische vs. physische Backups ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 1 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.
Was ist ein Datenbank-Backup?
Ein Backup ist eine Kopie Ihrer Datenbankdaten, mit der Sie das System nach Datenverlust, Beschädigung oder einer Katastrophe wiederherstellen können. Ohne zuverlässige Backups kann ein einziger Hardwareausfall oder ein versehentliches DELETE Monate oder Jahre an Daten dauerhaft zerstören.
PostgreSQL bietet zwei grundlegende Kategorien von Backup-Strategien: logische Backups und physische Backups. Beide haben unterschiedliche Eigenschaften, Einsatzbereiche und Abwägungen, die jeder DBA verstehen muss.
Logische Backups erklärt
Ein logisches Backup exportiert die Datenbank als für Menschen lesbare SQL-Anweisungen — CREATE TABLE, INSERT, COPY und ähnliche Befehle. Das gängigste Werkzeug dafür in PostgreSQL ist pg_dump.
Da die Ausgabe aus einfachem SQL besteht, ist ein logisches Backup portabel: Sie können es in einer anderen PostgreSQL-Version, auf einem anderen Betriebssystem oder sogar selektiv einzelne Tabellen oder Schemas wiederherstellen. Der Nachteil besteht darin, dass das Sichern und Wiederherstellen großer Datenbanken langsam sein kann.
pg_dump für ein logisches Backup verwenden
Das Dienstprogramm pg_dump wird über die Kommandozeile ausgeführt, nicht innerhalb von SQL. Es verbindet sich mit einem laufenden PostgreSQL-Server und exportiert die ausgewählte Datenbank. Sie können die Ausgabe als reines SQL, in einem benutzerdefinierten komprimierten Format oder im Verzeichnisformat erzeugen.
Das folgende SQL simuliert, was ein logisches Backup erfasst — die Struktur und die Daten einer Tabelle als reproduzierbare Anweisungen.
-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT NOT NULL,
amount NUMERIC(10,2),
created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO orders (customer, amount, created_at) VALUES
('Alice', 149.99, '2024-01-15 09:30:00+00'),
('Bob', 89.50, '2024-01-16 14:00:00+00'),
('Carol', 210.00, '2024-01-17 11:15:00+00');Ausgabeformate von pg_dump
pg_dump unterstützt vier Ausgabeformate, die jeweils für unterschiedliche Wiederherstellungsabläufe geeignet sind:
- plain — ein reines SQL-Skript, das in jedem Texteditor lesbar ist.
- custom — ein komprimiertes Binärformat; am flexibelsten und mit Unterstützung für parallele Wiederherstellung.
- directory — eine Datei pro Tabelle; unterstützt paralleles Sichern und Wiederherstellen.
- tar — ein tar-Archiv des Verzeichnisformats.
Das custom-Format wird für große Datenbanken empfohlen, weil pg_restore Objekte mit -j N Workern parallel wiederherstellen kann.
-- Checking which databases exist before choosing what to back up
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY pg_database_size(datname) DESC;Ein logisches Backup wiederherstellen
Ein logisches Backup im Plain-SQL-Format wird mit psql wiederhergestellt. Für ein Backup im custom-Format benötigen Sie pg_restore. Beide Werkzeuge spielen die SQL-Anweisungen erneut ab, um Tabellen, Indizes, Constraints und Daten wiederherzustellen.
Da logische Backups SQL enthalten, können Sie sie vor der Wiederherstellung bearbeiten — beispielsweise, um nur eine Tabelle wiederherzustellen oder einen Schemanamen zu ändern. Diese Flexibilität ist einer der größten Vorteile des logischen Ansatzes.
-- After restoring a backup, verify row counts match expectations
SELECT
schemaname,
relname AS table_name,
n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;Physische Backups erklärt
Ein physisches Backup (auch Basis-Backup genannt) kopiert die Rohdatendateien, die PostgreSQL auf dem Datenträger verwendet — die Seiten, WAL-(Write-Ahead-Log-)Segmente und Konfigurationsdateien. Das Ergebnis ist ein binärer Snapshot des gesamten Clusters zu einem bestimmten Zeitpunkt.
Physische Backups lassen sich bei großen Datenbanken in der Regel deutlich schneller wiederherstellen, da SQL nicht erneut ausgeführt werden muss. PostgreSQL liest die Dateien einfach wieder ein und spielt WAL erneut ab, um einen konsistenten Zustand zu erreichen.
pg_basebackup: Ein physisches Backup erstellen
pg_basebackup ist das Standardwerkzeug von PostgreSQL für physische Backups. Es überträgt das Datenverzeichnis von einem laufenden primären Server über eine Replikationsverbindung. Sie benötigen einen Benutzer mit Replikationsberechtigung sowie wal_level auf replica oder höher gesetzt.
Innerhalb der Datenbank können Sie die Replikationseinstellungen abfragen, um vor dem Erstellen eines Basis-Backups zu bestätigen, dass der Server korrekt konfiguriert ist.
-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
'wal_level',
'max_wal_senders',
'archive_mode',
'archive_command'
)
ORDER BY name;WAL-Archivierung und Point-in-Time Recovery
Ein Basis-Backup erfasst einen bestimmten Zeitpunkt. Um nach diesem Backup einen beliebigen Zeitpunkt wiederherzustellen, spielt PostgreSQL archivierte WAL-Segmente erneut ab — dies wird als Point-in-Time Recovery (PITR) bezeichnet.
Wenn archive_mode = on aktiviert und archive_command konfiguriert ist, kopiert PostgreSQL abgeschlossene WAL-Segmente an einen Archivspeicherort. Während der Wiederherstellung ruft restore_command diese Segmente ab, damit der Server sie bis zur gewünschten Zielzeit erneut abspielen kann.
-- Inspect current WAL position and archive status
SELECT
pg_current_wal_lsn() AS current_lsn,
pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file,
archived_count,
failed_count,
last_archived_wal,
last_archived_time
FROM pg_stat_archiver;Logische und physische Backups vergleichen
Die Wahl zwischen logischen und physischen Backups hängt von Ihren Anforderungen ab:
- Logisch (pg_dump): versionsübergreifend portabel, unterstützt partielle Wiederherstellung und ist menschenlesbar, aber bei großen Datenbanken langsam und ohne Granularität auf Untertransaktionsebene.
- Physisch (pg_basebackup + WAL): schnelle Wiederherstellung großer Cluster, unterstützt PITR, ist versionsspezifisch (muss in derselben Hauptversion wiederhergestellt werden) und stellt den gesamten Cluster wieder her — Sie können keine einzelne Tabelle wiederherstellen.
Produktionsumgebungen verwenden typischerweise beides: nächtliche physische Basis-Backups mit kontinuierlicher WAL-Archivierung sowie regelmäßige logische Dumps für Portabilität und gezielte Wiederherstellungen.
Backup-Integrität überprüfen
Ein Backup, das nie getestet wurde, ist kein Backup — sondern nur eine Hoffnung. Validieren Sie Backups immer, indem Sie sie in einer Testumgebung wiederherstellen und die Daten überprüfen.
Bei logischen Backups besteht eine schnelle Integritätsprüfung darin, Zeilen zu zählen und Prüfsummen zu vergleichen. Für physische Backups wurde ab PostgreSQL 14 pg_verifybackup eingeführt, das die von pg_basebackup geschriebene Manifestdatei überprüft.
-- After a test restore, compare row counts across critical tables
SELECT
relname AS table_name,
n_live_tup AS live_rows,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC
LIMIT 20;Backup-Überwachung und Zeitplanung
Die Automatisierung und Überwachung von Backups ist genauso wichtig wie deren Erstellung. Erfassen Sie, wann Backups zuletzt ausgeführt wurden, wie lange sie gedauert haben und ob sie erfolgreich waren. PostgreSQL stellt dafür nützliche Metadaten bereit.
Für physische Backups zeigt pg_stat_archiver die letzte erfolgreiche Archivierung und etwaige Fehler. Für logische Backups können Sie pg_dump in ein Skript einbinden, das Startzeit, Endzeit, Dateigröße und Exit-Code in einer Überwachungstabelle oder einem Benachrichtigungssystem protokolliert.
-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
id SERIAL PRIMARY KEY,
backup_type TEXT NOT NULL CHECK (backup_type IN ('logical', 'physical')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finished_at TIMESTAMPTZ,
size_bytes BIGINT,
status TEXT NOT NULL DEFAULT 'running',
notes TEXT
);
-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;Logisch oder physisch: Kurze Überprüfung
Testen Sie Ihr Verständnis der Strategien für logische und physische Backups in PostgreSQL.
Zusammenfassung der Lektion: Logische und physische Backups
In dieser Lektion haben Sie zwei grundlegende PostgreSQL-Backup-Strategien untersucht:
- Logische Backups verwenden
pg_dump, um Datenbanken als SQL-Anweisungen zu exportieren. Sie sind portabel, menschenlesbar und unterstützen partielle Wiederherstellungen, können bei sehr großen Datenbanken jedoch langsam sein. - Physische Backups verwenden
pg_basebackup, um Rohdatendateien zu kopieren. Zusammen mit der WAL-Archivierung ermöglichen sie schnelle Wiederherstellungen und Point-in-Time Recovery, sind jedoch versionsspezifisch und stellen immer den vollständigen Cluster wieder her. - Produktionssysteme kombinieren typischerweise beide Strategien: physische Basis-Backups mit WAL-Archivierung für eine schnelle, granulare Wiederherstellung sowie regelmäßige logische Dumps für Portabilität.
- Testen Sie Ihre Wiederherstellungen immer. Ein ungetestetes Backup kann in einer realen Katastrophensituation nicht als zuverlässig betrachtet werden.
Das Verständnis dieser beiden Ansätze ist entscheidend für die Entwicklung eines robusten Notfallwiederherstellungsplans für jede PostgreSQL-Bereitstellung.
Häufig gestellte Fragen
Ist die Lektion „Logische vs. physische Backups“ kostenlos?
Ja — der vollständige Text von „Logische vs. physische Backups“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.
Was lerne ich in „Logische vs. physische Backups“?
pg_dump und Basis-Backups Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.
Brauche ich Erfahrung, um SQL Academy zu starten?
Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 1 von 4.
Wie lange dauert die Lektion „Logische vs. physische Backups“?
Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.
Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?
Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.
Alle Lektionen in diesem Kurs
- Logische vs. physische Backups
- Wiederherstellung zu einem bestimmten Zeitpunkt
- Ihre Wiederherstellungen testen
- Notfallwiederherstellungsplanung