Learn pgBackRest - Restore: Full, Delta & PITR

#Introduction

Every previous episode built something: stanzas, backups, WAL, retention. But the whole system only means something when restore works. This is the irony of backups: they're most often neglected precisely when they're needed most critically. In episode 8 we reverse direction and master pgbackrest restore — from recovering the latest state to precise PITR to a time, LSN, or restore point.

Imagine pgBackRest as a photo archive: backup is the camera taking pictures, WAL is the film recording every moment, and restore is the print machine producing the photo at the moment you ask for. Episode 8 is practice using that machine correctly.

#Restore Basics

#Important Prerequisites

pgbackrest restore requires the target PGDATA directory to be in the correct state. pgBackRest's default is to copy to pg1-path from the config — and it refuses to overwrite existing files. For an empty target (new cluster), no extra option is needed:

Restore to an empty cluster

sudo -u postgres pgbackrest --stanza=main restore

#Choosing the Backup to Restore

By default pgBackRest takes the last available backup for the stanza. To select a specific backup, use --set with a label from pgbackrest info:

Restore a specific backup

sudo -u postgres pgbackrest --stanza=main --set=20260813-090000F restore

#Restore Types: Default, Immediate, and PITR

#--type=immediate: The Latest State

To recover to the most recent available state (replaying all archived WAL up to the latest point), use --type=immediate:

Restore to the latest state

sudo -u postgres pgbackrest --stanza=main --type=immediate restore

This is the most common choice for total recovery from a disaster: you want to get back to the state "before everything broke," as recent as possible.

#--type=time: Point-in-Time Recovery

True PITR — recover to a specific time. Very useful for undoing a mistake that happened at a particular time (for example a DROP TABLE at 09:30):

Restore to a specific time

sudo -u postgres pgbackrest --stanza=main --type=time '2026-08-13 09:30:00+07' restore

The time format follows the cluster's timezone configuration. When this restore type finishes, PostgreSQL stops at the requested point.

#--type=lsn: Precision to the Log Sequence Number

For maximum precision, specify the point as an LSN (Log Sequence Number). The LSN can be taken from error logs, application output, or pg_current_wal_lsn():

Restore to a specific LSN

sudo -u postgres pgbackrest --stanza=main --type=lsn '16/B374D848' restore

LSN allows recovery between two transactions — precision that time can't guarantee because clocks can drift.

#--type=name: Restore Point

PostgreSQL allows creating restore points with names — logical markers in the WAL stream. Create one in the database, then recover to that name:

sql

SELECT pg_create_restore_point('before-v2-migration');

Restore to a restore point

sudo -u postgres pgbackrest --stanza=main --type=name 'before-v2-migration' restore

Tip

Create a restore point before risky operations (schema migrations, batch updates, table drops). This gives you a named marker you can restore to at any time — much easier to remember than an LSN or timestamp.

#Delta Restore: Syncing an Existing Cluster

#The Problem with an Existing Cluster

If PGDATA still contains data (for example a server failed midway, or you want to "overwrite" a running cluster), the default restore refuses to work because files already exist. Deleting everything then restoring means longer downtime.

#The Solution: --delta

--delta compares the files in PGDATA with the backup contents and only overwrites the ones that differ — identical files are left alone. This is the fastest way to recover a cluster that's mostly intact:

Delta restore to an existing cluster

sudo -u postgres pgbackrest --stanza=main --delta restore

--delta is also useful for downgrading an old cluster to a specific backup version (episode 19), or syncing after a partial crash. Add --force if you want to force a restore into a non-empty directory without the delta check.

Warning

--delta removes files present in PGDATA but not in the backup — that's what makes it consistent with the backup contents. Make sure you understand this: don't use --delta on a PGDATA containing newer data you want to keep. For that case, restore to a separate directory.

#Handling Recovery: Target Action

After PITR stops at the requested point, the cluster enters recovery mode. For control during drills (episode 16), set the target action:

Pause after PITR

sudo -u postgres pgbackrest --stanza=main --type=time '2026-08-13 09:30:00+07' --target-action=pause restore

With --target-action=pause, PostgreSQL stops at the target without opening writes — allowing data verification before letting the cluster serve traffic. Once confident, resume with SELECT pg_wal_replay_resume();.

#Verifying the Restore Result

After the restore finishes, start the cluster and check:

Verify data after restore

sudo systemctl start postgresql
psql -U postgres -c "SELECT count(*) FROM aplikasi.pelanggan;"

If you restored to a time before a certain transaction, the rows added after that time must be gone. That's the proof that PITR works.

#Conclusion

Key takeaways:

Default restore uses the last backup; --set selects a specific backup.
--type=immediate = latest state; --type=time = PITR; --type=lsn = log precision; --type=name = named restore point.
--delta overwrites only what's different in an existing PGDATA — the fastest recovery.
--target-action=pause allows verification before the cluster serves traffic.
Always verify the data after a restore — don't trust until you prove it.

In the next episode we'll test real recovery scenarios: a corrupt cluster recovered to the last backup + WAL, the loss of the entire data directory that must be rebuilt, and cloning a new standby instance from a backup. All ending with a crash simulation and PITR to a specific time exercise — the skill that separates real DBAs!