Learn pgBackRest - Restore Testing (Drill)

#Introduction

In episode 9 we practiced manual recovery — now we level up: making restore testing a scheduled routine. This is the difference between a team that "has backups" and a team that "knows its backups work." Many organizations only discover their backups are broken when they need them — and that's when it's already too late.

This episode's philosophy is simple and uncompromising: a backup that was never restored isn't a backup. It's hope disguised as a plan.

#Why Restore Testing Matters

#Failures Invisible to Backups

A backup can succeed for years — clean logs, healthy-looking info — yet the restore still fails. The causes vary: silently corrupted backup files, missed WAL, broken compression, or configuration changed after the backup was made. Only unpacking and running it proves everything is intact.

#The "Believe Nothing, Verify Everything" Mindset

Every drill is evidence: the backup taken today can really bring the database back to life tomorrow. This isn't a fun exercise — it's an honesty audit of the system you've built over the previous 15 episodes.

#Doing a Restore Test to a Separate Instance

#The Principle: Don't Touch Production

A drill goes to a separate instance — a different PGDATA directory, a different port, and not the same host as production. The goal is to test the backup, not to break a healthy environment.

#The Basic Drill Procedure

sudo mkdir -p /var/lib/postgresql/drill
sudo chown postgres:postgres /var/lib/postgresql/drill
sudo -u postgres pgbackrest --stanza=main --delta \
  --pg1-path=/var/lib/postgresql/drill restore

Adjust the port in the restored postgresql.conf so it doesn't conflict with production:

sudo -u postgres sed -i "s/^port = .*/port = 5433/" /var/lib/postgresql/drill/postgresql.conf

Then start the drill instance:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/drill -l /tmp/drill.log start

#Validating Data: Checksums and Row Counts

A restore that "starts successfully" isn't enough. Validate the data with three layers:

#1. Row Count

SELECT count(*) FROM aplikasi.pelanggan;
SELECT count(*) FROM aplikasi.pesanan;

Compare against the numbers recorded during production (from a daily query or from a previous backup).

#2. Data Checksums

Run block validation at the PostgreSQL level:

SET default_table_access_method = heap;
SELECT sum(hashtextextended(row_to_json(t)::text, 0))
FROM aplikasi.pelanggan t;

Or use pg_checksums to validate the data file integrity:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_checksums -c -D /var/lib/postgresql/drill

#3. Sample Business Queries

Run a few queries that represent real workload — recent transactions, daily reports, searches. If the results make sense, the data was recovered correctly.

#Automating the Drill

#Drill Script + Report

A monthly drill should be runnable with a single command and produce a report. The core script:

#!/usr/bin/env bash
set -euo pipefail
 
stanza="main"
drill_dir="/var/lib/postgresql/drill"
report="/var/log/pgbackrest/drill-report.txt"
 
rm -rf "$drill_dir"
mkdir -p "$drill_dir"
chown postgres:postgres "$drill_dir"
 
sudo -u postgres pgbackrest --stanza="$stanza" --delta \
  --pg1-path="$drill_dir" restore
 
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
  -D "$drill_dir" -l /tmp/drill.log start
 
result=$(sudo -u postgres psql -p 5433 -d aplikasi -tAc \
  "SELECT 'row_count=' || count(*) FROM pelanggan;")
 
echo "=== PGBACKREST DRILL REPORT ===" > "$report"
echo "date: $(date -Is)" >> "$report"
echo "stanza: $stanza" >> "$report"
echo "result: $result" >> "$report"
echo "=== DONE ===" >> "$report"
 
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D "$drill_dir" stop -m fast

Schedule it monthly via a systemd timer, and create an alert (episode 14) that fires if the script fails or the report doesn't contain row_count=.

#Report as Audit Evidence

Store the report in an archived directory (for example S3, episode 12). This report becomes audit evidence that recovery was indeed tested — important for teams under compliance (SLA, ISO, HIPAA, etc.).

#Documenting the Runbook

A runbook is the document that saves the night while on-call. The required structure:

1. Symptoms — list of scenarios: corrupt cluster, dead disk, lost PGDATA, missed PITR.
2. Step-by-step recovery steps — exact commands, not vague instructions.
3. Verification — how to prove the recovery succeeded (row counts, checksums, sample queries).
4. Rollback — what to do if a step turns out wrong (for example a restore to the wrong time).
5. Contacts & escalation — who to contact and when.

## Scenario: Lost PGDATA
 
1. Stop the service: systemctl stop postgresql
2. Delete PGDATA: rm -rf /var/lib/postgresql/16/main
3. Restore: sudo -u postgres pgbackrest --stanza=main --type=immediate restore
4. Start: systemctl start postgresql
5. Verify: psql -c "SELECT count(*) FROM aplikasi.pelanggan;" (should = 10420)
6. If it doesn't match: contact a senior DBA before continuing

#Conclusion

Key takeaways:

• Periodic restore tests to a separate instance are the only way to prove a backup works.
• Three-layer validation: row counts, checksums, and business queries — not just "the server can start."
• Automate the drill with a script + report; archive the report as audit evidence.
• Alert when a drill fails — a drill failure is a backup system failure.
• A clear, complete runbook that's been tested by someone else.

In the next episode we'll follow the version developments: v2.59.0 & the latest features — the 20 July 2026 release with PostgreSQL 17/18 support, its important fixes, and the release trail of 2.56 (S3 fixes) and 2.54 (backup from standby). Keeping pgBackRest up to date is part of keeping your backups reliable!