Restore matters more than backup - the discipline of restore

A backup that has not been restored is a hope, not a backup. The restore discipline - test, document, run - is what makes backups real.

Why restore is more important

Backup is creating a copy. Restore is recovering from a disaster. The whole point of backup is to enable restore. A backup you cannot restore is worthless.

Common backup failure modes:

• Backup is corrupted.
• Backup is incomplete (some files missed).
• Restore procedure is wrong.
• Dependencies are missing (e.g. wrong kernel modules).
• Encryption key is lost.
• Backup is on the same failure domain as the source.

A tested restore catches all of these.

The restore discipline

For every backup system:

1. Test restore quarterly: full restore to a non-production host.
2. Document the procedure: each restore step in a runbook.
3. Time the restore: how long does it take? Is it within RTO?
4. Validate the data: is the data correct and complete?
5. Document the timing: every test restore produces a time. Track trends.

Build a restore runbook

A restore runbook is a step-by-step procedure:

RESTORE RUNBOOK: PostgreSQL on prod-db-01
========================================

Time to complete: 1-2 hours (target: 4 hours RTO)

Pre-requisites:
- Access to backup host via SSH
- Backup encryption key from vault
- Target host with PostgreSQL installed but stopped

Steps:
1. Retrieve encryption key from vault
2. SSH to backup host
3. List available backups:
   borg list /backup/repo
4. Choose the most recent backup before incident
5. Stop the service before touching anything:
   systemctl stop postgresql
6. Extract the backup to a STAGING path, never over PGDATA:
   mkdir -p /restore && cd /restore
   borg extract /backup/repo::backup-2026-08-09
7. PRESERVE the current data directory - this is the rollback:
   mv /var/lib/postgresql/16/main \
      /var/lib/postgresql/16/main.pre-restore-$(date +%F)
   mkdir /var/lib/postgresql/16/main
8. Move the restored data into the now-empty directory:
   rsync -aAXH --numeric-ids \
     /restore/var/lib/postgresql/16/main/ \
     /var/lib/postgresql/16/main/
9. Set ownership AND mode:
   chown -R postgres:postgres /var/lib/postgresql/16/main
   chmod 0700 /var/lib/postgresql/16/main
   restorecon -R /var/lib/postgresql        # RHEL family only
10. Start PostgreSQL:
    systemctl start postgresql
11. Verify data:
    psql -c "SELECT count(*) FROM users;"
12. Document the restore time and any issues
13. Keep main.pre-restore-<date> until the restore is signed off,
    then remove it and reclaim the space

Rollback:
- The restore is reversible ONLY because of step 7. To back out:
  systemctl stop postgresql
  rm -rf /var/lib/postgresql/16/main
  mv /var/lib/postgresql/16/main.pre-restore-<date> \
     /var/lib/postgresql/16/main
  systemctl start postgresql
- Confirm free space for the copy BEFORE step 7; the host must
  hold the old PGDATA and the restored one at the same time
- Document the failure and try a different backup

Escalation:
- If key is not in vault, escalate to security team
- If no good backup exists, escalate to data team

The runbook is the procedure. Update it every time the restore procedure changes.

Why those four details matter

Each of the changes above replaces a step that reads as reasonable and is not.

Rename, do not overwrite. The obvious version of step 8 is rsync -a /restore/var/lib/postgresql/ /var/lib/postgresql/ straight over the live directory. It destroys the pre-restore state the moment it runs, so by the time the failure shows up at step 10 there is nothing to go back to - not even the damaged-but-partially-readable original, which is frequently the better artefact for forensic recovery. A runbook that says “if restore fails, the original data is unchanged” after an in-place rsync is stating something false at the worst possible moment.

Restore into an empty directory. rsync without --delete merges. Files from the failed instance that are absent from the backup survive and mix into the restored cluster: stale pg_wal segments, an orphaned postmaster.pid, relation files from a different timeline. PostgreSQL may start on that and then fail under load with checksum or xlog errors, or serve inconsistent reads. Renaming the old directory away gives you an empty target, which is safer than trusting a flag.

-aAXH --numeric-ids, not -a. -a does not carry ACLs (-A), extended attributes (-X, which is where SELinux labels live), or hard links (-H). --numeric-ids stops UIDs being remapped through a different host’s /etc/passwd. Restoring with plain -a onto an SELinux host leaves the data directory mislabelled and the service is denied access to its own files.

Mode as well as ownership. PostgreSQL refuses to start if PGDATA is not 0700 or 0750, and chown does not fix modes. The failure message points at permissions, not at the restore, so it costs time you do not have.

Schedule restore tests

The cost of a missed test is high; the cost of running it is low.

Knowledge check