pgBackRest Standby Backup for PostgreSQL 18 on Oracle Linux 9

This instruction configures pgBackRest to source backup data files from a streaming standby server instead of the primary. This offloads backup I/O from the primary, eliminating the performance impact of backup operations on production workloads. The backup command continues to run on the primary (where the local repository resides), but pgBackRest SSH-es into the standby to read data files while reading WAL segments, pg_control, and configuration files locally from the primary.

This is Document 4 in a four-part series:

1. PostgreSQL 18 Installation on Oracle Linux 9 -- server installation with SSL/TLS and SCRAM-SHA-256
2. pgBackRest Backup Configuration for PostgreSQL 18 on Oracle Linux 9 -- backup with WAL archiving on the primary
3. PostgreSQL 18 Streaming Replication (2 Standbys) on Oracle Linux 9 -- physical streaming replication to two hot standbys
4. pgBackRest Standby Backup -- this document

Architecture:

+------------------+     SSH (data files)     +------------------+
|                  | -----------------------> |                  |
|     PRIMARY      |                          |    STANDBY 1     |
|  {PRIMARY_HOST}  |                          |  {STANDBY1_HOST} |
|  {PRIMARY_IP}    |                          |  {STANDBY1_IP}   |
|                  |                          +------------------+
|  pgbackrest      |
|  backup command   |
|  runs HERE       |
|                  |
|  Repository:     |
|  /var/lib/       |
|   pgbackrest     |
|                  |
|  Reads locally:  |
|  - WAL segments  |
|  - pg_control    |
|  - config files  |
+------------------+

- backup-standby=y tells pgBackRest to copy data files from a standby
- WAL, pg_control, and configuration files are always read from the primary
- The backup command runs on the host that holds the repository (primary)
- SSH is used to access data files on the remote standby

Assumptions

This instruction assumes:

• Document 1 is complete on all three servers: PostgreSQL 18 is installed from the PGDG repository, SSL/TLS is configured, and SCRAM-SHA-256 authentication is enabled
• Document 2 is complete on the primary: pgBackRest 2.58 is configured with stanza main, the repository is at /var/lib/pgbackrest with AES-256-CBC encryption, WAL archiving is active, and at least one full backup exists
• Document 3 is complete: both standbys are streaming from the primary via replication slots (standby1_slot, standby2_slot) with application_name = standby1 / standby2
• Data directory on all servers: /var/lib/pgsql/18/data
• The postgres OS user exists on all three servers
• SELinux is in enforcing mode on all servers
• Network connectivity between the primary and standby 1 on port 22/tcp (SSH) is required (verified or established in the prerequisites section below)
• Root access or sudo privileges are available on all servers
• The reader will substitute placeholders with actual values

Placeholder definitions:

• {PRIMARY_HOST} -- hostname of the primary server (e.g., pgprimary.example.com)
• {PRIMARY_IP} -- IP address of the primary server (e.g., 10.0.1.10)
• {STANDBY1_HOST} -- hostname of the first standby server (e.g., pgstandby1.example.com)
• {STANDBY1_IP} -- IP address of the first standby server (e.g., 10.0.1.11)
• {STANDBY2_HOST} -- hostname of the second standby server (e.g., pgstandby2.example.com)
• {STANDBY2_IP} -- IP address of the second standby server (e.g., 10.0.1.12)
• {ENCRYPTION_PASSPHRASE} -- the repository encryption passphrase configured in Document 2 (generated with openssl rand -base64 48)

Prerequisites

Automatic setup

No additional packages are required on the primary. The primary already has pgBackRest installed and configured from Document 2.

Manual setup

No manual setup alternative exists for this procedure.

Additional setup

1. Open the firewall on standby 1 for SSH from the primary

If SSH (port 22) is not already open on the standby, add a firewall rule.

Run on standby 1 ({STANDBY1_HOST}):

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="{PRIMARY_IP}" service name="ssh" accept'
sudo firewall-cmd --reload

Expected output:

success
success

Verify:

sudo firewall-cmd --list-rich-rules

Expected output:

rule family="ipv4" source address="{PRIMARY_IP}" service name="ssh" accept

If SSH is already open to all hosts (the default on most Oracle Linux 9 installations), this step is unnecessary. Verify with sudo firewall-cmd --list-services -- if ssh appears in the output, port 22 is already open.

Configuration

Step 1: Configure passwordless SSH from primary to standby 1

All commands in this step run on the primary ({PRIMARY_HOST}) unless otherwise noted.

Generate an Ed25519 SSH key pair for the postgres user. Skip this step if /var/lib/pgsql/.ssh/id_ed25519 already exists.

sudo -u postgres ssh-keygen -t ed25519 -f /var/lib/pgsql/.ssh/id_ed25519 -N ""

Expected output:

Generating public/private ed25519 key pair.
Created directory '/var/lib/pgsql/.ssh'.
Your identification has been saved in /var/lib/pgsql/.ssh/id_ed25519
Your public key has been saved in /var/lib/pgsql/.ssh/id_ed25519.pub
The key fingerprint is:
SHA256:... postgres@{PRIMARY_HOST}

