POSTGRES-pgbackrest remote

 Written by Granthana Biswas

https://www.cybertec-postgresql.com/en/remote-backup-and-restore-with-pgbackrest/

Table of Contents

• Installing pgbackrest
  • Create a pgbackrest user on the backup server
  • Install required Perl package and pgBackRest from a package or manually on pgbackup as below
  • Create pgBackRest configuration files, directories and repository on pgbackup
  • Exchange the public keys generated between the servers
• Configuration
• Remote Backup
• Restore

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

1	sudo adduser --disabled-password --gecos '' pgbackrest

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

1 2 3

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

1 2 3 4 5 6 7 8 9 10

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

1	ssh-keygen

command.

On the pgbackup server as pgbackrest user:

1 2 3

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:

1 2 3

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:

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

On db1:

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

Test the passwordless connection as follows:

1 2 3

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.

1 2 3 4 5 6

$ 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:

1 2 3 4 5 6

$ cat /etc/pgbackrest/pgbackrest.conf [demo] pg1-path=/var/lib/postgresql/10/main   [global] log-level-file=detail

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

1 2 3 4 5 6

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.

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

Check if the stanza configuration is correct on db1:

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

1 2 3 4 5 6 7 8 9 10 11 12

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:

1 2 3 4 5 6 7 8 9 10 11

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:

1	sudo -u pgbackrest pgbackrest --stanza=demo backup

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

1 2 3

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.

1 2 3 4 5 6 7 8

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.

1 2 3 4

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:

1 2 3 4 5 6 7 8 9 10 11

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.