Step-by-Step Guide to setup pgBackRest

In today’s data-driven world, ensuring your PostgreSQL production database is backed up reliably is crucial. pgBackRest is a powerful backup and restore solution that provides robust backup modes—including full and incremental backups—optimized performance, and seamless integration with remote storage such as Amazon S3. In this guide, we’ll walk you through every step from the initial setup to advanced configurations.

Why Use pgBackRest?

pgBackRest is purpose-built for PostgreSQL backup and restore operations. Here are some of the benefits that make it a preferred tool over traditional backup utilities:

• Efficiency: Offers incremental backups that copy only changed data, reducing storage needs and speeding up backup times.
• Reliability: Provides a consistent and reliable way to backup and restore entire clusters.
• Performance: Utilizes parallel processing and optimized compression.
• Remote Storage Support: Easily integrates with cloud storage solutions like S3 for offsite backups.
• Simplicity: Uses an INI-like configuration file that is straightforward to understand and modify

Initial Setup and Installation

Prerequisites

Before installing pgBackRest, ensure you have the following:

• A running PostgreSQL instance in production.
• Sufficient disk space for both local and remote backup repositories.
• Access to a remote S3 bucket (with necessary credentials).

Installation Steps

Build the Binary

Download the release and compile it on a machine matching your target architecture. The build process is a one-time task—once the binary is generated, it can be reused across multiple systems with the same architecture without rebuilding. For convenience, use the script below to automate the compilation process:

# Download release from github
mkdir -p /build
wget -q -O - \
https://github.com/pgbackrest/pgbackrest/archive/release/2.54.2.tar.gz | \
tar zx -C /build

# Install Dependencies
sudo apt-get install python3-distutils meson gcc libpq-dev libssl-dev libxml2-dev \
pkg-config liblz4-dev libzstd-dev libbz2-dev libz-dev libyaml-dev libssh2-1-dev

# Build the binary
meson setup /build/pgbackrest /build/pgbackrest-release-2.54.2
ninja -C /build/pgbackrest

Install Dependencies on Database Machine

Use your operating system’s package manager to install PostgreSQL client libraries and other dependencies. For Debian/Ubuntu, you need to run:

sudo apt-get install postgresql-client libxml2 libssh2-1

Install binary on Database Machine

Copy Generated binary /build/pgbackrest/src/pgbackrest file from build host to postgres host.

sudo scp build_host:/build/pgbackrest/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest

Configure pgBackRest

Set up configuration and log directories with proper permissions, you can use below script for creating default directories. Feel free to modify according to your usage.

# 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 /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

# Bakup Repository
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest

• /var/lib/pgbackrest directory is used to store database backups make sure this is big enough.

Configuring PostgreSQL and pgBackRest

Setting Up a Stanza

A stanza in pgBackRest is a configuration block that describes the database cluster’s settings. Open your configuration file /etc/pgbackrest/pgbackrest.conf and add a stanza section. For example, if your cluster is named production, you might include:

[production]
pg1-path=/var/lib/postgresql/15/production

[global]
repo1-path=/var/lib/pgbackrest

This configuration tells pgBackRest where the PostgreSQL data is located and where to store backups locally.

• pg1-path is data directory of postgres database.
• repo1-path is repository path where backup will be stored.

Configuring PostgreSQL for WAL Archiving

To enable backup of running database and point-in-time recovery, you need to configure PostgreSQL’s WAL archiving:

1. Enable Archive Mode

In PostgreSQL, the %p placeholder specifies the path for WAL (Write-Ahead Logging) segments during archiving. Setting wal_level to replica and increasing max_wal_senders is recommended, as it enables the addition of replicas without requiring a restart of the primary database cluster.

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

2. Restart PostgreSQL

Apply the changes by restarting your PostgreSQL service:

sudo systemctl restart postgresql

Setting Up Full and Incremental Backups

Full Backups

The very first backup you perform with pgBackRest is always a full backup. This backup captures the entire state of your PostgreSQL database cluster. To run a full backup, execute:

sudo -u postgres pgbackrest --stanza=production backup

A full backup is self-contained and does not rely on any previous backups, making it ideal for disaster recovery scenarios.

Incremental Backups

After the initial full backup, you can save time and space by running incremental backups. An incremental backup captures only the changes since the last backup. To perform an incremental backup, simply schedule subsequent backups; pgBackRest automatically determines the differences:

sudo -u postgres pgbackrest --stanza=production backup --type=incr

This command tells pgBackRest to back up only the changed files since the last backup. Regular incremental backups help minimize downtime and resource usage while maintaining backup integrity.

Configuring Remote S3 Storage

Storing backups offsite is an important part of a robust disaster recovery plan. pgBackRest supports S3-compatible object stores. To configure remote storage on S3, update your configuration file with the necessary S3 parameters:

[global]
repo1-type=s3
repo1-path=/var/lib/pgbackrest
repo1-s3-bucket=your-s3-bucket-name
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=us-east-1
repo1-s3-key=YOUR_AWS_ACCESS_KEY
repo1-s3-key-secret=YOUR_AWS_SECRET_KEY

This configuration tells pgBackRest to use S3 as the repository type and provides the credentials and bucket details. Ensure that your S3 bucket has the correct policies and that your credentials are securely stored. With this setup, your backups will be stored remotely, adding an extra layer of protection against local failures.

Scheduling Backups with Cron Jobs

Automating backups using cron jobs ensures that your production database is consistently protected without manual intervention. You can schedule different types of backups (full or incremental) on specific days based on your recovery needs.

Creating Cron Jobs

Open the Crontab Editor: Switch to the postgres user and open the crontab editor:

sudo -u postgres crontab -e

Add Cron Job Entries:
For example, if you want to run a full backup every Sunday at 3 AM and incremental backups on Monday, Wednesday, and Friday at 2 AM, add the following lines:

# Full backup every Sunday at 3 AM
0 3 * * 0 pgbackrest --stanza=production backup --type=full >> /var/log/pgbackrest/backup.log 2>&1

# Incremental backups on Monday, Wednesday, and Friday at 2 AM
0 2 * * 1,3,5 pgbackrest --stanza=production backup --type=incr >> /var/log/pgbackrest/backup.log 2>&1

These cron entries ensure that full backups are taken weekly while incremental backups capture only the changes on other specified days.

Verification and Best Practices

Verify the Configuration

After configuring everything, it’s important to verify that pgBackRest is correctly set up:

sudo -u postgres pgbackrest --stanza=production --log-level-console=info check

This command checks both the repository configuration and the archive setup, ensuring that your WAL segments are being archived as expected.

Best Practices for Production Environments

• Regular Testing: Periodically perform test restores to verify backup integrity.
• Monitor Disk Usage: Monitor both the local repository and the S3 bucket to ensure you have sufficient space.
• Automate Scheduling: Use cron jobs or other scheduling tools to automate full and incremental backups.
• Enhance Security: Encrypt your backup repository and secure your S3 credentials.
• Maintain Documentation: Keep updated documentation for your backup and restore procedures.

Conclusion

By following this step-by-step guide, you now have a reliable, production-ready pgBackRest setup for PostgreSQL. This configuration leverages full and incremental backups, secure offsite storage on S3, and automated cron job scheduling—ensuring your data remains safe and recoverable.

References: