pgBackRest setup¶

pgBackRest is a backup tool used to perform PostgreSQL database backup, archiving, restoration, and point-in-time recovery.

In our solution we deploy a pgBackRest server on a dedicated host and also deploy pgBackRest on the PostgreSQL servers. Them we configure PostgreSQL servers to use it for backups and archiving.

You also need a backup storage to store the backups. It can either be a remote storage such as AWS S3, S3-compatible storages or Azure blob storage, or a filesystem-based one.

Preparation¶

Make sure to complete the initial setup steps.

Install pgBackRest¶

Install pgBackRest on the following nodes: node1, node2, node3, backup

On Debian/Ubuntu On RHEL/derivatives

$ sudo apt install percona-pgbackrest

$ sudo yum install percona-pgbackrest

Configure a backup server¶

Do the following steps on the backup node.

Create the configuration file¶

Create environment variables to simplify the config file creation:

export SRV_NAME="bkp-srv"
export NODE1_NAME="node1"
export NODE2_NAME="node2"
export NODE3_NAME="node3"
export CA_PATH="/etc/ssl/certs/pg_ha"

Create the pgBackRest repository, if necessary

A repository is where pgBackRest stores backups. In this example, the backups will be saved to /var/lib/pgbackrest.

This directory is usually created during pgBackRest’s installation process. If it’s not there already, create it as follows:
```
$ sudo mkdir -p /var/lib/pgbackrest
$ sudo chmod 750 /var/lib/pgbackrest
$ sudo chown postgres:postgres /var/lib/pgbackrest
```
The default pgBackRest configuration file location is /etc/pgbackrest/pgbackrest.conf, but some systems continue to use the old path, /etc/pgbackrest.conf, which remains a valid alternative. If the former is not present in your system, create the latter.

Access the file’s parent directory (either cd /etc/ or cd /etc/pgbackrest/), and make a backup copy of it:
```
$ sudo cp pgbackrest.conf pgbackrest.conf.orig
```

Then use the following command to create a basic configuration file using the environment variables we created in a previous step. This example command adds the configuration file at the path /etc/pgbackrest.conf. Make sure to specify the correct path for the configuration file on your system:

On Debian/Ubuntu On RHEL/derivatives

echo "
[global] 

# Server repo details
repo1-path=/var/lib/pgbackrest 

### Retention ###
#  - repo1-retention-archive-type
#  - If set to full pgBackRest will keep archive logs for the number of full backups defined by repo-retention-archive
repo1-retention-archive-type=full 

# repo1-retention-archive
#  - Number of backups worth of continuous WAL to retain
#  - NOTE: WAL segments required to make a backup consistent are always retained until the backup is expired regardless of how this option is configured
#  - If this value is not set and repo-retention-full-type is count (default), then the archive to expire will default to the repo-retention-full
# repo1-retention-archive=2 

# repo1-retention-full
#  - Full backup retention count/time.
#  - When a full backup expires, all differential and incremental backups associated with the full backup will also expire. 
#  - When the option is not defined a warning will be issued. 
#  - If indefinite retention is desired then set the option to the max value. 
repo1-retention-full=4 

# Server general options
process-max=12
log-level-console=info
#log-level-file=debug
log-level-file=info
start-fast=y
delta=y
backup-standby=y 

########## Server TLS options ##########
tls-server-address=*
tls-server-cert-file=${CA_PATH}/${SRV_NAME}.crt
tls-server-key-file=${CA_PATH}/${SRV_NAME}.key
tls-server-ca-file=${CA_PATH}/ca.crt 

### Auth entry ###
tls-server-auth=${NODE1_NAME}=cluster_1
tls-server-auth=${NODE2_NAME}=cluster_1
tls-server-auth=${NODE3_NAME}=cluster_1 

### Clusters and nodes ###
[cluster_1]
pg1-host=${NODE1_NAME}
pg1-host-port=8432
pg1-port=5432
pg1-path=/var/lib/postgresql/17/main
pg1-host-type=tls
pg1-host-cert-file=${CA_PATH}/${SRV_NAME}.crt
pg1-host-key-file=${CA_PATH}/${SRV_NAME}.key
pg1-host-ca-file=${CA_PATH}/ca.crt
pg1-socket-path=/var/run/postgresql 

pg2-host=${NODE2_NAME}
pg2-host-port=8432
pg2-port=5432
pg2-path=/var/lib/postgresql/17/main
pg2-host-type=tls
pg2-host-cert-file=${CA_PATH}/${SRV_NAME}.crt
pg2-host-key-file=${CA_PATH}/${SRV_NAME}.key
pg2-host-ca-file=${CA_PATH}/ca.crt
pg2-socket-path=/var/run/postgresql 

pg3-host=${NODE3_NAME}
pg3-host-port=8432
pg3-port=5432
pg3-path=/var/lib/postgresql/17/main
pg3-host-type=tls
pg3-host-cert-file=${CA_PATH}/${SRV_NAME}.crt
pg3-host-key-file=${CA_PATH}/${SRV_NAME}.key
pg3-host-ca-file=${CA_PATH}/ca.crt
pg3-socket-path=/var/run/postgresql

" | sudo tee /etc/pgbackrest.conf

echo "
[global] 

# Server repo details
repo1-path=/var/lib/pgbackrest 

### Retention ###
#  - repo1-retention-archive-type
#  - If set to full pgBackRest will keep archive logs for the number of full backups defined by repo-retention-archive
repo1-retention-archive-type=full 

# repo1-retention-archive
#  - Number of backups worth of continuous WAL to retain
#  - NOTE: WAL segments required to make a backup consistent are always retained until the backup is expired regardless of how this option is configured
#  - If this value is not set and repo-retention-full-type is count (default), then the archive to expire will default to the repo-retention-full
# repo1-retention-archive=2 

# repo1-retention-full
#  - Full backup retention count/time.
#  - When a full backup expires, all differential and incremental backups associated with the full backup will also expire. 
#  - When the option is not defined a warning will be issued. 
#  - If indefinite retention is desired then set the option to the max value. 
repo1-retention-full=4 

# Server general options
process-max=12
log-level-console=info
#log-level-file=debug
log-level-file=info
start-fast=y
delta=y
backup-standby=y 

########## Server TLS options ##########
tls-server-address=*
tls-server-cert-file=${CA_PATH}/${SRV_NAME}.crt
tls-server-key-file=${CA_PATH}/${SRV_NAME}.key
tls-server-ca-file=${CA_PATH}/ca.crt 

### Auth entry ###
tls-server-auth=${NODE1_NAME}=cluster_1
tls-server-auth=${NODE2_NAME}=cluster_1
tls-server-auth=${NODE3_NAME}=cluster_1 

### Clusters and nodes ###
[cluster_1]
pg1-host=${NODE1_NAME}
pg1-host-port=8432
pg1-port=5432
pg1-path=/var/lib/postgresql/17/main
pg1-host-type=tls
pg1-host-cert-file=${CA_PATH}/${SRV_NAME}.crt
pg1-host-key-file=${CA_PATH}/${SRV_NAME}.key
pg1-host-ca-file=${CA_PATH}/ca.crt
pg1-socket-path=/var/run/postgresql 

pg2-host=${NODE2_NAME}
pg2-host-port=8432
pg2-port=5432
pg2-path=/var/lib/postgresql/17/main
pg2-host-type=tls
pg2-host-cert-file=${CA_PATH}/${SRV_NAME}.crt
pg2-host-key-file=${CA_PATH}/${SRV_NAME}.key
pg2-host-ca-file=${CA_PATH}/ca.crt
pg2-socket-path=/var/run/postgresql 

pg3-host=${NODE3_NAME}
pg3-host-port=8432
pg3-port=5432
pg3-path=/var/lib/postgresql/17/main
pg3-host-type=tls
pg3-host-cert-file=${CA_PATH}/${SRV_NAME}.crt
pg3-host-key-file=${CA_PATH}/${SRV_NAME}.key
pg3-host-ca-file=${CA_PATH}/ca.crt
pg3-socket-path=/var/run/postgresql

" | sudo tee /etc/pgbackrest.conf

NOTE: The option backup-standby=y above indicates the backups should be taken from a standby server. If you are operating with a primary only, or if your secondaries are not configured with pgBackRest, set this option to n.

Create the certificate files¶

Run the following commands as a root user or with sudo privileges

Create the folder to store the certificates:
```
$ sudo mkdir -p /etc/ssl/certs/pg_ha
```
Create the environment variable to simplify further configuration
```
$ export CA_PATH="/etc/ssl/certs/pg_ha"
```

Create the CA certificates and keys

$ sudo openssl req -new -x509 -days 365 -nodes -out ${CA_PATH}/ca.crt -keyout ${CA_PATH}/ca.key -subj "/CN=root-ca"

Create the certificate and keys for the backup server

$ sudo openssl req -new -nodes -out ${CA_PATH}/${SRV_NAME}.csr -keyout ${CA_PATH}/${SRV_NAME}.key -subj "/CN=${SRV_NAME}"

Create the certificates and keys for each PostgreSQL node

