Mastering Point-In-Time Recovery in PostgreSQL: A Step‑by‑Step Guide with WAL Archives and pgBackRest

Recovering a PostgreSQL database to a precise moment in the past—known as Point‑In‑Time Recovery (PITR)—is an essential skill for every DBA and DevOps engineer. Whether you’re dealing with accidental data deletion, a rogue UPDATE, or a catastrophic hardware failure, a well‑designed backup strategy that leverages Write‑Ahead Log (WAL) archives and a robust tool like pgBackRest can save you from costly downtime and data loss. In this article, we’ll walk through the fundamentals of PITR, set up continuous WAL archiving with pgBackRest, and then execute a recovery to an exact timestamp. By the end, you’ll have a repeatable, production‑ready workflow that you can document and automate.

1. Foundations of Point‑In‑Time Recovery

1.1 What is a Write‑Ahead Log?

PostgreSQL’s WAL is a sequential, append‑only record of every change made to the database. Before any data page is written to disk, the change is first logged in the WAL. This guarantees durability and enables crash recovery. Because WAL entries are immutable and strictly ordered, they also form the backbone of PITR.

1.2 How PITR Works

1. Base backup – A snapshot of the data directory taken at a known point in time.
2. WAL archive – A continuous stream of WAL segment files (typically 16 MB each) stored on external media.
3. Recovery target – The exact timestamp, transaction ID, or LSN you want to restore to.

During recovery, PostgreSQL restores the base backup and then replays archived WAL files up to—but not beyond—the specified target. The result is a database state that mirrors the original at that exact moment.

1.3 Why Use pgBackRest?

pgBackRest is an open‑source backup and restore solution that simplifies WAL archiving, supports parallel compression, and provides built‑in retention policies. Its key advantages for PITR include:

• Automatic WAL archiving and cleanup.
• Full, differential, and incremental backup support.
• Seamless pg_basebackup-style restores.
• Built‑in verification and checksum validation.

2. Preparing the PostgreSQL Cluster for PITR

2.1 Enable WAL Archiving in postgresql.conf

# postgresql.conf
wal_level = replica               # Must be at least 'replica' for archiving
archive_mode = on
archive_command = 'pgbackrest --stanza=mydb archive-push %p'
archive_timeout = 60              # Force a segment switch at least every minute
max_wal_senders = 5
wal_keep_size = 512MB             # Optional, helps with replication lag

After editing, reload the configuration:

SELECT pg_reload_conf();

2.2 Install and Configure pgBackRest

On a Debian‑based system:

sudo apt-get install pgbackrest
sudo mkdir -p /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest

Create the stanza configuration file /etc/pgbackrest.conf:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=3
repo1-retention-archive=30

[mydb]
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432

Initialize the stanza (this also creates the first base backup):

sudo -u postgres pgbackrest --stanza=mydb stanza-create
sudo -u postgres pgbackrest --stanza=mydb backup

2.3 Verify WAL Archiving

Generate a small transaction and confirm the WAL segment appears in the repository:

psql -c "CREATE TABLE pitr_test(id INT);"
sudo -u postgres pgbackrest --stanza=mydb archive-push /var/lib/postgresql/15/main/pg_wal/0000000100000000000000A1

List archived WAL files:

sudo -u postgres pgbackrest --stanza=mydb info

3. Executing a Point‑In‑Time Recovery

3.1 Identify the Desired Recovery Target

Choose a recovery target: a timestamp, LSN, or transaction ID. For this tutorial, we’ll recover to a timestamp. First, locate the time of the incident:

SELECT pg_xact_commit_timestamp('your_transaction_xid');

Assume the unwanted DELETE happened at 2026‑10‑14 09:42:15 UTC. We’ll recover to 2026‑10‑14 09:41:00 UTC, one minute before the error.

3.2 Stop the Current Instance

sudo systemctl stop postgresql@15-main

Make sure no processes are holding the data directory.

3.3 Restore the Base Backup

Use pgBackRest to restore the most recent full backup into a fresh data directory (or over the existing one after moving it aside):

sudo -u postgres mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main.bak
sudo -u postgres pgbackrest --stanza=mydb restore

3.4 Configure Recovery

PostgreSQL 15 uses standby.signal and postgresql.auto.conf for recovery settings. Place the following lines in the restored data directory (/var/lib/postgresql/15/main):

touch /var/lib/postgresql/15/main/standby.signal
echo "restore_command = 'pgbackrest --stanza=mydb archive-get %f \"%p\"'" >> /var/lib/postgresql/15/main/postgresql.auto.conf
echo "recovery_target_time = '2026-10-14 09:41:00 UTC'" >> /var/lib/postgresql/15/main/postgresql.auto.conf
echo "recovery_target_action = 'pause'" >> /var/lib/postgresql/15/main/postgresql.auto.conf

3.5 Start PostgreSQL and Verify

sudo systemctl start postgresql@15-main

PostgreSQL will launch, replay WAL up to the target, and then pause. Connect and confirm the state:

psql -c "\dt"
psql -c "SELECT COUNT(*) FROM pitr_test;"

Once verified, promote the server to normal operation:

SELECT pg_promote();   -- Or simply remove the recovery signal file and restart

3.6 Clean Up

After a successful promotion, remove the old backup directory and recovery files:

sudo -u postgres rm -rf /var/lib/postgresql/15/main.bak
sudo -u postgres rm /var/lib/postgresql/15/main/standby.signal

4. Automating and Safeguarding Your PITR Process

4.1 Scheduling Regular Backups

Use cron or systemd timers to run incremental backups every hour and a full backup daily:

# /etc/cron.d/pgbackrest
0 2 * * * postgres pgbackrest --stanza=mydb backup --type=full
0 * * * * postgres pgbackrest --stanza=mydb backup --type=diff

4.2 Retention Policies

Adjust repo1-retention-* settings in pgbackrest.conf to keep enough history for your SLA. A common pattern is:

• 2 full backups (weekly)
• 7 daily differential backups
• 30 days of archived WAL

4.3 Monitoring and Alerts

Expose pgbackrest --stanza=mydb info to your monitoring stack (Prometheus, Zabbix, etc.) and alert on:

• Failed backup jobs
• WAL archive lag exceeding a threshold
• Low free space in the archive repository

4.4 Testing Recovery Regularly

Schedule a quarterly “fire drill”:

1. Clone a fresh VM or container.
2. Run the full restore + PITR steps to a known point.
3. Validate data integrity with checksums or application‑level tests.

Document the results and adjust retention or archive settings as needed.

Conclusion

Point‑In‑Time Recovery transforms a potentially disastrous data incident into a manageable operation. By configuring continuous WAL archiving, leveraging pgBackRest for reliable backups, and following a disciplined restore procedure, you can bring any PostgreSQL cluster back to an exact moment in the past with confidence. Remember that the most critical part of any recovery plan is regular testing—automate backups, monitor archive health, and rehearse restores so that when the real event occurs, you’ll know exactly which commands to run.

Implement the steps outlined above, tailor the retention policies to your organization’s RPO/RTO targets, and you’ll have a production‑grade PITR solution that safeguards your data and keeps your services running smoothly.