PostgreSQL-Backup wiederherstellen, ohne die laufende Datenbank zu überschreiben

Donnerstag, 16:30 Uhr. Ein Deploy um 14:10 enthielt eine Migration, die in invoices die Spalte paid_at für 2.300 Zeilen auf NULL gesetzt hat. Seitdem bekommen zahlende Kunden Mahnungen. Das Backup von 3 Uhr nachts ist da, der Reflex liegt nahe: pg_restore --clean -d shop backup.dump gegen die Produktion. Die 2.300 Zeilen wären repariert – und alles seit 3 Uhr weg: 412 neue Bestellungen, 90 Registrierungen, jede Zahlung des Tages.

Ein PostgreSQL-Backup wiederherzustellen heißt, den gesicherten Stand einer Datenbank zurückzuschreiben – und die wichtigste Entscheidung ist, wohin. Wer nach „postgresql backup wiederherstellen“ sucht, findet meist den Befehl, selten diese Frage. Die sichere Antwort ist fast immer: in eine neue Datenbank.

Welche Arten von PostgreSQL-Backups gibt es?

Die PostgreSQL-Dokumentation unterscheidet drei Ansätze, und sie bestimmen, was Sie wiederherstellen können:

• SQL-Dump (logisch). pg_dump exportiert genau eine Datenbank, konsistent auch im laufenden Betrieb. Im Custom-Format (-Fc) ist der Dump komprimiert und lässt sich mit pg_restore gezielt zurückspielen; ein reiner SQL-Dump gehört in psql. Rollen fehlen – die exportiert nur pg_dumpall.
• Dateisystem-Backup oder Snapshot (physisch). Eine Kopie der Datendateien oder des Volumes, immer der ganze Cluster mit allen Datenbanken.
• Kontinuierliche Archivierung (Point-in-Time Recovery). Basis-Backup plus archiviertes WAL: Wiederherstellung auf jeden Zeitpunkt seit dem Basis-Backup – laut Doku ebenfalls nur für den ganzen Cluster.

Gezielt in eine einzelne Datenbank zurückspielen lässt sich also nur ein logisches Backup.

Warum gehört die Wiederherstellung in eine neue Datenbank?

• Die Produktion läuft weiter. Bestellungen, Zahlungen und Registrierungen seit dem Backup bleiben erhalten.
• Sie können vergleichen. Alter und neuer Stand liegen nebeneinander; Sie sehen, welche Zeilen wirklich betroffen sind.
• Sie holen nur zurück, was fehlt. Meist eine Tabelle oder ein paar tausend Zeilen, nicht die ganze Datenbank.
• Ein Fehlschlag kostet nichts. Bricht die Wiederherstellung ab, ist eine leere Datenbank kaputt, nicht die Produktion.
• Sie schalten bewusst um. Ist der alte Stand wirklich besser, wechseln Sie die Verbindung zu einem Zeitpunkt Ihrer Wahl.

Wie stelle ich ein Backup mit pg_restore in eine neue Datenbank wieder her?

1. Prüfen, was Sie haben. pg_restore -l backup.dump listet den Inhalt eines Archivs. Scheitert das, ist es vermutlich ein reiner SQL-Dump für psql.
2. Leere Datenbank anlegen. CREATE DATABASE shop_restore TEMPLATE template0; – template0 ist laut Doku die Vorlage ohne lokale Ergänzungen.
3. Zurückspielen. pg_restore --no-owner --no-privileges -d shop_restore -j 4 backup.dump. --no-owner macht den verbindenden Benutzer zum Eigentümer aller Objekte, --no-privileges lässt die GRANTs weg, -j 4 lädt parallel (nur Custom- und Directory-Format), -t invoices holt eine einzelne Tabelle.
4. Prüfen. Zeilenzahlen und SELECT max(created_at) FROM orders; zeigen, welchen Stand Sie vor sich haben.

Nicht hierher gehören zwei Optionen: --clean, laut Doku zum Überschreiben einer bestehenden Datenbank gedacht, und --create. Damit dient die Datenbank aus -d nur noch für CREATE DATABASE, die Daten landen im Namen aus dem Archiv – auf demselben Server also in der Produktion, mit --clean vorher gelöscht.

Eigentümer und Rechte: Warum sieht der Lesenutzer plötzlich nichts?

Ohne --no-owner will pg_restore jedem Objekt seinen alten Eigentümer geben; das scheitert laut Doku ohne Superuser-Rechte, sofern Sie nicht als genau dieser Eigentümer verbunden sind. Mit --no-owner gehört alles dem, der wiederherstellt. Die Folgen:

