Automate PostgreSQL Backups with pgBackRest: A DBA Guide

Automating PostgreSQL Backups with pgBackRest

As a Senior Database Administrator, I’ve seen the pain that manual backup processes can cause—forgotten schedules, inconsistent retention policies, and, worst of all, data loss during a crisis. PostgreSQL’s native tools (pg_dump, pg_basebackup) are powerful, but they lack the orchestration, verification, and reporting needed for production environments. That’s where pgBackRest shines. It is a reliable, open‑source backup and recovery framework that brings enterprise‑grade features to PostgreSQL without the complexity of commercial tools like Oracle RMAN or SQL Server’s RMAN‑like capabilities.

Why Automate PostgreSQL Backups?

Compliance – Regulatory frameworks (PCI‑DSS, HIPAA, GDPR) demand consistent, auditable backups.
Business Continuity – Rapid recovery reduces downtime and mitigates the financial impact of outages.
Operational Efficiency – Automating eliminates manual errors and frees DBAs to focus on performance tuning and architecture.
Resource Optimization – pgBackRest’s incremental backups and compression minimize storage consumption while ensuring quick restores.

Key Challenges with Manual Backup Processes

Inconsistent backup windows lead to data gaps.
Retention policies are hard to enforce across environments.
Verification and integrity checks are often skipped.
Reporting is ad‑hoc, making audits difficult.

What Is pgBackRest?

pgBackRest is a modern backup solution built specifically for PostgreSQL. Its core strengths include:

Full, Differential, and Incremental Backups – Only changed data is written after the initial full backup.
Compression & Encryption – AES‑256 encryption and LZ4 or GZIP compression keep data secure and space‑efficient.
Parallelism – Uses all available CPU cores for faster backups.
Retention Policies – Fine‑grained control over daily, weekly, monthly, and yearly retention.
Verification & Checksums – Integrity checks ensure the backup is usable.
Reporting & Metrics – RESTful API and log files provide status and performance metrics.
Cross‑Platform – Runs on Linux, macOS, and Windows (via WSL).

Getting Started: Installation and Configuration

pgBackRest is available as a package on major distributions, or you can compile from source. The following example covers installation on an Ubuntu server:



sudo apt update && sudo apt install -y postgresql-contrib pgbackrest

Once installed, configure the /etc/pgbackrest/pgbackrest.conf file to define a repository, retention policies, and PostgreSQL connection details.

Sample Configuration File



[global]

repo1-path=/var/lib/pgbackrest

repo1-retention-full=2

repo1-retention-diff=5

repo1-retention-arch=2

repo1-retention-archive=2

repo1-retention-archive=1

repo1-retention-archive=3

[database]
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432

Explanation of the key sections:

repo1-path – Directory where backups are stored.
repo1-retention-full – Keep only the two most recent full backups.
repo1-retention-diff – Keep the five most recent differential backups.
pg1-path and pg1-port – Point to the PostgreSQL data directory and port.

Setting Up the Repository

Create the backup directory and set correct permissions:



sudo mkdir -p /var/lib/pgbackrest

sudo chown -R postgres:postgres /var/lib/pgbackrest

Creating a Backup Schedule

Automating backups involves creating cron jobs that invoke pgbackrest with the desired backup type. Below is a typical cron setup for a production cluster:

Example Cron Jobs

Full backup every Sunday at 01:00
Incremental backup every day at 02:00
Archive backup (WAL segment archiving) every hour



# Full backup on Sundays

0 1 * * 0 /usr/bin/pgbackrest --stanza=db --type=full backup &>/var/log/pgbackrest/full.log

# Incremental backup daily
0 2 * * * /usr/bin/pgbackrest –stanza=db –type=incr backup &>/var/log/pgbackrest/incr.log# Hourly WAL archiving
0 * * * * /usr/bin/pgbackrest –stanza=db archive-push $(pg_waldump –list /var/lib/postgresql/15/main/pg_wal | tail -n1 | awk ‘{print $1}’) &>/var/log/pgbackrest/arch.log

Replace db with your stanza name, typically matching your database cluster.

