Detections
Analytics
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 (16.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
Item Details
Audit Name: CIS PostgreSQL 16 OS v1.0.0
Category: CONTINGENCY PLANNING
References: 800-53|CP-9, 800-53|CP-10, CSCv7|10.1, CSCv7|10.2
Plugin: Unix
Control ID: 0b1466e7dda3f89653897cc12f1df6c60dce3d6620de62fd2b434cceb93effc5