Lab 18: Take a physical backup, damage it, and prove the damage is detectable

Objective

A physical backup is a byte-level copy of the data directory, taken while the server runs. It is what production recovery is actually built on, and it has three properties a logical dump does not: it restores at the speed of a file copy, it is the starting point for point-in-time recovery, and it can be verified.

This lab is mostly about that third property. You will take a backup, verify it, then damage it two ways — one flipped byte and one deleted file — and confirm both are caught. A backup you have not verified is a hypothesis.

The lab opens with a failure that will waste an afternoon if you meet it unprepared: the first pg_basebackup in this lab run appeared to hang, wrote four kilobytes in two minutes, and had to be killed. The cause is a default, and the fix is two characters.

Architecture

One live cluster, four backups taken from it, and a fifth directory reconstructed from two of them and started as a separate server.

flowchart TD
    S["live cluster\nrbpg-pitr, 202 MB\nsummarize_wal = on"] --> F["full backup\npg_basebackup -c fast"]
    S --> I["incremental backup\npg_basebackup -i .../backup_manifest"]
    F --> V["pg_verifybackup\nclean, then damaged"]
    F --> C["pg_combinebackup"]
    I --> C
    C --> R["reconstructed directory\nstarted on port 5460"]
    R --> Q["SELECT: 200,000 rows,\n2,000 of them updated"]

Requirements

• A PostgreSQL 18 cluster with summarize_wal = on — required for incremental backup — and a replication connection available to the user taking the backup.
• Roughly 700 MB of free disk for the several copies this lab makes.
• The lab uses rbpg-pitr, the archiving cluster, because it already has summarize_wal enabled.

Scenario

Nightly physical backups have been running for two years and have never been restored. You have been asked whether they are any good.

Tasks

Task 1 — The backup that appears to hang

LAB="$HOME/rbpg-lab-18"
mkdir -p "$LAB"

docker exec rbpg-pitr bash -c "mkdir -p /lab18 && chown postgres:postgres /lab18"
docker exec -i -u postgres rbpg-pitr psql -X -c "CREATE DATABASE lab18;"
docker exec -i -u postgres rbpg-pitr psql -X -d lab18 <<'SQL'
CREATE TABLE t(id int PRIMARY KEY, payload text);
INSERT INTO t SELECT g, repeat('p',200) FROM generate_series(1,200000) g;
SQL

# Run this one with a timeout, because it will not finish quickly.
timeout 120 docker exec -u postgres rbpg-pitr \
  pg_basebackup -D /lab18/full -Fp -P -Xstream
docker exec rbpg-pitr du -sh /lab18/full

$ pg_basebackup with no -c option, killed after 120 seconds

waiting for checkpoint

4.0K	/lab18/full

Nothing is broken. pg_basebackup must begin from a checkpoint, and by default it requests a spread checkpoint — one paced to complete over checkpoint_timeout × checkpoint_completion_target, exactly as Lab 16 described. Then it waits for it.

docker exec -u postgres rbpg-pitr psql -X -c "
  SELECT (SELECT setting::int   FROM pg_settings WHERE name='checkpoint_timeout')
       * (SELECT setting::float FROM pg_settings WHERE name='checkpoint_completion_target')
       AS spread_window_s;" | tee "$LAB/checkpoint-mode.txt"

$ compute checkpoint_timeout times checkpoint_completion_target

 spread_window_s 
-----------------
           270
(1 row)

Up to 270 seconds of waiting before a single byte of data is copied.

docker exec -u postgres rbpg-pitr psql -X -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_replication WHERE application_name='pg_basebackup';"
docker exec rbpg-pitr rm -rf /lab18/full

docker exec -u postgres rbpg-pitr pg_basebackup -D /lab18/full -Fp -P -Xstream -c fast
docker exec rbpg-pitr du -sh /lab18/full

$ pg_basebackup -c fast

waiting for checkpoint
190521/190521 kB (100%), 0/1 tablespace
190521/190521 kB (100%), 1/1 tablespace

202M	/lab18/full

Task 2 — What the backup contains that the live directory does not

docker exec rbpg-pitr ls -la /lab18/full/ | grep -E "backup_label|backup_manifest|postgresql.auto"
docker exec rbpg-pitr cat /lab18/full/backup_label

$ list the backup-specific files, then cat backup_label

-rw-------  1 postgres postgres    227 Aug 28 05:55 backup_label
-rw-------  1 postgres postgres 183468 Aug 28 05:55 backup_manifest
-rw-------  1 postgres postgres    136 Aug 28 05:55 postgresql.auto.conf

