Implement backup with Barman

This tutorial is part of a multipage tutorial. Read the introduction first if you haven’t done so.

Why Barman ?

PostgreSQL, implements PITR thanks to use of WAL files and provides way to archive those. We could technically use plain rsync to backup our WAL files and other commands provided by PostgreSQL but Barman is more convenient. It helps manage the backups and makes the whole process much easier. You could, however, technically do it without. To understand all the concepts behind this backup solution I suggest you read the links mentioned in http://docs.pgbarman.org/release/2.4/#before-you-start which describes in more details some of the concepts we already talked about.

Architecture

To backup our database, we will need an extra server, different from the database you are backing up from. Keep in mind that you want your backup server to be close to the database so as to keep recovery and backup time reasonable. Depending on your situation, you might also want to put this server in a different region of your cloud provider, or even on a different cloud provider so you can be sure to have a recoverable data in case disaster strikes. It’s always possible to keep a backup of the backup.

For the time being, this is the result we want to achieve:

Simple target architecture

Installation

The packages are the on the PostgreSQL apt source. For more information you can check http://docs.pgbarman.org/release/2.4/#installation

Run the following command to install barman on Server-B

sudo apt-get install barman barman-cli

SSH connections

The installation of barman sets up the barman user so you don’t need to take care of that.

We want to establish trusted ssh connection between postgres@server-a and barman@server-b so that server-a can send WAL files to server-b and so that barman can on server-b trigger base backup, or launch recovery.

Target architecture for our PostgreSQL Barman backup with SSH connections

Create private key for postgres and barman user on respectively server-a and server-b.

## On server-a
## Create a private key for postgres user 
sudo -u postgres ssh-keygen -b 2048 -t rsa -N "" -C "postgres@server-a"
## On server-b
## Create a private key for barman user
sudo -u barman ssh-keygen -b 2048 -t rsa -N "" -C "barman@server-b"

Copy the public key of postgres and barman respectively to ~/.ssh/authorized_keys of barman@server-b and postgres@server-a.

You can test the connection is working by running:

## On server-a
sudo -u barman ssh postgres@10.20.0.2
## On server-b
sudo -u postgres ssh barman@10.20.0.3

Both commands should allow you to connect to the specified server. If it does not, please fix it before continuing further.

Configuration of barman

Now let’s configure our barman instance. On server-b, edit the /etc/barman.conf file, it should belong to the barman user.

We will start with the barman instance configuration:

[barman]
barman_home = /backup/barman
barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
reuse_backup = link
backup_method = rsync
archiver = on

Things to lookout for:

• For barman_home make sure the location has enough storage space.
• reuse_backup from the barman documentation itself: “Barman implements incremental backup through a global/server option called reuse_backup, that transparently manages the barman backup command. The `link` option reuses the last backup for a server and creating a hard link of the unchanged files (for backup space and time reduction)“. This is the most common.
• backup_method under the hood barman will use rsync via ssh, the other method is postgres which uses PostgreSQL streaming connection.

Now for every server you want to backup we will create a configuration. Don’t call the configuration with a name that suggests a role in a cluster (e.g. avoid calling your node “db-master” or such, it will get confusing later if your db-master server becomes a standby).

[server-a]
description = “Server A DB”
ssh_command = ssh postgres@server-a
conninfo = host=10.10.0.2 user=postgres port=5432
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main

• ssh_command the command to connect to server-a from server-b. Remember barman process will run as barman user. So we need to make sure that ssh connection to postgres user is working. Simply ssh to from on server to the other to check this.
• conninfo the connection information from barman to server-a. Note that server-a must accept connection in PostgreSQL from server-b. The connection must be made with a superuser OR a user with REPLICATION permissions.
• retention_policy: sets how far back we go back for a backup. For production, you might want to set something higher.

Don’t forget to configure PostgreSQL on server-a so that it can accept connections from server-b. You need to edit both pg_hba.conf and postgresql.conf.

