Full/Incremental Backup/Restore PostgreSQL

Using pgBackRest with Ansible

In this post I go through remote backup/restore with pgBackRest and use ansible to install and configure pgBackRest. Before begin, I assume the following:

• PostgreSQL and all its dependencies are installed (I explore the steps involved in configuring pgBackRest on a PostgreSQL v13.0).
• There are two servers for database, primary , standby with their cluster and the communication between them is enabled.
• There is repository host (vm) to runs and stores backups.
• Ansible is installed on control machine and can access to the servers (the playbooks are written using the ansible 2.5.1).

You can find ALL references at the end…

We follow the instructions of installation here, but before that let defines some terminologies:

• pg-primary a database server that allows a user to make changes is known as a master .
• pg-standby a database server that only allows read-only access is known as a slave server.
• repository take backup form standby to reduce load on the primary .

So, yeah lets start 😎

note! Add IP addresses of standby, primary & repository in control machine’s hosts:

> sudo vi /etc/hosts

IP_ADDRESS_PRIMARY pg-primary
IP_ADDRESS_REPOSITORY repository
IP_ADDRESS_STANDBY pg-standby

Build host
As mentions in documentation, “When building from source it is best to use a build host rather than building on production”; I download pgBackRest in my control device:

> wget -q -O - \  https://github.com/pgbackrest/pgbackrest/archive/release/2.30.tar.gz | tar zx -C /YOUR_PATH_WANT_TO_BUILD

Install build dependencies:

> sudo apt-get install make gcc libpq-dev libssl-dev \
libxml2-dev pkg-config liblz4-dev libzstd-dev libbz2-dev libz-dev -y

Configure & compile pgBackRest :

cd YOUR_PATH_WANT_TO_BUILD/pgbackrest-release-2.30/src && ./configure && make

Install dependencies:

sudo apt-get install postgresql-client libxml2 -y

and done 👯

Primary (the same steps applied on Standby)

Create an Ansible playbook to install pgbackrest:

ansible-playbook pgbackrest-databases.yml

Final output:

installation is worked

Repository

As recommended in the documentation , “is best not to use postgres user(if it exists) to avoid confusion” , so starts create a new user, then install pgbackrest:

> ansible-playbook pgbackrest-repository.yml

To enable communication between the servers, pgBackRest requires passwordless SSH.

> ansible-playbook psswordless.yml

Test that connections can be made from repository to pg-primary and pg-standby and vice versa:

repository

> sudo -u pgbackrest ssh postgres@pg-primary
> sudo -u pgbackrest ssh postgres@pg-standby 

pg-primary & pg-standby

sudo -u postgres ssh pgbackrest@repository

if everything is okay, you will be able to connect 👏

Configuration

repository

• The pg-primary will be configured aspg1 .
• The pg-standby will be configured aspg2 .

pgBackRest configuration files follow the Windows INI convention. Update /etc/pgbackrest/pgbackrest.conf :

> sudo vi /etc/pgbackrest/pgbackrest.conf
[main]
pg1-host=pg-primary
pg1-path=/var/lib/postgresql/13/main
pg2-host=pg-standby
pg2-path=/var/lib/postgresql/13/main
[global]
backup-standby=y
process-max=3
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
log-level-console=info

• [main]: defines a stanza for pg-primary and pg-standby backup.
• pg1-path/pg2-path: database path for pg-primary/pg-standby .To know where your configuration files are on the PostgreSQL host:

> sudo -iu postgres psql -U postgres -c 'SHOW config_file'

• [global]: defines the location of backups, logs and encryption settings .
• backup-standby: must be enabled to take the backup from pg-standby cluster.
• repo1-path: configure the pgBackRest repository path.
• To get more information about each parameters and what your environment needs please check here.

pg-primary & pg-standby

The configuration cover pgBackRest and PostgreSQL configuration files.

PostgreSQL

by editing: /etc/postgresql/13/main/postgresql.conf

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

in addition to the previous, add these to the pg-standby :

hot_standby = on
log_filename = 'postgresql.log'

then, create the Stanza in repository . The full playbook:

> ansible-playbook configuration.yml

Backup and Restore Testing

I have database school with students table:

Now, let’s take a full backup:

- name: Take full backup
  shell: pgbackrest --stanza=main --type=full backup
  when: type == 'full'

apply the task

> ansible-playbook backup.yml -e type=full

you will see the files are copied from the pg-standby and only a few are copied from the pg-primary .

the status of backup should be ok.

Let’s take incremental backup:

- name: Take incremental backup
  shell: pgbackrest --stanza=main --type=incr backup
  register: backup_info
  when: type == 'incr'

apply the task

> ansible-playbook backup.yml -e type=incr

we have two type of backups:

Now, we have the backup let’s go to test it. First, we need to remove some data from table:

To avoid having to clean the data directory before the restore, it’s possible to use the --delta option to automatically determine which files can be preserved and which ones need to be restored from the backup.

> ansible-playbook restore.yml

The backup should be restored successfully. To confirm that go to the postgresql server:

That’s all!

References

• pgBackRest User Documentation.
• Full configuration pgbackrest-ansible github.