pgBackRest with PostgreSQL Sandbox on Debian / Ubuntu

pgBackRest is one of the most powerful backup solutions available for PostgreSQL. It has enterprise level features like compression, multiple channels (threads) of backup execution, incremental and differential backups etc.
The official documentation is Debian-centric in its focus. I wanted to test it out with the PostgreSQL sandbox from the BigSQL project.

Setting up PostgreSQL Sandbox and Installing pgBackRest

The BigSQL Project makes it easy to install and setup PostgreSQL and its associated components across different operating systems. In this document, we are going to look at how to set it up on Ubuntu 14.04. Linux binaries of the sandbox can be downloaded from the BigSQL download page

The sandbox installation requires only the unpacking of the downloaded file.

tar -xvf bigsql-9.5.3-5-linux64.tar.bz2
cd bigsql/

Using the command line utility (pgc) supplied with the sandbox, its very easy to initialize and start a PostgreSQL instance.

./pgc init pg95
./pgc start pg95

A PostgreSQL instance should now be up and running.
The same pgc utility can be used to install pgBackRest.

./pgc install backrest

Install Perl Dependencies

An important aspect to keep in mind is that pgBackrest is written in Perl and has many dependencies on different perl libraries and modules.
An easy way to install all the dependencies in one shot is to instruct the apt-get utility to install one of the leaf components in the dependency chain.

sudo apt-get install libdbd-pg-perl

This command should fetch all the perl dependencies of pgBackRest.

Setting Up a Backup Repository Directory

Set up a backup repository directory for pgBackRest with the following commands.

sudo mkdir /var/log/pgbackrest
sudo chmod 750 /var/log/pgbackrest

IMPORTANT for this test:

  1. pgbackrest and the postgres server process should run as the same OS user.
  2. The backup repository directory should be owned by the same OS user.

Change the ownership of the repository directory to the user under which the postgres process is running. If the user is “postgres” and the group is “postgres” then:

sudo chown -R postgres:postgres /var/log/pgbackrest

pgBackRest configuration

sudo vi /etc/pgbackrest.conf

Append the following entries to this file.

[demo]
db-path=/home/postgres/bigsql/data/pg95

[global]
repo-path=/var/log/pgbackrest

Note: if the entries already exist, modify them accordingly.

Change the ownership of this configuration file to the OS user that owns the postgres and pgbackrest process

sudo chown -R postgres:postgres /etc/pgbackrest.conf
chmod +x pgbackrest.conf

Modification Database Parameters

The archive_command needs to be modified to use pgbackrest. If the pgbackrest executable doesn’t exist in the path, please make sure that the full path is mentioned

alter system set archive_command = '/home/postgres/bigsql/backrest/bin/pgbackrest --stanza=demo archive-push %p';

A few other parameters that are also important for the proper working of pgBackRest:

alter system set archive_mode=on;
alter system set listen_addresses = '*';
alter system set max_wal_senders=3;
alter system set wal_level = 'hot_standby';

Modification of all these parameters requires a restart of the PostgreSQL instance.

./pgc restart pg95

In the event that our operating system user doesn’t exist as a superuser in our database, we need to create the user and assign superuser privileges

postgres=# create user vagrant with password 'vagrant';
postgres=# alter user vagrant with superuser;

Backing up database using pgBackRest

pgBackRest uses .pgpass file for authentication.
Add a line to .pgpass with the password of the superuser in the following format:

*:*:*:*:vagrant

once this is done, we are ready to backup the PostgreSQL instance.

backrest/bin/pgbackrest --stanza=demo --db-socket-path=/tmp --log-level-console=info backup

Restoring from backup

Imagine a scenario where the files in your data directory are corrupt or lost and you want to restore it from backup.
The first step is to bring down the PostgreSQL instance. This should release all file descriptors pointing to the current data directory.

Clean up the Data directory:
Before restoring the backup make sure that the data directory is clean and is stored on a reliable medium. The full path to the new data directory should be the same as the previous one (we can override this default, but for the sake of simplicity lets assume that the location remains the same).

Run the pgBackRest “restore” command to restore the data directory from the latest backup.

backrest/bin/pgbackrest --stanza=demo --db-socket-path=/tmp --log-level-console=info restore

Now we should be able to start up the PostgreSQL instance with the restored data directory.

./pgc start pg95

Our PostgreSQL cluster is now back online from the backup we restored.