pgBackRest setup

pgBackRest :octicons-link-external-16: is a tool used to perform PostgreSQL database backups, archiving, restoration, and point-in-time recovery.

In this solution, a pgBackRest server on a dedicated host :octicons-link-external-16: is deployed. pgBackRest is also installed and configured on the PostgreSQL servers to perform backups and manage WAL archiving.

Preparation

Make sure to complete the initial setup steps.

Install pgBackRest

Install pgBackRest on all nodes: node1, node2, node3, and backup.

=== ":material-debian: On Debian/Ubuntu"

```{.bash data-prompt="$"}
$ sudo apt install percona-pgbackrest
```

=== ":material-redhat: On RHEL/derivatives"

```{.bash data-prompt="$"}
$ 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="backup"
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.

Go to 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:

=== ":material-debian: On Debian/Ubuntu"

 ```
 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=4  # This depends on the number of CPU resources your server has. The recommended value should equal or be less than the number of CPUs. While more processes can speed up backups, they will also consume additional system resources.
 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/{{pgversion}}/main
 pg1-host-type=tls
 pg1-host-cert-file=${CA_PATH}/${NODE1_NAME}.crt
 pg1-host-key-file=${CA_PATH}/${NODE1_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/{{pgversion}}/main
 pg2-host-type=tls
 pg2-host-cert-file=${CA_PATH}/${NODE2_NAME}.crt
 pg2-host-key-file=${CA_PATH}/${NODE2_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/{{pgversion}}/main
 pg3-host-type=tls
 pg3-host-cert-file=${CA_PATH}/${NODE3_NAME}.crt
 pg3-host-key-file=${CA_PATH}/${NODE3_NAME}.key
 pg3-host-ca-file=${CA_PATH}/ca.crt
 pg3-socket-path=/var/run/postgresql
 
 " | sudo tee /etc/pgbackrest.conf
 ```

=== ":material-redhat: 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=4  # This depends on the number of CPU resources your server has. The recommended value should equal or be less than the number of CPUs. While more processes can speed up backups, they will also consume additional system resources.
 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/{{pgversion}}/main
 pg1-host-type=tls
 pg1-host-cert-file=${CA_PATH}/${NODE1_NAME}.crt
 pg1-host-key-file=${CA_PATH}/${NODE1_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/{{pgversion}}/main
 pg2-host-type=tls
 pg2-host-cert-file=${CA_PATH}/${NODE2_NAME}.crt
 pg2-host-key-file=${CA_PATH}/${NODE2_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/{{pgversion}}/main
 pg3-host-type=tls
 pg3-host-cert-file=${CA_PATH}/${NODE3_NAME}.crt
 pg3-host-key-file=${CA_PATH}/${NODE3_NAME}.key
 pg3-host-ca-file=${CA_PATH}/ca.crt
 pg3-socket-path=/var/run/postgresql
 
 " | sudo tee /etc/pgbackrest.conf
 ```

Where:

pgX-host specifies the hostname of the PostgreSQL node
pgX-host-port specifies the port used by the pgBackRest server daemon on that node. The default pgBackRest server port is 8432
pgX-port specifies the PostgreSQL server port (default 5432)
pgX-path specifies the PostgreSQL data directory on the node

The pgX prefix is repeated for each PostgreSQL node in the cluster (for example pg1, pg2, pg3).

The numbering (pg1, pg2, pg3) represents individual PostgreSQL nodes defined in the cluster stanza.

!!! 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

[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

=== ":material-debian: On Debian/Ubuntu"

 ```{.bash data-prompt="$"}
 $ sudo apt install percona-pgbackrest
 ```

=== ":material-redhat: On RHEL/derivatives"

 ```{.bash data-prompt="$"}
 $ sudo yum install percona-pgbackrest
 ```

Export environment variables to simplify the config file creation:

$ export NODE_NAME=`hostname -f`
$ export SRV_NAME="backup"
$ 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:
```
$ 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:

=== ":material-debian: On Debian/Ubuntu"

 ```ini title="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=6
 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/{{pgversion}}/main
 " | sudo tee /etc/pgbackrest.conf
 ```

=== ":material-redhat: On RHEL/derivatives"

 ```ini title="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=6
 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/{{pgversion}}/data
 " | sudo tee /etc/pgbackrest.conf
 ```

Create the pgbackrest systemd unit file at the path /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 systemd and 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'

??? example "Sample output"

 ```{text .no-copy}
 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:

postgresql:
  parameters:
    archive_command: pgbackrest --stanza=cluster_1 archive-push /var/lib/postgresql/{{pgversion}}/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_size: 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 restart 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>

Next steps

Configure HAProxy :material-arrow-right:{.md-button}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

Next steps

FilesExpand file tree

pgbackrest.md

Latest commit

History

pgbackrest.md

File metadata and controls

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

Next steps

Create the `pgbackrest` daemon service