PostgreSQL DB auf Dateiebene wiederherstellen

Da hab ich doch schon länger auf einer Datenbank gearbeitet, bei der ich glaubte ich könnte auf ein gesondertes Backup verzichten, denn wir hätten ja auf jeden Fall das Backup des Servers. Das ist zwar richtig, aber es laufen viele Datenbanken auf meinem Linux-Server. Für die wichtigen habe ich ein Backup per pg_dump eingerichtet. Natürlich geht dann doch etwas schief und ich musste eine einzelne Datenbank eines Postgres-Clusters wiederherstellen. "Und nu?" hab ich mir gedacht. Kann ich die Dateien der einzelnen Datenbank wiederherstellen?

Ja, das geht, obwohl es etwas heikel ist. Natürlich benötigt man den Zugriff auf den von PostgreSQL verwendeten Bereich des Dateisystems. Also wohl zumeist Root-Rechte. OK - die hatte ich. Und ich hatte ein Backup des Dateisystems. Damit kann der zur Datenbank gehörige Zweig des Verzeichnisbaums wiederhergestellt werden. Aber welcher ist das?

Wo die Dateien zu finden sind kann mit der folgenden Abfrage

SHOW data_directory;

ermittelt werden. Zusätzlich wird die OID der Datenbank benötigt:

SELECT oid, datname FROM pg_database WHERE datname = 'deine_datenbank';

Der Pfad zur Datenbank ist dann unter

<data_directory>/base/<OID>

zu finden. Konkret war das in meinem Fall:

/var/lib/postgres/16/main/base/34142

Als ich mir die geöffneten Dateien in 34142 mit lsof angesehen hatte, ist mir aufgeallen, dass die Dateien geschlossen werden, sobald die letzte Datenbankverbindung beendet wird. Ich war also guter Dinge, dass ich diese Dateien bei laufendem Server wiederherstellen konnte. Da ich alle Verbindungen beenden konnte habe ich daraufhin das Verzeichnis aus dem Backup wiederhergestellt und dann sicherheitshalber den Postgres-Cluster neu gestartet.

Et Voilá: Meine Datenbank war wieder im alten Zustand und funktionierte. Eine zugegebenermaßen abenteuerliche Methode für den Notfall. Ich werde die Datenbank künftig über ein Skript per pg_dump sichern 😎