Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 9: Physische Backups und PITR

Ziel: Du sicherst pg1 physisch von pg2 aus, archivierst das WAL und holst eine gelöschte Tabelle per Point-in-Time-Recovery zurück – erst von Hand, dann mit pgBackRest. · Dauer: ca. 120 min · Voraussetzung: Tag 8

Briefing

  • pg_dump (Tag 8) sichert Inhalte, pg_basebackup kopiert den ganzen Cluster Datei für Datei – schneller und die Basis für Standbys.
  • Ein Basis-Backup plus lückenloses WAL-Archiv erlaubt Recovery auf jede Sekunde danach: Point-in-Time-Recovery (PITR).
  • Wiederhergestellt wird nie über pg1, sondern in einen zweiten Cluster 18/pitr auf pg2 (Port 5433).
  • pgBackRest automatisiert das: Archiv, Voll-/Differenz-/Inkrementalbackups, Aufbewahrung, Restore.
  pg1 .182  18/main                         pg2 .123
  WAL ---- archive_command (rsync) ----->  ~postgres/wal-archive/
  Daten <--- pg_basebackup (Port 5432) ---  ~postgres/backup/
                                            18/pitr :5433  <- Restore + WAL-Replay

Drill 1: Backup-Rolle und Zugang

pg_basebackup spricht das Replikationsprotokoll. Es braucht eine Rolle mit REPLICATION (kein Superuser) und eine pg_hba-Zeile für die Pseudo-Datenbank replication. Auf pg1:

psql -c "CREATE ROLE tag09_backup LOGIN REPLICATION PASSWORD 'lab-backup-123';"
echo 'host  replication  tag09_backup  172.16.0.123/32  scram-sha-256' \
  | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -c "SELECT line_number AS nr, database, user_name, address, error
  FROM pg_hba_file_rules WHERE 'replication' = ANY(database);"

Erwartet:

CREATE ROLE
host  replication  tag09_backup  172.16.0.123/32  scram-sha-256
t
 nr  |   database    |   user_name    |   address    | error
-----+---------------+----------------+--------------+-------
 130 | {replication} | {all}          |              |
 131 | {replication} | {all}          | 127.0.0.1    |
 132 | {replication} | {all}          | ::1          |
 134 | {replication} | {tag09_backup} | 172.16.0.123 |
(4 rows)

replication in pg_hba.conf passt nur auf Replikationsverbindungen, nicht auf eine Datenbank dieses Namens. Die Spalte error bleibt leer – die Zeile ist gültig.

Auf pg2 kommt das Passwort in die .pgpass des Linux-Benutzers postgres, der die Backups schreibt (Lab-Wert):

echo '172.16.0.182:5432:replication:tag09_backup:lab-backup-123' \
  | sudo -u postgres tee /var/lib/postgresql/.pgpass
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo -u postgres mkdir -p /var/lib/postgresql/backup

Fortschritt speichern? Kostenlos registrieren

Drill 2: Basis-Backup mit pg_basebackup

Weiter auf pg2. Erst als komprimiertes Tar (-Ft -z), dann als normales Verzeichnis (-Fp). -X stream holt das WAL, das während des Backups entsteht, über eine zweite Verbindung mit – das Backup ist damit allein startfähig. -c fast erzwingt sofort einen Checkpoint, statt auf den nächsten zu warten:

sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup \
  -D /var/lib/postgresql/backup/tar1 -Ft -z -X stream -c fast -P -v
sudo -u postgres ls -lh /var/lib/postgresql/backup/tar1

Erwartet (gekürzt):

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_4992"
71812/71812 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/3456CA8
pg_basebackup: base backup completed
-rw------- 1 postgres postgres  394K Nov 19 04:54 backup_manifest
-rw------- 1 postgres postgres  8.6M Nov 19 04:54 base.tar.gz
-rw------- 1 postgres postgres 1013K Nov 19 04:54 pg_wal.tar.gz

LSNs, Segmentnamen und Transaktionsnummern in diesem Kapitel stammen aus einem früheren Laborstand. Nach Tag 5 bis 8 sind sie bei dir deutlich größer – die Zahlen weichen bei dir ab, die Abläufe nicht.

