Recently, we worked with a client who was manually backing up their 800GB PostgreSQL database using pg_dump, which was growing rapidly and had backups stored on the same server as the database itself. This setup had several critical issues:
- Single point of failure: If the server failed, both the database and its backups would be lost.
- No point-in-time recovery: Accidental data deletion couldn’t be undone.
- Performance bottlenecks: Backups consumed local storage, impacting database performance.
To address these risks, we replaced their setup with pgBackRest, shifting backups to a dedicated backup server with automated retention policies and support for point-in-time recovery (PITR).
This guide will walk you through installing, configuring, and testing pgBackRest in a real-world scenario where backups will be configured on a dedicated backup server, separate from the data node itself.
PgBackRest Overview
pgBackRest is a robust, open-source backup and restore solution for PostgreSQL, developed by Crunchy Data. It is designed to meet the demands of large-scale PostgreSQL environments, with a focus on reliability, efficiency, and automation.
Key features include:
- Support for full, differential, and incremental backups to optimize storage usage and reduce backup time.
- Point-in-time recovery (PITR) for precise restoration in case of data loss or corruption.
- Parallel processing for faster backup and restore operations, particularly in large databases.
- Integrated compression and encryption to ensure secure and space-efficient backup storage.
- Remote backup capabilities to protect against single points of failure by decoupling backups from the primary database server.
pgBackRest Backup Types
Full Backups
Full backup captures the entire PostgreSQL database cluster and includes all necessary files to restore a fully functional instance. A full backup must be taken before any differential or incremental backups can be performed.
Differential Backups
Differential backups include only the data files that have changed since the last full backup. These backups are cumulative, meaning each new differential stores all changes made since the most recent full backup.
Incremental Backups
Incremental backups are more granular and contain only the files that have changed since the previous backup, whether that backup was full, differential, or another incremental backup.
Backup Strategy Considerations
Retention Policy
A retention policy determines how long backups are stored before being automatically purged. pgBackRest provides flexible retention options:
- Count-based retention: Keep a fixed number of backups.
- Time-based retention: Remove backups older than a specified period
Compression
pgBackRest supports multiple compression algorithms, each offering different trade-offs between speed and compression ratio. Compression type and level can be configured. Lower levels provide faster compression but less size reduction, while higher levels offer better compression at the cost of longer backup times. You can choose the compression type (lz4, zst, or gz) and level based on the performance and storage requirements.
Parallel Backup and Restore
pgBackRest supports parallel processing using the –process-max option to speed up compression and data transfer. It’s best to limit backup processes to around 25% of available CPUs to avoid impacting database performance. However, restores can safely use all available CPUs since the PostgreSQL cluster is offline during the operation. If the host contains multiple clusters, then that should be considered when setting restore parallelism. Tuning process-max appropriately can significantly reduce backup and restore times for large databases.
Setup
For this guide, we are working with a setup that includes one data server and one backup server:
Primary Node (Database Server):
- OS: RHEL 9
- PostgreSQL Version: 15
- pgBackRest Version: 2.55.1
Backup Node (Backup Server):
- OS: RHEL 9
- pgBackRest Version: 2.55.1
In this configuration, the backup server is responsible for storing all backups remotely using pgBackRest, while the primary node runs the PostgreSQL database.
Install pgBackRest
On Backup Node
To set up pgBackRest, we first need to add the official PostgreSQL repository, install the necessary dependencies, and then install pgBackRest itself.
RHEL’s default repositories do not include the latest PostgreSQL tools, so we begin by adding the official PostgreSQL repository:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
libssh2 is required to enable support for secure, encrypted remote backups:
sudo dnf install libssh2
Now install pgBackRest. Replace the version number as needed if you want a different release:
sudo dnf install -y pgbackrest-2.55.1
On DB Node
Same steps as on the backup node:
sudo dnf install libssh2
sudo dnf install -y pgbackrest-2.55.1
Create Users and Set Up SSH Passwordless Access
To enable remote, secure, and automated backups using pgBackRest, we need passwordless SSH access between the backup and database servers.
On Backup Node
We need a dedicated backup user because running backups as root is risky and not recommended.
sudo adduser pgbackrest
Generate an SSH key for pgbackrest. This key pair will serve as the authentication mechanism between the servers:
sudo -u pgbackrest ssh-keygen -t rsa -b 4096 -N "" -f
/home/pgbackrest/.ssh/id_rsa
Set the correct ownership and permissions for SSH access
sudo chmod 700 /home/pgbackrest /home/pgbackrest/.ssh
sudo chmod 600 /home/pgbackrest/.ssh/id_rsa
sudo chmod 644 /home/pgbackrest/.ssh/id_rsa.pub
sudo chmod 600 /home/pgbackrest/.ssh/authorized_keys
sudo chown -R pgbackrest:pgbackrest /home/pgbackrest
If you’re using SELinux (common on RHEL), make sure it recognizes the new SSH setup/
sudo restorecon -Rv /home/pgbackrest/.ssh
On DB Node
On the database server, switch to the postgres user and set up SSH:
sudo su - postgres
mkdir -p ~/.ssh && chmod 700 ~/.ssh
ssh-keygen -t rsa -b 4096 -N "" -f ~/.ssh/id_rsa
Set the correct file permissions
chmod 600 ~/.ssh/id_rsa
chmod 644 /home/postgres/.ssh/id_rsa.pub
chmod 600 ~/.ssh/authorized_keys
chown -R postgres:postgres ~/.ssh
exit
If SELinux is enabled, apply the appropriate contexts here as well:
sudo restorecon -Rv /var/lib/pgsql
sudo restorecon -Rv /var/lib/pgsql/.ssh
Exchange SSH Keys and Test the Connection
Now, let’s make sure the two servers trust each other.
On the backup node:
- Copy the pgbackrest public key (id_rsa.pub) to the DB node’s ~postgres/.ssh/authorized_keys.
On the database node:
- Copy the postgres public key (id_rsa.pub) to the backup node’s ~pgbackrest/.ssh/authorized_keys.
Now test:
sudo -u pgbackrest ssh postgres@<DB_NODE_IP>
sudo -u postgres ssh pgbackrest@<BACKUP_NODE_IP>
If this works, high-five yourself. You’ve just enabled secure and passwordless access.
Configure pgbackrest.conf
On Backup Node
First, create a backup directory where you will store the backups. We’ll use /home/pgbackrest/backups for this purpose. The pgbackrest user must own the directory to avoid permission issues.
sudo mkdir -p /home/pgbackrest/backups
sudo chown -R pgbackrest:pgbackrest /home/pgbackrest/backups
Next, edit /etc/pgbackrest.conf. This file controls backup behavior, retention policies, and performance settings.
[global]
repo1-path=/home/pgbackrest/backups # Local backup path
repo1-retention-full=7 # Retain 7 full backups
repo1-retention-full-type=time # Use time-based expiration
repo1-host-user=pgbackrest # SSH user on this host
log-level-console=info
log-path=/home/pgbackrest/backups
log-level-file=debug
start-fast=y # Start faster backup
delta=y # Use delta logic for changed blocks
[db]
pg1-host=<DB_NODE_IP> # Remote DB node IP
pg1-host-user=postgres
pg1-port=5432
pg1-path=/var/lib/pgsql/15/data # PostgreSQL data directory
Here :
- repo1-path specifies the backup repository location where all backups are stored.
- retention-full=7 means that a rolling snapshot of the last 7 full backups will be retained. Older backups beyond this limit are automatically deleted. You can adjust this setting according to your backup retention policy.
- delta=y enables delta backups, which means only the changes since the last backup are copied, rather than duplicating the entire dataset.
- start-fast=y ensures the backup process starts immediately without unnecessary delays.
- pg1-host is the IP address or hostname of the PostgreSQL server.
- pg1-path refers to the actual PostgreSQL data directory on the database server.
- pg1-host-user is a user on the database server with SSH access, used to remotely run the backup.
Backups can consume a significant amount of space, so compression is essential. pgBackRest supports multiple compression types. I recommend using ZST with compression level 3 for a good balance between speed and compression ratio. To enable it, add the following settings under the [global] section in your pgbackrest.conf:
compress-type=zst
compress-level=3
pgBackRest will automatically decompress the data during restore.
On DB Node
On your PostgreSQL node, you need to tell pgBackRest how to connect to the backup server and where your local PostgreSQL data is stored. Open /etc/pgbackrest.conf and edit it like so:
[global]
repo1-host=<BACKUP_NODE_IP> # Remote repo host
repo1-host-user=pgbackrest
log-level-console=info
[db]
pg1-path=/var/lib/pgsql/15/data # Local PostgreSQL data path
This tells pgBackRest to reach out to the remote backup host whenever needed and defines where your local database files live.
Next, you’ll need to instruct PostgreSQL to start archiving its WAL files and define how it should restore them during recovery. Open postgresql.conf and set the following:
archive_mode = on
archive_command = 'pgbackrest --stanza=db archive-push %p'
restore_command = 'pgbackrest --stanza=db archive-get %f "%p"'
- archive_mode = on tells PostgreSQL to start archiving WALs.
- archive_command defines how WALs are pushed to the backup system.
- restore_command is used during recovery to fetch WALs from the archive.
Finally, apply the configuration changes with a restart:
sudo systemctl restart postgresql-15
Initialize pgBackRest
On Backup node
To avoid having to prefix every command with sudo -u pgbackrest, you can switch to the pgbackrest user once using:
sudo -i -u pgbackrest
However, for consistency and clarity, this guide uses sudo -u pgbackrest with every command.
Create the stanza
A stanza is a logical name used by pgBackRest to group configuration for a specific PostgreSQL instance. Creating a stanza is the first operational step in setting up your backup environment.
sudo -u pgbackrest pgbackrest --stanza=db --log-level-console=info
stanza-create
This verifies your configuration and initializes the stanza metadata in the repository. Before proceeding, run a quick check to validate the connection between your DB node and the backup system:
sudo -u pgbackrest pgbackrest --stanza=db check
If everything is wired up correctly, pgBackRest will confirm that the database and repository are accessible.
Take the first full backup:
Your initial backup must be a full backup, which becomes the base for all future incremental or differential backups.
sudo -u pgbackrest pgbackrest --stanza=db --type=full --log-level-console=info backup
This will copy all relevant data from your PostgreSQL data directory to the configured backup repository. Once the full backup is complete, you can take smaller, more efficient backups that only include changes.
Incremental backup includes changes since the last backup of any type:
sudo -u pgbackrest pgbackrest --stanza=db --type=incr --log-level-console=info backup
Differential backup includes changes since the last full backup:
sudo -u pgbackrest pgbackrest --stanza=db --type=diff --log-level-console=info backup
Automate Backups with Cron
To keep your backups running on schedule, configure a cron job for the pgbackrest user:
sudo crontab -e -u pgbackrest
Add the following entries to automate daily backups:
# Sunday at 2 AM — Full
0 2 * * 0 /usr/bin/pgbackrest --stanza=db --type=full backup
# Monday–Saturday at 2 AM — Incremental
0 2 * * 1-6 /usr/bin/pgbackrest --stanza=db --type=incr backup
View Backup Contents
On Backup Node
To see a summary of all backup sets, including details like size, timestamp, and archive info
sudo -u pgbackrest pgbackrest info
To explore a specific backup:
sudo -u pgbackrest ls /home/pgbackrest/backups/backup/db/<BACKUP_ID>/pg_data
PITR Restore
Point-in-Time Recovery (PITR) is a powerful PostgreSQL feature that allows the database to restore to a specific moment right before accidental data loss, corruption, or a bad deployment. With pgBackRest, PITR becomes a streamlined and reliable process.
Here’s a complete walkthrough of how to perform a full restore, as well as a point-in-time restore, using pgBackRest.
How pgBackRest handles restores:
When you initiate a restore, pgBackRest automatically assembles everything needed:
- The latest full backup
- Any relevant differential or incremental backups
- All required WAL (Write-Ahead Log) files for replay
This ensures your restored database is consistent and up to date, either to the latest point or a specific time you choose.
On DB Node
Stop PostgreSQL
sudo systemctl stop postgresql-15
If you’re restoring to the same data directory, it must be empty:
sudo rm -rf /var/lib/pgsql/15/data/*
To restore everything to the most recent consistent state:
sudo -u pgbackrest pgbackrest --stanza=db restore
To restore to a specific point in time, specify the target time and type:
sudo -u pgbackrest pgbackrest --stanza=db --type=time --target="2025-05-23 14:30:00" restore
This is useful if you want to recover right before an accidental DROP or incorrect update.
sudo systemctl start postgresql-15
PostgreSQL will now start using the restored data.
Testing a Backup Restore
You should regularly test your backups without touching production. To verify that your backups are restorable, you can perform a test restore on the same backup server. This process is useful for validation and does not affect the primary database.
Steps
- Choose a safe, empty directory where you want to restore the database.
- Run the following command to perform a full restore: Update –pg1-path with your desired target restore location.
sudo -u pgbackrest pgbackrest --stanza=db --pg1-path=/var/lib/postgresql/15/test restore
To restore to a specific point in time,
sudo -u pgbackrest pgbackrest --stanza=db
--pg1-path=/var/lib/postgresql/15/test --log-level-console=info --type=time
--target="2025-06-13 15:37:00" restore
After the restore completes, check the restored data directory for expected contents:
PG_VERSION
base/
global/
postgresql.conf
(Optional): Start PostgreSQL using the restored directory:
pg_ctl -D /var/lib/postgresql/15/test start
Connect using psql and validate the database structure and contents:
psql -U postgres -c "SELECT datname FROM pg_database;
Conclusion
Even if your backups are running smoothly, restore tests are essential to verify end-to-end recovery. We strongly recommend scheduling periodic test restores to:
- Ensure backups aren’t silently corrupted
- Confirm WAL archiving is functioning
- Validate your recovery process
In an actual emergency, you don’t want your first restore attempt to be your first practice.