pgBackRest

Physical backup and PITR automation for self-hosted PostgreSQL 18.4 clusters - stanza config, WAL archiving, restore drills, and Patroni integration.

Recipe

Quick-reference recipe card - copy-paste ready.

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=pg-backups-prod
repo1-s3-region=us-east-1
repo1-retention-full=4
repo1-cipher-type=aes-256-cbc
 
[main]
pg1-path=/var/lib/postgresql/18/main
pg1-port=5432

pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main backup --type=full
pgbackrest --stanza=main check

When to reach for this:

• Self-hosted Postgres on VMs or bare metal needing encrypted S3 backups
• PITR requirements with tested restore runbooks
• Patroni clusters where managed snapshot APIs are insufficient

Working Example

Step 1 - Enable archiving in PostgreSQL

ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=main archive-push %p';
SELECT pg_reload_conf();

# Verify WAL archive flow
pgbackrest --stanza=main check

Step 2 - Scheduled backup cron

# /etc/cron.d/pgbackrest
0 2 * * 0 postgres pgbackrest --stanza=main backup --type=full
0 2 * * 1-6 postgres pgbackrest --stanza=main backup --type=diff

Step 3 - Restore drill to isolated host

# On restore sandbox (empty data directory)
pgbackrest --stanza=main --delta restore
pg_ctl -D /var/lib/postgresql/18/main start
psql -c "SELECT pg_is_in_recovery(), now();"

What this demonstrates:

• archive-push continuous WAL shipping for PITR
• check validates backup set and archive continuity before you need them
• --delta restore avoids full re-download when rehearsing monthly

Deep Dive

Backup Types

Patroni Notes

[main]
backup-standby=y
pg2-host=replica.internal
pg2-path=/var/lib/postgresql/18/main

• Run backups from a replica to reduce primary IO during business hours
• Ensure replica has hot_standby = on and is not lagging during backup window

Verify Backup Integrity

pgbackrest --stanza=main info
pgbackrest --stanza=main verify

-- After restore drill
SELECT count(*) FROM pg_database;
SELECT max(created_at) FROM app.orders;  -- app-specific sanity row

Gotchas

• Two backup tools - pg_basebackup cron plus pgBackRest on same cluster causes corruption risk and duplicate S3 cost. Fix: one agent only.
• archive_command silent failure - WAL piles on primary disk if push fails. Fix: monitor pg_stat_archiver, alert on failed_count.
• Restoring over running data directory - destroys production. Fix: restore drills use dedicated sandbox host or stopped instance only.
• Wrong pg1-path - stanza points at Debian path but RHEL uses different layout. Fix: match SHOW data_directory; exactly.
• S3 credentials on database host - broad IAM role on primary. Fix: dedicated backup IAM user; least privilege repo1-s3-* keys.

Alternatives

FAQs

Related

• Tools Basics - toolkit manifest
• PITR Basics - recovery targets
• Archive Mode and archive_command - WAL archiving
• Patroni and etcd/Consul - HA topology
• Essential Tools Best Practices - automate check

Stack versions: This page was written for PostgreSQL 18.4, pgBackRest 2.55+, and Patroni 4.x on self-hosted clusters.