pgBackRest setup

pgBackRest is the backup tool used to perform Postgres database backup, restoration, and point-in-time recovery. It is a server-client application, where the server runs on a dedicated host and a client runs on every PostgreSQL node.

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

Install pgBackRest

1. Enable the repository with percona-release

   $ sudo percona-release setup ppg-11       
   

2. Install pgBackRest package

   Debian/UbuntuRHEL/derivatives

   $ sudo apt install percona-pgbackrest
   

   $ sudo yum install percona-pgbackrest
   

Create the configuration file

1. 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"
   

2. Create the pgBackRest repository

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

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

3. The default location of the pgBackRest configuration file is /etc/pgbackrest/pgbackrest.conf. If it does not exist, then /etc/pgbackrest.conf is used next. Edit the pgbackrest.conf file to include the following configuration:

   [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=/pg_ha/certs/${SRV_NAME}.crt
   tls-server-key-file=/pg_ha/certs/${SRV_NAME}.key
   tls-server-ca-file=/pg_ha/certs/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/11/
   pg1-host-type=tls
   pg1-host-cert-file=/pg_ha/certs/${SRV_NAME}.crt
   pg1-host-key-file=/pg_ha/certs/${SRV_NAME}.key
   pg1-host-ca-file=/pg_ha/certs/ca.crt
   pg1-socket-path=/var/run/postgresql 
   
   
   pg2-host=${NODE2_NAME}
   pg2-host-port=8432
   pg2-port=5432
   pg2-path=/var/lib/postgresql/11/
   pg2-host-type=tls
   pg2-host-cert-file=/pg_ha/certs/${SRV_NAME}.crt
   pg2-host-key-file=/pg_ha/certs/${SRV_NAME}.key
   pg2-host-ca-file=/pg_ha/certs/ca.crt
   pg2-socket-path=/var/run/postgresql 
   
   pg3-host=${NODE3_NAME}
   pg3-host-port=8432
   pg3-port=5432
   pg3-path=/var/lib/postgresql/11/
   pg3-host-type=tls
   pg3-host-cert-file=/pg_ha/certs/${SRV_NAME}.crt
   pg3-host-key-file=/pg_ha/certs/${SRV_NAME}.key
   pg3-host-ca-file=/pg_ha/certs/ca.crt
   pg3-socket-path=/var/run/postgresql
   

4. 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
   StartLimitIntervalSec=0
   
   [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
   

Create the certificate files

1. Create the folder where to store the certificates. For example, /pg_ha/certs

2. Define the variable for the certificates path:

   export CA_PATH="/pg_ha/certs"
   

3. Create the certificates and keys

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

4. Create the certificate for the backup server

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

5. Create the certificates for each node: node1, node2, node3

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

6. Sign the certificates with the root-ca key

   $ sudo -iu postgres 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 -iu postgres 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 -iu postgres 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 -iu postgres 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
   

7. Remove temporary files

   $ rm ${CA_PATH}/*.csr
   

8. Reload, enable, and start the service

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

Configure database servers

Run the following command on node1, node2 and node3.

1. Create the certificates folder. For example, /pg_ha/certs

   $ sudo mkdir -p /pg_ha/certs
   

2. Export environment variables to simplify config file creation

   export NODE_NAME=`hostname -f`
   

3. Create the configuration file. The default path is /etc/pgbackrest.conf

   /etc/pgbackrest.conf

   [global]
   repo1-host=bkp-srv
   repo1-host-user=postgres
   repo1-host-type=tls
   repo1-host-cert-file=/pg_ha/certs/${NODE_NAME}.crt
   repo1-host-key-file=/pg_ha/certs/${NODE_NAME}.key
   repo1-host-ca-file=/pg_ha/certs/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=/pg_ha/certs/${NODE_NAME}.crt
   tls-server-key-file=/pg_ha/certs/${NODE_NAME}.key
   tls-server-ca-file=/pg_ha/certs/ca.crt
   tls-server-auth=bkp-srv=cluster_1
   
   [cluster_1]
   pg1-path=/var/lib/postgresql/11
   

4. 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
   StartLimitIntervalSec=0
   
   [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
   

5. Reload, enable, and start the service

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

6. Change Patroni configuration to use pgBackRest. Run this command on one node only, for example, on node1. Edit the /etc/patroni/patroni.yml file :

   /etc/patroni/patroni.yml

   loop_wait: 10
   maximum_lag_on_failover: 1048576
   postgresql:
     parameters:
       archive_command: pgbackrest --stanza=cluster_1 archive-push "/var/lib/postgresql/15/main/pg_wal/%f"
       archive_mode: true
       archive_timeout: 1800s
       hot_standby: true
       logging_collector: 'on'
       max_replication_slots: 10
       max_wal_senders: 5
       wal_keep_size: 4096
       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
   

Create backups

Run the following commands on the backup server

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

2. Create a full backup

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

3. Create an incremental backup

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

4. Check backup info

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

5. Expire (remove) a backup. Be careful with removal, because removing a full backup also removes dependent incremental backups

   $ sudo -iu postgres pgbackrest --stanza=cluster_1 expire --set=20230617-021338F
   

Test PostgreSQL cluster

Contact Us

For free technical help, visit the Percona Community Forum.

To report bugs or submit feature requests, open a JIRA ticket.

For paid support and managed or consulting services , contact Percona Sales.