Wiederherstellung zu einem bestimmten Zeitpunkt
Mit WAL jeden beliebigen Zeitpunkt wiederherstellen
Wiederherstellung zu einem bestimmten Zeitpunkt ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 2 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 Point-in-Time Recovery?
Point-in-Time Recovery (PITR) ermöglicht es Ihnen, eine Datenbank auf jeden beliebigen konkreten Zeitpunkt in der Vergangenheit zurückzusetzen, nicht nur auf den Zeitpunkt des letzten Backup-Snapshots.
Dies ist möglich, weil PostgreSQL jede Änderung kontinuierlich in einen Datenstrom schreibt, der als Write-Ahead Log (WAL) bezeichnet wird. Indem Sie WAL-Einträge auf einem Basis-Backup wiedergeben, gelangen Sie exakt zum gewünschten Zeitpunkt.
Das Write-Ahead Log (WAL)
Jede INSERT-, UPDATE-, DELETE- und DDL-Anweisung wird zuerst in das WAL geschrieben, bevor sie die eigentlichen Datendateien verändert. Dadurch ist die Dauerhaftigkeit auch dann gewährleistet, wenn der Server während eines Schreibvorgangs abstürzt.
Für PITR sind archivierte WAL-Segmente die Zeitmaschinenbänder. Das Basis-Backup ist der Ausgangspunkt; das WAL ergänzt alles, was danach geschehen ist.
-- Check current WAL write location
SELECT pg_current_wal_lsn() AS current_lsn,
pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;WAL-Archivierung aktivieren
Bevor PITR möglich ist, muss die WAL-Archivierung in postgresql.conf aktiviert werden. Die wichtigsten Parameter sind wal_level, archive_mode und archive_command.
Der Archivierungsbefehl kopiert jedes fertiggestellte WAL-Segment an einen sicheren Ort, beispielsweise in ein lokales Verzeichnis, einen NFS-Einhängepunkt oder einen Cloud-Speicher-Bucket.
-- View current WAL and archive settings
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
'wal_level',
'archive_mode',
'archive_command',
'archive_status'
)
ORDER BY name;Ein Basis-Backup erstellen
Ein Basis-Backup ist eine konsistente Kopie des gesamten Datenverzeichnisses, die während des laufenden Serverbetriebs erstellt wird. PostgreSQL stellt dafür das Dienstprogramm pg_basebackup sowie die SQL-Funktionen auf niedriger Ebene bereit, mit denen sich dieser Vorgang koordinieren lässt.
Das Backup bildet zusammen mit den archivierten WAL-Segmenten, die ab dem Startzeitpunkt des Backups aufgezeichnet wurden, alles, was Sie für PITR benötigen.
-- Check the latest base backup information
SELECT backup_start,
backup_end,
pg_size_pretty(backup_total) AS total_size
FROM pg_stat_basebackup;Einen Wiederherstellungszeitpunkt festlegen
Wenn eine Katastrophe eintritt, besteht der erste Schritt darin, den genauen Zeitstempel zu bestimmen, auf den Sie die Wiederherstellung zurücksetzen möchten. Häufig sucht man nach dem Zeitpunkt unmittelbar vor dem schädlichen Ereignis, beispielsweise einem versehentlichen massenhaften DELETE.
Transaktionsprotokolltabellen oder Anwendungsprotokolle enthalten diesen Zeitstempel häufig. Das WAL zeichnet Commit-Zeitstempel auf, wenn track_commit_timestamp aktiviert ist.
-- Find the commit timestamp of recent transactions (requires track_commit_timestamp=on)
SELECT xid,
pg_xact_commit_timestamp(xid::xid) AS committed_at
FROM pg_stat_activity
WHERE state = 'idle'
LIMIT 10;Die recovery.conf-/Recovery-Parameter
Das Verhalten bei der Wiederherstellung wird durch Parameter gesteuert, die in postgresql.conf (PostgreSQL 12+) oder in der älteren Datei recovery.conf festgelegt werden. Die wichtigsten sind:
restore_command— wie archivierte WAL-Segmente abgerufen werdenrecovery_target_time— der genaue Zeitstempel, an dem angehalten wirdrecovery_target_action— was beim Erreichen des Ziels geschehen soll (promote,pause,shutdown)
-- Inspect recovery settings that are currently active
SELECT name, setting
FROM pg_settings
WHERE name IN (
'restore_command',
'recovery_target_time',
'recovery_target_action',
'recovery_target_inclusive'
)
ORDER BY name;Andere Typen von Wiederherstellungszielen
Die Zeit ist nicht die einzige Möglichkeit, ein Wiederherstellungsziel festzulegen. PostgreSQL unterstützt vier Zieltypen:
recovery_target_time— eine Zeitstempelzeichenfolgerecovery_target_xid— eine bestimmte Transaktions-IDrecovery_target_lsn— eine WAL-Logsequenznummerrecovery_target_name— ein benannter Wiederherstellungspunkt, der mitpg_create_restore_point()erstellt wurde
-- Create a named restore point before a risky migration
SELECT pg_create_restore_point('before_migration_2024_07_01') AS restore_lsn;Den Notfall simulieren
Um PITR in einer Testumgebung zu üben, beschädigen oder löschen Sie absichtlich Daten, nachdem Sie die aktuelle Zeit notiert haben. So können Sie überprüfen, ob die Wiederherstellung am richtigen Zeitpunkt endet und ob die gelöschten Zeilen wiederhergestellt werden.
-- Record the 'safe' timestamp, then simulate an accident
SELECT now() AS safe_point;
-- (In a test DB only!) accidentally delete important rows
DELETE FROM orders WHERE created_at < '2024-01-01';
-- Confirm the damage
SELECT COUNT(*) AS remaining_orders FROM orders;Die Wiederherstellung durchführen
Die Wiederherstellung umfasst drei Schritte:
- Beenden Sie die laufende PostgreSQL-Instanz.
- Stellen Sie das Basis-Backup im Datenverzeichnis wieder her.
- Konfigurieren Sie die Wiederherstellungsparameter und starten Sie PostgreSQL im Wiederherstellungsmodus — dabei werden WAL-Daten so lange erneut angewendet, bis der Zielzeitpunkt erreicht ist. Anschließend wird der Server zu einem normalen Lese-/Schreibserver hochgestuft.
Die Serverprotokolle zeigen, welche WAL-Datei jeweils angewendet wird. Dadurch lässt sich der Fortschritt leicht verfolgen.
-- After recovery completes, confirm the cluster is no longer in recovery
SELECT pg_is_in_recovery() AS in_recovery,
pg_last_xact_replay_timestamp() AS last_replayed_txn;Die Wiederherstellung überprüfen
Sobald der Server hochgestuft wurde, überprüfen Sie, ob sich die Daten im erwarteten Zustand befinden. Prüfen Sie die Anzahl der Zeilen, die Zeitstempel der neuesten Zeilen und alle geschäftskritischen Werte.
Es ist sinnvoll, diese Prüfungsabfragen vor der erneuten Zulassung von Anwendungsdatenverkehr auszuführen. So können Sie bei Bedarf die Wiederherstellung fortsetzen oder den Zielzeitpunkt anpassen.
-- Verify row counts and the latest transaction timestamp after recovery
SELECT 'orders' AS tbl, COUNT(*) AS rows FROM orders
UNION ALL
SELECT 'order_items' AS tbl, COUNT(*) AS rows FROM order_items
UNION ALL
SELECT 'customers' AS tbl, COUNT(*) AS rows FROM customers
ORDER BY tbl;Bewährte Vorgehensweisen für PITR
Eine zuverlässige PITR-Konfiguration erfordert mehr, als nur die Archivierung zu aktivieren. Zu den wichtigsten Vorgehensweisen gehören:
- Stellen Sie regelmäßig Backups wieder her — Ein nicht getestetes Backup ist kein Backup.
- Überwachen Sie den Rückstand des WAL-Archivs — Lücken im archivierten WAL machen eine vollständige Wiederherstellung unmöglich.
- Bewahren Sie ausreichend WAL-Verlauf auf — Bewahren Sie Segmente mindestens bis zum Zeitpunkt Ihres ältesten gültigen Basis-Backups auf.
- Verwenden Sie einen separaten Speicherort — Wenn die primäre Festplatte ausfällt, geht auch das auf derselben Festplatte gespeicherte archivierte WAL verloren.
-- Check for any WAL archiving failures
SELECT archived_count,
failed_count,
last_archived_wal,
last_archived_time,
last_failed_wal,
last_failed_time
FROM pg_stat_archiver;Kurztest
Testen Sie Ihr Verständnis der Point-in-Time-Recovery.
Zusammenfassung: Point-in-Time-Recovery
In dieser Lektion haben Sie gelernt, wie das Write-Ahead-Log von PostgreSQL eine Wiederherstellung zu jedem beliebigen vergangenen Zeitpunkt ermöglicht:
- WAL-Archivierung erfasst jede Änderung nach einem Basis-Backup.
- Wiederherstellungsziele können ein Zeitstempel, eine Transaktions-ID, eine LSN oder ein benannter Wiederherstellungspunkt sein.
- Der Wiederherstellungsprozess stellt das Basis-Backup wieder her, wendet archivierte WAL-Daten erneut an und stuft den Server hoch, sobald das Ziel erreicht ist.
- Die Überprüfung bestätigt die Datenintegrität, bevor wieder Datenverkehr zugelassen wird.
- Bewährte Vorgehensweisen umfassen regelmäßige Wiederherstellungstests, die Überwachung der Archivierung und die Speicherung des WAL an einem externen Standort.
PITR ist Ihr Sicherheitsnetz gegen menschliche Fehler und Datenbeschädigung — beherrschen Sie PITR, beherrschen Sie auch die Notfallwiederherstellung.
Lerne SQL mit einem KI-Tutor — kostenlos
Schreibe und führe echten Code in deinem Browser aus, bekomme sofortige Hilfe von einem 24/7 KI-Tutor und setze dein Lernen im Web oder in der App fort.
- Kurse
- 46
- Lektionen
- 183
Häufig gestellte Fragen
Ist die Lektion „Wiederherstellung zu einem bestimmten Zeitpunkt“ kostenlos?
Ja — der vollständige Text von „Wiederherstellung zu einem bestimmten Zeitpunkt“ 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 „Wiederherstellung zu einem bestimmten Zeitpunkt“?
Mit WAL jeden beliebigen Zeitpunkt wiederherstellen 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 2 von 4.
Wie lange dauert die Lektion „Wiederherstellung zu einem bestimmten Zeitpunkt“?
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