Jump toUpdate content
Backup PostgreSQL databases with Barman
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.
- You have an account and are logged into the Scaleway Console
- You have configured your SSH Key
- You have created two Instances running a PostgreSQL database on Ubuntu Bionic or Xenial
- You have sudo privileges or access to the root user.
This tutorial requires that you have already set up two Instances running a PostgreSQL database. They will be called
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:
Update the APT packet cache and upgrade the already installed packages to their latest version:
sudo apt-get update && sudo apt-get upgrade -y
sudo apt-get install barman
In order for Barman to copy data between the two servers, some pre-work is required:
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 (
Connect on the server
pgsqland log into the
sudo -i -u postgres
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
Enable the connection to the server from
pgsql-backupby editing the file /etc/postgresql/9.5/postgresql.conf. Change the value of
listen_addressesto the new server, or use an asterisk to enable the connection from any IP.
listen_addresses = 'localhost, pgsql-backup'
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
During the installation of Barman on the server
pgsql-backup, a system user account called barman was created.
Set a password for the user:
sudo passwd barman
Enter the user account:
sudo -i -u barman
Create the .pgpass file for the barman user:
echo "pgsql:5432:*:barman:password" >> ~/.pgpassNote:
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).
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.
Connect to the server
pgsql-backup, switch to user barman and generate the keys
ssh-keygen -t rsa
Copy the key to the user account postgres on
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.
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 postgres@localhost -C true
Once this is done, log in as postgres user on
pgsqland generate an SSH key:
ssh-keygen -t rsa
Copy the generated key to the list of authorized keys of the user barman on
Test the connection to the server:
ssh barman@pgsql-backup -C true
The configuration of Barman is done on the server
Create the directory
Open the file
/etc/barman.confand remove the leading semicolon (;) from the following line:
;configuration_files_directory = /etc/barman.d
Enter the directory
/etc/barman.dand create a file
pgsql.confwith the following content:
description = "Old PostgreSQL server"
conninfo = host=10.55.66.77 user=barman dbname=Our_Database
ssh_command = ssh postgres@pgsql
retention_policy = RECOVERY WINDOW OF 2 WEEKS
The information in this file is 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.
pgsqland add this line to the file
host all all 10.55.66.88/32 trust
Where 10.55.66.77 should be replaced with the IP of
Open the file
/etc/postgresql/9.5/main/postgresql.confand edit the values as shown:
wal_level = archive
archive_mode = on
archive_command = 'rsync -a %p email@example.com:/var/lib/barman/pgsql/incoming/%f'
Restart the postgresql service:
sudo systemctl restart postgresql
Check if the connection to the server
pgsqlworks from Barman, by runnning the command
barman check pgsqlafter logging into the barman user account on
barman@pgsql-backup:~$ barman check pgsql
continuous archiving: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
ssh: OK (PostgreSQL server)
not in recovery: OK
If all values are OK, you are ready to make your first backup.
Start a backup with the command
barman backup pgsql. You will see an output like the following:
barman@pgsql-backup:~$ barman backup pgsql
Starting backup for server pgsql in /var/lib/barman/pgsql/base/20180911T115925
Backup start at xlog location: 0/2000028 (000000010000000000000002, 00000028)
Asking PostgreSQL server to finalize the backup.
Backup size: 20.7 MiB
Backup end at xlog location: 0/20000C0 (000000010000000000000002, 000000C0)
To view a list of all backups available of
pgsql, use the command
barman list-backup pgsql
To view the details about a backup, use the following command:
barman show-backup pgsql backup_id
The backup_id is visible from the backup list and in the form like this example 20180911T115925.
It is possible to import the backup in the PostgreSQL server running on
Stop the postgresql service:
sudo systemctl stop postgresql
Load the backup into the database with the following command:
barman recover --remote-ssh-command "ssh postgres@localhost" pgsql 20180911T115925 /var/lib/postgresql/9.5/main
You will see an output like the following:
Starting remote restore for server pgsql using backup 20180911T115925
Destination directory: /var/lib/postgresql/9.5/main
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Your PostgreSQL server has been successfully prepared for recovery!
Once the backup has been imported, restart the postgresql service:
sudo systemctl start postgresql
Your database has now successfully migrated to your new database server.
It is possible to run Barman automatically to backup the content of your database regularly. This can be done easily by setting up a cron job on
Switch into the barman user:
sudo -i -u barman
Edit the crontab of the user:
To run a backup daily at 3:30, add the following line to the cron tab and save it:
30 03 * * * /usr/bin/barman backup pgsql