Lab: Design and validate a backup strategy

This lab designs a backup strategy for a sample workload and runs a test restore. By the end you will have a documented strategy and a verified restore procedure.

Tasks

Task 1: Define the workload

Sample workload:

Application: PostgreSQL database with 50 GB of data.
Criticality: business-critical. RPO 1 hour, RTO 4 hours.
Compliance: 30 days retention, encrypted at rest.
Operations: 1 application, 1 host, 1 region.

Task 2: Choose backup method

For PostgreSQL, take an application-consistent base backup. Two supported methods exist, and only one of them is a single command:

# Option 1 (recommended): pg_basebackup.
# It streams the WAL generated during the copy and writes
# backup_label for you, so the result is self-consistent.
pg_basebackup -D /backup/base -Ft -z -Xs -P -c fast

# Option 2: low-level file copy.
# A bare rsync of a running PGDATA is NOT a backup. It is only
# valid when bracketed in a SINGLE, continuously open session
# and paired with archived WAL:
#
#   psql -c "SELECT pg_backup_start('base', fast => true);"   <- session stays open
#   ...copy PGDATA now...
#   psql -c "SELECT * FROM pg_backup_stop();"                 <- returns backup_label
#
# and it requires archive_mode = on with every WAL segment
# between the start and stop LSNs retained. Write the returned
# backup_label into the copy before you call it a backup.

Choose pg_basebackup. It is not merely simpler - it removes the two ways Option 2 is usually got wrong.

`rsync -a /var/lib/postgresql` of a live database is not a backup

It looks like one. It completes without error, the byte count looks right, and it lands in your monitoring as a green job. It is unrestorable.

Two independent reasons:

No backup_label, so recovery has no starting LSN. The copy contains a control file pointing at a checkpoint from whenever the copy happened to read it. PostgreSQL cannot know which WAL record to begin replaying from. Archived WAL does not rescue this - the WAL is useless without a valid start point, which is exactly what pg_backup_start() and pg_backup_stop() establish. The trailing ”# Plus WAL archiving” comment implies otherwise, and that implication is the trap.
The copy is not internally atomic. rsync walks a 50 GB tree over minutes while the postmaster rewrites pages underneath it. Individual 8 kB pages are copied mid-write, so the result contains torn pages.

The failure mode is delayed and expensive. The restore either refuses to start, or starts and then throws invalid page header in block ... of relation ... or could not read block as it reaches the torn pages - during the incident you took the backup for. Note that the lab’s own test restore in Task 5 exercises the Borg path, so a broken Option 2 would never be caught in-lab either. Test the path you actually run.

Task 3: Design 3-2-1-1-0

3 copies: production database, local backup, offsite backup.
2 media: local disk, cloud (S3).
1 offsite: S3 in a different region.
1 immutable: S3 Object Lock COMPLIANCE.
0 errors: quarterly test restore.

Task 4: Configure encryption

Use BorgBackup or pg_basebackup with encryption:

# BorgBackup with encryption
borg init --encryption repokey-blake2 /backup/repo

# Archive the pg_basebackup OUTPUT, not the live PGDATA
borg create /backup/repo::base-{now} /backup/base

Choose BorgBackup for the offsite copy; pg_basebackup for the local copy.

Escrow the key immediately after borg init, before the first archive exists:

# Machine-readable export - store offline, off-site
borg key export /backup/repo /root/borg-key-backup.txt

# Printable form for a safe or paper escrow
borg key export --paper /backup/repo /root/borg-key-paper.txt

# Prove the export works before relying on it
borg key import /backup/repo /root/borg-key-backup.txt

Then move both files off the host.

Task 5: Run a test restore

Restore the most recent backup to a test host:

# On the test host. borg extract writes into the CURRENT directory, and any
# trailing argument is an in-archive path FILTER, not a destination - so cd
# there first rather than passing the target as an argument.
sudo mkdir -p /var/lib/postgresql/restore
cd /var/lib/postgresql/restore

# Borg has no '::latest' selector (that is restic's). Resolve the newest
# archive by name.
LATEST=$(borg list --last 1 --format '{archive}' /backup/repo)
echo "restoring: $LATEST"
borg extract --list /backup/repo::"$LATEST"

# PostgreSQL refuses to start unless PGDATA is owned by postgres and mode 0700
sudo chown -R postgres:postgres /var/lib/postgresql/restore
sudo chmod 0700 /var/lib/postgresql/restore

sudo -u postgres pg_ctl -D /var/lib/postgresql/restore start
sudo -u postgres psql -c 'SELECT count(*) FROM users;'

The test restore verifies that the backup is restorable, the restore procedure is correct, and the application can start with the restored data.

A restore that extracts nothing exits 0

Three details in that block are the difference between a proven restore and a green tick over an empty directory.

borg extract ARCHIVE PATH... does not take a destination. It always extracts relative to the current working directory, and every trailing argument selects which members to pull out. Passing /var/lib/postgresql/restore as the second argument therefore asks borg for archive members under that path — which do not exist, because paths in the archive are stored without the leading slash. Borg extracts nothing, exits 0, and the directory you created stays empty. --list is there so you see the files go past.

::latest is restic syntax. Borg resolves an archive by name and will fail with “archive does not exist” — which at least is loud.

The ownership step matters because pg_ctl refuses to start on a data directory it does not own or that is group- or world-readable, and the extracted tree is owned by whoever ran the extract.

Finish the task by proving the count matches the source, not merely that the query ran.

Task 6: Document the strategy

BACKUP STRATEGY
===============
Workload: PostgreSQL database
Application: <name>
Criticality: business-critical
RPO: 1 hour
RTO: 4 hours
Compliance: 30 days retention, encrypted

Method: pg_basebackup (consistent base) + WAL archiving,
        then BorgBackup of the pg_basebackup output
        NOT a bare rsync/borg of the live PGDATA
Prerequisite settings: wal_level = replica, archive_mode = on,
        archive_command retaining every segment for 30 days

3-2-1-1-0:
- 3 copies: production, local backup, offsite
- 2 media: local disk, S3
- 1 offsite: S3 in different region
- 1 immutable: S3 Object Lock COMPLIANCE
- 0 errors: quarterly test restore

Schedule:
- Hourly: WAL archive
- Daily: pg_basebackup to local disk
- Daily: BorgBackup to S3 with Object Lock

Encryption:
- Local: pg_basebackup native
- Offsite: BorgBackup repokey (AES-OCB)
- Key: stored in vault, 2 copies
- Key escrow: borg key export (+ --paper) held OUTSIDE the
        vault and OUTSIDE the primary site; opened once a
        quarter to prove it works
- Passphrase rotation: quarterly, for hygiene only.
        borg key change-passphrase re-wraps the same key; it
        does NOT re-encrypt data, so an old exported key still
        reads every old archive. True rotation = new repository.

Test restore:
- Quarterly: full restore to test host
- Procedure: extract from BorgBackup, start PostgreSQL, verify
- Last tested: <date>

Objectives

Prerequisites