PostgreSQL Backup and Recovery with Barman

Recently a friend pointed me in the direction of a rather useful piece of software for doing quite funky things with backing up and restoring PostgreSQL databases.

Most organizations I have seen using PostgreSQL and MySQL have always chosen to perform database backups locally on each server. This is fine and it works, but you need to ensure you take a backup of those backup files. I’ve not come across any piece of software that will do complete database backups remotely… that is, until now. This is where “barman” fits in. You can check out what they are all about on the project’s website. http://www.pgbarman.org/

In this article I’ll be going through a really quick howto on getting up and running with Barman in order to perform remote backups of postgresql servers.

In this example I will be using the below details.

Backup server:    barman.example.com 
Database servers: postgres01.example.com and postgres02.example.com
Operating System: Red Hat Enterprise Linux 6.5

I will be assuming that you already have existing PostgreSQL servers running so I won’t be covering a complete PostgreSQL server setup.

Install Barman

To begin with, lets start with the barman backup server. On this server, you will need access to the EPEL repository for RHEL 6.

Note: If you would like to use barman in Fedora, you will also find it in the official repositories.

Once you have added the EPEL repository, you can run the following.

# yum install -y barman

Optional: For testing purposes, I used the “psql” command in order to test my remote connections, this is provided by the “postgresql” package, however it is not a requirement to have this installed.

Create SSH keys

When finished installing barman, you will need to create an ssh key on your barman server, as well as all of your remote PostgreSQL servers. They need bidirectional access over SSH for barman to function.

To do this, switch to your barman user, and run ssh-keygen

e.g:

[root@barman ~]# su -l barman
-bash-4.1$ ssh-keygen 
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/barman/.ssh/id_rsa): 
Created directory '/var/lib/barman/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /var/lib/barman/.ssh/id_rsa.
Your public key has been saved in /var/lib/barman/.ssh/id_rsa.pub.
The key fingerprint is:
10:16:21:23:f1:06:54:9a:cc:33:26:ff:b7:82:33:8b barman@barman.example.com
The key's randomart image is:
+--[ RSA 2048]----+
| .=o+ =o         |
| o * + .         |
|. O o .          |
| + +   .         |
|  .     S        |
|   .             |
|   .. .          |
| .+ .. .         |
|E .+ ..          |
+-----------------+ 
-bash-4.1$

Once you have created your key, you will need to copy it to all of your remote postgresql servers.
As logins are disabled on both the barman and postgres accounts, I have simply copied the contents of my public key onto each server.

On the barman server, copy the contents of your id_rsa.pub file which is stored in /var/lib/barman/.ssh/id_rsa.pub

e.g: My example key is as follows.

ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA7ywbwGikDBZdCye9oDoi3xeYKvZgrb1lLyxeJWNQmIcK4YGkb9vRhneRe6k4aGCv0Z9mnMriYX4CxoJjs7Qn7Fl6ogYVthPnfA8ZZ0E7taaVHK3av5De3fe9FAcZGGc92LlUuwAlMKp2uI2KOTXmIOv2X0ymyuNC/1PeV+hYh7ns7l6xuIGTIo2cwZXStd5/5cbky7FrPO3llJjDvC6bgJu6OoleKu8GSai70vOHzxGfDetR4ObvCL/uj2d4xXFvpK931lh1OxXC97eNA23/lV+8uZZ21bUzH16ZT/Y13TUPGHqdEfHBkQNK6K3WLeGiYGmYbSZx9Y/qbaWM+Fl9Iw== barman@barman.example.com

and save it into the the file /var/lib/pgsql/.ssh/authorized_keys file.

If this file does not currently exist, ensure that the owner and group ownerships are both postgres:postgres.

You should do this for all of your database servers.

Once you have finished with the barman ssh key, repeat the process for your postgres users on each database server and copy those keys to the /var/lib/barman/.ssh/authorized_keys on your barman server.

You should now be able to SSH from barman to your PostgreSQL servers without a password prompt.

E.g:

-bash-4.1$ whoami
barman
-bash-4.1$ ssh postgres@postgres01.example.com
Last login: Tue Feb  4 21:05:58 2014 from barman.example.com
-bash-4.1$ whoami
postgres
-bash-4.1$

as well as

-bash-4.1$ whoami
postgres
-bash-4.1$ ssh barman@barman.example.com
Last login: Tue Feb  4 21:06:30 2014 from postgres01.example.com
-bash-4.1$ whoami 
barman 
-bash-4.1$

Please note, that if you have SELinux enforcing, you will need to change the SELinux context of the .ssh directory. This is because postgresql resides in non-standard home directorys.

To correct SELinux contexts run the following on your postgresql servers.

$ chcon -t ssh_home_t ~/.ssh/ -R

Barman Configuration

Barman has its main configuration file in /etc/barman/barman.conf where there are a few global configuration options available to you. It also uses a seperate directory to load .conf files where you can have individual server configurations.

In this example, I have created /etc/barman/conf.d/postgres01.example.com.conf which appears as follows.

