pgBackRest: PostgreSQL Backup, PITR Recovery, and Troubleshooting

Why pgBackRest?

pg_dump is fine for small databases, but it’s slow, doesn’t support PITR, and cannot do incremental backups. pgBackRest solves all three:

Parallel compression — 10x faster backups than pg_dump on multi-core servers.
Incremental/differential — Only back up changed data.
Point-in-Time Recovery (PITR) — Restore to any second between backups.
Remote backup — Send backups to a dedicated backup server via SSH or S3.

Prerequisites

PostgreSQL 12+ running on Linux.
At least 2x the database size in free disk space for backups.
Root or sudo access.

Step 1: Install pgBackRest

# Ubuntu/Debian
sudo apt install pgbackrest -y

# RHEL/CentOS
sudo yum install pgbackrest -y

Step 2: Configure pgBackRest

`/etc/pgbackrest/pgbackrest.conf`

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
process-max=4
compress-type=zst
compress-level=3
log-level-console=info

[mydb]
pg1-path=/var/lib/postgresql/16/main

Key Settings

Setting	Purpose
`repo1-path`	Where backups are stored
`repo1-retention-full=2`	Keep 2 full backups
`repo1-retention-diff=7`	Keep 7 differential backups
`process-max=4`	Parallel backup/restore threads
`compress-type=zst`	Zstandard compression (fastest)

Step 3: Configure WAL Archiving

Edit /etc/postgresql/16/main/postgresql.conf:

archive_mode = on
archive_command = 'pgbackrest --stanza=mydb archive-push %p'
wal_level = replica
max_wal_senders = 3

Restart PostgreSQL:

sudo systemctl restart postgresql

Step 4: Create Stanza and First Backup

# Initialize the stanza (must run once)
sudo -u postgres pgbackrest --stanza=mydb stanza-create

# Take a full backup
sudo -u postgres pgbackrest --stanza=mydb --type=full backup

# Take a differential backup (daily)
sudo -u postgres pgbackrest --stanza=mydb --type=diff backup

# Check backup status
sudo -u postgres pgbackrest --stanza=mydb info

Step 5: Point-in-Time Recovery (PITR)

Restore to a specific timestamp:

# Stop PostgreSQL
sudo systemctl stop postgresql

# Restore to a specific point in time
sudo -u postgres pgbackrest --stanza=mydb \
  --type=time "--target=2026-02-27 14:30:00" \
  --target-action=promote restore

# Start PostgreSQL
sudo systemctl start postgresql

Automated Backup Schedule (Cron)

# /etc/cron.d/pgbackrest
# Full backup every Sunday at 2 AM
0 2 * * 0 postgres pgbackrest --stanza=mydb --type=full backup
# Differential backup every day at 2 AM (except Sunday)
0 2 * * 1-6 postgres pgbackrest --stanza=mydb --type=diff backup

Troubleshooting

Problem	Solution
”WAL segment not found” during restore	Verify `archive_mode=on` and `archive_command` in postgresql.conf; check archive directory
Backup takes too long	Increase `process-max` for parallelism; use `compress-type=zst` instead of `gz`
”stanza already exists” error	The stanza was already created; this is safe to ignore
Restore fails with permission error	Ensure the `postgres` user owns the data directory and backup repository
Repository disk full	Reduce `repo1-retention-full`; move repository to larger disk or S3

Summary

Use full + differential for a practical weekly/daily schedule.
Configure WAL archiving for PITR capability.
Use compress-type=zst and process-max=4+ for fast backups.
Test restores regularly — a backup you’ve never tested is not a backup.

Frequently Asked Questions

What is pgBackRest and why use it instead of pg_dump?

pgBackRest is a purpose-built PostgreSQL backup tool that supports full, differential, and incremental backups with parallel compression. Unlike pg_dump (logical backup), pgBackRest takes physical backups of the entire data directory, enabling Point-in-Time Recovery (PITR) to any second between backups. It also handles WAL archiving automatically.

What is the difference between full, differential, and incremental backups?

A full backup copies everything. A differential backup copies only data changed since the last full backup. An incremental backup copies only data changed since the last backup of any type. For production, a common strategy is: weekly full + daily differential, or weekly full + daily incremental.

How does Point-in-Time Recovery (PITR) work with pgBackRest?

PITR lets you restore your database to any specific second in time. pgBackRest achieves this by restoring the latest full backup, then replaying WAL (Write-Ahead Log) files up to the target timestamp. This requires continuous WAL archiving to be configured.

Why do I get 'WAL segment not found' during restore?

pgBackRest can't find a required WAL file. This means WAL archiving was interrupted or the archive-push command failed silently. Check that archive_mode=on and archive_command points to pgbackrest in postgresql.conf. Verify the archive directory has all expected WAL files.