Verification and Integrity Checks

pgBackRest automatically verifies the consistency of each backup. However, you can perform a manual integrity check to ensure the repository is healthy:



pgbackrest check --stanza=db

This command scans all backup data, verifies checksums, and reports any discrepancies. It’s a good idea to run this as part of a nightly maintenance job and alert on failures.

Using Checksums for Data Protection

To enable per-file checksums, add the following line to pgbackrest.conf:



repo1-checksum-type=md5

pgBackRest will then compute MD5 checksums during backup and verify them during restore, providing an extra layer of data integrity.

Restoring Data Efficiently

Restoring a PostgreSQL cluster from pgBackRest is straightforward. There are two primary scenarios: full recovery and point‑in‑time recovery (PITR). Both are supported out of the box.

Full Recovery

Stop PostgreSQL, remove the existing data directory, and restore from the latest backup:



sudo systemctl stop postgresql

sudo rm -rf /var/lib/postgresql/15/main

sudo -u postgres pgbackrest --stanza=db restore

sudo systemctl start postgresql

Point‑in‑Time Recovery

Specify the exact time you want to recover to. For example, to recover to 2025‑08‑01 14:30:00:



sudo systemctl stop postgresql

sudo rm -rf /var/lib/postgresql/15/main

sudo -u postgres pgbackrest --stanza=db --target-action=promote --target-time="2025-08-01 14:30:00" restore

sudo systemctl start postgresql

The --target-action=promote option ensures the server is ready for read/write after recovery.

Advanced Features for Enterprise Deployments

Multi‑Repository Replication

pgBackRest supports multiple backup repositories for high availability. Define a second repository in pgbackrest.conf:



repo2-path=/mnt/remote-backup

repo2-retention-full=2

Then, when running a backup, specify both repositories:



pgbackrest --repo=repo1 --repo=repo2 --stanza=db backup

This creates synchronized backups across two locations, mitigating the risk of single‑point failures.

Encryption for Sensitive Data

pgBackRest supports AES‑256 encryption. Enable it by adding:



repo1-encrypt=true

repo1-encrypt-key="mysecretkey1234567890abcdef"

Keep the encryption key secure (e.g., in a vault or pgpass) and never commit it to version control.

Automated Cleanup and Retention

Retention policies automatically remove old backups. To force immediate cleanup (e.g., after a major migration), run:



pgbackrest --stanza=db cleanup

This ensures the repository stays within the defined storage limits.

Monitoring and Reporting

pgBackRest generates detailed logs that can be parsed by monitoring tools. For example, integrate with Prometheus via the built‑in REST API:



pgbackrest --stanza=db --type=info --output=json

Parse the JSON to extract metrics such as backup duration, size, and status. Combine this data with alerts (e.g., via Grafana or Opsgenie) to stay ahead of backup failures.

Sample Log Parsing Script (Python)



import json, requests

response = requests.get(‘http://localhost:5432/pgbackrest/api/v1/info’)
data = response.json()
print(f”Backup size: {data[‘backup’][‘size’]} bytes”)

Custom dashboards provide instant visibility into backup health, enabling rapid response to anomalies.

Best Practices Checklist

Use a dedicated backup user with minimal permissions.
Encrypt both backup data and WAL segments.
Validate backups nightly with pgbackrest check.
Store backups in at least two geographically separate repositories.
Automate restores in a test environment monthly.
Integrate backup status into your monitoring stack.
Document the backup strategy and retain it in version control.

Conclusion

pgBackRest delivers the robustness, flexibility, and automation that modern PostgreSQL deployments demand. By combining incremental backups, encryption, retention policies, and automated verification, DBAs can achieve enterprise‑grade data protection without the overhead of proprietary tools like Oracle’s RMAN or Microsoft’s SQL Server backup utilities. The result is a streamlined, auditable backup process that keeps your data safe and your operations running smoothly.

Ready to take your PostgreSQL backup strategy to the next level? Subscribe to our newsletter for more DBA insights, or follow us on LinkedIn to stay connected with the latest trends in database administration.

Person-IT