# pg_hba.conf
host all all 10.10.0.3/32 trust 

We use trust connection here for the sake of simplicity. It’s up to you to modify this using any other more secure way of authenticating, even if it’s on a private network. I leave this part to you. Note this is the IP of server-b we are entrusting here.

# postgresql.conf
listen_addresses = ’10.20.0.2’ # what IP address(es) to listen on;

Here we are listening on the IP of server-a so we can accept connection coming from this network.

Setting up WAL archiving on your PostgreSQL server

WAL archive will backup every WAL files in server-b. A single missing WAL file or a corrupted WAL file will make a recovery fail, so it is important to make sure this step works properly.

First let’s find out where our WAL files will be archived on server-b.

barman show-server server-a | grep streaming_wals_directory

Copy it, we will call it STREAMING_WALS_DIRECTORY.

Back to PostgreSQL on server-a, in postgresql.conf, WAL archiving is controlled by the following parameters

archive_mode = on
archive_command = ‘rsync -a %p barman@10.20.0.3:STREAMING_WALS_DIRECTORY/%f`

%p is the location of the WAL files, %f its name. Reload PostgreSQL configuration or restart PostgreSQL.

Testing the backup

At this stage you should have a workable backup configuration and working WAL archiving. Let’s start by testing it and make our first backup, first let’s connect as barmanuser.

List the available servers

$ barman list-server
server-a — Server A DB

Checking WAL archiving

In order to make sure we have something to backup (.i.e. a WAL file) and that everything is configured correctly, run:

$ barman check server-a
Server server-a:
    PostgreSQL: OK
    is_superuser: OK
    wal_level: OK
    directories: OK
    retention policy settings: OK
    backup maximum age: FAILED (interval provided: 1 day, latest         backup age: No available backups)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: OK (have 0 backups, expected at least 0)
    ssh: OK (PostgreSQL server)
    not in recovery: OK
    archive_mode: OK
    archive_command: OK
    archiver errors: OK

You should see OK status everywhere. Don’t worry about “backup maximum age”, you simply don’t have anybackup yet.

If you see this line:

WAL archive: FAILED (please make sure WAL shipping is setup)

Probably the database has not produced any WAL files yet or they already have been deleted or the rsync is failing. Check the PostgreSQL logs to find out more. But if the rsync is working correctly, basically if no data is being written to the database, the server won’t produce any WAL files and therefore there is nothing to backup. WAL files are created after a certain amount of data is produced. I suggest you create a table, and insert some data. You can force closing the current WAL file by using:

$ barman switch-wal server-a

This will force the server to switch WAL files, .i.e it will close the current WAL files, and create a new one. The closed WAL files is ready for backup. Run:

$ barman check server-a

Everything should be at OK. If you still see a FAILED, make sure you followed all the steps.

Build a base backup

Now we are ready to start our backup. The backup command will create a base backup. This is a binary file that contains all data for the cluster, on which WAL files increment will be applied upon.

$ barman backup server-a

If everything is successful, you should see the name of the backup when listing:

$ barman list-backup server-a
server-a 20180605T053654 — Tue Jun 5 05:36:59 2018 — Size: 30.2 MiB — WAL Size: 0 B

You can show the backup details by running:

$ barman show-backup server-a 20180605T053654

Schedule a backup with crontab

As per documentation, the barman cron command “Perform maintenance tasks, such as enforcing retention policies or WAL files management.”

So add it to barman user’s cronjob along with the backup command, so you run a base backup regularly. Add the following lines when editing the crontab crontab -e:

* * * * * /usr/bin/barman cron
0 4 * * * /usr/bin/barman backup server-a

Now your database will be backed up everyday at 4:00 AM and maintained regularly by barman cron.

Congratulation you have successfully setup a backup using barman. Next chapter, we’ll learn how to recover from a database crash using your backup.