Day 9: Physical Backups and PITR
Goal: You back up
pg1physically frompg2, archive the WAL and bring back a dropped table with point-in-time recovery – first by hand, then with pgBackRest. · Duration: approx. 120 min · Prerequisite: Day 8
Briefing
pg_dump(Day 8) backs up contents;pg_basebackupcopies the whole cluster file by file – faster, and the basis for standbys.- A base backup plus a gapless WAL archive allows recovery to any second after it: point-in-time recovery (PITR).
- You never restore over
pg1, but into a second cluster18/pitronpg2(port 5433). - pgBackRest automates all of this: archive, full/differential/incremental backups, retention, restore.
pg1 .182 18/main pg2 .123
WAL ---- archive_command (rsync) -----> ~postgres/wal-archive/
Data <--- pg_basebackup (port 5432) --- ~postgres/backup/
18/pitr :5433 <- restore + WAL replay
Drill 1: Backup role and access
pg_basebackup speaks the replication protocol. It needs
a role with REPLICATION (no superuser) and a
pg_hba line for the pseudo-database
replication. On 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);"Expected:
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 only matches
replication connections, not a database of that name. The
error column stays empty – the line is valid.
On pg2, the password goes into the
.pgpass of the Linux user postgres, which
writes the backups (lab value):
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/backupSave your progress? Sign up for free
Drill 2: Base backup with pg_basebackup
Still on pg2. First as a compressed tar
(-Ft -z), then as a normal directory (-Fp).
-X stream fetches the WAL produced during the backup over a
second connection – so the backup can start on its own.
-c fast forces a checkpoint right away instead of waiting
for the next one:
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/tar1Expected (abridged):
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, segment names and transaction numbers in this chapter come from an earlier lab state. After Days 5 to 8 they are considerably larger on your system – your numbers differ, the procedures do not.
Now in plain format, with SHA-256 instead of CRC32C in the 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_labelExpected (abridged):
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 can be started immediately and is a prerequisite for
incremental backups (Drill 6); tar saves space. Along the way,
pg_basebackup verifies the checksum of every data page
while reading (data_checksums = on, Day 0) and aborts on
errors – you could turn that off with
--no-verify-checksums.
Save your progress? Sign up for free
Drill 3: Verify backups with pg_verifybackup
The manifest lists every file with its size and checksum.
pg_verifybackup compares the backup against it and also
reads the included WAL. It has no wrapper in /usr/bin,
hence the full path:
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/tar1Expected:
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
pg_verifybackup verifies tar backups since PostgreSQL
18, but not the WAL inside them. Now deliberately damage a copy –
overwrite one byte, delete one WAL segment:
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/kaputtExpected:
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
A backup without verification is a hope. pg_verifybackup
belongs in monitoring right after every backup – but only a restore
delivers real proof.
Save your progress? Sign up for free
Drill 4: WAL archiving to pg2
For PITR, every completed WAL segment (16 MB) has to leave the
server. The target is pg2, not a directory on
pg1: if pg1 fails along with its disk, a local
archive is gone too. On pg2, the target directory:
sudo -u postgres mkdir -m 700 /var/lib/postgresql/wal-archiveOn pg1, the settings go into a separate file under
conf.d instead of using ALTER SYSTEM:
postgresql.auto.conf lives in the data directory and
travels along with every base backup – a restored clone would otherwise
write to the same archive. archive_mode needs a
restart:
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'Expected:
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 is the path of the segment file, %f its
name. rsync first writes to a temporary file and then
renames it – a half-copied segment can never sit in the archive as
complete. --ignore-existing does not overwrite anything
that is already there. The target path wal-archive/ is
relative to the home directory of postgres on
pg2 (SSH key from Day 0). pg_switch_wal()
closes the current segment immediately; in production,
archive_timeout makes sure archiving happens regularly even
under light load.
Warning: If
archive_commandfails, the segment stays inpg_waland is retried over and over. A broken archive thus fills up the primary’s disk.failed_countandlast_failed_walbelong in monitoring (Challenge).
Save your progress? Sign up for free
Drill 5: PITR – bring back the dropped table
PITR needs a base backup that was taken after archiving started. On pg2:
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup \
-D /var/lib/postgresql/backup/base2 -Fp -X stream -c fastOn pg1, the accident: create data, note the time, drop
the table. Then close the segment so that the DROP is
safely in the archive:
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()'Expected:
CREATE TABLE
INSERT 0 1000
2026-11-19 04:57:13.492875+00
DROP TABLE
0/A48F840
On pg2, create an empty cluster 18/pitr
(port 5433), replace its data directory with the backup, and configure
recovery. The file recovery.signal puts the server into
recovery mode; restore_command fetches the segments from
the archive, recovery_target_time says where it stops. Use
your own time from above, including the fractional seconds: recovery
stops before the first commit after the target time. Truncated to whole
seconds, the target time might lie before your INSERT, and
the table would be missing.
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 startExpected (if pg1 has its own
certificates from Day 3):
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.confExpected:
... 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'
Exactly the problem from Drill 4: postgresql.auto.conf
came along with the backup and points to certificates that only exist on
pg1. Remove the two ssl_ lines, and the
snakeoil certificate from the configuration of 18/pitr
takes over. The shared_preload_libraries line from Day 7
may travel along – pg_stat_statements is part of the
postgresql-18 package, which is installed on
pg2 too:
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;'Expected (abridged, your numbers will differ):
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)
The server stops before the first transaction after the target time
and pauses (recovery_target_action = pause
is the default). That lets you check read-only whether the point in time
is right – if not: stop, change the target time, start again. The table
is there. It goes back to pg1 via pg_dump, as
postgres with the SSH key from Day 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'"Expected (abridged):
CREATE TABLE
ALTER TABLE
COPY 1000
ALTER TABLE
1000
If the clone itself is to live on,
pg_wal_replay_resume() ends the recovery: it becomes
writable and switches to a new 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()'Expected:
f|2
Timeline 2 separates the clone’s new history from the old one on
pg1. Nothing is archived on pg2 –
archive_mode is off there, because the archive
settings of pg1 were not in the backup.
Save your progress? Sign up for free
Drill 6: Incremental backup (since PostgreSQL 17)
An incremental pg_basebackup copies only blocks that
have changed since an earlier backup. The server learns which ones from
the WAL summarizer – turn it on on pg1 (a reload is
enough):
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()'Expected:
summarize_wal = on
t
summarized_tli | summarized_lsn | pending_lsn | summarizer_pid
----------------+----------------+-------------+----------------
1 | 0/9000028 | 0/12F48D58 | 9606
(1 row)
On pg2: a full backup, then load on pg1
(pgbench with 500,000 rows), then the incremental backup
against the manifest of the full backup:
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 -lExpected (abridged):
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
The incremental backup contains the new pgbench tables in full, and
for all other files only INCREMENTAL.* pieces with the
changed blocks. It cannot be started like this:
pg_combinebackup assembles the chain into a complete
backup.
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/kombiExpected:
318M /var/lib/postgresql/backup/kombi
backup successfully verified
Save your progress? Sign up for free
Drill 7: pgBackRest with a repository on pg2
pgBackRest is the tool of choice once things get serious: parallel,
compressed, with retention rules and its own verification. Setup:
pg2 is the repository host and starts the
backups; on pg1, archive-push pushes the WAL
there. Both talk over SSH as postgres. Install it on
both VMs – the same version is mandatory:
sudo apt-get install -y pgbackrest
pgbackrest versionExpected:
pgBackRest 2.60.1
On pg2, the configuration. The stanza
main describes a cluster to back up:
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
CONFOn pg1, the other side, and from now on
archive_command points to 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()'Warning: Without
repo1-host-user=postgres,archive-pushlogs in as the userpgbackrest(the default) – the log ofpg1then shows[email protected]: Permission denied (publickey), and the WAL piles up.
On pg2, create the stanza and check everything;
check forces a WAL segment and waits until it is in the
repository:
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main checkExpected (abridged):
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)
Save your progress? Sign up for free
Drill 8: Full, differential, incremental and restore to a point in time
On pg2. Between the backups, pgbench
generates changes on 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 infoExpected (abridged):
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
Differential means: everything since the last full
backup; incremental: everything since the last backup
of any kind. repo1-retention-full=2 keeps two full backups
including their dependents and cleans up the archive accordingly. Thanks
to compression, only a fraction of the database size ends up in the
repository. (Other databases on pg1 grow too, which is why
the size goes up for the incremental backup.)
The second accident on pg1, this time the
UPDATE without 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()'Expected:
5005000
2026-11-19 05:03:34.798387+00
UPDATE 1000
0/2C657BB0
Restore on pg2 into the cluster
18/pitr. The configuration of pg2 only knows
main with pg1-host;
--reset-pg1-host removes that for this call, so the restore
lands locally. --delta compares by checksum and replaces
only what differs:
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.confExpected (abridged):
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 picks the right backup itself (the last one before the
target time) and writes restore_command, the target time
and recovery.signal. The ssl_ lines from
pg1 have to go again:
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'Expected (abridged, your numbers will differ):
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
The sum is right again, and with --target-action=promote
the clone is writable right away.
Save your progress? Sign up for free
Exercise: Speed Drills
- On
pg1, show how many segments were archived and how many attempts failed. Target time: 20 s - On
pg1, show the name of the current WAL segment. Target time: 20 s - On
pg2, verify the tar backuptar1. Target time: 20 s - On
pg2, show only the status and backup list of the stanzamain. Target time: 15 s
Solution
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:'Expected (abridged, your numbers will differ):
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 counts all failed attempts since the last
statistics reset. failed_count > 0 is normal if the
archive got stuck at some point (for example without
repo1-host-user). What matters is whether
last_archived_time is current.
Save your progress? Sign up for free
Checkpoint
psql -Atc 'SHOW archive_command'Expected:
pgbackrest --stanza=main archive-push %p
On pg2:
sudo -u postgres pgbackrest --stanza=main checkExpected: last line
check command end: completed successfully.
pg_lsclustersExpected: 18 main 5432 online and
18 pitr 5433 online.
sudo -u postgres psql -p 5433 -d tag09 -Atc 'SELECT sum(saldo) FROM konto'Expected: 5005000 – the state before
the UPDATE.
sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup \
/var/lib/postgresql/backup/kombiExpected:
backup successfully verified
Challenge
Simulate a failed archive target: on pg2, rename the
pgBackRest repository /var/lib/pgbackrest, force a WAL
switch on pg1 and show how you recognize the error. Fix it
and prove that PostgreSQL delivers the stuck segment on its own.
Hint: Look at pg_stat_archiver, at
pg_wal/archive_status and at the log of
pg1.
Solution
On pg2:
sudo mv /var/lib/pgbackrest /var/lib/pgbackrest.wegOn 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 -2Expected (abridged, your segment names and counters will differ):
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 means: waiting for the archive;
.done: finished, may be recycled. On pg2,
rename it back, and on pg1 check again about a minute
later:
sudo mv /var/lib/pgbackrest.weg /var/lib/pgbackrestpsql -x -c 'SELECT archived_count, last_archived_wal, failed_count
FROM pg_stat_archiver'Expected:
archived_count | 2
last_archived_wal | 0000000300000001000000AA
failed_count | 3
After three failures in a row, the archiver tries again after a
pause. No segment is lost – as long as the disk of pg1
holds out.
Save your progress? Sign up for free
Cleanup
Day 10 needs none of this: no archive, no pgBackRest, no second cluster. On 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/.pgpassOn pg1 (the restart turns off
archive_mode):
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'Expected:
DROP ROLE
off
off
Interview Note
- “What do you need for point-in-time recovery?” – A base backup and
the gapless WAL archive from its start onward; the target via
recovery_target_time(or_xid,_lsn,_name);recovery.signalstarts the recovery. - “How do you know your backup is good?” –
pg_verifybackuporpgbackrest check/verifyautomatically, plus regular test restores with timing (RTO). - “Full, differential, incremental?” – Differential: since the last full backup; incremental: since the last backup. A restore needs the whole chain; incrementals are small, but the chain is longer.
Enterprise Note
- Repository outside the data center: pgBackRest supports S3, Azure,
GCS (also several
repoNin parallel), encrypted withrepo1-cipher-type=aes-256-cbc. - Asynchronous archiving (
archive-async=y) under heavy WAL load; alert whenlast_archived_timeis older than a few minutes orpg_walgrows. - RPO is determined by
archive_timeoutor streaming into the archive (pg_receivewal), RTO by restore speed – measure both, don’t guess. - Backups from the standby (
backup-standby=y) take load off the primary. - Regular automated restore tests into a throwaway environment, with plausibility queries and a record for the audit.