pgBackRest Backup Configuration for PostgreSQL 18 on Oracle Linux 9

This instruction configures pgBackRest 2.58 to manage encrypted backups for a PostgreSQL 18 database cluster on Oracle Linux 9. It covers WAL archiving integration, local repository setup with AES-256-CBC encryption, full/differential/incremental backup execution, restore verification, and cron-based scheduling.

This is Document 2 in a four-part series. Document 1 (PostgreSQL 18 Installation on Oracle Linux 9) must be complete before starting this procedure.

Assumptions

This instruction assumes:

• Document 1 is complete: PostgreSQL 18 is installed, initialized, and running on Oracle Linux 9
• The service name is postgresql-18
• The data directory is /var/lib/pgsql/18/data
• The postgres OS user exists and owns the data directory
• The PGDG repository (pgdg-redhat-repo-latest) is configured and the OL9 PostgreSQL module is disabled
• SELinux is in enforcing mode
• Firewalld is active
• Root access or sudo privileges are available
• The server has sufficient disk space for the backup repository (3-5x the database size, depending on data change rate and retention settings)
• Backups are stored locally on the same server (remote and cloud repositories are out of scope)

Prerequisites

Automatic setup

Install pgBackRest from the PGDG common repository. The PGDG repository is already configured from Document 1.

sudo dnf install -y pgbackrest

Expected output:

...
Installed:
  pgbackrest-2.58.0-1PGDG.rhel9.x86_64
Complete!

The version shown (2.58.0) was current at time of writing. You may see a newer version. The procedures in this document apply to pgBackRest 2.x.

This package installs the pgbackrest binary, default configuration directory at /etc/pgbackrest/, and man pages. It does not create the backup repository directory or the configuration file.

Manual setup

No manual setup alternative exists. pgBackRest must be installed from the PGDG repository.

Additional setup

1. Create the backup repository directory

sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest

Verify:

ls -ld /var/lib/pgbackrest

Expected output:

drwxr-x--- 2 postgres postgres 6 ... /var/lib/pgbackrest

2. Create the log directory

sudo mkdir -p /var/log/pgbackrest
sudo chmod 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest

Verify:

ls -ld /var/log/pgbackrest

Expected output:

drwxrwx--- 2 postgres postgres 6 ... /var/log/pgbackrest

3. Set SELinux context on the repository directory

The PGDG packages do not set an SELinux file context for /var/lib/pgbackrest. With SELinux enforcing, the default var_lib_t context will deny PostgreSQL access. Set the postgresql_db_t context:

sudo dnf install -y policycoreutils-python-utils
sudo semanage fcontext -a -t postgresql_db_t "/var/lib/pgbackrest(/.*)?"
sudo restorecon -Rv /var/lib/pgbackrest

Expected output:

Relabeled /var/lib/pgbackrest from unconfined_u:object_r:var_lib_t:s0 to unconfined_u:object_r:postgresql_db_t:s0

Verify:

ls -dZ /var/lib/pgbackrest

Expected output:

unconfined_u:object_r:postgresql_db_t:s0 /var/lib/pgbackrest

4. Generate the encryption passphrase

Warning: The encryption passphrase cannot be changed after the repository is initialized. Store it securely. If the passphrase is lost, backups in the repository are unrecoverable.

openssl rand -base64 48

Expected output:

(a 64-character base64 string, e.g., k8B3xL9mQ7pR2tY5wA0dF4hJ6nV8cZ1eG3iK5oM7qS9uW2yB4rT6vX8zA0dF3hJ)

Copy this value. It will be used as {ENCRYPTION_PASSPHRASE} in the next section.

{ENCRYPTION_PASSPHRASE} -- the base64 string generated by openssl rand -base64 48. This value is permanent for the life of the repository.

Configuration

Step 1: Create the pgBackRest configuration file

Create /etc/pgbackrest/pgbackrest.conf with the stanza definition, repository settings, encryption, retention policy, and performance options.

Before running this command, replace {ENCRYPTION_PASSPHRASE} below with the passphrase generated in prerequisite step 4. The heredoc does not perform variable expansion, so the placeholder must be replaced in the command itself before pasting. Alternatively, run the command as-is and edit the file afterward with sudo vi /etc/pgbackrest/pgbackrest.conf.

sudo tee /etc/pgbackrest/pgbackrest.conf > /dev/null <<'EOF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass={ENCRYPTION_PASSPHRASE}
repo1-retention-full=2
repo1-retention-diff=2
repo1-block=y
repo1-bundle=y
compress-type=lz4
start-fast=y
log-level-console=info
log-level-file=detail
process-max=2

