Learn pgBackRest - Recovery Scenarios: Corrupt, Lost, Clone

#Introduction

In episode 8 we mastered the restore command. Now comes the real test: recovery scenarios in real situations. Because disasters never arrive as neat cases — a cluster corrupts in the middle of peak hours, the data directory is lost after a disk failure, or there's a sudden need for a second instance. In episode 9 we train three scenarios: corrupt, lost, and clone, then close with a crash simulation and PITR you can repeat on your own.

This is why a good DBA differs from an "adequate" one: not because they can run restore, but because they've trained disaster scenarios before the disaster comes.

#Scenario 1: Corrupt Cluster

#Symptoms

PostgreSQL can't start, or shows corruption errors like invalid page in block or could not read block. The server still exists, the data still exists, but it's broken.

#Recovery Procedure

Since the data is corrupt, trusting local files makes no sense — we overwrite with the backup using --delta so only the different files are replaced:

sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta restore
sudo systemctl start postgresql

Important note: if the corruption comes from physical disk (bad sectors, corrupt filesystem), don't restore to the same disk — that only copies the problem. Move PGDATA to a healthy disk, then restore.

#Scenario 2: Lost Data Directory

#Symptoms

/var/lib/postgresql/16/main is gone or unreadable — dead disk, accidental format, or a deleted instance. Nothing can be salvaged locally; everything must be rebuilt from the repository.

#Rebuild Procedure

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main
sudo -u postgres pgbackrest --stanza=main --type=immediate restore
sudo systemctl start postgresql

Note: restore recreates the entire PGDATA including postgresql.conf (the backed-up version) and the PG_VERSION file. If server parameters were changed after the last backup, you'll need to realign them — postgresql.auto.conf and other config files outside PGDATA are not included in the backup.

#Rebuilding the Configuration

After the restore, check important parameters that might differ from expectations:

psql -U postgres -c "SHOW archive_mode; SHOW listen_addresses;"

Adjust postgresql.conf/postgresql.auto.conf if needed, then restart once more. This procedure is full recovery — losing the entire data directory no longer means losing data.

#Scenario 3: Cloning an Instance from a Backup

#Use Cases

You need a second instance: a staging environment close to production, analytics without burdening the primary, or a new standby for HA (covered in depth in episode 18). All of these can be cloned from the same backup — without touching the primary.

#Clone Procedure

Restore to a separate directory with --delta against an empty PGDATA:

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

--pg1-path points the restore at a different directory than the config. After the restore, adjust the port and socket in the clone's postgresql.conf to avoid conflicts, then start it as a separate instance:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/staging start

#Exercise: Crash Simulation and PITR

Let's combine everything into one exercise you can repeat anytime.

#1. Set up data and a restore point

CREATE TABLE uji_pit (id int primary key, ts timestamptz default now());
INSERT INTO uji_pit (id) SELECT generate_series(1,100);
SELECT pg_create_restore_point('before-crash');

#2. Keep writing, then "crash"

INSERT INTO uji_pit (id) SELECT generate_series(101,200);

Then stop the cluster forcefully — simulating a crash without a clean shutdown:

sudo systemctl stop postgresql

#3. Restore to a specific time / restore point

Recover to the before-crash restore point (only the 100 rows that existed then):

sudo -u postgres pgbackrest --stanza=main --type=name 'before-crash' --target-action=pause restore
sudo systemctl start postgresql

#4. Verify

SELECT count(*) FROM uji_pit;  -- should be 100, not 200

If the result is 100, PITR works perfectly. Repeat the exercise with --type=time or --type=lsn to practice the other variants.

#Conclusion

Key takeaways:

• Corrupt: stop the server, --delta restore, restart — move to a healthy disk if the cause is physical.
• Lost: rebuild PGDATA from scratch with restore --type=immediate, then realign the configuration.
• Clone: restore to a new directory with --pg1-path for staging, analytics, or a standby.
• A crash + PITR exercise tests the entire chain: backups, WAL, and team procedures.
• Verifying the row count is the proof of recovery, not just "the server can start."

In the next episode we'll make all of this automatic: scheduling & retention policy — scheduling weekly fulls and daily differentials via cron or a systemd timer, understanding the role of real-time WAL archiving, and determining retention values based on your business RPO and RTO. A reliable backup shouldn't depend on human memory!