In my previous post about pgBackRest, we saw how to install and setup pgBackRest and make a backup of a PostgreSQL database with it. It was a very basic single server setup,  only intended to get the hang of the tool. Such setups are not used in a production environment, as it is not recommended (or rather does not serve the purpose) to perform the backup on the same server where the database is running. So: let’s get familiar with how remote backup servers are set up with pgBackRest, and how a full and incremental backup is performed from the backup server and restored on the database server.

We need two servers. Let’s call ours:

  1. pgbackup
  2. db1

 

Installing pgbackrest:

We need to install pgBackRest on the database and the backup server. Make sure you install the same version on both.

 

For the database server, please follow the installation steps from my previous post. The steps are slightly different for the backup server, since it is a better practice to create a separate user to own the pgBackRest repository.

 

Create a pgbackrest user on the backup server

 


sudo adduser --disabled-password --gecos "" pgbackrest

 

Install required Perl package and pgBackRest from a package or manually on pgbackup as below

 

sudo apt-get install libdbd-pg-perl
sudo scp BUILD_HOST:/root/pgbackrest-release-2.14/src/pgbackrest /usr/bin/
sudo chmod 755 /usr/bin/pgbackrest

 

Create pgBackRest configuration files, directories and repository on pgbackup

 


sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf

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

 

Now we are ready to proceed with enabling communication between the database and the backup server. For that, pgBackRest requires a passwordless SSH connection.

We can do that by generating an SSH authentication key file using the

ssh-keygen

command.

 

On the pgbackup server as pgbackrest user:

sudo -u pgbackrest mkdir -m 750 /home/pgbackrest/.ssh
sudo -u pgbackrest ssh-keygen -f /home/pgbackrest/.ssh/id_rsa \
                   -t rsa -b 4096 -N ""

On db1 as postgres user:

sudo -u postgres mkdir -m 750 -p /var/lib/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa \
                 -t rsa -b 4096 -N ""

Exchange the public keys generated between the servers

On pgbackup:

cat ~/.ssh/id_rsa.pub | ssh postgres@db1 "cat >> ~/.ssh/authorized_keys"

 

On db1:

cat ~/.ssh/id_rsa.pub | ssh pgbackrest@pgbackup "cat >> ~/.ssh/authorized_keys" 

 

Test the passwordless connection as follows:


root@pgbackup:~$ sudo -u pgbackrest ssh postgres@db1

root@db1:~$ sudo -u postgres ssh pgbackrest@repository

 

You can find more details about passwordless SSH connection here.

 

Configuration

On the pgbackup server, configure the pgBackRest configuration file with the database host and path, along with the repository path where the backups will be stored. We have added the database host as ‘pg1-host‘ so that we can add more database hosts later as ‘pg2-host‘ and so on. The same goes for pg1-path.

cat /etc/pgbackrest/pgbackrest.conf
[demo]
pg1-host=db1
pg1-path=/var/lib/postgresql/10/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y

The Start Fast Option (--start-fast) has been used to force a checkpoint to start the backup quickly. Otherwise, the backup will start after the next regular checkpoint.

 

On server db1, configure the pgBackRest configuration file with the database path and backup host as follows:

cat /etc/pgbackrest/pgbackrest.conf
[demo]
pg1-path=/var/lib/postgresql/10/main

[global]
log-level-file=detail
repo1-host=pgbackup

 

On db1, update postgresql.conf to have the following changes:

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
listen_addresses = '*'
log_line_prefix = ''
max_wal_senders = 3
wal_level = replica

 

Now restart PostgreSQL to reflect the configuration changes.

 

Create a stanza on the pgbackup server and check if it’s working. Getting no result means the stanza has been created successfully.


sudo -u pgbackrest pgbackrest --stanza=demo stanza-create
sudo -u pgbackrest pgbackrest --stanza=demo check

 

Check if the stanza configuration is correct on db1:

 
sudo -u postgres pgbackrest --stanza=demo check

Remote Backup

With the setup ready, let’s take our first remote backup from pgbackup. The default backup is incremental, but the first backup is always a full backup.

 


sudo -u pgbackrest pgbackrest --stanza=demo backup
sudo -u pgbackrest pgbackrest --stanza=demo info
stanza: demo
    status: ok
    cipher: none

db (current)
    wal archive min/max (10-1): 000000010000000000000007/000000010000000000000008

    full backup: 20190726-133657F
        timestamp start/stop: 2019-07-26 13:36:57 / 2019-07-26 13:37:07
        wal start/stop: 000000010000000000000007 / 000000010000000000000007
        database size: 22.5MB, backup size: 22.5MB
        repository size: 2.6MB, repository backup size: 2.6MB

 

You will find directories and files being created in the backup location.

 

 

Restore

Taking a full backup was easy. Let’s see some examples of incremental backup and recovery with a target:

 

Create a test database on db1:

postgres=# create database test;
CREATE DATABASE
postgres=# \l
List of databases
    Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres  postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

 

Make another backup. By default, this will be incremental since it’s the second backup and we are not specifying the type:

 

sudo -u pgbackrest pgbackrest --stanza=demo backup

 

To restore, stop PostgreSQL on db1 and run pgbackrest with the restore command:

 

sudo service postgresql stop
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo service postgresql start

 

If you check the database now, you will not find the test database. This is because the backup was restored from the first full backup.


postgres=# \l
List of databases
   Name    |  Owner   | Encoding | Collate     | Ctype       | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres  | UTF8    | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres  | UTF8    | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres
 template1 | postgres  | UTF8    | en_US.UTF-8 | en_US.UTF-8 | =c/postgres postgres=CTc/postgres
(3 rows)

 

To restore the data up to the incremental backup, run the restore command with recovery_target in --recovery-option.

sudo service postgresql stop
sudo -u postgres pgbackrest --stanza=demo --delta restore \
                            --recovery-option=recovery_target=immediate
sudo service postgresql start

 

Let’s check the database now:

 

 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

I hope this exercise could help to get you started with remote backup and pgBackRest! To learn more commands, please visit the pgBackRest official site here.

 

Stay tuned for more tutorials and exercises with pgBackRest. If you are not sure how to use PostgreSQL efficiently or how to setup your systems in general consider checking out our support services.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.