Point-in-Time Recovery with pgBackRest and Continuous WAL Streaming

For production databases supporting live customer transactions, traditional logical exports via pg_dump are insufficient. A pg_dump export represents a single point in time, incurs high read IO, and leaves every transaction executed between backup windows completely vulnerable to data loss. Enterprise data durability requires physical base backups combined with continuous Write-Ahead Log (WAL) archiving.

pgBackRest is an open-source backup tool tailored specifically for PostgreSQL. It provides asynchronous WAL archiving, parallel multi-threaded compression (LZ4/Zstandard), delta restores, and point-in-time recovery (PITR) down to the exact microsecond.

The Operational Mechanics of Physical WAL Streaming

PostgreSQL guarantees ACID compliance through its Write-Ahead Log. Before any table row modification is written to table heap files, the corresponding binary change record is flushed to the WAL. Under streaming WAL archiving, as soon as a 16MB WAL segment fills up (or when archive_timeout triggers), PostgreSQL invokes an archive command to push that segment to offsite repository storage.

If a catastrophic database corruption or human error (such as an unindexed TRUNCATE or DROP TABLE) occurs at 14:17:32, an administrator can restore the base backup from Sunday morning and instruct PostgreSQL to replay WAL segments up to 14:17:31, recovering every committed transaction without losing the intervening work.

Installing and Configuring pgBackRest

Install pgBackRest alongside PostgreSQL 16 on Ubuntu 24.04 from the PostgreSQL Global Development Group (PGDG) repository:

# Install pgBackRest binary
sudo apt-get update
sudo apt-get install -y pgbackrest

# Create logging and configuration directories
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest

Edit /etc/pgbackrest/pgbackrest.conf to define your primary database cluster stanza:

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
process-max=4
log-level-console=info
log-level-file=detail
compress-type=lz4
compress-level=3
start-fast=y

[appdb]
pg1-path=/var/lib/postgresql/16/main
pg1-user=postgres

Setting process-max=4 allows pgBackRest to leverage 4 CPU cores on your Intel Xeon instance simultaneously during full backups.

Wiring PostgreSQL WAL Archiving

Next, configure PostgreSQL to push completed 16MB WAL segments directly to pgBackRest using archive_command:

# /etc/postgresql/16/main/conf.d/98-pgbackrest.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=appdb archive-push %p'
max_wal_senders = 10
wal_level = replica
hot_standby = on
archive_timeout = 300

Restart PostgreSQL and initialize the pgBackRest stanza:

sudo systemctl restart postgresql
sudo -u postgres pgbackrest --stanza=appdb stanza-create
sudo -u postgres pgbackrest --stanza=appdb check

The check command confirms that the database server can communicate with pgBackRest and that archive-push successfully writes WAL segments into the repository directory.

Expand

Executing Full, Differential, and Incremental Backups

pgBackRest supports three distinct backup tiers:

• **Full**: Copies every file in the database cluster.
• **Differential**: Copies only files that changed since the last full backup.
• **Incremental**: Copies only files that changed since the last full or differential backup.

# Trigger a full base backup
sudo -u postgres pgbackrest --stanza=appdb --type=full backup

# Trigger a fast differential backup
sudo -u postgres pgbackrest --stanza=appdb --type=diff backup

# Inspect the backup manifest and WAL timelines
sudo -u postgres pgbackrest --stanza=appdb info

Performing a Point-in-Time Recovery

If an erroneous migration or accidental table deletion occurs, pgBackRest allows restoring the cluster to the exact second prior to the incident:

# Stop PostgreSQL before executing a restore
sudo systemctl stop postgresql

# Execute Point-in-Time restore to a precise timestamp
sudo -u postgres pgbackrest --stanza=appdb   --type=time   --target="2026-09-23 14:15:00 UTC"   --target-action=promote   --delta restore

# Restart the database
sudo systemctl start postgresql
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

The --delta flag compares existing database files on disk and transfers only differing chunks, slashing restore durations from hours to minutes.

Deploy mission-critical PostgreSQL databases on Netbay Linux VPS in under 60 seconds with High-Speed SSD and DDoS protection in Lucknow DC01 — netbayhosts.in.