$ sudo openssl req -new -nodes -out ${CA_PATH}/${NODE1_NAME}.csr -keyout ${CA_PATH}/${NODE1_NAME}.key -subj "/CN=${NODE1_NAME}"
$ sudo openssl req -new -nodes -out ${CA_PATH}/${NODE2_NAME}.csr -keyout ${CA_PATH}/${NODE2_NAME}.key -subj "/CN=${NODE2_NAME}"
$ sudo openssl req -new -nodes -out ${CA_PATH}/${NODE3_NAME}.csr -keyout ${CA_PATH}/${NODE3_NAME}.key -subj "/CN=${NODE3_NAME}"

Sign all certificates with the root-ca key

$ sudo openssl x509 -req -in ${CA_PATH}/${SRV_NAME}.csr -days 365 -CA ${CA_PATH}/ca.crt -CAkey ${CA_PATH}/ca.key -CAcreateserial -out ${CA_PATH}/${SRV_NAME}.crt
$ sudo openssl x509 -req -in ${CA_PATH}/${NODE1_NAME}.csr -days 365 -CA ${CA_PATH}/ca.crt -CAkey ${CA_PATH}/ca.key -CAcreateserial -out ${CA_PATH}/${NODE1_NAME}.crt
$ sudo openssl x509 -req -in ${CA_PATH}/${NODE2_NAME}.csr -days 365 -CA ${CA_PATH}/ca.crt -CAkey ${CA_PATH}/ca.key -CAcreateserial -out ${CA_PATH}/${NODE2_NAME}.crt
$ sudo openssl x509 -req -in ${CA_PATH}/${NODE3_NAME}.csr -days 365 -CA ${CA_PATH}/ca.crt -CAkey ${CA_PATH}/ca.key -CAcreateserial -out ${CA_PATH}/${NODE3_NAME}.crt

Remove temporary files, set ownership of the remaining files to the postgres user, and restrict their access:

