DEV Community

Cover image for Install and Configure pgBackRest for PostgreSQL 17 on Ubuntu 24.04 LTS
Chitt Ranjan Mahto (Chirag)
Chitt Ranjan Mahto (Chirag)

Posted on

Install and Configure pgBackRest for PostgreSQL 17 on Ubuntu 24.04 LTS

inchirags@gmail.com PostgreSQL DBA Tutorial https://www.chirags.in

  • Install and Configure pgBackRest for PostgreSQL 17 on Ubuntu 24.04 LTS *

YouTube Video:

https://youtu.be/S5NPR0H_kv4

pgBackRest is a reliable and easy-to-use backup and restore tool for PostgreSQL databases. It supports full, differential, and incremental backups, encryption, and parallel processing.

  1. Prerequisites

PostgreSQL 17 installed and running
A non-root user with sudo privileges
A PostgreSQL superuser (e.g., postgres)
Network access between the backup server and database server (if remote backups are planned)

If PostgreSQL 17 is not installed then install with below command.

Installation, and Configuration PostgreSQL 17 on Ubuntu 24.04 LTS

  1. Update and Upgrade the System and Install PostgreSQL 17 First, update your system packages to ensure everything is up-to-date. 
sudo apt update
sudo apt upgrade -y
Enter fullscreen mode Exit fullscreen mode

i. Add the PostgreSQL APT Repository

To get PostgreSQL 17, we need to add the official PostgreSQL APT repository.

sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
Enter fullscreen mode Exit fullscreen mode

Next, add the PostgreSQL repository:

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Enter fullscreen mode Exit fullscreen mode

ii. Install PostgreSQL 17

After adding the repository, update the package list and install PostgreSQL 17.

sudo apt update
sudo apt -y install postgresql postgresql-client
Enter fullscreen mode Exit fullscreen mode

iii. Start and Enable PostgreSQL Service

Ensure the PostgreSQL service starts automatically.

sudo systemctl start postgresql
sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode
  1. Install pgBackRest

2.1 Update the System

sudo apt update && sudo apt upgrade -y
Enter fullscreen mode Exit fullscreen mode

2.2 Install Required Dependencies

sudo apt install wget gnupg2 lsb-release -y
Enter fullscreen mode Exit fullscreen mode

2.3 Install pgBackRest

sudo apt install pgbackrest -y
Enter fullscreen mode Exit fullscreen mode

Verify Installation:

pgbackrest --version
Enter fullscreen mode Exit fullscreen mode
  1. Configure pgBackRest

3.1 Create Backup Directory

sudo mkdir -p /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
Enter fullscreen mode Exit fullscreen mode

3.2 Create Configuration File

Edit the pgBackRest configuration file:

sudo nano /etc/pgbackrest.conf
Enter fullscreen mode Exit fullscreen mode

Add the following configuration:

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

[pg17]
pg1-path=/var/lib/postgresql/17/main
pg1-port=5432
pg1-user=postgres
Enter fullscreen mode Exit fullscreen mode

Save and exit.

3.3 Set Permissions for the Config File

sudo chmod 640 /etc/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest.conf
Enter fullscreen mode Exit fullscreen mode
  1. Configure PostgreSQL for pgBackRest 4.1 Enable Archive Mode Edit the postgresql.conf file: 
sudo nano /etc/postgresql/17/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Add/Update the following lines:

archive_mode = on
archive_command = 'pgbackrest --stanza=pg17 archive-push %p'
wal_level = replica
max_wal_senders = 3
Enter fullscreen mode Exit fullscreen mode

4.2 Restart PostgreSQL

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode
  1. Create a Stanza

A stanza represents a configured database cluster.

sudo -u postgres pgbackrest --stanza=pg17 --log-level-console=info stanza-create
Enter fullscreen mode Exit fullscreen mode

Check the Stanza Status:

sudo -u postgres pgbackrest --stanza=pg17 --log-level-console=info check
Enter fullscreen mode Exit fullscreen mode

Testing data:

Let’s perform the following steps to create a database, create a table, and insert sample data in PostgreSQL 17 for testing purposes.

  1. Access PostgreSQL Shell

Log in as the postgres user:

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode
  1. Create a Test Database 
CREATE DATABASE test_db;
Enter fullscreen mode Exit fullscreen mode

Switch to the new database:

\c test_db
Enter fullscreen mode Exit fullscreen mode
  1. Create a Test Table 
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    position VARCHAR(50),
    salary NUMERIC(10, 2)
);
Enter fullscreen mode Exit fullscreen mode
  1. Insert Sample Data 
INSERT INTO employees (name, position, salary) VALUES 
('Arun Mahto', 'Manager', 75000.00),
('Purab Kumar', 'Sr. Developer', 60000.00),
('Dhruv Sanchit', 'Jr. Developer', 50000.00);
Enter fullscreen mode Exit fullscreen mode
  1. Query the Table

Verify the inserted data:

SELECT * FROM employees;
Enter fullscreen mode Exit fullscreen mode

You should see output like:

test_db=# SELECT * FROM employees;

 id |     name      |   position    |  salary