;; ; 'postgres01.example.com' PostgreSQL Server configuration
[postgres01.example.com]
;; ; Human readable description
description =  "PostgreSQL01 PostgreSQL Database"
;;
;; ; SSH options
ssh_command = ssh postgres@postgres01.example.com
;;
;; ; PostgreSQL connection string
conninfo = host=postgres01.example.com user=postgres
;;
;; ; Minimum number of required backups (redundancy)
;; ; minimum_redundancy = 1
;;
;; ; Examples of retention policies
;;
;; ; Retention policy (disabled)
;; ; retention_policy =
;; ; Retention policy (based on redundancy)
;; ; retention_policy = REDUNDANCY 2
;; ; Retention policy (based on recovery window)
;; ; retention_policy = RECOVERY WINDOW OF 4 WEEKS

You can see on the second line, that I have named this server profile “postgres01.example.com”. You can of course use a different name. It is simply what barman uses to differentiate between different server profiles.

This file also contains the host specific retention policies. Perhaps you may want a separate policy per server compared to the default.

You should verify this new server profile by using the “barman list-server” command. I have added two servers which appear as follows.

-bash-4.1$ barman list-server
postgres01.example.com - PostgreSQL01 PostgreSQL Database
postgres02.example.com - PostgreSQL02 PostgreSQL Database
-bash-4.1$

PostgreSQL Configuration

There isn’t much you need to change on the database servers, however we do need to set a couple of variables.

On your barman server, you need to grab the location barman expects backups to be pushed to from the databases servers themselves.

On your barman server, run the following for one of your server profiles. E.g: I am using postgres01.example.com which is a server as well as a profile name.
We need to grab the expected incoming wals directory location. You can grab the location using the following on your barman server.

-bash-4.1$ barman show-server postgres01.example.com | grep incoming
    incoming_wals_directory: /var/lib/barman/postgres01.example.com/incoming

Copy the location you are presented with, and we will need to use this on our postgresql servers.

On each postgresql server, you should edit the file /var/lib/pgsql/data/postgresql.conf and add the below lines to the end of the file.

archive_mode = on
archive_command = 'rsync -a %p barman@barman.example.com:/var/lib/barman/postgres01.example.com/incoming/%f'

If you are using PostgreSQL 9.0 or higher, you will also need the following line as well.

wal_level = 'archive' # For PostgreSQL >= 9.0

The archive_command is where you should make sure the incoming wal directory is set. You should reflect each server’s config file to the relevant barman server profile which you created earlier.

Once you have finished, restart postgresql on each of your database servers

[root@postgres01 ~]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
[root@postgres01 ~]#

Verify Settings

Once you have finished making all the above changes, jump back to your barman server and verify you have the correct results from a server check.

Below I am verifying that my postgres01.example.com server is accessible correctly to barman.

-bash-4.1$ barman check postgres01.example.com
Server postgres01.example.com:
    ssh: OK
    PostgreSQL: OK
    archive_mode: OK
    archive_command: OK
    directories: OK
    retention policy settings: OK
    compression settings: OK
    minimum redundancy requirements: OK (have 0 backups, expected at least 0)

-bash-4.1$

If you see all OK’s then you’re ready to go.

Create a backup

If all the above has been completed successfully, you’re now ready to have barman kick off a backup.

To do this, simply run “barman backup [profile name]”. In my case, I have run the following.

-bash-4.1$ barman backup postgres01.example.com
Starting backup for server postgres01.example.com in /var/lib/barman/postgres01.example.com/base/20140204T221400
Backup start at xlog location: 0/9000020 (000000010000000000000009, 00000020)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup end at xlog location: 0/9000088 (000000010000000000000009, 00000088)
Backup completed

-bash-4.1$

You can see from the above that the backup was successful.

You can also list all previous backups of a host with the following. The value “20140204T221400” is not only an ID in date format, but that is also the ID that you will need to use when performing a restore.

-bash-4.1$ barman list-backup postgres01.example.com
postgres01.example.com 20140204T221400 - Tue Feb  4 22:14:05 2014 - Size: 16.0 MiB - WAL Size: 0 B
-bash-4.1$

Please keep in mind that all barman backups will be stored in /var/lib/barman, so make sure you have enough disk space to be able to handle the backups of your databases.

Perform a Restore

Restoration is equally as simple.

Using the above backup ID, I have restored the contents of this backup to the local disk on my barman server.

-bash-4.1$ barman recover postgres01.example.com 20140204T221400 ~/test_restore/
Starting local restore for server postgres01.example.com using backup 20140204T221400 
Destination directory: /var/lib/barman/test_restore/
Copying the base backup.
Copying required wal segments.
The archive_command was set to 'false' to prevent data losses.

Your PostgreSQL server has been successfully prepared for recovery!

Please review network and archive related settings in the PostgreSQL
configuration file before starting the just recovered instance.

-bash-4.1$

With the above backup files, you are now able to bring that server back online by using those restored files. As I am not a PostgreSQL guru, I would highly recommend consulting the official PostgreSQL documentation for proper restoration procedures.