pgBackRest setup

· 6 min read · PostgreSQL

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. For our demonestration we will create a virtual machine like the previous tutorial.

Create a VM as backup server

Please refer to previous tutorial if you need to install Virt-Manager and setup Ubuntu server. For this tutorial I will consider that you already have completed setting up the VM. Also please consider increasing or decreasing storage size if needed.

Configure backup server

Like the previous tutorial please ssh to your VM for convenience.

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:

1

sudo su -

Install pgBackRest

  1. Enable the repository with percona-release

    1

     percona-release setup ppg-15

  2. Install pgBackRest package

    1

     apt install percona-pgbackrest

Create the configuration file

  1. Create environment variables to simplify the config file creation:

    1
2
3
4
5

     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"

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

    1
2
3

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

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

    1

     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:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

       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 

   ### s and nodes ###
   [cluster_1]
   pg1-host=${NODE1_NAME}
   pg1-host-port=8432
   pg1-port=5432
   pg1-path=/var/lib/postgresql/15/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/15/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/15/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

    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

  1. Create the folder to store the certificates:

    1

     mkdir -p ${CA_PATH}

  2. Create the certificates and keys

    1

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

  3. Create the certificate for the backup and the PostgreSQL servers

    1
2
3
4

     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

  4. Sign the certificates with the root-ca key

    1
2
3
4

     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

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

    1
2
3

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

Create the pgbackrest daemon service

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

    ```ini title=”/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 ```

  2. Reload, start, and enable the service

    1
2
3

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

Configure database servers

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

  1. Install pgBackRest package

    1

       apt install percona-pgbackrest

  2. Export environment variables to simplify the config file creation:

    1
2
3

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

  3. Create the certificates folder:

    1

     mkdir -p ${CA_PATH}

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

    1
2
3

     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}/*

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

    ```ini title=”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/15/main EOF ```

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

    ```ini title=”/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 ```

  7. Reload, start, and enable the service

    1
2
3

     systemctl daemon-reload
 systemctl start pgbackrest
 systemctl enable pgbackrest

    The pgBackRest daemon listens on port 8432 by default:

    1
2
3
4
5

     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

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

    1

     patronictl -c /etc/patroni/patroni.yml edit-config

    1
2
3
4
5
6
7
8
9

     postgresql:
   parameters:
     # Add archive_mode parameter here
     archive_mode: on
     archive_command: pgbackrest --stanza=cluster_1 archive-push /var/lib/postgresql/15/main/pg_wal/%f
     ...
   recovery_conf:
     restore_command: pgbackrest --config=/etc/pgbackrest.conf --stanza=cluster_1 archive-get %f %p
     ...

    Reload the changed configurations:

    1

     patronictl -c /etc/patroni/patroni.yml reload cluster_1

    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:

  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.

    1

     sudo -iu postgres pgbackrest --stanza=cluster_1 stanza-create

  2. Create a full backup

    1

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

  3. Check backup info

    1

     sudo -iu postgres pgbackrest --stanza=cluster_1 info

  4. Expire (remove) a backup:

    1

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

Test PostgreSQL cluster{.md-button}

Tags: backup pgbackrest postgresql s3 ubuntu

Related Posts