$ sudo rm -f ${CA_PATH}/*.csr
$ sudo chown postgres:postgres -R ${CA_PATH}
$ sudo chmod 0600 ${CA_PATH}/*

Create the `pgbackrest` daemon service¶

Create the systemd unit file at the path /etc/systemd/system/pgbackrest.service

/etc/systemd/system/pgbackrest.service

[Unit]
Description=pgBackRest Server
After=network.target

[Service]
Type=simple
User=postgres
Restart=always
RestartSec=1
ExecStart=/usr/bin/pgbackrest server
#ExecStartPost=/bin/sleep 3
#ExecStartPost=/bin/bash -c "[ ! -z $MAINPID ]"
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

Make systemd aware of the new service:
```
$ sudo systemctl daemon-reload
```

Enable pgBackRest:

$ sudo systemctl enable --now pgbackrest.service

Configure database servers¶

Run the following commands on node1, node2, and node3.

Install pgBackRest package

On Debian/Ubuntu On RHEL/derivatives

$ sudo apt install percona-pgbackrest

$ sudo yum install percona-pgbackrest

Export environment variables to simplify the config file creation:

$ export NODE_NAME=`hostname -f`
$ export SRV_NAME="bkp-srv"
$ export CA_PATH="/etc/ssl/certs/pg_ha"

Create the certificates folder:
```
$ sudo mkdir -p ${CA_PATH}
```
Copy the .crt, .key certificate files and the ca.crt file from the backup server where they were created to every respective node. Then change the ownership to the postgres user and restrict their access. Use the following commands to achieve this:
```
$ sudo scp ${SRV_NAME}:${CA_PATH}/{$NODE_NAME.crt,$NODE_NAME.key,ca.crt} ${CA_PATH}/
$ sudo chown postgres:postgres -R ${CA_PATH}
$ sudo chmod 0600 ${CA_PATH}/* 
```
Make a copy of the configuration file. The path to it can be either /etc/pgbackrest/pgbackrest.conf or /etc/pgbackrest.conf:

{.bash data-prompt="$"} $ sudo cp pgbackrest.conf pgbackrest.conf.orig

Create the configuration file. This example command adds the configuration file at the path /etc/pgbackrest.conf. Make sure to specify the correct path for the configuration file on your system:

On Debian/Ubuntu On RHEL/derivatives

pgbackrest.conf

echo "
[global]
repo1-host=${SRV_NAME}
repo1-host-user=postgres
repo1-host-type=tls
repo1-host-cert-file=${CA_PATH}/${NODE_NAME}.crt
repo1-host-key-file=${CA_PATH}/${NODE_NAME}.key
repo1-host-ca-file=${CA_PATH}/ca.crt

# general options
process-max=16
log-level-console=info
log-level-file=debug

# tls server options
tls-server-address=*
tls-server-cert-file=${CA_PATH}/${NODE_NAME}.crt
tls-server-key-file=${CA_PATH}/${NODE_NAME}.key
tls-server-ca-file=${CA_PATH}/ca.crt
tls-server-auth=${SRV_NAME}=cluster_1

[cluster_1]
pg1-path=/var/lib/postgresql/17/main
" | sudo tee /etc/pgbackrest.conf

pgbackrest.conf

echo "
[global]
repo1-host=${SRV_NAME}
repo1-host-user=postgres
repo1-host-type=tls
repo1-host-cert-file=${CA_PATH}/${NODE_NAME}.crt
repo1-host-key-file=${CA_PATH}/${NODE_NAME}.key
repo1-host-ca-file=${CA_PATH}/ca.crt

# general options
process-max=16
log-level-console=info
log-level-file=debug

# tls server options
tls-server-address=*
tls-server-cert-file=${CA_PATH}/${NODE_NAME}.crt
tls-server-key-file=${CA_PATH}/${NODE_NAME}.key
tls-server-ca-file=${CA_PATH}/ca.crt
tls-server-auth=${SRV_NAME}=cluster_1

[cluster_1]
pg1-path=/var/lib/pgsql/17/data
" | sudo tee /etc/pgbackrest.conf

Create the pgbackrest systemd unit file at the path /etc/systemd/system/pgbackrest.service

/etc/systemd/system/pgbackrest.service

[Unit]
Description=pgBackRest Server
After=network.target

[Service]
Type=simple
User=postgres
Restart=always
RestartSec=1
ExecStart=/usr/bin/pgbackrest server
#ExecStartPost=/bin/sleep 3
#ExecStartPost=/bin/bash -c "[ ! -z $MAINPID ]"
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

Reload the systemd, the start the service

$ sudo systemctl daemon-reload
$ sudo systemctl enable --now pgbackrest

The pgBackRest daemon listens on port 8432 by default:

$ netstat -taunp | grep '8432'

Sample output

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/systemd           
tcp        0      0 0.0.0.0:8432            0.0.0.0:*               LISTEN      40224/pgbackrest

If you are using Patroni, change its configuration to use pgBackRest for archiving and restoring WAL files. Run this command only on one node, for example, on node1:
```
$ patronictl -c /etc/patroni/patroni.yml edit-config
```
This opens the editor for you.

Change the configuration as follows:

/etc/patroni/patroni.yml

postgresql:
  parameters:
    archive_command: pgbackrest --stanza=cluster_1 archive-push /var/lib/postgresql/17/main/pg_wal/%f
    archive_mode: true
    archive_timeout: 600s
    hot_standby: true
    logging_collector: 'on'
    max_replication_slots: 10
    max_wal_senders: 5
    max_wal_size: 10GB
    wal_keep_segments: 10
    wal_level: logical
    wal_log_hints: true
  recovery_conf:
    recovery_target_timeline: latest
    restore_command: pgbackrest --config=/etc/pgbackrest.conf --stanza=cluster_1 archive-get %f "%p"
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
slots:
  percona_cluster_1:
    type: physical
ttl: 30

Reload the changed configurations. Provide the cluster name or the node name for the following command. In our example we use the cluster_1 cluster name:
```
$ patronictl -c /etc/patroni/patroni.yml reload cluster_1
```
It may take a while to reload the new configuration.

Note: When configuring a PostgreSQL server that is not managed by Patroni to archive/restore WALs from the pgBackRest server, edit the server’s main configuration file directly and adjust the archive_command and restore_command variables as shown above.

Create backups¶

Run the following commands on the backup server:

Create the stanza. A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc.
```
$ sudo -iu postgres pgbackrest --stanza=cluster_1 stanza-create
```

Create a full backup

$ sudo -iu postgres pgbackrest --stanza=cluster_1 --type=full backup

Check backup info

$ sudo -iu postgres pgbackrest --stanza=cluster_1 info

Expire (remove) a backup:

$ sudo -iu postgres pgbackrest --stanza=cluster_1 expire --set=<BACKUP_ID>

Test PostgreSQL cluster

Get expert help¶

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert Join Percona Squad

pgBackRest setup¶

Preparation¶

Install pgBackRest¶

Configure a backup server¶

Create the configuration file¶

Create the certificate files¶

Create the pgbackrest daemon service¶

Configure database servers¶

Create backups¶

Get expert help¶

Create the `pgbackrest` daemon service¶