Ein häufiger Fall aus der Praxis, der ein klassisches Point In Time Recovery (PITR) auf einem temporären Server beschreibt.
Aufgabe und Ausgangssituation
Eine PostgreSQL 16.9er Produktionsdatenbank läuft auf einer Maschine db-srv8 mit Oracle Linux 8.10. Das Backup wird mit pgbackrest umgesetzt. Aufgrund eines Anwenderfehlers soll eine Tabelle mit Namen robots wiederhergestellt werden. Dies soll auf einem temporären Server – ebenfalls ein Oracle Linux 8.10 – geschehen. Die Datenbank soll zum Zeitpunkt 2025-06-30 16:00 restored, die Tabelle robots exportiert und diese dann in die Prodsuktionsdatenbank importiert werden. Auf dem temporären Server db-srv10 ist bereits die Software PostgreSQL 16.9 und das Backup Tool pgbackrest installiert. Es ist jedoch kein PostgreSQL Cluster vorhanden und es existiert auch keine pgbackrest-Konfiguration.
Die beschriebene Situation ist ein häufiger Fall aus der Praxis und beschreibt ein klassisches Point In Time Recovery (PITR) auf einem temporären Server.
Vorhandene Backups der Produktions DB
pgbackrest --config=/opt/pgsql/etc/pgbackrest/pgbackrest.conf info
stanza: my_stanza
status: ok
cipher: none
db (current)
wal archive min/max (16): 000000010000000000000072/00000002000000000000007B
full backup: 20250630-082434F
timestamp start/stop: 2025-06-30 08:24:34+02 / 2025-06-30 08:24:52+02
wal start/stop: 000000010000000000000072 / 000000010000000000000072
database size: 1.1GB, database backup size: 1.1GB
repo1: backup set size: 193.5MB, backup size: 193.5MB
incr backup: 20250630-082434F_20250630-170032I
timestamp start/stop: 2025-06-30 17:00:32+02 / 2025-06-30 17:00:34+02
wal start/stop: 000000010000000000000076 / 000000010000000000000076
database size: 1.1GB, database backup size: 2.3MB
repo1: backup set size: 193.6MB, backup size: 233.5KB
backup reference total: 1 full
full backup: 20250630-175835F
timestamp start/stop: 2025-06-30 17:58:35+02 / 2025-06-30 17:58:52+02
wal start/stop: 000000020000000000000076 / 000000020000000000000077
database size: 1.1GB, database backup size: 1.1GB
repo1: backup set size: 193.5MB, backup size: 193.5MB
incr backup: 20250630-175835F_20250701-172029I
timestamp start/stop: 2025-07-01 17:20:29+02 / 2025-07-01 17:20:32+02
wal start/stop: 00000002000000000000007A / 00000002000000000000007A
database size: 1.1GB, database backup size: 2.2MB
repo1: backup set size: 193.6MB, backup size: 216.2KB
backup reference total: 1 full
PITR
Notwendige Directories anlegen
sudo mkdir -p /opt/pgsql/data/log
sudo chown -vR postgres.postgres /opt/pgsql/data
sudo chmod 700 /opt/pgsql/data
sudo mkdir /opt/pgsql/etc
sudo chown postgres.postgres /opt/pgsql/etc
sudo mkdir /opt/pgsql/bak
sudo chown postgres.postgres /opt/pgsql/bak
sudo mkdir /opt/pgsql/log
sudo chown postgres.postgres /opt/pgsql/log
Leeren PostgreSQL-Cluster initialisieren
export PATH=$PATH:/opt/pgsql/16_9/bin
/opt/pgsql/16_9/bin/initdb -D /opt/pgsql/data
Archive Mode anschalten und konfigurieren
vi /opt/pgsql/data/postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --config=/opt/pgsql/etc/pgbackrest/pgbackrest.conf --stanza=my_stanza --pg1-path=/opt/pgsql/data archive-push %p'
Leeren PostgreSQL-Cluster starten
/opt/pgsql/16_9/bin/pg_ctl -D /opt/pgsql/data -l /opt/pgsql/data/log/postgres_169.log start
pgbackrest Konfiguration kopieren und anpassen
[postgres@db-srv8]# scp -vr /opt/pgsql/etc/* postgres@db-srv10:/opt/pgsql/etc/
[root@db-srv10 ~]# ln -s /opt/pgsql/etc/pgbackrest/pgbackrest.conf /etc/pgbackrest.conf
vi /opt/pgsql/etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/opt/pgsql/bak
repo1-retention-full=2
process-max=2
log-level-console=info
log-level-file=debug
log-path=/opt/pgsql/log/pgbackrest
[my_stanza]
pg1-path=/opt/pgsql/data
# Pfade für repo1-path , pg1-path und log-path erzeugen und/oder ggf. in der pgbackrest.conf anpassen
# mkdir -p /opt/pgsql/log/pgbackrest
Backups von der Produktions-Maschine auf die Temporäre Maschine kopieren
[postgres@db-srv10 ~]# rsync -avz postgres@db-srv8:/opt/pgsql/bak/ /opt/pgsql/bak/
Stanza auf dem Zielsystem anlegen und prüfen
pgbackrest --stanza=my_stanza --log-level-console=info stanza-create
pgbackrest --stanza=my_stanza --log-level-console=info check
pgbackrest --config=/opt/pgsql/etc/pgbackrest/pgbackrest.conf info
Recovery auf den gewünschten Zeitpunkt vorbereiten
Backup bestimmen, das dem Zielzeitpunkt am nächsten, aber davor liegt. In konkreten Fall:
full backup: 20250630-082434F (bis 08:24 Uhr)
incr backup: 20250630-082434F_20250630-170032I (bis 17:00 Uhr)
Das incremental Backup endet um 17:00 Uhr, Der Zeitpunkt der Wiederherstellung liegt aber 1 Stunde davor auf 16:00 Uhr. Das heißt, es muss das Full Backup von 08:24 Uhr plus das Incremental Backup bis 17:00 Uhr wiederhergestellt und dann einen PITR (Point-in-Time Recovery) bis 16:00 Uhr durchführt werden.
PostgreSQL-Cluster auf Zielsystem stoppen
/opt/pgsql/16_9/bin/pg_ctl -D /opt/pgsql/data -l /opt/pgsql/data/log/postgres_169.log stop
Point-in-Time Recovery (PITR)
pgbackrest --stanza=my_stanza --delta --type=time --target="2025-06-30 16:00:00+02" --log-level-console=info restore
# Wichtig Ausgabe mit successfully!
# --> INFO: restore command end: completed successfully (5724ms)
PostgreSQL-Cluster auf Zielsystem starten
/opt/pgsql/16_9/bin/pg_ctl -D /opt/pgsql/data -l /opt/pgsql/data/log/postgres_169.log start
Logfile prüfen
view /opt/pgsql/data/log/postgres_169.log
2025-07-02 10:57:17.984 P00 INFO: archive-get command end: completed successfully (3ms)
2025-07-02 10:57:17.985 CEST [1841] LOG: recovery stopping before commit of transaction 35344, time 2025-07-01 17:19:22.478816+02
2025-07-02 10:57:17.985 CEST [1841] LOG: pausing at the end of recovery
2025-07-02 10:57:17.985 CEST [1841] HINT: Execute pg_wal_replay_resume() to promote.
2025-07-02 11:02:17.090 CEST [1839] LOG: restartpoint starting: time
2025-07-02 11:02:18.705 CEST [1839] LOG: restartpoint complete: wrote 17 buffers (0.1%); 0 WAL file(s) added, 6 removed, 0 recycled; write=1.607 s, sync=0.001 s, total=1.616 s; sync files=4, longest=0.001 s, average=0.001 s; distance=98304 kB, estimate=98304 kB; lsn=0/78000098, redo lsn=0/78000060
2025-07-02 11:02:18.705 CEST [1839] LOG: recovery restart point at 0/78000060
PostgreSQL kann nur an Transaktionsgrenzen stoppen. Wenn zwischen dem Zielzeitpunkt und der nächsten Transaktion keine Aktivität war, dann ist die letzte Transaktion, die vor dem Zielzeitpunkt committed wurde, der letzte Stand, den PostgreSQL exakt wiederherstellen kann. Wenn die nächste Transaktion nach dem Zielzeitpunkt committed wird, steht im Log „recovery stopping before commit … time …“, und diese Zeit ist dann nach dem Zielzeitpunkt. Es wird niemals eine Transaktion wiederhergestellt, die nach dem Zielzeitpunkt committed wurde – aber der Log-Eintrag zeigt die Zeit der ersten Transaktion nach deinem Zielzeitpunkt an. Das bedeutet:
- Die Datenbank ist im Zustand direkt vor der ersten Transaktion, die nach deinem Zielzeitpunkt committed wurde.
- Es ist technisch nicht möglich, exakt „in die Mitte“ zwischen Transaktionen zu gehen.
Zustand der Datenbank nach dem Restore
Die Datenbank ist nach dem Restore und dem Recovery immer noch im Recovery-Modus. Im Log steht typischerweise eine Meldung wie: pausing at the end of recovery und HINT: Execute pg_wal_replay_resume() to promote. Erst wenn eine Promotion durchgeführt wurde, ist die Datenbank wieder voll funktionsfähig und man kann z.B. mit SELECT auf Daten zugreifen. Den Status kann man jedoch prüfen mit:
psql -p 5432
SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
Hier ist also noch true für pg_is_in_recovery zu sehen und unter /opt/pgsql/data ist zusätzlich eine recovery.signal-Datei zu finden.
Recovery abschließen und Datenbank öffnen
SELECT pg_wal_replay_resume();
SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
Tabelle robots wiederherstellen
Tabelle robots auf temporärem System prüfen
psql -p 5432
\c my_db
select * from robots;
id | name | fullname | nickname
---+----------+--------------------------------+------------
1 | R2-D2 | R2-D2 Astromech Droid | Artoo
2 | C-3PO | C-3PO Protocol Droid | Threepio
3 | BB-8 | BB-8 Astromech Droid | Bebo
4 | T-800 | Cyberdyne Systems Model 101 | Terminator
5 | T-1000 | Prototype Liquid Metal Android | Liquid
6 | WALL-E | Waste Allocation Load Lifter | Wally
7 | EVE | Extraterrestrial Vegetation | Eve
8 | Data | Data Soong-Type Android | Mr. Data
9 | Bender | Bender Bending Rodriguez | Bendie
10 | K-2SO | K-2SO Imperial Droid | K
11 | HK-47 | Assassin Droid HK-47 | Meatbag-Hunter
12 | IG-88 | IG-88 Assassin Droid | Iggy
13 | Optimus | Optimus Prime | Prime
14 | Bumblebee| Bumblebee Autobot | Bee
15 | R5-D4 | R5-D4 Astromech Droid | Red
16 | Chappie | Scout Robot 22MS | Chap
17 | Robby | Robby the Robot | Rob
18 | ASIMO | Advanced Step in Mobility | A-S
19 | GLaDOS | Genetic Lifeform OS | Glad
20 | Claptrap | CL4P-TP General Purpose Robot | Clappy
21 | ED-209 | Enforcement Droid Series 209 | Eddie
22 | Marvin | Paranoid Android | Marvin
23 | Kara | AX400 Android | Kara
24 | Sonny | Sonny NS-5 | Sunny
(24 rows)
Tabelle robots exportieren
cd /opt/pgsql/data
# Passendes Format für pg_restore
pg_dump -t robots -Fc -f robots.dump my_db
# Oder als SQL
pg_dump -t robots -f robots.sql my_db
Tabelle robots in Prod-System prüfen
Prüfen, ob es im Prod-System Fremdschlüssel auf die Tabelle robots gibt.
SELECT
conname AS constraint_name,
conrelid::regclass AS referencing_table,
a.attname AS referencing_column
FROM
pg_constraint AS c
JOIN pg_class ON c.conrelid = pg_class.oid
JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE
c.contype = 'f'
AND c.confrelid = 'robots'::regclass;
constraint_name | referencing_table | referencing_column
-----------------+-------------------+--------------------
(0 rows)
Sollte es Fremdschlüssel-Constraints geben, diese entfernen.
ALTER TABLE andere_tabelle DROP CONSTRAINT constraint_name;
Tabelle auf dem Produktionssystem truncaten
TRUNCATE TABLE robots;
Falls andere Tabellen per Fremdschlüssel auf robots verweisen, müsste man das mit CASCADE erzwingen. Dabei werden auch die abhängigen Zeilen in den referenzierenden Tabellen gelöscht. TRUNCATE TABLE robots CASCADE; ist also mit Vorsicht zu betrachten!
Tabelle auf Produktionssystem kopieren und importieren
# Mit pg_restore
pg_restore -v -p 5432 -U postgres -d my_db --data-only -t robots robots.dump
# Als SQL
psql -d ziel_datenbank -f /tmp/robots.sql
Nach dem Import ggf. die Fremdschlüssel bei anderen Tabellen wieder hinzufügen.
ALTER TABLE andere_tabelle
ADD CONSTRAINT constraint_name FOREIGN KEY (spalte) REFERENCES robots(id);