pgBackRest Setup and PostgreSQL 15 Backup Guide

This guide walks you through installing, configuring, backing up, restoring, and automating PostgreSQL 15 backups using pgBackRest.

Installation

# Add PostgreSQL Yum repo

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module

sudo dnf -qy module disable postgresql

# Install pgBackRest

sudo dnf install -y pgbackrest --nobest


# Verify installation

which pgbackrest

# Output: /usr/bin/pgbackrest

1. Create Backup Directory

mkdir -p /u01/pgbackrest
chown postgres:postgres /u01/pgbackrest
chmod 750 /u01/pgbackrest

2. Configure pgBackRest

Backup and edit the config file:

cp /etc/pgbackrest.conf /etc/pgbackrest.conf_bkp

vi /etc/pgbackrest.conf

[global]
repo1-path=/u01/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
log-level-console=info
log-level-file=debug
log-path=/var/log/pgbackrest

[pg15]
pg1-path=/u01/postgres/pgdata_15/data
pg1-port=5432
pg1-user=postgres

3. Set Permissions

chmod 640 /etc/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest.conf

4. Confirm PostgreSQL Is Running

ps aux | grep postgres

Look for:

postgres ... -D /u01/postgres/pgdata_15/data

5. Test PostgreSQL Connection

sudo -u postgres psql -h /tmp -p 5432 -d postgres

6. Create Stanza

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --log-level-console=info stanza-create

7. Configure WAL Archiving

vi /u01/postgres/pgdata_15/data/postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=pg15 archive-push %p'

Reload PostgreSQL:

sudo -u postgres pg_ctl reload -D /u01/postgres/pgdata_15/data

8. Verify Stanza and Archiving

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --log-level-console=info check

9. Run Full Backup

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=full backup

10. View Backup Info

sudo -u postgres pgbackrest --stanza=pg15 info

PostgreSQL Test Database Setup

# Log in

sudo -u postgres psql

# Create Database

CREATE DATABASE test_db;

\c test_db

# Create Table

CREATE TABLE employees (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, position VARCHAR(50),salary NUMERIC(10, 2));

# Insert Sample Data

INSERT INTO employees (name, position, salary) VALUES
('Ayaan Siddiqui', 'Manager', 75000.00),
('Zara Qureshi', 'Sr. Developer', 60000.00),
('Imran Sheikh', 'Jr. Developer', 50000.00);

# Query Table

SELECT * FROM employees;

# Exit

\q

Backup Types

Full Backup:

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=full backup

Differential Backup:

\c test_db

INSERT INTO employees (name, position, salary) VALUES
('Fatima Ansari', 'DBA', 70000.00),
('Yusuf Khan', 'Sr. Developer', 50000.00),
('Nadia Rahman', 'Jr. Developer', 40000.00);

SELECT * FROM employees;

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=diff backup

Incremental Backup:

\c test_db

INSERT INTO employees (name, position, salary) VALUES
('Bilal Ahmed', 'DEO', 34000.00),
('Sana Mirza', 'System Admin', 45000.00),
('Tariq Hussain', 'Accountant', 50000.00);

SELECT * FROM employees;

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --type=incr backup

Verify Backups:

sudo -u postgres pgbackrest --stanza=pg15 info

Restore Options:

Option 1: Partial Restore

systemctl stop postgresql
sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 --delta restore
systemctl start postgresql
systemctl status postgresql

Option 2: Full Clean Restore

systemctl stop postgresql
mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main_backup_$(date +%F)
mkdir /var/lib/postgresql/15/main
chown -R postgres:postgres /var/lib/postgresql/15/main
chmod 700 /var/lib/postgresql/15/main
sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 restore
systemctl start postgresql
systemctl status postgresql

Verify Restore:

sudo -u postgres psql -c "SELECT datname FROM pg_database;"

tail -f /var/log/pgbackrest/pgbackrest.log

Automate Backups with Cron:

crontab -u postgres -e

Add:

# Full Backup - Sunday 2 AM
0 2 * * 0 pgbackrest --stanza=pg15 --type=full backup

# Incremental Backup - Mon-Sat 2 AM
0 2 * * 1-6 pgbackrest --stanza=pg15 --type=incr backup

Monitor and Test:

Monitor Logs

tail -f /var/log/pgbackrest/pgbackrest.log

journalctl -u postgresql

Test Recovery Plan:

Regularly test backup restoration
Document recovery steps

Troubleshooting:

sudo -u postgres env PGHOST=/tmp pgbackrest --stanza=pg15 check