Widerherstellen der Datenbank aus Backup und WAL (Write-Ahead Logging)

Hintergrund

Mit Hilfe des pg_basebackup erzeugen wir ein vollständiges, konsistentes Abbild des Postgres Clusters inkl. der DBMS Konfigurationsdateien und zusätzlich erzeugen wir WAL-Dateien, die jedwede Änderung an den Daten der Datenbank dokumentiert und diese Änderungen in den WAL-Dateien ablegt.

Vorteile

• Komplettes Cluster-Backup: Es sichert das gesamte Datenbank-Cluster, einschließlich aller darin enthaltenen Datenbanken.
• Konsistenz: Gewährleistet ein konsistentes Abbild des gesamten Datenbank-Clusters.
• Geschwindigkeit: Typischerweise schneller für große Datenbanken, da es auf Dateisystemebene arbeitet.
• Sicherheit: Es lässt sich die Datenbank zu einem Zeitpunkt wiederherstellen an dem Änderungen an den Daten vorgenommen wurden, die durch das Komplett-Backup nicht erfasst werden.

Nachteile

• Größe: Die Backups können groß sein, da sie alle Datenbankdateien einschließlich leerem Speicherplatz enthalten.
• Flexibilität: Hoch flexibel bei der Wiederherstellung einzelner Datenbanken oder Tabellen, da diese Änderungen in den WAL-Dateien vermerkt ist.

Einrichtung des Point in Time Recovery

Vorraussetzung ist ein existierendes Backup

Beschrieben bei: "Backup erstellen und Datenbank aus Backup wieder herstellen"

Konfiguration der "postgresql.conf"

Wir melden uns an den container an

docker exec -t <projekt>-postgres-1 bash

und bearbeiten die postgresql.conf

nano /var/lib/posgresql/data/pgdata/postgresql.conf

Folgende Werte werden Einkommentiert (# entfernen) und geändert:

wal_level = replica

max_wal_size = 1GB

min_wal_size = 80MB

archive_mode = on

archiv_command = 'lftp -e "set ftp:ssl-protect-data true; set ssl:verify-certificate false;set ftp:ssl-force true; put %p -o /backup/<projekt>-wal/%f; bye" -u FTPUser,FTPPass ftp://ftpservername.de'

archive_timeout = 10800

Nach dem Neustart des Containers und damit der PostgreSQL Datenbank, erfolgt die Erstellung der WAL-Dateien auf dem FTP Server.

Wiederherstellung der Datenbank zu einem gewünschten Zeitpunkt

Um nun die Datenbank zu einem Zeitpunkt/Zustand wiederherzustellen wie diese nach falschen Eingaben oder Löschungen von Daten besteht wird folgendermaßen vorgegangen:

1. Holen der gesicherten WAL-Dateien vom FTP-Server

docker exec -t <projekt>-postgres-1 lftp -e "set ftp:ssl-protect-data true; set ssl:verify-certificate false;set ftp:ssl-force true; mget -O /tmp/werner/wal/ /backup/<project>-wal/*; bye" -uFTPUser,FTPPass ftp://ftpservername.de'

Anmelden an den Container

docker exec -t <projekt>-postgres-1 bash

und setzen der Berechtigung "postgres"

chown -R postgres:postgres /tmp/werner/wal

Stoppen des Containers

docker compose -p <projekt> down

2. Recovery Container starten und Sicherung einspielen

docker compose -p <projekt> --profile recovery up postgres-recovery -d

und holen die Datensicherung

docker exec -t <projekt>-postgres-1 lftp -e "set ftp:ssl-protect-data true; set ssl:verify-certificate false;set ftp:ssl-force true; mget -O /tmp/werner/ /backup/<project>-DayOfTheWeek.tar.gz; bye" -uFTPUser,FTPPass ftp://ftpservername.de'

Die Datensicherung kann man auch alternativ mit diesem Befehl in den Container kopieren

docker cp /tmp/<project>-DayOfTheWeek.tar.gz <projekt>-postgres-recovery-1:/tmp/werner

Anmelden am Recovery Container

docker exec -it <projekt>-postgres-recovery-1 bash

und das TAR entpacken sowie die Rechte setzen

cd /tmp/werner
tar xfz <project>-DayOfTheWeek.tar.gz
chown -R postgres:postgres /tmp/werner/

Das eigentliche Datenverzeichniss leeren

rm -R /var/lib/postgresql/data/pgdata/*

und die entpackten Daten der Sicherung nach pgdata verschieben. Damit die Berechtigungen erhalten bleiben, den Befehl "umask" verwenden.

umask 000
mv /tmp/werner/sicherungsdaten/* /var/lib/postgresql/data/pgdata/

3. Bearbeiten der postgresql.conf und anlegen der Recovery-Datei

Folgende Werte werden Einkommentiert (# entfernen) und geändert:

restore_command = 'cp /tmp/werner/wal/%f %p'

recovery_target_time = '2024-11-18 08:15:00 UTC' (Beispiel, hier benötigtes Datum und Zeit setzen)

recovery_target_action = 'promote'

Zum Schluss noch die recovery.signal im pgdata Ordner erstellen

touch recovery.signal

Stoppen des Recovery Containers

docker compose -p <projekt> down

Anschliessend erfolgt der Start der Anwendung im normalen Modus durch die Ausführung des scripts im übergeordneten Verzeichnis

./start.sh <project> .env.<project> run

Nach erfolgtem Start des Containers, die Recovery-Einträge in der postgresql.conf wieder auskommentieren (# davorsetzen) sonst würde bei einem Neustart des Containers wieder das recovery anlaufen.