In continuation of our blog series on effectively implementing backup strategies in a production environment, this article delves into the topic of Efficient PostgreSQL Backup Strategies: Leveraging pgBackRest for Performance & Recovery and how it enhances database reliability.
pgBackRest is a robust, open-source backup and restore tool for PostgreSQL, designed for scalability and high-performance databases. It supports full, differential, and incremental backups, parallel processing, encryption, and compression. Integrated with PostgreSQL’s archive logs, it enables Point-in-Time Recovery (PITR), ensuring minimal downtime and data loss for mission-critical systems.
By leveraging pgBackRest’s capabilities, organizations can streamline their backup processes, optimize storage usage, and ensure rapid data recovery when needed. Implementing an efficient strategy with pgBackRest strengthens PostgreSQL’s resilience, making it a preferred solution for production environments.
Prerequisites
Before installing pgBackRest, certain dependencies need to be installed, including PostgreSQL libraries, compression utilities, and necessary system packages to ensure compatibility and proper functionality.
Step01: Installing packages (postgresql16-libs postgresql16-devel) and dependencies (libssh2 libxml2-devel bzip2-devel) based on specific PostgreSQL versions.
- Ensure your repository has the correct PostgreSQL packages available.
| 1 2 | sudo yum install postgresql16-libs postgresql16-devel libssh2 libxml2-devel bzip2-devel [root@mysql-02 pgsql]# sudo yum install epel-release |
Step01.1: Download the source package: http://pyyaml.org/download/libyaml/yaml-0.2.5.tar.gz.
To build and install LibYAML
| 1 2 | [root@mysql-02 src]# wget http://pyyaml.org/download/libyaml/yaml-0.2.5.tar.gz [root@mysql-02 src]# tar -xvf yaml-0.2.5.tar.gz |
Step02: Download build in 2.54.2 release of pgbackrest in tar format and unzip using below command.
| 1 2 3 4 5 6 | [postgres@mysql-02 ~]$ which pg_config /usr/pgsql-16/bin/pg_config [root@mysql-02 pgsql]# mkdir -p /build [root@mysql-02 pgsql]# wget -q -O - \ > https://github.com/pgbackrest/pgbackrest/archive/release/2.54.2.tar.gz | \ > tar zx -C /build |
Installing pgbackrest 2.54.2 using below command in centos 08
| 1 | [root@mysql-02 src]# cd /build/pgbackrest-release-2.54.2/src && ./configure && make |
Step03: After completing the installation, grant the necessary permissions to the directory and the PostgreSQL user to ensure seamless operations and secure database access.
| 1 2 3 4 5 | [root@mysql-02 src]# sudo mkdir -p /etc/pgbackrest [root@mysql-02 src]# sudo mkdir -p /etc/pgbackrest/conf.d [root@mysql-02 src]# sudo touch /etc/pgbackrest/pgbackrest.conf [root@mysql-02 src]# sudo chmod 640 /etc/pgbackrest/pgbackrest.conf [root@mysql-02 src]# sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf |
Step04: Verify different option in pgbackrest using below command:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | [postgres@mysql-02 archive_status]$ pgbackrest --help pgBackRest 2.54.2 - General help Usage: pgbackrest [options] [command] Commands: annotate add or modify backup annotation archive-get get a WAL segment from the archive archive-push push a WAL segment to the archive backup backup a database cluster check check the configuration expire expire backups that exceed retention help get help info retrieve information about backups repo-get get a file from a repository repo-ls list files in a repository restore restore a database cluster server pgBackRest server server-ping ping pgBackRest server stanza-create create the required stanza data stanza-delete delete a stanza stanza-upgrade upgrade a stanza start allow pgBackRest processes to run stop stop pgBackRest processes from running verify verify contents of the repository version get version Use 'pgbackrest help [command]' for more information. |
Step05: Modify postgresql.conf to enable replication and archiving using pgBackRest.
Set wal_level = replica to support replication
archive_mode = on for backup management
archive_command to store WAL files efficiently.
max_wal_senders to allow multiple standby connections, ensuring robust data integrity, disaster recovery, and optimized database performance.
| 1 2 3 4 5 6 | [postgres@mysql-02 archive_status]vi /var/lib/pgsql/16/data/postgresql.conf wal_level = replica archive_mode = on archive_command = '/var/lib/pgbackrest --stanza=my-stanza archive-push %p' max_wal_senders = 10 hot_standby = on |
Full Backup:
The entire database cluster’s contents are copied to the backup via pgBackRest. A full backup is always the database cluster’s initial backup. A complete backup can always be directly restored with pgBackRest. No files outside of the whole backup are necessary for the full backup to remain consistent.
Execute the following command to perform a full backup in pgBackRest:
EXPLAIN:
sudo -u postgres – Runs the command as the PostgreSQL user (postgres),
– pgbackrest – This is the pgBackRest utility,
–stanza=my-stanza – Defines the stanza, which is a named configuration set for managing backups.
–start-fast – Speeds up the backup process by forcing PostgreSQL to switch to a new WAL (Write-Ahead Log) segment immediately instead of waiting for the current one to complete.
–type=full backup – Specifies a full backup, meaning all database files will be included rather than an incremental or differential backup.
The main action, instructing pgBackRest to start the backup process.
–log-level-console=info – Sets the logging level for the console output to info, ensuring relevant status updates are displayed during execution.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [postgres@mysql-02 archive_status]$ sudo -u postgres pgbackrest --stanza=my-stanza --start-fast --type=full backup --log-level-console=info 2025-04-11 13:45:09.900 P00 INFO: backup command begin 2.54.2: --archive-timeout=500 --exec-id=58043-653bcd8c --log-level-console=info --pg1-path=/var/lib/pgsql/16/data --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=my-stanza --start-fast --type=full 2025-04-11 13:45:09.950 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2025-04-11 13:45:10.069 P00 INFO: backup start archive = 000000010000000000000033, lsn = 0/33000028 2025-04-11 13:45:10.069 P00 INFO: check archive for prior segment 000000010000000000000032 2025-04-11 13:45:34.884 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2025-04-11 13:45:35.009 P00 INFO: backup stop archive = 000000010000000000000033, lsn = 0/33000138 2025-04-11 13:45:35.016 P00 INFO: check archive for segment(s) 000000010000000000000033:000000010000000000000033 2025-04-11 13:45:35.687 P00 INFO: new backup label = 20250411-134509F 2025-04-11 13:45:35.998 P00 INFO: full backup size = 51.5MB, file total = 1969 2025-04-11 13:45:36.000 P00 INFO: backup command end: completed successfully (26119ms) 2025-04-11 13:45:36.001 P00 INFO: expire command begin 2.54.2: --exec-id=58043-653bcd8c --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=my-stanza 2025-04-11 13:45:36.022 P00 INFO: repo1: 16-1 remove archive, start = 000000010000000000000025, stop = 000000010000000000000030 2025-04-11 13:45:36.023 P00 INFO: expire command end: completed successfully (23ms) |
Differential Backup:
A differential backup is useful when we want a balance between storage efficiency and recovery speed, as it requires the last full backup plus the latest differential backup to restore the system.
For differential backup in pgbackrest use below command.
EXPLAIN:
Here –type=diff backup – Instead of a full backup, this captures only the differences since the last full backup, reducing storage usage and backup time.
– Other parameters remain the same to ensure efficiency and fast processing
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [postgres@mysql-02 archive_status]$ sudo -u postgres pgbackrest --stanza=my-stanza --start-fast --type=diff backup --log-level-console=info 2025-04-11 13:46:26.331 P00 INFO: backup command begin 2.54.2: --archive-timeout=500 --exec-id=58477-7ac8fddd --log-level-console=info --pg1-path=/var/lib/pgsql/16/data --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=my-stanza --start-fast --type=diff 2025-04-11 13:46:26.474 P00 INFO: last backup label = 20250411-134509F, version = 2.54.2 2025-04-11 13:46:26.474 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2025-04-11 13:46:26.615 P00 INFO: backup start archive = 000000010000000000000035, lsn = 0/35000028 2025-04-11 13:46:26.615 P00 INFO: check archive for prior segment 000000010000000000000034 2025-04-11 13:46:28.235 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2025-04-11 13:46:28.415 P00 INFO: backup stop archive = 000000010000000000000035, lsn = 0/35000100 2025-04-11 13:46:28.420 P00 INFO: check archive for segment(s) 000000010000000000000035:000000010000000000000035 2025-04-11 13:46:28.780 P00 INFO: new backup label = 20250411-134509F_20250411-134626D 2025-04-11 13:46:29.074 P00 INFO: diff backup size = 108KB, file total = 1969 2025-04-11 13:46:29.074 P00 INFO: backup command end: completed successfully (2755ms) 2025-04-11 13:46:29.074 P00 INFO: expire command begin 2.54.2: --exec-id=58477-7ac8fddd --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=my-stanza 2025-04-11 13:46:29.078 P00 INFO: repo1: 16-1 no archive to remove 2025-04-11 13:46:29.079 P00 INFO: expire command end: completed successfully (5ms) |
Incremental Backup:
An incremental backup is beneficial for reducing storage requirements and backup time even further than a differential backup, but restoring from it requires multiple backups (the last full, the last differential if applicable, and all subsequent incremental backups).
For differential backup in pgbackrest use below command.
EXPLAIN:
Here –type=incr backup – Instead of a full or differential backup, this captures only the changes since the last backup (full or differential).
– The other parameters remain unchanged to ensure fast processing and accurate logging.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [postgres@mysql-02 ~]$ sudo -u postgres pgbackrest --stanza=my-stanza --start-fast --type=incr backup --log-level-console=info 2025-04-11 14:49:40.191 P00 INFO: backup command begin 2.54.2: --archive-timeout=500 --exec-id=80224-a7bc0d13 --log-level-console=info --pg1-path=/var/lib/pgsql/16/data --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=my-stanza --start-fast --type=incr 2025-04-11 14:49:40.379 P00 INFO: last backup label = 20250411-134509F_20250411-134626D, version = 2.54.2 2025-04-11 14:49:40.380 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2025-04-11 14:49:40.590 P00 INFO: backup start archive = 000000020000000000000038, lsn = 0/38000060 2025-04-11 14:49:40.590 P00 INFO: check archive for prior segment 000000020000000000000037 2025-04-11 14:49:42.002 P00 WARN: a timeline switch has occurred since the 20250411-134509F_20250411-134626D backup, enabling delta checksum HINT: this is normal after restoring from backup or promoting a standby. 2025-04-11 14:49:45.607 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2025-04-11 14:49:45.750 P00 INFO: backup stop archive = 000000020000000000000038, lsn = 0/38000138 2025-04-11 14:49:45.772 P00 INFO: check archive for segment(s) 000000020000000000000038:000000020000000000000038 2025-04-11 14:49:46.503 P00 INFO: new backup label = 20250411-134509F_20250411-144940I 2025-04-11 14:49:46.734 P00 INFO: incr backup size = 2.7MB, file total = 1969 2025-04-11 14:49:46.735 P00 INFO: backup command end: completed successfully (6559ms) 2025-04-11 14:49:46.735 P00 INFO: expire command begin 2.54.2: --exec-id=80224-a7bc0d13 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=my-stanza 2025-04-11 14:49:46.744 P00 INFO: repo1: 16-1 no archive to remove 2025-04-11 14:49:46.746 P00 INFO: expire command end: completed successfully (11ms) |
Restoring Backup:
For Restoring backup using below command:
| 1 2 3 4 5 6 7 8 9 10 | [postgres@mysql-02 global]$ sudo -u postgres pgbackrest --stanza=my-stanza restore [postgres@mysql-02 global]$ sudo systemctl start postgresql-16.service [postgres@mysql-02 global]$ sudo systemctl status postgresql-16.service ● postgresql-16.service - PostgreSQL 16 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; vendor preset: disabled) Active: active (running) since Fri 2025-04-11 14:11:41 EDT; 11s ago Docs: https://www.postgresql.org/docs/16/static/ Process: 34945 ExecReload=/bin/kill -HUP $MAINPID (code=exited, status=0/SUCCESS) Process: 67027 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 67036 (postgres) |
Verify:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | postgres=# \l+ List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges | Size | Tablespace | Description -----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------+---------+------------+------ -------------------------------------- dvdrental | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | | 14 MB | pg_default | master | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | | 7385 kB | pg_default | postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | | 7644 kB | pg_default | defau lt administrative connection database repmgr | repmgr | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | | 7545 kB | pg_default | template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +| 7329 kB | pg_default | unmod ifiable empty database | | | | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +| 7385 kB | pg_default | defau lt template for new databases | | | | | | | | postgres=CTc/postgres | | | (6 rows) |
Monitoring backup Process:
This PL/pgSQL function allows monitoring pgBackRest within PostgreSQL, converting backup details into JSON format for direct queries. It retrieves data, stores it temporarily, and returns structured backup insights for efficient database analysis.
[postgres@mysql-02 ~]$ vi pgsql-pgbackrest-info.sql
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | -- An example of monitoring pgBackRest from within PostgreSQL -- Use copy to export data from the pgBackRest info command into the jsonb -- type so it can be queried directly by PostgreSQL. -- Create monitor schema create schema monitor; -- Get pgBackRest info in JSON format create function monitor.pgbackrest_info() returns jsonb AS $$ declare data jsonb; begin -- Create a temp table to hold the JSON data create temp table temp_pgbackrest_data (data text); -- Copy data into the table directly from the pgBackRest info command copy temp_pgbackrest_data (data) from program 'pgbackrest --output=json info' (format text); select replace(temp_pgbackrest_data.data, E'\n', '\n')::jsonb into data from temp_pgbackrest_data; drop table temp_pgbackrest_data; return data; end $$ language plpgsql; |
Get last successful backup for each stanza:
[postgres@mysql-02 ~]$ vi pgsql-pgbackrest-query.sql
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- Get last successful backup for each stanza -- -- Requires the monitor.pgbackrest_info function. with stanza as ( select data->'name' as name, data->'backup'->( jsonb_array_length(data->'backup') - 1) as last_backup, data->'archive'->( jsonb_array_length(data->'archive') - 1) as current_archive from jsonb_array_elements(monitor.pgbackrest_info()) as data ) select name, to_timestamp( (last_backup->'timestamp'->>'stop')::numeric) as last_successful_backup, current_archive->>'max' as last_archived_wal from stanza; |
Verifying Backup Process:
| 1 2 3 4 5 | postgres=# \i pgsql-pgbackrest-query.sql name | last_successful_backup | last_archived_wal -------------+------------------------+-------------------------- "my-stanza" | 2025-04-11 13:46:28-04 | 000000010000000000000036 (1 row) |
Reference:https://pgbackrest.org/user-guide-rhel.html