START WAL LOCATION: 0/2C000028 (file 00000001000000000000002C)
CHECKPOINT LOCATION: 0/2C000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-08-28 05:55:09 UTC
LABEL: pg_basebackup base backup
START TIMELINE: 1

backup_label is what tells a server starting from this directory that it is a restored backup rather than a crashed server. START WAL LOCATION is where recovery must begin — not where pg_control says, because the files in this directory were copied over a period during which the server kept writing, so individual pages are from different moments. Replaying WAL from that LSN forward is what makes them consistent.

Task 3 — The manifest, and what it makes possible

docker exec rbpg-pitr bash -c "head -c 400 /lab18/full/backup_manifest; echo"
docker exec rbpg-pitr bash -c "grep -o '\"Path\"' /lab18/full/backup_manifest | wc -l"

$ head the manifest, then count the Path entries

{ "PostgreSQL-Backup-Manifest-Version": 2,
"System-Identifier": 7678824809233125419,
"Files": [
{ "Path": "backup_label", "Size": 227, "Last-Modified": "2026-08-28 05:55:09 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "5b506a8c" },
{ "Path": "pg_multixact/offsets/0000", "Size": 8192, "Last-Modified": "2026-08-27 21:41:03 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "23464490" },

1282

Note System-Identifier. That is the same number from Lab 1’s pg_controldata — the manifest records which cluster this backup came from, so it cannot be silently combined with a backup of a different one.

docker exec -u postgres rbpg-pitr pg_verifybackup /lab18/full
echo "exit status: $?"

$ pg_verifybackup /lab18/full

backup successfully verified
exit status: 0

Task 4 — Damage it, twice

docker exec rbpg-pitr bash -c '
  F=$(find /lab18/full/base -type f -size +100k | head -1)
  echo "target file: $F"
  printf "X" | dd of="$F" bs=1 seek=5000 conv=notrunc status=none
'
docker exec -u postgres rbpg-pitr pg_verifybackup /lab18/full
echo "exit status: $?"

$ overwrite a single byte at offset 5000, then verify

target file: /lab18/full/base/5/2618
pg_verifybackup: error: checksum mismatch for file "base/5/2618"
exit status: 1

docker exec rbpg-pitr bash -c '
  F=$(find /lab18/full/base -type f -size +100k | sed -n 2p)
  echo "deleting: $F"
  rm -f "$F"
'
docker exec -u postgres rbpg-pitr pg_verifybackup /lab18/full
echo "exit status: $?"

$ delete a second file, then verify again

deleting: /lab18/full/base/5/2691
pg_verifybackup: error: "base/5/2691" is present in the manifest but not on disk
pg_verifybackup: error: checksum mismatch for file "base/5/2618"
exit status: 1

One flipped byte and one missing file, both detected, both named, exit status 1 in each case.

Task 5 — Incremental backup

docker exec rbpg-pitr rm -rf /lab18/full /lab18/incr
docker exec -u postgres rbpg-pitr psql -X -c "SHOW summarize_wal;"
docker exec -u postgres rbpg-pitr pg_basebackup -D /lab18/full -Fp -c fast
docker exec rbpg-pitr du -sh /lab18/full

# Change one percent of the rows.
docker exec -u postgres rbpg-pitr psql -X -d lab18 -c \
  "UPDATE t SET payload = repeat('q',200) WHERE id <= 2000;"
docker exec -u postgres rbpg-pitr psql -X -c "SELECT pg_switch_wal();"

docker exec -u postgres rbpg-pitr pg_basebackup -D /lab18/incr -Fp -c fast \
  -i /lab18/full/backup_manifest

echo "full:        $(docker exec rbpg-pitr du -sh /lab18/full | cut -f1)"
echo "incremental: $(docker exec rbpg-pitr du -sh /lab18/incr | cut -f1)"
docker exec rbpg-pitr bash -c "find /lab18/incr -name 'INCREMENTAL.*' | wc -l"

$ a full backup, an update, then pg_basebackup -i pointing at the full backup's manifest

 summarize_wal 
---------------
on
(1 row)

202M	/lab18/full
UPDATE 2000

full:        202M
incremental: 25M
884

The incremental backup is 12% of the full one. The -i argument is the manifest of the previous backup, which is how the server knows which blocks to send.

docker exec rbpg-pitr bash -c "find /lab18/incr -name 'INCREMENTAL.*' | head -2"

$ list two of the INCREMENTAL stub files

/lab18/incr/global/INCREMENTAL.6001
/lab18/incr/global/INCREMENTAL.1213_vm

Task 6 — Reconstruct, and prove it

docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_combinebackup \
  /lab18/full /lab18/incr -o /lab18/combined
echo "exit status: $?"
docker exec rbpg-pitr du -sh /lab18/combined
docker exec rbpg-pitr bash -c "find /lab18/combined -name 'INCREMENTAL.*' | wc -l"
docker exec -u postgres rbpg-pitr pg_verifybackup /lab18/combined

$ pg_combinebackup /lab18/full /lab18/incr -o /lab18/combined, then verify

exit status: 0
203M	/lab18/combined
0
backup successfully verified

Now start it, which is the only proof that counts:

docker exec rbpg-pitr bash -c "chmod 0700 /lab18/combined && chown -R postgres:postgres /lab18/combined"
docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_ctl \
  -D /lab18/combined -o "-p 5460 -c archive_mode=off" -l /tmp/combined.log start
sleep 4

docker exec -u postgres rbpg-pitr psql -X -p 5460 -d lab18 -c "
  SELECT count(*) AS rows,
         count(*) FILTER (WHERE payload LIKE 'q%') AS updated_rows,
         count(*) FILTER (WHERE payload LIKE 'p%') AS original_rows
  FROM t;"
docker exec rbpg-pitr grep -E "consistent|redo done|ready" /tmp/combined.log

$ start the combined directory on port 5460 and query it

waiting for server to start.... done
server started

rows  | updated_rows | original_rows 
--------+--------------+---------------
200000 |         2000 |        198000
(1 row)

2026-08-28 05:56:17.751 UTC [2778] LOG:  completed backup recovery with redo LSN 0/30000028 and end LSN 0/30000120
2026-08-28 05:56:17.751 UTC [2778] LOG:  consistent recovery state reached at 0/30000120
2026-08-28 05:56:17.751 UTC [2778] LOG:  redo done at 0/30000120
2026-08-28 05:56:17.786 UTC [2772] LOG:  database system is ready to accept connections

200,000 rows, 2,000 of them carrying the post-backup update. The reconstruction is exact, and the log shows it performing recovery from backup_label’s LSN before declaring itself consistent.

Task 7 — Compression

docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_ctl -D /lab18/combined stop -m fast

for C in none gzip lz4 zstd; do
  docker exec rbpg-pitr rm -rf /lab18/tar
  S=$(date +%s%N)
  if [ "$C" = "none" ]; then
    docker exec -u postgres rbpg-pitr pg_basebackup -D /lab18/tar -Ft -c fast
  else
    docker exec -u postgres rbpg-pitr pg_basebackup -D /lab18/tar -Ft -c fast --compress=$C
  fi
  E=$(date +%s%N)
  printf -- "--compress=%-6s  %6s  %s ms\n" "$C" \
    "$(docker exec rbpg-pitr du -sh /lab18/tar | cut -f1)" "$(( (E-S)/1000000 ))"
done

$ pg_basebackup -Ft at four compression settings

--compress=none      203M  230 ms
--compress=gzip      9.4M  938 ms
--compress=lz4        32M  188 ms
--compress=zstd       24M  255 ms

The ratios here are flattering — this table is repeat('p',200), which compresses extraordinarily well — but the relationship between the options holds. lz4 was the fastest of all four, faster even than no compression, because it wrote 170 MB less. gzip compressed hardest and took four times as long.

Task 8 — What -Xstream gives you

docker exec rbpg-pitr rm -rf /lab18/tar
docker exec -u postgres rbpg-pitr pg_basebackup -D /lab18/tar -Ft -c fast -Xstream
docker exec rbpg-pitr ls /lab18/tar

$ pg_basebackup -Ft -Xstream, then list the output directory

backup_manifest
base.tar
pg_wal.tar

pg_wal.tar is the difference. -Xstream (the default) opens a second connection and streams the WAL generated during the backup alongside the data, so the result is self-contained: it can be restored and started with no WAL archive at all.

The alternative, -Xnone, produces a backup that is useless without the archive covering the backup’s duration. That is a legitimate choice when you have a reliable archive and want to avoid a second connection, and a catastrophic one when you assumed you had an archive and did not.

Validation

test -s "$LAB/checkpoint-mode.txt" && echo "OK checkpoint-mode"
test -s "$LAB/verify.txt"          && echo "OK verify"
test -s "$LAB/incremental.txt"     && echo "OK incremental"
test -s "$LAB/reconstructed.txt"   && echo "OK reconstructed"

# The real validation is the one in Task 6: the reconstructed cluster
# started and returned the right rows. Repeat it as a check.
docker exec -u postgres rbpg-pitr psql -X -p 5460 -d lab18 -c \
  "SELECT count(*) FILTER (WHERE payload LIKE 'q%') = 2000 AS updates_present FROM t;"

Questions to answer without looking anything up:

1. pg_basebackup has printed “waiting for checkpoint” for three minutes. Is it broken?
2. A restored data directory will not start. Somebody suggests deleting backup_label. What happens if you do?
3. What does pg_verifybackup detect, and where in the backup’s life should you run it?
4. You keep one full and six daily incrementals. The Wednesday incremental is lost. Which backups can still be restored?
5. -Xnone was used and the WAL archive has a gap covering the backup window. Is the backup usable?

Expected Outcome

You have taken physical backups four ways, detected two kinds of damage, and reconstructed a working cluster from a full backup plus a 25 MB incremental — then started it and confirmed the exact row state.

The procedure to carry away:

# Take it, quickly and self-contained.
pg_basebackup -D /backup/$(date +%F) -Ft -Xstream -c fast --compress=zstd

# Verify it where it will be used, not where it was made.
pg_verifybackup /backup/2026-08-28
rc=$?; [ "$rc" -eq 0 ] || echo "BACKUP INVALID: $rc"

And the judgement: a backup that has been verified is a backup. A backup that has been started and queried is a restore capability. Lab 20 makes that the routine.

Troubleshooting

pg_basebackup appears to hang for a minute or more. It is waiting for a spread checkpoint. This is the default and it is not a fault — measured here, the same backup completed in 2.2 seconds with -c fast. Use -c fast when you want it now and can accept the I/O spike.

pg_verifybackup reports a checksum mismatch. That is the tool working. It compares every file against backup_manifest. Take the backup again and verify it where it will be restored, not only where it was made.

pg_verifybackup reports a file the manifest does not list. Something wrote into the backup directory after the backup completed. Measured on 18.6, it names the file and exits non-zero:

pg_verifybackup: error: "an_extra_file.txt" is present on disk but not in the manifest

A backup directory is immutable; treat any extra file as a reason to distrust the whole thing.

Incremental backup fails with WAL summarization is not enabled. summarize_wal must be on, and it must have been on continuously since the reference backup. Turning it on now does not make an older backup a valid reference.

pg_combinebackup fails on a missing chain member. The reconstruction needs every backup in the chain, in order. A missing member breaks everything after it while leaving earlier points intact — which is a genuinely different retention problem from a series of independent fulls, and the retention policy has to be written for it.

The reconstructed cluster will not start. Check the permissions on the data directory. Measured on 18.6, the server refuses and names both acceptable modes itself:

FATAL:  data directory "/tmp/dd" has invalid permissions
DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

Cleanup

docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_ctl -D /lab18/combined stop -m fast
docker exec rbpg-pitr rm -rf /lab18
docker exec -u postgres rbpg-pitr psql -X -c "DROP DATABASE IF EXISTS lab18;"

Production notes

• Verify the backup where it will be restored. A backup verified only on the host that produced it has not been shown to have survived the transfer, and the transfer is where backups are damaged.
• -c fast trades an I/O spike for a fast start. On a busy primary the default spread checkpoint is the kinder choice; during an incident it is the wrong one.
• -Xstream makes the backup self-contained: the WAL needed to reach a consistent state travels with it, so a restore does not depend on the archive being reachable.
• Incremental backups change the retention problem. Independent fulls expire independently; a chain expires as a chain, and losing one member invalidates everything downstream of it. Write the policy for the shape you have.
• A verified backup is a backup. A backup that has been started and queried is a restore capability, and only the second one is what an RTO commitment rests on.

What You Learned

• The default checkpoint is why pg_basebackup seems to hang — 2.2 seconds with -c fast against over two minutes without it.
• backup_manifest makes damage detectable, and pg_verifybackup catches both altered files and unexpected ones.
• Incremental backup needs summarize_wal on continuously, from before the reference backup was taken.
• pg_combinebackup needs the whole chain. A missing member breaks every later point and leaves earlier ones intact.
• Compression and -Xstream are independent choices: one changes the size, the other changes whether the backup can stand alone.
• A data directory must be 0700 or the server refuses to start, which is the commonest reason a restored cluster does not come up.