If the key already exists, the command will prompt to overwrite. Press n and skip to the ssh-copy-id step.

Copy the public key to the standby:

sudo -u postgres ssh-copy-id -i /var/lib/pgsql/.ssh/id_ed25519.pub postgres@{STANDBY1_HOST}

Expected output:

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_ed25519.pub"
...
Number of key(s) added: 1

Enter the postgres user's password on the standby when prompted. If password authentication is not configured for the postgres user on the standby, manually append the contents of /var/lib/pgsql/.ssh/id_ed25519.pub (from the primary) to /var/lib/pgsql/.ssh/authorized_keys on the standby.

Set the SELinux context for the SSH directory on the primary:

sudo semanage fcontext -a -t ssh_home_t "/var/lib/pgsql/.ssh(/.*)?"
sudo restorecon -Rv /var/lib/pgsql/.ssh

Expected output:

Relabeled /var/lib/pgsql/.ssh from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:ssh_home_t:s0
Relabeled /var/lib/pgsql/.ssh/id_ed25519 from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:ssh_home_t:s0
Relabeled /var/lib/pgsql/.ssh/id_ed25519.pub from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:ssh_home_t:s0

Run the same SELinux context command on standby 1 ({STANDBY1_HOST}):

sudo semanage fcontext -a -t ssh_home_t "/var/lib/pgsql/.ssh(/.*)?"
sudo restorecon -Rv /var/lib/pgsql/.ssh

Expected output:

Relabeled /var/lib/pgsql/.ssh from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:ssh_home_t:s0
Relabeled /var/lib/pgsql/.ssh/authorized_keys from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:ssh_home_t:s0

Verify passwordless SSH from the primary:

sudo -u postgres ssh postgres@{STANDBY1_HOST} 'echo SSH connection successful'

Expected output:

SSH connection successful

If this is the first SSH connection to the standby, you will be prompted to accept the host key. Type yes to continue.

Step 2: Install and configure pgBackRest on standby 1

All commands in this step run on standby 1 ({STANDBY1_HOST}).

Install pgBackRest from the PGDG 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!

Create the log directory:

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

Verify:

ls -ld /var/log/pgbackrest

Expected output:

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

Create a minimal pgBackRest configuration file on the standby. The standby only needs to define the local PostgreSQL data directory so that pgBackRest can locate it when the primary connects via SSH.

sudo mkdir -p /etc/pgbackrest
sudo tee /etc/pgbackrest/pgbackrest.conf > /dev/null <<'EOF'
[main]
pg1-path=/var/lib/pgsql/18/data
EOF
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

Verify:

cat /etc/pgbackrest/pgbackrest.conf

Expected output:

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

Verify the pgBackRest binary is available:

pgbackrest version

Expected output:

pgBackRest 2.58.0

Step 3: Update pgbackrest.conf on the primary

All commands in this step run on the primary ({PRIMARY_HOST}).

Edit the pgBackRest configuration file to add the backup-standby option and the standby host definition.

sudo -u postgres vi /etc/pgbackrest/pgbackrest.conf

The complete updated configuration file:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass={ENCRYPTION_PASSPHRASE}
repo1-block=y
repo1-bundle=y
compress-type=lz4
start-fast=y
log-level-console=info
log-level-file=detail
delta=y
process-max=2
backup-standby=y

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

[main]
pg1-path=/var/lib/pgsql/18/data
pg2-host={STANDBY1_HOST}
pg2-path=/var/lib/pgsql/18/data

The three new options compared to the Document 2 configuration:

pg1-path defines the local PostgreSQL instance (the primary). pg2-host and pg2-path define a remote standby. pgBackRest uses SSH to connect to pg2-host and read data files from pg2-path.

The backup-standby option accepts three values: y (require standby), n (primary only, the default), and prefer (use standby if available, fall back to primary). This document uses y for explicitness -- if the standby is unavailable, the backup will fail rather than silently falling back to the primary. If your environment requires fallback behavior, use prefer instead.

Verify the configuration file:

sudo -u postgres cat /etc/pgbackrest/pgbackrest.conf

Expected output:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=(your passphrase)
repo1-block=y
repo1-bundle=y
compress-type=lz4
start-fast=y
log-level-console=info
log-level-file=detail
delta=y
process-max=2
backup-standby=y

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

[main]
pg1-path=/var/lib/pgsql/18/data
pg2-host={STANDBY1_HOST}
pg2-path=/var/lib/pgsql/18/data

Step 4: Verify the configuration

Run on the primary ({PRIMARY_HOST}).

The check command validates that pgBackRest can communicate with both the local primary and the remote standby, 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 repo1 configuration (standby)
... INFO: check command end: completed successfully ...

The output must show both (primary) and (standby) checks passing. If the standby check fails, see the Troubleshooting section.

Step 5: Run a full backup from the standby

Run on the primary ({PRIMARY_HOST}).

Execute a full backup with detailed console logging to confirm data files are sourced from the standby.

sudo -u postgres pgbackrest --stanza=main --type=full --log-level-console=detail backup

Expected output (key lines):