----+---------------+---------------+----------
  1 | Arun Mahto    | Manager       | 75000.00
  2 | Purab Kumar   | Sr. Developer | 60000.00
  3 | Dhruv Sanchit | Jr. Developer | 50000.00
(3 rows)
Enter fullscreen mode Exit fullscreen mode
  1. Exit PostgreSQL Shell

Exit the PostgreSQL prompt:

\q
Enter fullscreen mode Exit fullscreen mode
  1. Perform Backups

6.1 Full Backup

sudo -u postgres pgbackrest --stanza=pg17 --type=full backup
Enter fullscreen mode Exit fullscreen mode

6.2 Differential Backup

sudo -u postgres psql
\c test_db
Enter fullscreen mode Exit fullscreen mode

Insert again some Data:

INSERT INTO employees (name, position, salary) VALUES 
('Sanju Mehta', 'DBA', 70000.00),
('Arun Kumar', 'Sr. Developer', 50000.00),
('Ram Kishore', 'Jr. Developer', 40000.00);
Enter fullscreen mode Exit fullscreen mode

Verify the inserted data:

SELECT * FROM employees;
Enter fullscreen mode Exit fullscreen mode

You should see output like:

test_db=# SELECT * FROM employees;
 id |     name      |   position    |  salary
----+---------------+---------------+----------
  1 | Arun Mahto    | Manager       | 75000.00
  2 | Purab Kumar   | Sr. Developer | 60000.00
  3 | Dhruv Sanchit | Jr. Developer | 50000.00
  4 | Sanju Mehta   | DBA           | 70000.00
  5 | Arun Kumar    | Sr. Developer | 50000.00
  6 | Ram Kishore   | Jr. Developer | 40000.00
(6 rows)
Enter fullscreen mode Exit fullscreen mode 
sudo -u postgres pgbackrest --stanza=pg17 --type=diff backup
Enter fullscreen mode Exit fullscreen mode

6.3 Incremental Backup

sudo -u postgres psql
\c test_db
Enter fullscreen mode Exit fullscreen mode

Insert again some Data:

INSERT INTO employees (name, position, salary) VALUES 
('Kumar Gaurav', 'DEO', 34000.00),
('Madhu Kumari', 'System Admin', 45000.00),
('Kesri Ram', 'Accountant', 50000.00);
Enter fullscreen mode Exit fullscreen mode

Verify the inserted data:

SELECT * FROM employees;
Enter fullscreen mode Exit fullscreen mode

You should see output like:

test_db=# SELECT * FROM employees;
 id |     name      |   position    |  salary
----+---------------+---------------+----------
  1 | Arun Mahto    | Manager       | 75000.00
  2 | Purab Kumar   | Sr. Developer | 60000.00
  3 | Dhruv Sanchit | Jr. Developer | 50000.00
  4 | Sanju Mehta   | DBA           | 70000.00
  5 | Arun Kumar    | Sr. Developer | 50000.00
  6 | Ram Kishore   | Jr. Developer | 40000.00
  7 | Kumar Gaurav  | DEO           | 34000.00
  8 | Madhu Kumari  | System Admin  | 45000.00
  9 | Kesri Ram     | Accountant    | 50000.00
(9 rows)
Enter fullscreen mode Exit fullscreen mode 
sudo -u postgres pgbackrest --stanza=pg17 --type=incr backup
Enter fullscreen mode Exit fullscreen mode

6.4 Verify Backups

sudo -u postgres pgbackrest --stanza=pg17 info
Enter fullscreen mode Exit fullscreen mode
  1. Restore Backup

7.1 Stop PostgreSQL

sudo systemctl stop postgresql
Enter fullscreen mode Exit fullscreen mode

7.2 Restore

Option 1: Restore with --delta (Recommended for Partial Restore)

The --delta option allows pgBackRest to restore only the changed files, reducing time and risk.

sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=pg17 --delta restore
Enter fullscreen mode Exit fullscreen mode

Once the restore is complete, restart PostgreSQL:

sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Check the status:

sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

*Option 2: Perform a Full Clean Restore
*
If you want to fully restore the database without any remnants from the existing data directory:

  1. Stop PostgreSQL 
sudo systemctl stop postgresql
Enter fullscreen mode Exit fullscreen mode
  1. Backup Existing Data Directory (Optional but Recommended)

Create a backup of the current data directory:

sudo mv /var/lib/postgresql/17/main /var/lib/postgresql/17/main_backup_$(date +%F)
sudo mkdir /var/lib/postgresql/17/main
sudo chown -R postgres:postgres /var/lib/postgresql/17/main
sudo chmod 700 /var/lib/postgresql/17/main
Enter fullscreen mode Exit fullscreen mode
  1. Perform the Restore 
sudo -u postgres pgbackrest --stanza=pg17 restore
Enter fullscreen mode Exit fullscreen mode

This time, it should run without issues.

  1. Start PostgreSQL 
sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Verify PostgreSQL is running:

sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode
  1. Check Database Integrity

Connect to PostgreSQL and ensure the restore was successful:

sudo -u postgres psql -c "SELECT datname FROM pg_database;"
Enter fullscreen mode Exit fullscreen mode

Verify Logs After Restore

Check the pgBackRest logs to ensure everything went smoothly:

sudo tail -f /var/log/pgbackrest/pgbackrest.log
Enter fullscreen mode Exit fullscreen mode

*Summary of Differences Between the Two Options
*
Method When to Use Effect

--delta Partial Restore Restores only changed files

Clean Restore Full Restore Wipes data and fully restores

If you are recovering from corruption, a clean restore is better.

If you are recovering from data loss or specific issues, use --delta.

7.3 Start PostgreSQL

sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode
  1. Automate Backups with Cron

Edit the crontab for postgres user:

sudo crontab -u postgres -e
Enter fullscreen mode Exit fullscreen mode

Add the following lines:

# Full Backup every Sunday at 2 AM

0 2 * * 0 pgbackrest --stanza=pg17 --type=full backup
# Incremental Backup every day at 2 AM (except Sunday)

0 2 * * 1-6 pgbackrest --stanza=pg17 --type=incr backup
9. Monitor pgBackRest Logs
Enter fullscreen mode Exit fullscreen mode

Logs are stored at:

sudo tail -f /var/log/pgbackrest/pgbackrest.log
Enter fullscreen mode Exit fullscreen mode
  1. Test Recovery Plan

Regularly test backup restoration to ensure reliability.

Document the recovery process.

  1. Troubleshooting

Check PostgreSQL and pgBackRest logs:

sudo journalctl -u postgresql
sudo tail -f /var/log/pgbackrest/pgbackrest.log
Enter fullscreen mode Exit fullscreen mode

Verify configuration:

sudo -u postgres pgbackrest --stanza=pg17 check
Enter fullscreen mode Exit fullscreen mode

*Conclusion
*
You’ve successfully installed, configured, and tested pgBackRest for PostgreSQL 17 on Ubuntu 24.04 LTS. Regular backups and periodic recovery tests will ensure database resilience and reliability.

*What is pgbackrest?
*
pgBackRest is an open-source backup and restore tool designed specifically for PostgreSQL databases. It provides reliable, efficient, and customizable backup and recovery solutions for PostgreSQL, ensuring data protection and disaster recovery readiness.

*Key Features of pgBackRest
*
Reliable Backups

Supports full, differential, and incremental backups.

Ensures consistent backups even for large databases.

Parallel Backup and Restore

Utilizes multiple threads to speed up backup and restore processes.

Compression and Encryption

Compresses backups to save storage space.

Supports AES-256 encryption for secure backups.

Incremental Backups

Backs up only the changes since the last backup, reducing storage usage and time.

Backup Retention Policies

Supports configurable retention policies to automatically manage older backups.

WAL (Write-Ahead Log) Archiving

Handles WAL archiving to ensure point-in-time recovery (PITR).

Remote Backups

Supports remote backup and restore over SSH.

Backup Integrity Checks

Validates backups for consistency to prevent corruption issues.

Scalability

Efficient for databases ranging from small deployments to multi-terabyte clusters.

How pgBackRest Works

Stanza Configuration:

A stanza is a logical name representing a PostgreSQL cluster.

It contains configuration for the cluster, including backup paths and parameters.

Backup Types:

Full Backup: Complete copy of the database.

Differential Backup: Changes since the last full backup.

Incremental Backup: Changes since the last backup (full, differential, or incremental).

Backup Storage:

Stored in a configured repository (repo1-path) on a local or remote system.

Restore Operations:

Supports full and point-in-time recovery.

Automation:

Cron jobs are often configured to automate scheduled backups.

Use Cases of pgBackRest

Disaster Recovery: Reliable backups ensure data is recoverable after failure.

High Availability Environments: Supports replication setups.

Compliance: Backup encryption ensures data privacy and meets regulatory requirements.

Large Database Backups: Handles multi-terabyte databases efficiently.

Comparison with Traditional Backup Tools (e.g., pg_dump)

Feature pgBackRest pg_dump

Backup Type Full, Incremental, Differential Full Only

Compression Yes Yes

Encryption Yes (AES-256) No

Backup Speed Fast (Parallel) Slower

WAL Archiving Yes No

PITR (Point-In-Time Recovery) Yes No

Conclusion

pgBackRest is an advanced backup and restore tool tailored for PostgreSQL environments. Its scalability, speed, encryption, and integrity checks make it an ideal choice for critical production databases requiring robust backup solutions.

For any doubts and query, please write on YouTube video comments section.

Note : Flow the Process shown in video.

Please, Subscribe and like for more videos:

https://www.youtube.com/@chiragstutorial

Don't forget to, Follow, Like, Share &, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

Note: All scripts used in this demo will be available in our website.

Link will be available in description.

Top comments (0)

Read next

morayodeji profile image

How I obtained the true lines of code in my project.

Ayodeji Moronkeji -

davidasx profile image

How to backup a Linux system?

Aoshi Xu -

turingvangisms profile image

Next.js Getting Started Guide

Turing -

turingvangisms profile image

Best Next.js Course

Turing -