[global:archive-push]
compress-level=3

[main]
pg1-path=/var/lib/pgsql/18/data
EOF

Set the file permissions:

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

Verify:

ls -l /etc/pgbackrest/pgbackrest.conf

Expected output:

-rw-r----- 1 postgres postgres ... /etc/pgbackrest/pgbackrest.conf

The configuration options:

Step 2: Configure PostgreSQL for WAL archiving

Warning: Changing archive_mode and wal_level requires a full PostgreSQL restart, not just a configuration reload. Plan for a brief service interruption.

Edit postgresql.conf to enable WAL archiving:

sudo -u postgres vi /var/lib/pgsql/18/data/postgresql.conf

Add the following parameters:

archive_mode = on
wal_level = replica
archive_command = 'pgbackrest --stanza=main archive-push %p'

archive_mode = on -- enables WAL archiving. Requires a full restart to change.

wal_level = replica -- generates sufficient WAL data for archiving and replication. Requires a full restart to change.

archive_command -- the shell command PostgreSQL executes to archive each completed WAL segment. The %p placeholder is replaced with the full path to the WAL file.

Step 3: Restart PostgreSQL

A restart is required because archive_mode and wal_level cannot be changed with a reload.

sudo systemctl restart postgresql-18

Verify the service is running:

sudo systemctl status postgresql-18 --no-pager

Expected output:

● postgresql-18.service - PostgreSQL 18 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-18.service; enabled; preset: disabled)
     Active: active (running) since ...

Verify the WAL archiving parameters:

sudo -u postgres psql -c "SHOW archive_mode;" -c "SHOW wal_level;" -c "SHOW archive_command;"

Expected output:

 archive_mode
--------------
 on
(1 row)

 wal_level
-----------
 replica
(1 row)

                    archive_command
-----------------------------------------------------
 pgbackrest --stanza=main archive-push %p
(1 row)

Step 4: Create the stanza

The stanza-create command initializes the repository structure for the main stanza.

sudo -u postgres pgbackrest --stanza=main --log-level-console=info stanza-create

Expected output:

... INFO: stanza-create command begin 2.58.0: ...
... INFO: stanza-create command end: completed successfully ...

Step 5: Verify the configuration

The check command validates that pgBackRest can communicate with PostgreSQL and that WAL archiving is functional.

sudo -u postgres pgbackrest --stanza=main --log-level-console=info check

Expected output:

... INFO: check command begin 2.58.0: ...
... INFO: check repo1 configuration (primary)
... INFO: check repo1 archive for WAL (primary)
... INFO: WAL segment ... successfully archived ...
... INFO: check command end: completed successfully ...

If the check command fails, see the Troubleshooting section before proceeding.

Step 6: Perform a full backup

sudo -u postgres pgbackrest --stanza=main --type=full backup

Expected output:

... INFO: backup command begin 2.58.0: ...
... INFO: new backup label = ...F
... INFO: full backup size = ...
... INFO: backup command end: completed successfully ...

Verify with the info command:

sudo -u postgres pgbackrest info

Expected output:

stanza: main
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (18): ...

        full backup: ...
            timestamp start/stop: ...
            wal start/stop: ...
            database size: ..., database backup size: ...
            repo1: backup set size: ..., backup size: ...

Step 7: Perform a differential backup

A differential backup captures all changes since the last full backup. Restoring a differential requires only the full backup and the differential.

sudo -u postgres pgbackrest --stanza=main --type=diff backup

Expected output:

... INFO: backup command begin 2.58.0: ...
... INFO: new backup label = ...F_...D
... INFO: diff backup size = ...
... INFO: backup command end: completed successfully ...

Step 8: Perform an incremental backup

An incremental backup captures changes since the last backup of any type (full, differential, or incremental). Restoring an incremental requires the full backup, any intermediate differentials, and all incrementals in the chain.

sudo -u postgres pgbackrest --stanza=main --type=incr backup

Expected output:

... INFO: backup command begin 2.58.0: ...
... INFO: new backup label = ...F_...I
... INFO: incr backup size = ...
... INFO: backup command end: completed successfully ...

View the complete backup chain:

sudo -u postgres pgbackrest info

Expected output:

stanza: main
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (18): ...

        full backup: ...
            timestamp start/stop: ...
            wal start/stop: ...
            database size: ..., database backup size: ...
            repo1: backup set size: ..., backup size: ...

        diff backup: ...
            timestamp start/stop: ...
            wal start/stop: ...
            database size: ..., database backup size: ...
            repo1: backup set size: ..., backup size: ...
            backup reference list: ...F

        incr backup: ...
            timestamp start/stop: ...
            wal start/stop: ...
            database size: ..., database backup size: ...
            repo1: backup set size: ..., backup size: ...
            backup reference list: ...F, ...F_...D

