By clicking or navigating this website site, you agree to allow our collection of information on Scaleway to offer you an optimal user experience and to keep track of statistics through cookies. Learn more about our Cookie Policy.

Backup PostgreSQL databases with Barman

Barman Overview

Barman is an open source administration tool for disaster recovery of PostgreSQL servers. It is written in Python and allows to perform remote backups of multiple servers in business critical environments.

The tool creates backups based on the PostgreSQL logs and not by performing a simple SQL dump.

Installing Barman

Note: This tutorial requires that you have already set up two servers running a PostgreSQL database. They will be called pgsql and pgsql-backup in this tutorial.

As you will backup your databases with Barman, it is recommended to host the application on a different instance than your databases. Ideally in a geographically distant data center. In this tutorial, we install Barman on a second server to migrate the database from one to the other server for backup and redundancy purposes.

Barman is available in the official APT repositories of Ubuntu. Connect to the second server, called pgsql-backup and install the tool:

Configuring Barman

In order for Barman to copy data between the two servers, some pre-work is required:

Creating a dedicated PostgreSQL user on `pgsql`

As Barman has been installed on the second server (pgsql-backup), it is required to create a database user for the tool on the first server (pgsql).

1 . Connect on the server pgsql and log into the postgres account:

sudo -i -u postgres

2 . Create the user and enter twice the desired password. When asked if the account should have superuser privileges, type y and press Enter:

createuser --interactive -P barman

3 . Enable the connection to the server from pgsql-backup by editing the file /etc/postgresql/9.5/postgresql.conf. Change the value of listen_addresses to the new server, or use an asterisk to enable the connection from any IP.

listen_addresses = 'localhost, pgsql-backup'

4 . Restart the service

sudo service postgresql restart

Test the connection from pgsql-backup by running a SQL query:

psql -c 'SELECT version()' -U barman -h pgsql postgres

Creating the .pgpass file on pgsql-backup

During the installation of Barman on the server pgsql-backup, a system user account called barman was created.

1 . Set a password for the user:

sudo passwd barman

2 . Enter the user account:

sudo -i -u barman

3 . Create the .pgpass file for the barman user:

echo "pgsql:5432:*:barman:password" >> ~/.pgpass

Note: The data format for the .pgpass file is hostname:port:database:username:password.If an asterisk is used in any of the first four fields, it will correspond to everything. The username refers to the PostgreSQL user that was created previously, not to the linux user account. You may refer to the official documentation for more information about this file.

The file .pgpass in a user’s home directory can contain passwords to be used if the connection requires a password (and no password has been specified otherwise).

Setting up key-based authentication

To perform backups without manual authentication, generate an SSH key that will be used for authentication:

In order to perform backups without user intervention, we need to set up and copy SSH keys for passwordless authentication. Barman makes use of this method to copy data through rsync.

1 . Connect to the server pgsql-backup, switch to user barman and generate the keys

ssh-keygen -t rsa

2 . Copy the key to the user account postgres on pgsql:

ssh-copy-id postgres@pgsql

The following message will appear if the transfer of the key has been successful:

Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@pgsql'"
and check to make sure that only the key(s) you wanted were added.

3 . Barman also requires access to the postgres account on the server pgsql-backup. Copy the key into the directory of the postgres user and test the connection:

ssh-copy-id postgres@localhost
ssh postgres@localhost -C true

4 . Once this is done, log in as postgres user on pgsql and generate an SSH key:

ssh-keygen -t rsa

5 . Copy the generated key to the list of authorized keys of the user barman on pgsql-backup:

ssh-copy-id barman@pgsql-backup

6 . Test the connection to the server:

ssh barman@pgsql-backup -C true

Configuring Barman

The configuration of Barman is done on the server pgsql-backup.

1 . Create the directory /etc/barman.d:

mkdir /etc/barman.d

2 . Open the file /etc/barman.conf and remove the leading semicolon (;) from the following line:

;configuration_files_directory = /etc/barman.d

3 . Enter the directory /etc/barman.d and create a file pgsql.conf with the following content:

The information in this file are self-explanatory. The retention_policy defines the time frame during which backups are kept. This can be set to your needs. For more information about this file you may refer to the official documentation.

Configuring PostgreSQL

1 . Connect to pgsql and add this line to the file /etc/postgresql/9.5/main/pg_hba.conf:

host all all 10.55.66.88/32 trust

Where 10.55.66.77 should be replaced with the IP of pgsql-backup.

2 . Open the file /etc/postgresql/9.5/main/postgresql.conf and edit the values as shown: