8.2 Ensure the backup and restore tool, 'pgBackRest', is installed and configured

Information

pgBackRest aims to be a simple, reliable backup and restore system that can seamlessly scale up to the largest databases and workloads. Instead of relying on traditional backup tools like tar and rsync, pgBackRest implements all backup features internally and uses a custom protocol for communicating with remote systems. Removing reliance on tar and rsync allows for better solutions to database-specific backup challenges. The custom remote protocol allows for more flexibility and limits the types of connections that are required to perform a backup which increases security.

Rationale:

The native PostgreSQL backup facility pg_dump provides adequate logical backup operations but does not provide for Point In Time Recovery (PITR). The PostgreSQL facility pg_basebackup performs a physical backup of the database files and does provide for PITR, but it is constrained by single threading. Both of these methodologies are standard in the PostgreSQL ecosystem and appropriate for particular backup/recovery needs. pgBackRest offers another option with much more robust features and flexibility.

pgBackRest is open-source software developed to perform efficient backups on PostgreSQL databases that measure in tens of terabytes and greater. It supports per-file checksums, compression, partial/failed backup resume, high-performance parallel transfer, asynchronous archiving, tablespaces, expiration, full/differential/incremental backups, local/remote operation via SSH or TLS, hard-linking, restore, backup encryption, and more. pgBackRest is written in C and does not depend on rsync or tar but instead performs its own deltas which give it maximum flexibility. Finally, pgBackRest provides an easy-to-use internal repository listing backup details accessible via the pgbackrest info command, as illustrated below.

$ pgbackrest info

stanza: proddb01

status: ok

db (current)

wal archive min/max (15.0-1): 000000010000000000000012 / 000000010000000000000017

full backup: 20231012-153106F

timestamp start/stop: 2023-10-12 15:31:06 / 2023-10-12 15:31:49

wal start/stop: 000000010000000000000012 / 000000010000000000000012

database size: 29.4MB, backup size: 29.4MB

repository size: 3.4MB, repository backup size: 3.4MB

diff backup: 20231012-153106F_20231012-173109D

timestamp start/stop: 2023-10-12 17:31:09 / 2023-10-12 17:31:19

wal start/stop: 000000010000000000000015 / 000000010000000000000015

database size: 29.4MB, backup size: 2.6MB

repository size: 3.4MB, repository backup size: 346.8KB

backup reference list: 20231012-153106F

incr backup: 20231012-153106F_20231012-183114I

timestamp start/stop: 2023-10-12 18:31:14 / 2023-10-12 18:31:22

wal start/stop: 000000010000000000000017 / 000000010000000000000017

database size: 29.4MB, backup size: 8.2KB

repository size: 3.4MB, repository backup size: 519B

backup reference list: 20231012-153106F, 20231012-153106F_20231012-173109D

Solution

pgBackRest is not installed nor configured for PostgreSQL by default, but instead is maintained as a GitHub project. Fortunately, it is a part of the PGDG repository and can be easily installed:

# whoami
root
# dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm
<snip>
Installed:
epel-release-9-7.el9.noarch

Complete!
# dnf -y install pgbackrest
<snip>
Installed:
libssh2-1.10.0-1.el9.x86_64 pgbackrest-2.48-1PGDG.rhel9.x86_64

Complete!

Once installed, pgBackRest must be configured for things like stanza name, backup location, retention policy, logging, etc. Please consult the configuration guide.
If employing pgBackRest for your backup/recovery solution, ensure the repository, base backups, and WAL archives are stored on a reliable file system separate from the database server. Further, the external storage system where backups reside should have limited access to only those system administrators as necessary. Finally, as with any backup/recovery solution, stringent testing must be conducted. A backup is only good if it can be restored successfully.

See Also

https://workbench.cisecurity.org/benchmarks/15126

Item Details

Category: CONTINGENCY PLANNING

References: 800-53|CP-9, 800-53|CP-10, CSCv7|10.1, CSCv7|10.2

Plugin: Unix

Control ID: 2442ab4f1828eb4f0b5ac448cf26051744e4b7f9db076b87180daa5585b4bd94