Step 9: Test a restore

Warning: This procedure stops PostgreSQL and deletes the contents of the data directory. All uncommitted transactions will be lost. Do not perform this on a production system without a verified backup.

1. Stop PostgreSQL:

sudo systemctl stop postgresql-18

2. Clear the data directory:

sudo -u postgres find /var/lib/pgsql/18/data -mindepth 1 -delete

3. Restore from the latest backup:

sudo -u postgres pgbackrest --stanza=main restore

Expected output:

... INFO: restore command begin 2.58.0: ...
... INFO: repo1: restore backup set ...
... INFO: restore command end: completed successfully ...

4. Start PostgreSQL:

sudo systemctl start postgresql-18

5. Verify PostgreSQL is running and data is intact:

sudo systemctl status postgresql-18 --no-pager

Expected output:

● postgresql-18.service - PostgreSQL 18 database server
     Active: active (running) since ...

sudo -u postgres psql -c "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname;"

Expected output:

 datname
---------
 appdb
 postgres
(2 rows)

The restore recovered all databases. If the application database (appdb) was created in Document 1, it appears in the list.

As an alternative to clearing the data directory, use --delta to restore only changed files using checksum comparison. --delta is safer because it preserves unchanged files and only replaces files that differ by checksum, but it requires the data directory to still exist (not empty or deleted).

sudo -u postgres pgbackrest --stanza=main --delta restore

Step 10: Configure backup scheduling

Add cron entries for the postgres user to automate backups: a weekly full backup on Sunday and daily differential backups Monday through Saturday.

Verify the cron service is running:

sudo systemctl is-active crond

Expected output:

active

If crond is not active, enable it: sudo systemctl enable --now crond.

Edit the crontab for the postgres user:

sudo -u postgres crontab -e

Add the following lines:

30 06 * * 0   pgbackrest --type=full --stanza=main backup
30 06 * * 1-6 pgbackrest --type=diff --stanza=main backup

Verify the crontab:

sudo -u postgres crontab -l

Expected output:

30 06 * * 0   pgbackrest --type=full --stanza=main backup
30 06 * * 1-6 pgbackrest --type=diff --stanza=main backup

This schedule runs:

• Sunday 06:30 -- full backup
• Monday through Saturday 06:30 -- differential backup

Adjust the time to a low-activity period appropriate for your environment.

Validation

Quick check

sudo -u postgres pgbackrest --stanza=main check

Expected output:

... INFO: check command end: completed successfully ...

Full validation

1. Verify WAL archiving is active

sudo -u postgres psql -c "SELECT archived_count, failed_count, last_archived_time FROM pg_stat_archiver;"

Expected output:

 archived_count | failed_count |     last_archived_time
----------------+--------------+----------------------------
             12 |            0 | 2026-02-28 ...
(1 row)

archived_count must be greater than 0. failed_count must be 0.

2. Verify backup info shows successful backups

sudo -u postgres pgbackrest info

Expected output: The output shows the main stanza with status ok, cipher aes-256-cbc, WAL archive range, and at least one full backup with timestamps and sizes.

3. Verify the repository directory structure

sudo -u postgres ls -la /var/lib/pgbackrest/archive/main/ /var/lib/pgbackrest/backup/main/

Expected output:

/var/lib/pgbackrest/archive/main/:
total ...
drwxr-x--- ... 18-1

/var/lib/pgbackrest/backup/main/:
total ...
drwxr-x--- ... (backup label directories)
-rw-r----- ... backup.info
-rw-r----- ... backup.info.copy

4. Verify encryption is active

sudo -u postgres pgbackrest info --output=json | python3 -c "import sys,json; d=json.load(sys.stdin); print(d[0]['cipher'])"

Expected output:

aes-256-cbc

5. Verify cron is scheduled

sudo -u postgres crontab -l | grep pgbackrest

Expected output:

30 06 * * 0   pgbackrest --type=full --stanza=main backup
30 06 * * 1-6 pgbackrest --type=diff --stanza=main backup

Troubleshooting

References

• pgBackRest User Guide - RHEL
• pgBackRest Configuration Reference
• pgBackRest Command Reference
• PostgreSQL 18: WAL Configuration
• PostgreSQL 18: Continuous Archiving and Point-in-Time Recovery