2025-08-14

Configuring pgBackRest in an HA environment

This document outlines the pgBackRest configuration used for managing PostgreSQL backups in a high-availability environment with both a primary (read/write) and standby (read-only) node.

Overview

pgBackRest is used to handle backup, restore, and WAL archiving for a PostgreSQL cluster. In this setup:

• Backups are stored in Azure Blob Storage
• Supports standby-based backups
• WAL archiving is asynchronous
• Retention policies automatically prune old backups

Backups are configured to only be taken from the standby node.

Configuration File

Path: /etc/pgbackrest.conf

[global]
repo1-type=azure
repo1-path=/
repo1-azure-container=pgbackps
repo1-azure-account=pgbackups
repo1-azure-key=<sas token>
repo1-azure-key-type=sas
repo1-retention-full=4
repo1-retention-diff=7
start-fast=y
log-level-file=debug
log-level-console=info
log-path=/pgdata/log
process-max=4
compress-type=lz4
archive-async=y

[pgbackup]
pg1-path=/pgdata
pg2-path=/pgdata
pg2-host=<IP_of_secondary_host>
backup-standby=y

• Assumes that /pgdata is the main data directory for Postgres
• <sas token> should be replaced by the generated SAS token for the pgbackups storage account
• backup-standby=y allows backups to run from a standby node, reducing load on the primary.
• Both nodes are configured with the same data path; standby is addressed via IP.
• Note that the above config file is taken from node 1. For node 2, the config file should be identical except for the IP address for pg2-host (should be the address for node 1).

WAL Archiving

WAL files are archived using Postgres’ archive command in the postgresql.conf. This should be set to the following:

pgbackrest --stanza=pgbackup archive-push %p

The archive-async=y setting enables asynchronous WAL archiving, minimizing write latency during high-load periods. WAL files must be archived from the primary node.

Backup Scheduling

Backups are scheduled using cron jobs configured directly on the standby node. The schedule ensures a mix of full, differential, and incremental backups:

Crontab entries:

#full postgres backup on Sunday evening
0 20 * * 0 pgbackrest --stanza=pgbackup --type=full backup

#differential backups mon-sat evenings
0 20 * * 1-6 pgbackrest --stanza=pgbackup --type=diff backup

#incremental backups every 6 hours daily (except 8pm slot)
0 2,8,14 * * * pgbackrest --stanza=pgbackup --type=incr backup

Note: All backups are initiated from the standby node only. The backup-standby=y setting ensures backups can safely run on the standby while the primary continues handling traffic.

Retention Policy

pgBackRest enforces automatic backup pruning using the following rules:

• 4 full backups retained
• 7 differential backups retained
• Incrementals are retained as needed to support the full/diff chain

Retention logic is automatically applied during backup or by invoking:

pgbackrest expire --stanza=pgbackup

Security Notes

• The Azure container is accessed using a SAS token, specified in the config file.
• This file must be protected with restricted permissions (chmod 600) to prevent credential exposure.

Operational Notes

• Logs are stored in: /pgdata/log/
• To check backup status:
  pgbackrest info --stanza=pgbackup
• All WAL archives and backups are stored in the Azure Blob container pgbackups.
• To verify that pgbackrest configuration is correct:
  pgbackrest check --stanza=pgbackup

Troubleshooting Tips

• Ensure pgbackrest binary is in root’s $PATH if used in cron.
• The standby must have network access to Azure Blob Storage.
• Clock drift across nodes can cause archive/backup errors — verify NTP sync.
• Use --log-level-console=debug temporarily to troubleshoot issues interactively.