Skip to content
Hands-on IT IT Drills

Day 9: Physical Backups and PITR

Goal: You back up pg1 physically from pg2, 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_basebackup copies 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 cluster 18/pitr on pg2 (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/backup

Save 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/tar1

Expected (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_label

Expected (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/tar1

Expected:

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/kaputt

Expected:

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-archive

On 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_command fails, the segment stays in pg_wal and is retried over and over. A broken archive thus fills up the primary’s disk. failed_count and last_failed_wal belong 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 fast

On 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 start

Expected (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.conf

Expected:

... 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 -l

Expected (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/kombi

Expected:

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 version

Expected:

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
CONF

On 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-push logs in as the user pgbackrest (the default) – the log of pg1 then 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 check

Expected (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 info

Expected (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.conf

Expected (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

  1. On pg1, show how many segments were archived and how many attempts failed. Target time: 20 s
  2. On pg1, show the name of the current WAL segment. Target time: 20 s
  3. On pg2, verify the tar backup tar1. Target time: 20 s
  4. On pg2, show only the status and backup list of the stanza main. 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 check

Expected: last line check command end: completed successfully.

pg_lsclusters

Expected: 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/kombi

Expected: 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.weg

On 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

Expected (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/pgbackrest
psql -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/.pgpass

On 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.signal starts the recovery.
  • “How do you know your backup is good?” – pg_verifybackup or pgbackrest check/verify automatically, 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 repoN in parallel), encrypted with repo1-cipher-type=aes-256-cbc.
  • Asynchronous archiving (archive-async=y) under heavy WAL load; alert when last_archived_time is older than a few minutes or pg_wal grows.
  • RPO is determined by archive_timeout or 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.