Jetzt im Plain-Format, mit SHA-256 statt CRC32C im Manifest:

sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup \
  -D /var/lib/postgresql/backup/plain1 -Fp -X stream -c fast \
  --manifest-checksums=SHA256
sudo -u postgres du -sh /var/lib/postgresql/backup/*
sudo -u postgres cat /var/lib/postgresql/backup/plain1/backup_label

Erwartet (gekürzt):

86M     /var/lib/postgresql/backup/plain1
9.9M    /var/lib/postgresql/backup/tar1
START WAL LOCATION: 0/4000028 (file 000000010000000000000004)
CHECKPOINT LOCATION: 0/4000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-11-19 04:54:07 UTC

Plain ist sofort startbar und Voraussetzung für inkrementelle Backups (Drill 6), Tar spart Platz. Nebenbei prüft pg_basebackup beim Lesen die Prüfsumme jeder Datenseite (data_checksums = on, Tag 0) und bricht bei Fehlern ab – abschalten ließe sich das mit --no-verify-checksums.

Fortschritt speichern? Kostenlos registrieren

Drill 3: Backups prüfen mit pg_verifybackup

Das Manifest listet jede Datei mit Größe und Prüfsumme. pg_verifybackup vergleicht das Backup damit und liest zusätzlich das enthaltene WAL. Es hat keinen Wrapper in /usr/bin, daher der volle Pfad:

V=/usr/lib/postgresql/18/bin/pg_verifybackup
sudo -u postgres $V /var/lib/postgresql/backup/plain1
sudo -u postgres $V /var/lib/postgresql/backup/tar1
sudo -u postgres $V -n /var/lib/postgresql/backup/tar1

Erwartet:

backup successfully verified
pg_verifybackup: error: pg_waldump cannot read tar files
pg_verifybackup: hint: You must use -n/--no-parse-wal when verifying a tar-format
backup.
backup successfully verified

Tar-Backups prüft pg_verifybackup seit PostgreSQL 18, das WAL darin aber nicht. Jetzt eine Kopie absichtlich beschädigen – ein Byte überschreiben, ein WAL-Segment löschen:

B=/var/lib/postgresql/backup
sudo -u postgres cp -a $B/plain1 $B/kaputt
F=$(sudo -u postgres find $B/kaputt/base -type f -size +100k | head -1)
echo x | sudo -u postgres dd of=$F bs=1 seek=5000 conv=notrunc status=none
sudo -u postgres rm $B/kaputt/pg_wal/000000010000000000000004
sudo -u postgres $V $B/kaputt; echo rc=$?
sudo -u postgres rm -rf $B/kaputt

Erwartet:

pg_verifybackup: error: checksum mismatch for file "base/16431/1259"
pg_waldump: error: could not find any WAL file
pg_verifybackup: error: WAL parsing failed for timeline 1
rc=1

Ein Backup ohne Prüfung ist eine Hoffnung. pg_verifybackup gehört direkt hinter jedes Backup ins Monitoring – den echten Beweis liefert aber erst ein Restore.

Fortschritt speichern? Kostenlos registrieren

Drill 4: WAL-Archivierung auf pg2

Für PITR muss jedes fertige WAL-Segment (16 MB) den Server verlassen. Ziel ist pg2, nicht ein Verzeichnis auf pg1: Fällt pg1 samt Platte aus, ist ein lokales Archiv mit weg. Auf pg2 das Zielverzeichnis:

sudo -u postgres mkdir -m 700 /var/lib/postgresql/wal-archive

Auf pg1 kommen die Einstellungen in eine eigene Datei unter conf.d statt per ALTER SYSTEM: postgresql.auto.conf liegt im Datenverzeichnis und reist mit jedem Basis-Backup mit – ein wiederhergestellter Klon würde sonst ins selbe Archiv schreiben. archive_mode braucht einen Neustart:

cat <<'CONF' | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag09-archiv.conf
archive_mode = on
archive_command = 'rsync -a --ignore-existing %p 172.16.0.123:wal-archive/%f'
CONF
sudo systemctl restart postgresql@18-main
psql -Atc 'SELECT pg_walfile_name(pg_current_wal_lsn())'
psql -Atc 'SELECT pg_switch_wal()'
psql -x -c 'SELECT archived_count, last_archived_wal, failed_count
  FROM pg_stat_archiver'

Erwartet:

archive_mode = on
archive_command = 'rsync -a --ignore-existing %p 172.16.0.123:wal-archive/%f'
000000010000000000000005
0/5033468
-[ RECORD 1 ]-----+-------------------------
archived_count    | 1
last_archived_wal | 000000010000000000000005
failed_count      | 0

%p ist der Pfad der Segmentdatei, %f ihr Name. rsync schreibt erst in eine temporäre Datei und benennt sie dann um – ein halb kopiertes Segment kann nie als fertig im Archiv liegen. --ignore-existing überschreibt nichts, was schon da ist. Der Zielpfad wal-archive/ ist relativ zum Home von postgres auf pg2 (SSH-Schlüssel aus Tag 0). pg_switch_wal() schließt das aktuelle Segment sofort ab; im Betrieb sorgt archive_timeout dafür, dass auch bei wenig Last regelmäßig archiviert wird.

Achtung: Schlägt archive_command fehl, bleibt das Segment in pg_wal liegen und wird immer wieder versucht. Ein kaputtes Archiv füllt so die Platte des Primary. failed_count und last_failed_wal gehören ins Monitoring (Challenge).

Fortschritt speichern? Kostenlos registrieren

Drill 5: PITR – die gelöschte Tabelle zurückholen

PITR braucht ein Basis-Backup, das nach dem Start der Archivierung entstand. Auf pg2:

sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup \
  -D /var/lib/postgresql/backup/base2 -Fp -X stream -c fast

Auf pg1 der Unfall: Daten anlegen, Uhrzeit merken, Tabelle löschen. Danach das Segment abschließen, damit das DROP sicher im Archiv liegt:

createdb tag09
psql -d tag09 <<'SQL'
CREATE TABLE konto (id int PRIMARY KEY, inhaber text, saldo numeric);
INSERT INTO konto SELECT i, 'Kunde ' || i, i * 10 FROM generate_series(1, 1000) i;
SQL
psql -Atc 'SELECT now()'
psql -d tag09 -c 'DROP TABLE konto;'
psql -Atc 'SELECT pg_switch_wal()'

Erwartet:

CREATE TABLE
INSERT 0 1000
2026-11-19 04:57:13.492875+00
DROP TABLE
0/A48F840

Auf pg2 einen leeren Cluster 18/pitr anlegen (Port 5433), sein Datenverzeichnis durch das Backup ersetzen, Recovery konfigurieren. Die Datei recovery.signal schaltet den Server in den Recovery-Modus; restore_command holt die Segmente aus dem Archiv, recovery_target_time sagt, wo er aufhört. Nimm deine eigene Uhrzeit von oben, samt Sekundenbruchteilen: Die Recovery stoppt vor dem ersten Commit nach der Zielzeit. Abgeschnitten auf ganze Sekunden läge die Zielzeit womöglich vor deinem INSERT, und die Tabelle fehlt.

sudo pg_createcluster 18 pitr
sudo -u postgres rm -rf /var/lib/postgresql/18/pitr
sudo -u postgres cp -a /var/lib/postgresql/backup/base2 /var/lib/postgresql/18/pitr
cat <<'CONF' | sudo -u postgres tee /etc/postgresql/18/pitr/conf.d/recovery.conf
restore_command = 'cp /var/lib/postgresql/wal-archive/%f %p'
recovery_target_time = '2026-11-19 04:57:13.492875+00'
CONF
sudo -u postgres touch /var/lib/postgresql/18/pitr/recovery.signal
sudo pg_ctlcluster 18 pitr start

Erwartet (wenn pg1 aus Tag 3 eigene Zertifikate hat):

Job for [email protected] failed because the service did not take the
steps required by its unit configuration.
sudo tail -2 /var/log/postgresql/postgresql-18-pitr.log
sudo -u postgres cat /var/lib/postgresql/18/pitr/postgresql.auto.conf

Erwartet:

... FATAL:  could not load server certificate file
"/etc/postgresql/18/main/server.crt": No such file or directory
... LOG:  database system is shut down
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file = '/etc/postgresql/18/main/server.key'
shared_preload_libraries = 'pg_stat_statements'

Genau das Problem aus Drill 4: postgresql.auto.conf kam mit dem Backup und zeigt auf Zertifikate, die es nur auf pg1 gibt. Die beiden ssl_-Zeilen raus, dann greift das snakeoil-Zertifikat aus der Konfiguration von 18/pitr. Die Zeile shared_preload_libraries aus Tag 7 darf mitreisen – pg_stat_statements steckt im Paket postgresql-18, das auch auf pg2 installiert ist:

sudo -u postgres sed -i '/^ssl_/d' /var/lib/postgresql/18/pitr/postgresql.auto.conf
sudo pg_ctlcluster 18 pitr start
sudo grep -E 'recovery|restored|consistent' /var/log/postgresql/postgresql-18-pitr.log
sudo -u postgres psql -p 5433 -Atc \
  'SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state()'
sudo -u postgres psql -p 5433 -d tag09 -c 'SELECT count(*), sum(saldo) FROM konto;'

Erwartet (gekürzt, Zahlen weichen bei dir ab):

LOG:  starting backup recovery with redo LSN 0/9000028, checkpoint LSN 0/9000080, ...
LOG:  restored log file "000000010000000000000009" from archive
LOG:  starting point-in-time recovery to 2026-11-19 04:57:13.492875+00
LOG:  restored log file "00000001000000000000000A" from archive
LOG:  consistent recovery state reached at 0/9000120
LOG:  recovery stopping before commit of transaction 849, time 2026-11-19 04:57:13.6...
LOG:  pausing at the end of recovery
t|paused
 count |   sum
-------+---------
  1000 | 5005000
(1 row)

Der Server stoppt vor der ersten Transaktion nach der Zielzeit und pausiert (recovery_target_action = pause ist Standard). So kannst du lesend prüfen, ob der Zeitpunkt stimmt – wenn nicht: stoppen, Zielzeit ändern, neu starten. Die Tabelle ist da. Sie wandert per pg_dump zurück nach pg1, als postgres über den SSH-Schlüssel aus Tag 0:

sudo -u postgres pg_dump -p 5433 -d tag09 -t konto \
  | sudo -u postgres ssh 172.16.0.182 psql -d tag09
sudo -u postgres ssh 172.16.0.182 psql -d tag09 -Atc "'SELECT count(*) FROM konto'"

Erwartet (gekürzt):

CREATE TABLE
ALTER TABLE
COPY 1000
ALTER TABLE
1000

Soll der Klon selbst weiterleben, beendet pg_wal_replay_resume() die Recovery: Er wird beschreibbar und wechselt auf eine neue Timeline.

sudo -u postgres psql -p 5433 -Atc 'SELECT pg_wal_replay_resume()'
sudo -u postgres psql -p 5433 -Atc \
  'SELECT pg_is_in_recovery(), timeline_id FROM pg_control_checkpoint()'

Erwartet:


f|2

Timeline 2 trennt die neue Geschichte des Klons von der alten auf pg1. Archiviert wird auf pg2 nichts – archive_mode steht dort auf off, weil die Archiv-Einstellungen von pg1 nicht im Backup lagen.

Fortschritt speichern? Kostenlos registrieren

Drill 6: Inkrementelles Backup (seit PostgreSQL 17)

Ein inkrementelles pg_basebackup kopiert nur Blöcke, die sich seit einem früheren Backup geändert haben. Woher der Server das weiß, liefert der WAL-Summarizer – auf pg1 einschalten (Reload genügt):

echo 'summarize_wal = on' \
  | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag09-summarize.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -c 'SELECT * FROM pg_get_wal_summarizer_state()'

Erwartet:

summarize_wal = on
t
 summarized_tli | summarized_lsn | pending_lsn | summarizer_pid
----------------+----------------+-------------+----------------
              1 | 0/9000028      | 0/12F48D58  |           9606
(1 row)

Auf pg2: Vollbackup, dann Last auf pg1 (pgbench mit 500 000 Zeilen), dann das Inkrement gegen das Manifest des Vollbackups:

B=/var/lib/postgresql/backup
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup -D $B/voll -c fast
sudo -u postgres ssh -n 172.16.0.182 "pgbench -i -s 5 -q tag09"
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup -D $B/inkr1 \
  -c fast --incremental=$B/voll/backup_manifest
sudo -u postgres du -sh $B/voll $B/inkr1
sudo -u postgres find $B/inkr1 -name 'INCREMENTAL.*' | wc -l

Erwartet (gekürzt):

done in 2.29 s (drop tables 0.00 s, create tables 0.01 s, client-side generate ...
243M    /var/lib/postgresql/backup/voll
110M    /var/lib/postgresql/backup/inkr1
2172

Das Inkrement enthält die neuen pgbench-Tabellen komplett, für alle anderen Dateien nur INCREMENTAL.*-Stücke mit den geänderten Blöcken. Starten lässt es sich so nicht: pg_combinebackup setzt die Kette zu einem vollständigen Backup zusammen.

P=/usr/lib/postgresql/18/bin
sudo -u postgres $P/pg_combinebackup $B/voll $B/inkr1 -o $B/kombi
sudo -u postgres du -sh $B/kombi
sudo -u postgres $P/pg_verifybackup $B/kombi

Erwartet:

318M    /var/lib/postgresql/backup/kombi
backup successfully verified

Fortschritt speichern? Kostenlos registrieren

Drill 7: pgBackRest mit Repository auf pg2

pgBackRest ist das Werkzeug der Wahl, sobald es ernst wird: parallel, komprimiert, mit Aufbewahrungsregeln und eigener Prüfung. Aufbau: pg2 ist Repository-Host und startet die Backups; auf pg1 schiebt archive-push das WAL dorthin. Beide reden per SSH als postgres. Auf beiden VMs installieren – gleiche Version ist Pflicht:

sudo apt-get install -y pgbackrest
pgbackrest version

Erwartet:

pgBackRest 2.60.1

Auf pg2 die Konfiguration. Die Stanza main beschreibt einen zu sichernden Cluster:

cat <<'CONF' | sudo tee /etc/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
log-level-console=info

[main]
pg1-host=172.16.0.182
pg1-path=/var/lib/postgresql/18/main
CONF

Auf pg1 die Gegenseite, und archive_command zeigt ab jetzt auf pgBackRest:

cat <<'CONF' | sudo tee /etc/pgbackrest.conf
[global]
repo1-host=172.16.0.123
repo1-host-user=postgres
log-level-console=info

[main]
pg1-path=/var/lib/postgresql/18/main
CONF
cat <<'CONF' | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag09-archiv.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
CONF
psql -Atc 'SELECT pg_reload_conf()'

Achtung: Ohne repo1-host-user=postgres meldet sich archive-push als Benutzer pgbackrest an (Standardwert) – im Log von pg1 steht dann [email protected]: Permission denied (publickey), und das WAL staut sich.

Auf pg2 die Stanza anlegen und alles prüfen; check erzwingt ein WAL-Segment und wartet, bis es im Repository ist:

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

Erwartet (gekürzt):

INFO: stanza-create for stanza 'main' on repo1
INFO: stanza-create command end: completed successfully (1474ms)
INFO: check repo1 configuration (primary)
INFO: check repo1 archive for WAL (primary)
INFO: WAL segment 00000001000000000000001A successfully archived to '/var/lib/pg...
INFO: check command end: completed successfully (3483ms)

Fortschritt speichern? Kostenlos registrieren

Drill 8: Voll, Differenz, Inkrement und Restore auf Zeit

Auf pg2. Zwischen den Backups erzeugt pgbench Änderungen auf pg1:

sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres ssh -n 172.16.0.182 "pgbench -t 2000 tag09"
sudo -u postgres pgbackrest --stanza=main --type=diff backup
sudo -u postgres ssh -n 172.16.0.182 "pgbench -t 200 tag09"
sudo -u postgres pgbackrest --stanza=main --type=incr backup
sudo -u postgres pgbackrest --stanza=main info

Erwartet (gekürzt):

INFO: new backup label = 20261119-050209F
INFO: full backup size = 293.5MB, file total = 2823
INFO: new backup label = 20261119-050209F_20261119-050256D
INFO: diff backup size = 76.2MB, file total = 2825
INFO: new backup label = 20261119-050209F_20261119-050308I
INFO: incr backup size = 93.1MB, file total = 3132
stanza: main
    status: ok
    db (current)
        wal archive min/max (18): 000000010000000000000021/00000001000000000000002A
        full backup: 20261119-050209F
            database size: 293.5MB, database backup size: 293.5MB
            repo1: backup set size: 21.5MB, backup size: 21.5MB
        diff backup: 20261119-050209F_20261119-050256D
            database size: 293.9MB, database backup size: 76.2MB
            repo1: backup set size: 21.6MB, backup size: 4.3MB
            backup reference total: 1 full
        incr backup: 20261119-050209F_20261119-050308I
            database size: 311.5MB, database backup size: 93.1MB
            repo1: backup set size: 21.6MB, backup size: 4.2MB
            backup reference total: 1 full, 1 diff

Differenz heißt: alles seit dem letzten Vollbackup, Inkrement: alles seit dem letzten Backup gleich welcher Art. repo1-retention-full=2 behält zwei Vollbackups samt Abhängigen und räumt das Archiv dazu auf. Im Repository landet dank Kompression nur ein Bruchteil der Datenbankgröße. (Andere Datenbanken auf pg1 wachsen mit, deshalb steigt die Größe beim Inkrement.)

Der zweite Unfall auf pg1, diesmal das UPDATE ohne WHERE:

psql -d tag09 -Atc 'SELECT sum(saldo) FROM konto'
psql -Atc 'SELECT now()'
psql -d tag09 -c 'UPDATE konto SET saldo = 0;'
psql -Atc 'SELECT pg_switch_wal()'

Erwartet:

5005000
2026-11-19 05:03:34.798387+00
UPDATE 1000
0/2C657BB0

Restore auf pg2 in den Cluster 18/pitr. Die Konfiguration von pg2 kennt main nur mit pg1-host; --reset-pg1-host streicht das für diesen Aufruf, damit der Restore lokal landet. --delta vergleicht per Prüfsumme und ersetzt nur, was abweicht:

sudo pg_ctlcluster 18 pitr stop
sudo rm /etc/postgresql/18/pitr/conf.d/recovery.conf
sudo -u postgres pgbackrest --stanza=main --reset-pg1-host \
  --pg1-path=/var/lib/postgresql/18/pitr --delta --type=time \
  --target='2026-11-19 05:03:34.798387+00' --target-action=promote restore
sudo -u postgres cat /var/lib/postgresql/18/pitr/postgresql.auto.conf

Erwartet (gekürzt):

INFO: repo1: restore backup set 20261119-050209F_20261119-050308I, recovery will ...
INFO: remove invalid files/links/paths from '/var/lib/postgresql/18/pitr'
INFO: write updated /var/lib/postgresql/18/pitr/postgresql.auto.conf
INFO: restore size = 311.5MB, file total = 3132
INFO: restore command end: completed successfully (7597ms)
listen_addresses = '*'
ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file = '/etc/postgresql/18/main/server.key'
shared_preload_libraries = 'pg_stat_statements'

# Recovery settings generated by pgBackRest restore on 2026-11-19 05:03:58
restore_command = 'pgbackrest --reset-pg1-host --pg1-path=/var/lib/postgresql/18/...
recovery_target_time = '2026-11-19 05:03:34.798387+00'
recovery_target_action = 'promote'

pgBackRest wählt selbst das passende Backup (das letzte vor der Zielzeit) und schreibt restore_command, Zielzeit und recovery.signal. Die ssl_-Zeilen von pg1 müssen wieder raus:

sudo -u postgres sed -i '/^ssl_/d' /var/lib/postgresql/18/pitr/postgresql.auto.conf
sudo pg_ctlcluster 18 pitr start
sudo grep -E 'stopping|timeline|complete' /var/log/postgresql/postgresql-18-pitr.log
sudo -u postgres psql -p 5433 -d tag09 -Atc \
  'SELECT pg_is_in_recovery(), sum(saldo) FROM konto'

Erwartet (gekürzt, Zahlen weichen bei dir ab):

LOG:  recovery stopping before commit of transaction 3167, time 2026-11-19 05:03:3...
LOG:  selected new timeline ID: 2
LOG:  archive recovery complete
f|5005000

Die Summe stimmt wieder, und mit --target-action=promote ist der Klon gleich beschreibbar.

Fortschritt speichern? Kostenlos registrieren

Übung: Speed-Drills

  1. Zeige auf pg1, wie viele Segmente archiviert wurden und wie viele Versuche fehlschlugen. Zeitziel: 20 s
  2. Zeige auf pg1 den Namen des aktuellen WAL-Segments. Zeitziel: 20 s
  3. Prüfe auf pg2 das Tar-Backup tar1. Zeitziel: 20 s
  4. Zeige auf pg2 nur Status und Backup-Liste der Stanza main. Zeitziel: 15 s

Lösung

psql -x -c 'SELECT archived_count, failed_count FROM pg_stat_archiver'
psql -Atc 'SELECT pg_walfile_name(pg_current_wal_lsn())'
sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup -n \
  /var/lib/postgresql/backup/tar1
sudo -u postgres pgbackrest --stanza=main info | grep -E 'status|backup:'

Erwartet (gekürzt, Zahlen weichen bei dir ab):

archived_count | 25
failed_count   | 12
00000001000000000000002D
backup successfully verified
    status: ok
        full backup: 20261119-050209F
        diff backup: 20261119-050209F_20261119-050256D
        incr backup: 20261119-050209F_20261119-050308I

failed_count zählt seit dem letzten Statistik-Reset alle Fehlversuche. failed_count > 0 ist normal, wenn das Archiv zwischendurch klemmte (etwa ohne repo1-host-user). Entscheidend ist, ob last_archived_time aktuell ist.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

psql -Atc 'SHOW archive_command'

Erwartet: pgbackrest --stanza=main archive-push %p

Auf pg2:

sudo -u postgres pgbackrest --stanza=main check

Erwartet: zuletzt check command end: completed successfully.

pg_lsclusters

Erwartet: 18 main 5432 online und 18 pitr 5433 online.

sudo -u postgres psql -p 5433 -d tag09 -Atc 'SELECT sum(saldo) FROM konto'

Erwartet: 5005000 – der Stand vor dem UPDATE.

sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup \
  /var/lib/postgresql/backup/kombi

Erwartet: backup successfully verified

Challenge

Simuliere ein ausgefallenes Archivziel: Benenne auf pg2 das pgBackRest-Repository /var/lib/pgbackrest um, erzwinge auf pg1 einen WAL-Wechsel und zeige, woran du den Fehler erkennst. Behebe ihn und weise nach, dass PostgreSQL das liegengebliebene Segment selbst nachliefert.

Hinweis: Schau in pg_stat_archiver, in pg_wal/archive_status und ins Log von pg1.

Lösung

Auf pg2:

sudo mv /var/lib/pgbackrest /var/lib/pgbackrest.weg

Auf pg1:

psql -Atc 'SELECT pg_switch_wal()'
psql -x -c 'SELECT archived_count, failed_count, last_failed_wal FROM pg_stat_archiver'
sudo ls /var/lib/postgresql/18/main/pg_wal/archive_status
sudo grep -E 'archive command failed|valid repository' \
  /var/log/postgresql/postgresql-18-main.log | tail -2

Erwartet (gekürzt, Segmentnamen und Zähler weichen bei dir ab):

1/AA000078
archived_count  | 1
failed_count    | 3
last_failed_wal | 0000000300000001000000AA
0000000300000001000000A9.done
0000000300000001000000AA.ready
... P00  ERROR: [103]: unable to find a valid repository:
... LOG:  archive command failed with exit code 103

.ready heißt: wartet aufs Archiv, .done: erledigt, darf recycelt werden. Auf pg2 zurückbenennen und auf pg1 etwa eine Minute später nachsehen:

sudo mv /var/lib/pgbackrest.weg /var/lib/pgbackrest
psql -x -c 'SELECT archived_count, last_archived_wal, failed_count
  FROM pg_stat_archiver'

Erwartet:

archived_count    | 2
last_archived_wal | 0000000300000001000000AA
failed_count      | 3

Der Archiver versucht es nach drei Fehlschlägen in Folge nach einer Pause erneut. Kein Segment geht verloren – solange die Platte von pg1 durchhält.

Fortschritt speichern? Kostenlos registrieren

Aufräumen

Tag 10 braucht nichts davon: kein Archiv, kein pgBackRest, keinen zweiten Cluster. Auf pg2:

sudo pg_dropcluster --stop 18 pitr
sudo -u postgres pgbackrest --stanza=main stop
sudo -u postgres pgbackrest --stanza=main stanza-delete --force
sudo apt-get purge -y pgbackrest
sudo rm -rf /var/lib/pgbackrest /var/log/pgbackrest
sudo -u postgres rm -rf /var/lib/postgresql/backup /var/lib/postgresql/wal-archive \
  /var/lib/postgresql/.pgpass

Auf pg1 (der Neustart schaltet archive_mode ab):

sudo rm /etc/postgresql/18/main/conf.d/tag09-archiv.conf \
  /etc/postgresql/18/main/conf.d/tag09-summarize.conf
sudo sed -i '/^host  replication  tag09_backup  172.16.0.123\/32/d' \
  /etc/postgresql/18/main/pg_hba.conf
sudo systemctl restart postgresql@18-main
dropdb tag09
psql -c 'DROP ROLE tag09_backup;'
sudo apt-get purge -y pgbackrest
sudo rm -rf /var/lib/pgbackrest /var/log/pgbackrest
psql -Atc 'SHOW archive_mode' -c 'SHOW summarize_wal'

Erwartet:

DROP ROLE
off
off

Interview-Notiz

  • „Was brauchst du für Point-in-Time-Recovery?“ – Ein Basis-Backup und das lückenlose WAL-Archiv ab dessen Start; Ziel per recovery_target_time (oder _xid, _lsn, _name), recovery.signal startet die Recovery.
  • „Woher weißt du, dass dein Backup taugt?“ – pg_verifybackup bzw. pgbackrest check/verify automatisch, und regelmäßige Test-Restores mit Zeitmessung (RTO).
  • „Voll, differenziell, inkrementell?“ – Differenz: seit letztem Vollbackup; Inkrement: seit letztem Backup. Restore braucht die ganze Kette, Inkremente sind klein, aber die Kette ist länger.

Enterprise-Notiz

  • Repository außerhalb des Rechenzentrums: pgBackRest kann S3, Azure, GCS (auch mehrere repoN parallel), mit repo1-cipher-type=aes-256-cbc verschlüsselt.
  • Asynchrones Archivieren (archive-async=y) bei hoher WAL-Last; Alarm, wenn last_archived_time älter als wenige Minuten ist oder pg_wal wächst.
  • RPO bestimmt archive_timeout bzw. Streaming ins Archiv (pg_receivewal), RTO die Restore-Geschwindigkeit – beides messen, nicht schätzen.
  • Backups vom Standby (backup-standby=y) entlasten den Primary.
  • Restore-Tests regelmäßig automatisiert in eine Wegwerf-Umgebung, mit Plausibilitätsabfragen und Protokoll für das Audit.