Backing Up PostgreSQL with pgBackRest

pgBackRest is a reliable tool for backing up and restoring PostgreSQL. It supports incremental and differential backups, encryption, repositories on local disk and in the cloud (S3), and automatic WAL archive management.

Installing pgBackRest (Ubuntu/Debian)

sudo apt update
sudo apt install pgbackrest -y

Directory Structure

Let’s create directories for backups and configuration:

# Local storage
sudo mkdir -p /mnt/HC_Volume_102602874/bi_backup   # specify the real path
sudo chown postgres:postgres /mnt/HC_Volume_102602874/bi_backup
sudo chmod 750 /mnt/HC_Volume_102602874/bi_backup

# Configuration directories
sudo mkdir -p /etc/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest

Configuration /etc/pgbackrest/pgbackrest.conf

Local Repository

[global]
log-level-console=info
log-level-file=debug
start-fast=y
retention-archive-type=incr
retention-archive=7

repo1-path=/mnt/HC_Volume_102602874/bi_backup   # specify the real path
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=pKSm1J+0Px3mXYDceWE6yC2y3lv3xjPZUS6ztXijEuk=

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

S3 Repository (Optional)

You can configure a second repository to offload to S3. In practice, however, rclone is often used to copy backups to the cloud.

repo2-type=s3
repo2-path=/pgbackrest
repo2-s3-bucket=my-postgres-backups
repo2-s3-endpoint=s3.eu-central-1.amazonaws.com
repo2-s3-region=eu-central-1
repo2-s3-key=AWS_ACCESS_KEY_ID
repo2-s3-key-secret=AWS_SECRET_ACCESS_KEY
repo2-retention-full=4
repo2-cipher-type=aes-256-cbc
repo2-cipher-pass=... # the same key or a different one

You can use only repo1, or both at the same time.

Explanation of Key Parameters

• retention-archive — how many days to keep WAL files for the entire cluster.
• retention-archive-type — which WAL files may be deleted (full, diff, incr). Typically: incr.
• start-fast=y — start the backup without waiting for a checkpoint.
• log-level-console / log-level-file — logging levels.
• repoX-path — path where backups are stored (locally or on S3).
• repoX-retention-full — number of full backups to keep.
• repoX-cipher-type — encryption (AES-256 recommended).
• repoX-cipher-pass — encryption key.
• pg1-path — PostgreSQL data_directory.

Encryption Keys

To generate an encryption key:

openssl rand -base64 32

Or save it to a file:

sudo -u postgres mkdir -p /etc/pgbackrest/keys
openssl rand -base64 32 | sudo tee /etc/pgbackrest/keys/repo1.key
sudo chmod 600 /etc/pgbackrest/keys/repo1.key

Configuring PostgreSQL for WAL Archiving

In postgresql.conf:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Restart:

sudo systemctl restart postgresql

Initializing the stanza

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

Make sure .pgpass contains the login/password for postgres.

Backup Policies (cron)

# Full backup every Sunday
0 2 * * 0 pgbackrest --stanza=main --type=full backup

# Differential every day (except Sun)
0 2 * * 1-6 pgbackrest --stanza=main --type=diff backup

# Incremental every 15 minutes
*/15 * * * * pgbackrest --stanza=main --type=incr backup

You should also specify in the config:

retention-archive=7
retention-archive-type=incr

Verifying Backups

pgbackrest --stanza=main info

We expect to see:

cipher: aes-256-cbc
status: ok
last backup: full/diff/incr...

Restoring

Full Restore

sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main restore
sudo systemctl start postgresql

Point-in-Time Recovery (PITR)

sudo -u postgres pgbackrest --stanza=main restore   --type=time --target="2025-05-21 12:45:00"

Monitoring with Prometheus

We use pgbackrest_exporter.

Prometheus job:

- job_name: 'pgbackrest'
  static_configs:
    - targets: ['<host-ip>:9898']

What’s Important to Remember

• Always store encryption keys separately.
• Regularly test restores.
• Keep an eye on free space in repo1-path and on S3.
• Set up notifications via Alertmanager.

Conclusion

pgBackRest is a powerful tool that lets you build a reliable PostgreSQL backup system. With the right configuration, you can minimize the risk of data loss and ensure fast recovery both locally and in the cloud.