... INFO: backup command begin 2.58.0: ...
... DETAIL: backup file {STANDBY1_HOST}:/var/lib/pgsql/18/data/base/... (standby)
... DETAIL: backup file {STANDBY1_HOST}:/var/lib/pgsql/18/data/base/... (standby)
... DETAIL: backup file {PRIMARY_HOST}:/var/lib/pgsql/18/data/pg_control (primary)
... INFO: new backup label = ...F
... INFO: full backup size = ...
... INFO: backup command end: completed successfully ...

The DETAIL lines show (standby) for data files and (primary) for pg_control and WAL-related files. This confirms the backup is reading data files from the standby as intended.

Step 6: Verify the backup

Run on the primary ({PRIMARY_HOST}).

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: ...

The new full backup appears in the list with current timestamps. The stanza status is ok.

Step 7: Cron schedule

No changes are needed to the cron schedule from Document 2. The existing cron entries already execute pgbackrest --type=full --stanza=main backup and pgbackrest --type=diff --stanza=main backup. Because backup-standby=y is set in the [global] section of the configuration file, all backups (whether run manually or by cron) will automatically source data files from the standby.

Verify the existing cron entries are in place:

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

Optional: Add Standby 2 as a Second Backup Source

If standby 1 becomes unavailable during a backup, pgBackRest will fail (because backup-standby=y requires a standby). Adding standby 2 as a third pg host provides failover -- pgBackRest will use the first available standby.

Step 1: Configure SSH to standby 2

Run on the primary ({PRIMARY_HOST}):

sudo -u postgres ssh-copy-id -i /var/lib/pgsql/.ssh/id_ed25519.pub postgres@{STANDBY2_HOST}

Expected output:

Number of key(s) added: 1

Verify:

sudo -u postgres ssh postgres@{STANDBY2_HOST} 'echo SSH connection successful'

Expected output:

SSH connection successful

Step 2: Install pgBackRest on standby 2

Run on standby 2 ({STANDBY2_HOST}):

sudo dnf install -y pgbackrest

Expected output:

...
Installed:
  pgbackrest-...
Complete!

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

Create the minimal pgBackRest configuration:

sudo mkdir -p /etc/pgbackrest
sudo tee /etc/pgbackrest/pgbackrest.conf > /dev/null <<'EOF'
[main]
pg1-path=/var/lib/pgsql/18/data
EOF
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

Verify the configuration file:

cat /etc/pgbackrest/pgbackrest.conf

Expected output:

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

Set the SELinux context for the SSH directory on standby 2:

sudo semanage fcontext -a -t ssh_home_t "/var/lib/pgsql/.ssh(/.*)?"
sudo restorecon -Rv /var/lib/pgsql/.ssh

Expected output:

Relabeled /var/lib/pgsql/.ssh from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:ssh_home_t:s0
Relabeled /var/lib/pgsql/.ssh/authorized_keys from unconfined_u:object_r:postgresql_db_t:s0 to unconfined_u:object_r:ssh_home_t:s0

Step 3: Update pgbackrest.conf on the primary

Run on the primary ({PRIMARY_HOST}):

Add pg3-host and pg3-path to the [main] stanza section:

sudo -u postgres vi /etc/pgbackrest/pgbackrest.conf

The updated [main] section:

[main]
pg1-path=/var/lib/pgsql/18/data
pg2-host={STANDBY1_HOST}
pg2-path=/var/lib/pgsql/18/data
pg3-host={STANDBY2_HOST}
pg3-path=/var/lib/pgsql/18/data

All other sections remain unchanged.

Step 4: Verify the configuration

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 repo1 configuration (standby)
... INFO: check command end: completed successfully ...

pgBackRest automatically selects the first available standby when multiple standbys are configured. If standby 1 is down, it will use standby 2.

Validation

Quick check

Run on the primary ({PRIMARY_HOST}):

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

Expected output:

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

Both (primary) and (standby) checks must pass.

Full validation

1. Run a full backup with detailed logging and confirm standby is used

Run on the primary:

sudo -u postgres pgbackrest --stanza=main --type=full --log-level-console=detail backup

Expected output: The DETAIL log lines show (standby) for data file backups and (primary) for pg_control and WAL files.

2. Verify backup info shows the new backup

Run on the primary:

sudo -u postgres pgbackrest info

Expected output: The main stanza shows status ok, cipher aes-256-cbc, and the new full backup with current timestamps and sizes.

3. Verify SSH connectivity to the standby

Run on the primary:

sudo -u postgres ssh postgres@{STANDBY1_HOST} 'pgbackrest version'

Expected output:

pgBackRest 2.58.0

4. Verify the standby is streaming

Run on the primary:

sudo -u postgres psql -c "SELECT application_name, state FROM pg_stat_replication WHERE application_name = 'standby1';"

Expected output:

 application_name |   state
------------------+-----------
 standby1         | streaming
(1 row)

The standby must be in streaming state. pgBackRest requires the standby to be actively streaming to use it as a backup source.

Troubleshooting

References

• pgBackRest User Guide - RHEL
• pgBackRest Configuration Reference
• pgBackRest Command Reference