Skip to content

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-15       
    
  2. Install pgBackRest package

    $ 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 pgBackRest configuration file location 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

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.