pgBackRest setup¶

pgBackRest is a backup tool used to perform PostgreSQL database backup, archiving, restoration, and point-in-time recovery. While it can be used for local backups, this procedure shows how to deploy a pgBackRest server running on a dedicated host and how to 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.

Configure backup server¶

To make things easier when working with some templates, run the commands below as the root user. Run the following command to switch to the root user:

$ sudo su -

Install pgBackRest¶

Enable the repository with percona-release
```
$ percona-release setup ppg-12       
```

Install pgBackRest package

On Debian andUbuntu On RHEL/derivatives

$ apt install percona-pgbackrest

$ yum install percona-pgbackrest

Create the configuration file¶

Create environment variables to simplify the config file creation:

export SRV_NAME="bkp-srv"
export NODE1_NAME="node-1"
export NODE2_NAME="node-2"
export NODE3_NAME="node-3"
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:
```
$ mkdir -p /var/lib/pgbackrest
$ chmod 750 /var/lib/pgbackrest
$ 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:

$ cp pgbackrest.conf pgbackrest.conf.bak

Then use the following command to create a basic configuration file using the environment variables we created in a previous step:

Debian/UbuntuRHEL/derivatives

cat <<EOF > pgbackrest.conf
[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/12/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/12/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/12/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
EOF

cat <<EOF > pgbackrest.conf
[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/pgsql/12/data
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/pgsql/12/data
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/pgsql/12/data
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
EOF

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¶

Create the folder to store the certificates:
```
$ mkdir -p ${CA_PATH}
```

Create the certificates and keys

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

Create the certificate for the backup and the PostgreSQL servers

$ for node in ${SRV_NAME} ${NODE1_NAME} ${NODE2_NAME} ${NODE3_NAME}
do
openssl req -new -nodes -out ${CA_PATH}/$node.csr -keyout ${CA_PATH}/$node.key -subj "/CN=$node";
done

Sign the certificates with the root-ca key

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

Remove temporary files, set ownership of the remaining files to the postgres user, and restrict their access:
```
$ rm -f ${CA_PATH}/*.csr
$ chown postgres:postgres -R ${CA_PATH}
$ 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

Reload, start, and enable the service

$ systemctl daemon-reload
$ systemctl start pgbackrest.service
$ systemctl enable pgbackrest.service

Configure database servers¶

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

Install pgBackRest package

On Debian/Ubuntu On RHEL/derivatives

$ apt install percona-pgbackrest

$ 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:
```
$ 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:
```
$ scp ${SRV_NAME}:${CA_PATH}/{$NODE_NAME.crt,$NODE_NAME.key,ca.crt} ${CA_PATH}/
$ chown postgres:postgres -R ${CA_PATH}
$ chmod 0600 ${CA_PATH}/* 
```

Edit or create the configuration file which, as explained above, can be either at the /etc/pgbackrest/pgbackrest.conf or /etc/pgbackrest.conf path:

Debian/UbuntuRHEL/derivatives

pgbackrest.conf

cat <<EOF > pgbackrest.conf
[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/12/main
EOF

pgbackrest.conf

cat <<EOF > pgbackrest.conf
[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/12/data
EOF

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, start, and enable the service

$ systemctl daemon-reload
$ systemctl start pgbackrest
$ systemctl enable pgbackrest

The pgBackRest daemon listens on port 8432 by default:

$ netstat -taunp
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

Debian/UbuntuRHEL/derivatives

/etc/patroni/patroni.yml

postgresql:
  (...)
  parameters:
    (...)
    archive_command: pgbackrest --stanza=cluster_1 archive-push /var/lib/postgresql/12/main/pg_wal/%f
    (...)
  recovery_conf:
    (...)
    restore_command: pgbackrest --config=/etc/pgbackrest.conf --stanza=cluster_1 archive-get %f %p
    (...)

/etc/patroni/patroni.yml

postgresql:
  (...)
  parameters:
    archive_command: pgbackrest --stanza=cluster_1 archive-push /var/lib/pgsql/12/data/pg_wal/%f
    (...)
  recovery_conf:
    restore_command: pgbackrest --config=/etc/pgbackrest.conf --stanza=cluster_1 archive-get %f %p
    (...)

Reload the changed configurations:

$ patronictl -c /etc/patroni/postgresql.yml reload

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

pgBackRest setup¶

Configure backup server¶

Install pgBackRest¶

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¶