Patroni + pgBackRest: Centralized Pull Backup for High Availability PostgreSQL Clusters

Automated, centralized, and reliable backups for PostgreSQL clusters managed by Patroni.
This project showcases a production-grade architecture where a dedicated pgBackRest server performs remote pull backups — both full and incremental — from a high-availability PostgreSQL cluster managed by Patroni.

---

🧠 Project Goals

• ✅ Set up a highly available PostgreSQL cluster using Patroni.
• ✅ Use a centralized pgBackRest repository host for backups.
• ✅ Support remote pull backups (not push), including full and incremental.
• ✅ Store all WALs and backup sets in one place for easy disaster recovery.
• ✅ Enable replica rebuilds from pgBackRest backups instead of pg_basebackup.
• ✅ Automate backup scheduling and retention.

---

🔧 Tech Stack

• PostgreSQL 14+
• Patroni for high availability
• pgBackRest for backup & restore
• etcd (as Patroni DCS)
• Systemd/Cron for backup scheduling
• (Optional) HAProxy / PgBouncer / Grafana

---

📁 Project Structure

patroni-pgbackrest-ha-backup/
├── docker-compose.yml              # For local cluster setup (optional)
├── patroni/                        # Patroni config files
│   ├── install_pgbackrest.sh        
│   ├── patroni.yml
│   ├── pgbackrest.conf
│   └── postgresql.conf
├── pgbackrest/
│   ├── pgbackrest.conf             # Centralized backup config
│   └── install.sh                # setup script
├── scripts/
│   ├── backup-full.sh
│   ├── backup-incr.sh
│   └── restore.sh
└── README.md

---

⚙️ How It Works

🟢 Backup Workflow

1. The pgBackRest server connects to Patroni nodes via SSH.

2. It pulls backups from the current leader (or a replica).

3. WALs are archived continuously using archive_command.

4. Backups are automated using cron/systemd timers:

   • 02:00 AM: full backup
   • Every hour: incremental backups

🔁 Restore / Replica Bootstrap

• Replicas can be rebuilt using pgbackrest restore during Patroni bootstrap.
• Supports Point-In-Time Recovery (PITR) using WALs.
• Easy failover and rejoin after node failure.

---

📦 Sample pgBackRest Configuration

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
compress-type=zst
start-fast=y
process-max=2
log-level-console=info

[patronicluster]
pg1-host=patroni-node1
pg1-path=/var/lib/postgresql/data
pg2-host=patroni-node2
pg2-path=/var/lib/postgresql/data
pg3-host=patroni-node3
pg3-path=/var/lib/postgresql/data

---

🚀 Getting Started

1. Clone the repository

   git clone https://github.com/your-username/patroni-pgbackrest-ha-backup.git
   cd patroni-pgbackrest-ha-backup

2. Set up SSH access

   • Copy SSH key from backup server to all Patroni nodes:

     ssh-copy-id postgres@patroni-node1

3. Install dependencies

   • Install pgBackRest (same version) on all nodes and backup server.
   • Configure Patroni nodes to archive WALs using archive_command.

4. Create stanza

   pgbackrest --stanza=patronicluster stanza-create

5. Run backups

   pgbackrest --stanza=patronicluster --type=full backup
   pgbackrest --stanza=patronicluster --type=incr backup

---

🧪 Disaster Recovery Scenarios

• ✔️ Restore to any point in time using WALs
• ✔️ Rebuild a failed replica using pgbackrest restore
• ✔️ Validate backup integrity with check command
• ✔️ Automated failover with Patroni

---

🧠 Lessons Learned

• Using a pull backup model centralizes control and scales better.
• Properly synchronizing versions of pgBackRest is crucial.
• Automating replica rebuilds helps reduce downtime and human error.
• Separation of storage (WALs, backups) from database servers simplifies DR.

---

📈 Possible Improvements

• Integrate with Prometheus + Grafana to monitor backup success/failure.
• Store backups in object storage (e.g. S3, MinIO).
• Use Ansible to automate deployment.
• Enable TLS encryption for secure SSH/WAL transmission.

---

📜 License

This project is licensed under the MIT License.

---

🤝 Contributing

PRs and ideas welcome! Please open issues for enhancements, bugs, or improvements.

---

🙌 Acknowledgments

• pgBackRest Documentation
• Patroni Documentation
• Cybertec PostgreSQL Blog
• Percona Tutorials