• Verbindet sich die App als anderer Benutzer, bekommt sie „permission denied“, bis sie Rechte erhält oder als neuer Eigentümer verbindet.
• --no-privileges bringt keine GRANTs mit, auch nicht die Ihres Lesenutzers für Auswertungen – der sieht nichts.
• ALTER DEFAULT PRIVILEGES gilt laut Doku nur für Objekte, die eine bestimmte Rolle künftig anlegt. Für einen neuen Eigentümer setzen Sie sie neu, sonst ist der Lesenutzer nach der nächsten Migration wieder blind.

Mehr zu Rollen und Default Privileges pro Service im Beitrag Eine Datenbank pro Service.

Zurückkopieren oder umschalten?

Meist ist nur ein Teil kaputt. Dann kopieren Sie zurück, mit einem Haken: Eine PostgreSQL-Verbindung sieht laut Doku nur eine Datenbank, ein Join zwischen Produktion und Wiederherstellung geht nicht. Der Weg führt über eine Datei:

1. Wiederhergestellte Datenbank: \copy (SELECT id, paid_at FROM invoices) TO 'fix.csv' CSV
2. Produktion: Hilfstabelle fix anlegen und mit \copy fix FROM 'fix.csv' CSV füllen.
3. In einer Transaktion UPDATE invoices i SET paid_at = f.paid_at FROM fix f WHERE i.id = f.id AND i.paid_at IS NULL;, Zeilenzahl prüfen, dann COMMIT.

Ist die ganze Datenbank unbrauchbar, schalten Sie um: DATABASE_URL der App auf die neue Datenbank, neu starten; was seit dem Backup in der alten landete, ziehen Sie bewusst nach. Danach aufräumen: die alte Datenbank einige Tage als Beleg behalten, dann löschen – und heruntergeladene Dumps mit Kundendaten von Laptops entfernen.

Was bietet Scaleway Managed Database für PostgreSQL?

• Automatische Backups sind bei Block Storage Snapshots der ganzen Instanz, standardmäßig täglich und sieben Tage aufbewahrt; wiederhergestellt wird in eine neue, zusätzlich berechnete Instanz.
• Logische Backups einer Datenbank legen Sie bei Block Storage manuell an (laut Doku bis 585 GB Speicher); bei lokalem Speicher sind die automatischen Backups logisch. Beim Wiederherstellen fragt die Konsole: ursprüngliche oder neue Datenbank? Nehmen Sie die neue.
• Export: „Prepare export“, dann „Download“. PostgreSQL-Backups kommen im komprimierten PostgreSQL-Format; pg_restore -l zeigt, ob es ein Archiv ist.

Wie Clusterward das umsetzt

Für Datenbanken, die Clusterward verwaltet (siehe Managed Datenbanken), zeigt die Instanzseite die Karte „Backups“: automatische Snapshots mit änderbarem Zeitplan und Datenbank-Backups, die Sie mit „Back up now“ anlegen und 7, 30 oder 90 Tage behalten. Heruntergeladen wird über einen kurzlebigen Link, den Clusterward nicht speichert; Snapshots stellen Sie in der verlinkten Scaleway-Konsole wieder her.

„Restore into a new database“ überschreibt nie die laufende Datenbank: Nach Eingabe des Datenbanknamens legt Clusterward daneben db_<name>_r<datum> mit eigenem Benutzer usr_<name>_r<datum> und generiertem Passwort an und lässt Scaleway das Backup hineinspielen. Meldet Scaleway „fertig“, setzt Clusterward die Rechte des neuen Benutzers erneut – die Wiederherstellung legt Tabellen nach der ersten Freigabe an. Vergleichen können Sie nur lesend in der SQL-Konsole; zum Umschalten zeigt die App auf die neue Datenbank, oder Sie weisen sie dem Service zu und starten neu. Die alte bleibt, bis Sie sie löschen. Mehr unter Backups & Wiederherstellung.

Fazit

Ein Backup über die laufende Datenbank zu spielen, repariert den Fehler und vernichtet alles seitdem. In eine neue Datenbank wiederhergestellt, wird es zur Quelle: vergleichen, gezielt zurückkopieren, bewusst umschalten. Planen Sie Eigentümer und Rechte ein, bevor die nächste Mahnung rausgeht.

Ihre Wiederherstellung einmal durchspielen? Erzählen Sie uns, wie Ihre Datenbanken heute gesichert werden. Wir zeigen, wie eine Wiederherstellung ohne Risiko für die Produktion aussieht. Frage stellen →

Quellen und weiterführende Links

• PostgreSQL-Doku: Backup and Restore (englisch)
• PostgreSQL-Doku: pg_restore (englisch)
• PostgreSQL-Doku: ALTER DEFAULT PRIVILEGES (englisch)
• Scaleway: Backups von Database Instances verwalten (englisch)
• Scaleway: Snapshots verwalten (englisch)