Tutorial

How To Back Up, Restore, and Migrate PostgreSQL Databases with Barman on CentOS 7

Published on January 20, 2016
How To Back Up, Restore, and Migrate PostgreSQL Databases with Barman on CentOS 7

Introduction

PostgreSQL is an open-source database platform quite popular with web and mobile application developers for its ease of maintenance, cost effectiveness, and simple integration with other open-source technologies.

One critical task of maintaining a PostgreSQL environment is to back up its databases regularly. Backups form part of the Disaster Recovery (DR) process for any organization. This is important for a few reasons:

  • Safeguarding against data loss due to failure of underlying infrastructure components like storage or the server itself
  • Safeguarding against data corruption and unwanted or malicious loss of data
  • Migrating production databases into development or test environments

Usually the responsibility of database backup and restoration falls on the shoulder of a DBA. In smaller organizations or startups, however, system administrators, DevOps engineers, or programmers often have to create their own database backends. So, it’s important for everyone using PostgreSQL to understand how backups work and how to restore from a backup.

In this tutorial you’ll set up the Barman backup server, make a backup from a primary database server, and restore to a standby server.

A Brief Introduction to PostgreSQL Backup Methods

Before launching into your Barman setup, let’s take a moment to review the types of backups available for PostgreSQL, and their uses. (For an even broader overview of backup strategies, read our article about effective backups.)

PostgreSQL offers two types of backup methods:

  • Logical backups
  • Physical backups

Logical backups are like snapshots of a database. These are created using the pg_dump or pg_dumpall utility that ships with PostgreSQL. Logical backups:

  • Back up individual databases or all databases
  • Back up just the schemas, just the data, individual tables, or the whole database (schemas and data)
  • Create the backup file in proprietary binary format or in plain SQL script
  • Can be restored using the pg_restore utility which also ships with PostgreSQL
  • Do not offer point-in-time recovery (PITR)

This means if you make a logical backup of your database(s) at 2:00 AM in the morning, when you restore from it, the restored database will be as it was at 2:00 AM. There is no way to stop the restore at a particular point in time, say at 1:30 AM. If you are restoring the backup at 10:00 AM, you have lost eight hours’ worth of data.

Physical backups are different from logical backups because they deal with binary format only and makes file-level backups. Physical backups:

  • Offer point-in-time recovery
  • Back up the contents of the PostgreSQL data directory and the WAL (Write Ahead Log) files
  • Take larger amounts of disk space
  • Use the PostgreSQL pg_start_backup and pg_stop_backup commands. However, these commands need to be scripted, which makes physical backups a more complex process
  • Do not back up individual databases, schemas only, etc. It’s an all-or-nothing approach

WAL files contain lists of transactions (INSERT, UPDATE or DELETE) that happen to a database. The actual database files containing the data are located within the data directory. So when it comes to restoring to a point in time from a physical backup, PostgreSQL restores the contents of the data directory first, and then plays the transactions on top of it from the WAL files. This brings the databases to a consistent state in time.

How Barman Backups Work

Traditionally, PostgreSQL DBAs would write their own backup scripts and scheduled cron jobs to implement physical backups. Barman does this in a standardized way.

Barman or Backup and Recovery Manager is a free, open-source PostgreSQL backup tool from 2ndQuadrant - a professional Postgres solutions company. Barman was written in Python and offers a simple, intuitive method of physical backup and restoration for your PostgreSQL instance. Some benefits of using Barman are:

  • It’s totally free
  • It’s a well-maintained application and has professional support available from the vendor
  • Frees up the DBA / Sysadmin from writing and testing complex scripts and cron jobs
  • Can back up multiple PostgreSQL instances into one central location
  • Can restore to the same PostgreSQL instance or a different instance
  • Offers compression mechanisms to minimize network traffic and disk space

Goals

In this tutorial we will create three DigitalOcean Droplets, install PostgreSQL 9.4 on two of these machines, and install Barman on the third.

One of the PostgreSQL servers will be our main database server: this is where we will create our production database. The second PostgreSQL instance will be empty and treated as a standby machine where we can restore from the backup.

The Barman server will communicate with the main database server and perform physical backups and WAL archiving.

We will then emulate a “disaster” by dropping a table from our live database.

Finally, we will restore the backed up PostgreSQL instance from the Barman server to the standby server.

Prerequisites

To follow this tutorial, you will need to create three DigitalOcean Droplets (or your own Linux servers), each with at least 2 GB of RAM and 2 CPU cores. We won’t go into the details of creating a Droplet; you can find more information here.

All three servers should have the same OS (CentOS 7 x64 bit).

We will name the machines as follows:

  • main-db-server (we will denote its IP address as main-db-server-ip)
  • standby-db-server (we will denote its IP address as standby-db-server-ip)
  • barman-backup-server (we will denote its IP address as barman-backup-server-ip)

The actual IP addresses of the machines can be found from the DigitalOcean control panel.

You should also set up a sudo user on each server and use that for general access. Most of the commands will be executed as two different users (postgres and barman), but you will need a sudo user on each server as well so you can switch to those accounts. To understand how sudo privileges work, see this DigitalOcean tutorial about enabling sudo access.

Note: This tutorial will be use the default Barman installation directory as the backup location. In CentOS, this location is: /var/lib/barman/. 2ndQuadrant recommends it’s best to keep the default path. In real-life use cases, depending on the size of your databases and the number of instances being backed up, you should check that there is enough space in the file system hosting this directory.

Warning: You should not run any commands, queries, or configurations from this tutorial on a production server. This tutorial will involve changing configurations and restarting PostgreSQL instances. Doing so in a live environment without proper planning and authorization would mean an outage for your application.

Step 1 — Installing PostgreSQL Database Servers

We will first set up our database environment by installing PostgreSQL 9.4 on the main-db-server and the standby-db-server.

Please complete the PostgreSQL installation steps from this LEPP stack tutorial. From this tutorial, you will need to:

  • Follow the section Step One — Installing PostgreSQL
  • Follow the section Step Two — Configuring PostgreSQL

In Step Two — Configuring PostgreSQL, instead of making changes to the pg_hba.conf file to allow access to the database for a web server, add this line so the Barman server can connect, using the barman-backup-server IP address, followed by /32:

  1. host all all barman-backup-server-ip/32 trust

This configures PostgreSQL to accept any connection coming from the Barman server.

The rest of the instructions in that section can be followed as they are.

Note: Installing PostgreSQL will create an operating system user called postgres on the database server. This account does not have a password; you’ll switch to it from your sudo user.

Make sure you have installed PostgreSQL on both the main-db-server and the standby-db-server, and that you have allowed access on both of them from the barman-backup-server.

Next we’ll add some sample data to the main database server.

Step 2 — Creating PostgreSQL Database and Tables

Once PostgreSQL is installed and configured on both the machines, we’ll add some sample data to the main-db-server to simulate a production environment.

On the main-db-server, switch to the user postgres:

  1. sudo su - postgres

Start the psql utility to access the database server:

  1. psql

From the psql prompt, run the following commands to create a database and switch to it:

  1. CREATE DATABASE mytestdb;
  2. \connect mytestdb;

An output message will tell you that you are now connected to database mytestdb as user postgres.

Next, add two tables in the database:

  1. CREATE TABLE mytesttable1 (id integer NULL);
  2. CREATE TABLE mytesttable2 (id integer NULL);

These are named mytesttable1 and mytesttable2.

Quit the client tool by typing \q and pressing ENTER.

Step 3 — Installing Barman

Now we’ll install Barman on the backup server, which will both control and store our backups.

Complete this step on the barman-backup-server.

To do this, you will first need to install the following repositories:

  • Extra Packages for Enterprise Linux (EPEL) repository
  • PostgreSQL Global Development Group RPM repository

Run the following command to install EPEL:

  1. sudo yum -y install epel-release

Run these commands to install the PostgreSQL repo:

  1. sudo wget http://yum.postgresql.org/9.4/redhat/rhel-7Server-x86_64/pgdg-centos94-9.4-1.noarch.rpm
  2. sudo rpm -ivh pgdg-centos94-9.4-1.noarch.rpm

Finally, run this command to install Barman:

  1. sudo yum -y install barman

Note: Installing Barman will create an operating system user called barman. This account does not have a password; you can switch to this user from your sudo user account.

Barman is installed! Now, let’s make sure the servers can connect to each other securely.

Step 4 — Configuring SSH Connectivity Between Servers

In this section, we’ll establish SSH keys for a secure passwordless connection between the main-db-server and the barman-backup-server, and vice versa.

Likewise, we’ll establish SSH keys between the standby-db-server and the barman-backup-server, and vice versa.

This is to ensure PostgreSQL (on both database servers) and Barman can “talk” to each other during backups and restores.

For this tutorial you will need to make sure:

  • User postgres can connect remotely from the main-db-server to the barman-backup-server
  • User postgres can connect remotely from the standby-db-server to the barman-backup-server
  • User barman can connect remotely from the barman-backup-server to the main-db-server
  • User barman can connect remotely from the barman-backup-server to the standby-db-server

We will not go into the details of how SSH works. There’s a very good article on DigitalOcean about SSH essentials which you can refer to.

All the commands you’ll need are included here, though.

We’ll show you how to do this once for setting up the connection for the user postgres to connect from the main-db-server to the barman-backup-server.

From the main-db-server, switch to user postgres if it’s not already the current user:

  1. sudo su - postgres

Run the following command to generate an SSH key pair:

  1. ssh-keygen -t rsa

Accept the default location and name for the key files by pressing ENTER.

Press ENTER twice to create the private key without any passphrase.

Once the keys are generated, there will be a .ssh directory created under the postgres user’s home directory, with the keys in it.

You will now need to copy the SSH public key to the authorized_keys file under the barman user’s .ssh directory on the barman-backup-server.

Note: Unfortunately you can’t use the ssh-copy-id barman@barman-backup-server-ip command here. That’s because this command will ask for the barman user’s password, which is not set by default. You will therefore need to copy the public key contents manually.

Run the following command to output the postgres user’s public key contents:

  1. cat ~/.ssh/id_rsa.pub

Copy the contents of the output.

Switch to the console connected to the barman-backup-server server and switch to the user barman:

  1. sudo su - barman

Run the following commands to create a .ssh directory, set its permissions, copy the public key contents to the authorized_keys file, and finally make that file readable and writable:

  1. mkdir -p ~/.ssh
  2. chmod 700 ~/.ssh
  3. echo "public_key_string" >> ~/.ssh/authorized_keys
  4. chmod 600 ~/.ssh/authorized_keys

Make sure you put the long public key string starting with ssh-rsa between the quotation marks, instead of public_key_string.

You’ve copied the key to the remote server.

Now, to test the connection, switch back to the main-db-server and test the connectivity from there:

  1. ssh barman@barman-backup-server-ip

After the initial warning about the authenticity of the remote server not being known and you accepting the prompt, a connection should be established from the main-db-server server to the barman-backup-server. If successful, log out of the session by executing the exit command.

  1. exit

You need to set up SSH key connections three more times. You can skip making the .ssh directory if it’s already made (although this isn’t necessary).

  • Run the same commands again, this time from the standby-db-server to the barman-backup-server
  • Run them a third time, this time originating from the barman user on the barman-backup-server, and going to the postgres user on the main-db-server
  • Finally, run the commands to copy the key from the barman user on the barman-backup-server to the postgres user on the standby-db-server

Make sure you test the connection each way so that you can accept the initial warning about the new connection.

From standby-db-server:

  1. ssh barman@barman-backup-server-ip

From barman-backup-server:

  1. ssh postgres@main-db-server-ip

From barman-backup-server:

  1. ssh postgres@standby-db-server-ip

Note: Ensuring SSH connectivity between all three servers is a requirement for backups to work.

Step 5 — Configuring Barman for Backups

You will now configure Barman to back up your main PostgreSQL server.

The main configuration file for BARMAN is /etc/barman.conf. The file contains a section for global parameters, and separate sections for each server that you want to back up. The default file contains a section for a sample PostgreSQL server called main, which is commented out. You can use it as a guide to set up other servers you want to back up.

A semicolon (;) at the beginning of a line means that line is commented out. Just like with most Linux-based applications, a commented-out configuration parameter for Barman means the system will use the default value unless you uncomment it and enter a different value.

One such parameter is the configuration_files_directory, which has a default value of /etc/barman.d. What this means is, when enabled, Barman will use the .conf files in that directory for different Postgres servers’ backup configurations. If you find the main file is getting too lengthy, feel free to make separate files for each server you want to back up.

For the sake of simplicity in this tutorial, we will put everything in the default configuration file.

Open /etc/barman.conf in a text editor as your sudo user (user barman has only read access to it):

  1. sudo vi /etc/barman.conf

The global parameters are defined under the [barman] section. Under this section, make the following changes. The finished values are shown below the bullet points:

  • Uncomment the line for compression and keep the default value of gzip. This means the PostgreSQL WAL files - when copied under the backup directory - will be saved in gzip compressed form
  • Uncomment the line for reuse_backup and keep the default value of link. When creating full backups of the PostgreSQL server, Barman will try to save space in the backup directory by creating file-level incremental backups. This uses rsync and hard links. Creating incremental full backups has the same benefit of any data de-duplication method: savings in time and disk space
  • Uncomment the line for immediate_checkpoint and set its value to true. This parameter setting ensures that when Barman starts a full backup, it will request PostgreSQL to perform a CHECKPOINT. Checkpointing ensures any modified data in PostgreSQL’s memory cache are written to data files. From a backup perspective, this can add some value because BARMAN would be able to back up the latest data changes
  • Uncomment the line for basebackup_retry_times and set a value of 3. When creating a full backup, Barman will try to connect to the PostgreSQL server three times if the copy operation fails for some reason
  • Uncomment the line for basebackup_retry_sleep and keep the default value of 30. There will be a 30-second delay between each retry
  • Uncomment the line for last_backup_maximum_age and set its value to 1 DAYS

The new settings should look like this exactly:

Excerpts from /etc/barman.conf
[barman]
barman_home = /var/lib/barman

. . .

barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
reuse_backup = link

. . .

immediate_checkpoint = true

. . .

basebackup_retry_times = 3
basebackup_retry_sleep = 30
last_backup_maximum_age = 1 DAYS

What we are doing here is this:

  • Keeping the default backup location
  • Specifying that backup space should be saved. WAL logs will be compressed and base backups will use incremental data copying
  • Barman will retry three times if the full backup fails halfway through for some reason
  • The age of the last full backup for a PostgreSQL server should not be older than 1 day

At the end of the file, add a new section. Its header should say [main-db-server] in square brackets. (If you want to back up more database servers with Barman, you can make a block like this for each server and use a unique header name for each.)

This section contains the connection information for the database server, and a few unique backup settings.

Add these parameters in the new block:

Excerpt from /etc/barman.conf
[main-db-server]
description = "Main DB Server"
ssh_command = ssh postgres@main-db-server-ip
conninfo = host=main-db-server-ip user=postgres
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main

The retention_policy settings mean that Barman will overwrite older full backup files and WAL logs automatically, while keeping enough backups for a recovery window of 7 days. That means we can restore the entire database server to any point in time in the last seven days. For a production system, you should probably set this value higher so you have older backups on hand.

You’ll need to use the IP address of the main-db-server in the ssh_command and conninfo parameters. Otherwise, you can copy the above settings exactly.

The final version of the modified file should look like this, minus all the comments and unmodified settings:

Excerpts from /etc/barman.conf
[barman]
barman_home = /var/lib/barman

. . .

barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
reuse_backup = link

. . .

immediate_checkpoint = true

. . .

basebackup_retry_times = 3
basebackup_retry_sleep = 30
last_backup_maximum_age = 1 DAYS

. . .

[main-db-server]
description = "Main DB Server"
ssh_command = ssh postgres@main-db-server-ip
conninfo = host=main-db-server-ip user=postgres
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main

Save and close the file.

Next, we’ll make sure our main-db-server is configured to make backups.

Step 6 — Configuring the postgresql.conf File

There is one last configuration to be made on the main-db-server, to switch on backup (or archive) mode.

First, we need to locate the value of the incoming backup directory from the barman-backup-server. On the Barman server, switch to the user barman:

  1. sudo su - barman

Run this command to locate the incoming backup directory:

  1. barman show-server main-db-server | grep incoming_wals_directory

This should output something like this:

barman show-server command output
incoming_wals_directory: /var/lib/barman/main-db-server/incoming

Note down the value of incoming_wals_directory; in this example, it’s /var/lib/barman/main-db-server/incoming.

Now switch to the main-db-server console.

Switch to the user postgres if it’s not the current user already.

Open the postgresql.conf file in a text editor:

  1. vi $PGDATA/postgresql.conf

Make the following changes to the file:

  • Uncomment the wal_level parameter and set its value to archive instead of minimal
  • Uncomment the archive_mode parameter and set its value to on instead of off
  • Uncomment the archive_command parameter and set its value to 'rsync -a %p barman@barman-backup-server-ip:/var/lib/barman/main-db-server/incoming/%f' instead of ''. Use the IP address of the Barman server. If you got a different value for incoming_wals_directory, use that one instead
Excerpts from postgresql.conf
wal_level = archive                     # minimal, archive, hot_standby, or logical

. . .

archive_mode = on               # allows archiving to be done

. . .

archive_command = 'rsync -a %p barman@barman-backup-server-ip:/var/lib/barman/main-db-server/incoming/%f'                # command to use to archive a logfile segment

Switch back to your sudo user.

Restart PostgreSQL:

  1. sudo systemctl restart postgresql-9.4.service

Note: If you are configuring an existing production PostgreSQL instance, there’s a good chance these three parameters will be set already. You will then have to add/modify only the archive_command parameter so PostgreSQL sends its WAL files to the backup server.

Step 7 — Testing Barman

It’s now time to check if Barman has all the configurations set correctly and can connect to the main-db-server.

On the barman-backup-server, switch to the user barman if it’s not the current user. Run the following command to test the connection to your main database server:

  1. barman check main-db-server

Note that if you entered a different name between the square brackets for the server block in the /etc/barman.conf file in Step 5, you should use that name instead.

If everything is okay, the output should look like this:

barman check command output
Server main-db-server: PostgreSQL: OK archive_mode: OK wal_level: OK archive_command: OK continuous archiving: OK directories: OK retention policy settings: OK backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups) compression settings: OK minimum redundancy requirements: OK (have 0 backups, expected at least 0) ssh: OK (PostgreSQL server) not in recovery: OK

Don’t worry about the backup maximum age FAILED state. This is happening because we have configured Barman so that the latest backup should not be older than 1 day. There is no backup made yet, so the check fails.

If any of the other parameters are in a FAILED state, you should investigate further and fix the issue before proceeding.

There can be multiple reasons for a check to fail: for example, Barman not being able to log into the Postgres instance, Postgres not being configured for WAL archiving, SSH not working between the servers, etc. Whatever the cause, it needs to be fixed before backups can happen. Run through the previous steps and make sure all the connections work.

To get a list of PostgreSQL servers configured with Barman, run this command:

  1. barman list-server

Right now it should just show:

Output
main-db-server - Main DB Server

Step 8 — Creating the First Backup

Now that you have Barman ready, let’s create a backup manually.

Run the following command as the barman user on the barman-backup-server to make your first backup:

  1. barman backup main-db-server

Again, the main-db-server value is what you entered as the head of the server block in the /etc/barman.conf file in Step 5.

This will initiate a full backup of the PostgreSQL data directory. Since our instance has only one small database with two tables, it should finish very quickly.

Output
Starting backup for server main-db-server in /var/lib/barman/main-db-server/base/20151111T051954
Backup start at xlog location: 0/2000028 (000000010000000000000002, 00000028)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup size: 26.9 MiB. Actual size on disk: 26.9 MiB (-0.00% deduplication ratio).
Backup end at xlog location: 0/20000B8 (000000010000000000000002, 000000B8)
Backup completed
Processing xlog segments for main-db-server
        Older than first backup. Trashing file 000000010000000000000001 from server main-db-server
        000000010000000000000002
        000000010000000000000002.00000028.backup

Backup File Location

So where does the backup get saved? To find the answer, list the contents of the /var/lib/barman directory:

  1. ls -l /var/lib/barman

There will be one directory there: main-db-server. That’s the server Barman is currently configured to back up, and its backups live there. (If you configure Barman to back up other servers, there will be one directory created per server.) Under the main-db-server directory, there will be three sub-directories:

  • base: This is where the base backup files are saved
  • incoming: PostgreSQL sends its completed WAL files to this directory for archiving
  • wals: Barman copies the contents of the incoming directory to the wals directory

During a restoration, Barman will recover contents from the base directory into the target server’s data directory. It will then use files from the wals directory to apply transaction changes and bring the target server to a consistent state.

Listing Backups

There is a specific Barman command to list all the backups for a server. That command is barman list-backup. Run the following command to see what it returns for our main-db-server:

barman list-backup main-db-server
Output
main-db-server 20151111T051954 - Wed Nov 11 05:19:46 2015 - Size: 26.9 MiB - WAL Size: 0 B
  • The first part of the output is the name of the server. In this case, main-db-server
  • The second part - a long alphanumeric value - is the backup ID for the backup. A backup ID is used to uniquely identify any backup Barman makes. In this case, it’s 20151111T051954. You will need the backup ID for the next steps
  • The third piece of information tells you when the backup was made
  • The fourth part is the size of the base backup (26.9 MB in this case)
  • The fifth and final part of the string gives the size of the the WAL archive backed up

To see more details about the backup, execute this command using the name of the server, and the backup ID (20151111T051954 in our example) from the previous command:

  1. barman show-backup main-db-server backup-id

A detailed set of information will be shown:

Output
Backup 20151111T051954: Server Name : main-db-server Status : DONE PostgreSQL Version : 90405 PGDATA directory : /var/lib/pgsql/9.4/data Base backup information: Disk usage : 26.9 MiB (26.9 MiB with WALs) Incremental size : 26.9 MiB (-0.00%) Timeline : 1 Begin WAL : 000000010000000000000002 End WAL : 000000010000000000000002 WAL number : 1 WAL compression ratio: 99.84% Begin time : 2015-11-11 05:19:44.438072-05:00 End time : 2015-11-11 05:19:46.839589-05:00 Begin Offset : 40 End Offset : 184 Begin XLOG : 0/2000028 End XLOG : 0/20000B8 WAL information: No of files : 0 Disk usage : 0 B Last available : 000000010000000000000002 Catalog information: Retention Policy : VALID Previous Backup : - (this is the oldest base backup) Next Backup : - (this is the latest base backup)

To drill down more to see what files go into the backup, run this command:

  1. barman list-files main-db-server backup-id

This will give a list of the base backup and WAL log files required to restore from that particular backup.

Step 9 — Scheduling Backups

Ideally your backups should happen automatically on a schedule.

In this step we’ll automate our backups, and we’ll tell Barman to perform maintenance on the backups so files older than the retention policy are deleted. To enable scheduling, run this command as the barman user on the barman-backup-server (switch to this user if necessary):

  1. crontab -e

This will open a crontab file for the user barman. Edit the file, add these lines, then save and exit:

cron
30 23 * * * /usr/bin/barman backup main-db-server
* * * * * /usr/bin/barman cron

The first command will run a full backup of the main-db-server every night at 11:30 PM. (If you used a different name for the server in the /etc/barman.conf file, use that name instead.)

The second command will run every minute and perform maintenance operations on both WAL files and base backup files.

Step 10 — Simulating a “Disaster”

You will now see how you can restore from the backup you just created. To test the restoration, let’s first simulate a “disaster” scenario where you have lost some data.

We’re dropping a table here. Don’t do this on a production database!

Go back to the main-db-server console and switch to the user postgres if it’s not already the current user.

Start the psql utility:

  1. psql

From the psql prompt, execute the following command to switch the database context to mytestdb:

  1. \connect mytestdb;

Next, list the tables in the database:

  1. \dt

The output will show the tables you created at the beginning of this tutorial:

Output
List of relations Schema | Name | Type | Owner --------+--------------+-------+---------- public | mytesttable1 | table | postgres public | mytesttable2 | table | postgres

Now, run this command to drop one of the tables:

  1. drop table mytesttable2;

If you now execute the \dt command again:

  1. \dt

You will see that only mytesttable1 remains.

This is the type of data loss situation where you would want to restore from a backup. In this case, you will restore the backup to a separate server: the standby-db-server.

Step 11 — Restoring or Migrating to a Remote Server

You can follow this section to restore a backup, or to migrate your latest PostgreSQL backup to a new server.

Go to the standby-db-server.

First, stop the PostgreSQL service as the sudo user. (The restart will choke if you try to run the restoration while the service is running.)

  1. sudo systemctl stop postgresql-9.4.service

Once the service stops, go to the barman-backup-server. Switch to the user barman if it’s not already the current user.

Let’s locate the details for the latest backup:

  1. barman show-backup main-db-server latest
Output
Backup 20160114T173552:
  Server Name            : main-db-server
  Status                 : DONE
  PostgreSQL Version     : 90405
  PGDATA directory       : /var/lib/pgsql/9.4/data

  Base backup information:

. . .

    Begin time           : 2016-01-14 17:35:53.164222-05:00
    End time             : 2016-01-14 17:35:55.054673-05:00

From the output, note down the backup ID printed on the first line (20160114T173552 above). If the latest backup has the data you want, you can use latest as the backup ID.

Also check when the backup was made, from the Begin time field (2016-01-14 17:35:53.164222-05:00 above).

Next, run this command to restore the specified backup from the barman-backup-server to the standby-db-server:

  1. barman recover --target-time "Begin time" --remote-ssh-command "ssh postgres@standby-db-server-ip" main-db-server backup-id /var/lib/pgsql/9.4/data

There are quite a few options, arguments, and variables here, so let’s explain them.

  • --target-time "Begin time": Use the begin time from the show-backup command
  • --remote-ssh-command "ssh postgres@standby-db-server-ip": Use the IP address of the standby-db-server
  • main-db-server: Use the name of the database server from your /etc/barman.conf file
  • backup-id: Use the backup ID from the show-backup command, or use latest if that’s the one you want
  • /var/lib/pgsql/9.4/data: The path where you want the backup to be restored. This path will become the new data directory for Postgres on the standby server. Here, we have chosen the default data directory for Postgres in CentOS. For real-life use cases, choose the appropriate path

For a successful restore operation, you should receive output like this:

Output from Barman Recovery
Starting remote restore for server  main-db-server using backup backup-id
Destination directory: /var/lib/pgsql/9.4/data
Doing PITR. Recovery target time: Begin time
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Identify dangerous settings in destination directory.

IMPORTANT
These settings have been modified to prevent data losses

postgresql.conf line 207: archive_command = false

Your PostgreSQL server has been successfully prepared for recovery!

Now switch to the standby-db-server console again. As the sudo user, start the PostgreSQL service:

  1. sudo systemctl start postgresql-9.4.service

That should be it!

Let’s verify that our database is up. Switch to user postgres and start the psql utility:

  1. sudo su - postgres
  2. psql

Switch the database context to mytestdb and list the tables in it:

  1. \connect mytestdb;
  2. \dt
Output
            List of relations
 Schema |     Name     | Type  |  Owner   
--------+--------------+-------+----------
 public | mytesttable1 | table | postgres
 public | mytesttable2 | table | postgres
(2 rows)

The list should show two tables in the database. In other words, you have just recovered the dropped table.

Depending on your larger recovery strategy, you may now want to fail over to the standby-db-server, or you may want to check that the restored database is working, and then run through this section again to restore to the main-db-server.

To restore to any other server, just make sure you’ve installed PostgreSQL and made the appropriate connections to the Barman server, and then follow this section using the IP address of your target recovery server.

Conclusion

In this tutorial we have seen how to install and configure Barman to back up a PostgreSQL server. We have also learned how to restore or migrate from these backups.

With careful consideration, Barman can become the central repository for all your PostgresSQL databases. It offers a robust backup mechanism and a simple command set. However, creating backups is only half the story. You should always validate your backups by restoring them to a different location. This exercise should be done periodically.

Some questions for fitting Barman into your backup strategy:

  • How many PostgreSQL instances will be backed up?
  • Is there enough disk space on the Barman server for hosting all the backups for a specified retention period? How can you monitor the server for space usage?
  • Should all the backups for different servers start at the same time or can they be staggered throughout off-peak period? Starting backups of all servers at the same time can put unnecessary strain on the Barman server and the network
  • Is the network speed between the Barman server and Postgres servers reliable?

Another point to be mindful of is that Barman cannot backup and restore individual databases. It works on the file system level and uses an all-or-nothing approach. During a backup, the whole instance with all its data files are backed up; when restoring, all those files are restored. Similarly, you can’t do schema-only or data-only backups with Barman.

We therefore recommend you design your backup strategy so it makes use of both logical backups with pg_dump or pg_dumpall and physical backups with Barman. That way, if you need to restore individual databases quickly, you can use pg_dump backups. For point-in-time recovery, use Barman backups.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors


Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

I have followed each and every step in this guide (checked it 4 or 6 times now) and when i run the ‘barman check’ command im getting the follwing WAL archive: FAILED (please make sure WAL shipping is setup) PostgreSQL: FAILED 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 archiver errors: OK

I can SSH from my 2 postgresql servers to the barman server (using the command ssh barman@pgbarman01) and from barman to the 2 postgresql servers (ssh postgres@pgpoc01 and ssh postgres@pgpoc02).I am doing this in RHEL 7.3 (does this actually matter though?). I am also using servers that I have on premise at my work if that helps.

Please let me know thanks.

As creators, developers and maintainers of Barman, we really thank you for this great tutorial. Very well done!

Tried the above procedure. but on running barman check postgres-server I keep getting POSTGRESQL: failed and WAL archive: FAILED (please make sure WAL shipping is setup) despite my repeated attempts and troubleshooting measures. Following is the barman.conf file [postgres-server] description = “postgres DB server” ssh_command = ssh barman@172.16.147.129 conninfo = host=172.16.147.129 user=postgres dbname=postgres retention_policy_mode = auto retention_policy = RECOVERY WINDOW OF 7 days wal_retention_policy = main archiver = on Pl identify the mistakes and provide relevant solutions A quick response would be highly appreciated

Good night people.

Has anyone got any error like this below in this logfiles?

2019-02-21 21: 42: 02,989 [11293] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ 2019-02-21 21: 42: 03,383 [11300] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 42: 03,592 [11302] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ No xlog segments found from file archival for server2 2019-02-21 21: 43: 02,236 [11676] barman.wal_archiver 2019-02-21 21: 43: 02,367 [11699] barman.wal_archiver INFO: No xlog segments found from file archival for simpliss. 2019-02-21 21: 43: 02,721 [11711] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 43: 02,887 [11713] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ 2019-02-21 21: 43: 03,249 [11717] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 43: 03,436 [11719] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ [12086] barman.wal_archiver INFO: No xlog segments found from file archival for server2 2019-02-21 21: 44: 02,176 [12086] 2019-02-21 21: 44: 02,248 [12111] barman.wal_archiver INFO: No xlog segments found from file archival for simpliss. 2019-02-21 21: 44: 02,636 [12124] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 44: 02,858 [12126] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ 2019-02-21 21: 44: 03,228 [12130] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 44: 03,382 [12132] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ 2019-02-21 21: 45: 02,825 [12566] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 45: 02,877 [12567] barman.wal_archiver INFO: No xlog segments found from file archival for server2 2019-02-21 21: 45: 02,980 [12570] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ 2019-02-21 21: 45: 02,990 [12568] barman.wal_archiver INFO: No xlog segments found from file archival for simpliss. 2019-02-21 21: 45: 03,269 [12576] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 45: 03,413 [12578] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ 21: 46: 02,476 [12994] barman.wal_archiver INFO: No xlog segments found from file archival for server2 2019-02-21 21: 46: 02,567 [13004] barman.wal_archiver INFO: No xlog segments found from file archival for simpliss. 2019-02-21 21: 46: 02,873 [13015] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 46: 03,084 [13017] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’ 2019-02-21 21: 46: 03,506 [13021] barman.cli ERROR: Unknown server ‘asiashipping - Asia Shipping PostgreSQL Database’ 2019-02-21 21: 46: 03,710 [13023] barman.cli ERROR: Unknown server ‘server1- server1PostgreSQL Database’

Before the days 01/31/2019 the logfiles were this way:

INFO: No xlog segments found from file archival for server2 2019-01-20 04: 02: 01,956 [12809] barman.wal_archiver 2019-01-20 04: 02: 02,073 [12810] barman.wal_archiver INFO: No xlog segments found from file archival for server1. [13039] barman.wal_archiver INFO: No xlog segments found from file archival for server2 2019-01-20 04: 03: 01,972 [13039] 2019-01-20 04: 03: 02,122 [13040] barman.wal_archiver INFO: Found 1 xlog segments from file archival for server1. Archive all segments in one run. Archived Segment 1 of 1 from file archival: server1 / 00000002000004D0000000D9 2019-01-20 04: 03: 02,122 [13040] barman.wal_archiver 2019-01-20 04: 04: 03,011 [13267] barman.wal_archiver INFO: Found 1 xlog segments from archive file for server2 Archive all segments in one run. [13267] barman.wal_archiver INFO: Archiving segment 1 of 1 from file archival: asiashipping / 000000010000030A000000DA 2019-01-20 04: 04: 03,012 [13268] barman.wal_archiver INFO: No xlog segments found from file archival for server1. [13485] barman.wal_archiver INFO: No xlog segments found from file archival for server2 2019-01-20 04: 05: 02,440 [13485] 2019-01-20 04: 05: 02,579 [13486] barman.wal_archiver INFO: No xlog segments found from file archival for server1. INFO: No xlog segments found from file archival for server2 2019-01-20 04: 06: 01,891 [13699] barman.wal_archiver 2019-01-20 04: 06: 02,046 [13700] barman.wal_archiver INFO: No xlog segments found from file archival for server1. NOTICE: No xlog segments found from file archival for server2 2019-01-20 04: 07: 02,334 [13915] barman.wal_archiver 2019-01-20 04: 07: 02,465 [13916] barman.wal_archiver INFO: No xlog segments found from file archival for server1. [14136] barman.wal_archiver INFO: No xlog segments found from file archival for server2 2019-01-20 04: 08: 01,841 [14136] 2019-01-20 04: 08: 01,898 [14138] barman.wal_archiver INFO: Found 1 xlo

I already researched some content on the web, but nothing like that. If anyone can help, I appreciate it.

I think I found a small mistake:

You write in the recovery section:

Also check when the backup was made, from the Begin time field (2016-01-14 17:35:53.164222-05:00 above).

Next, run this command to restore the specified backup from the barman-backup-server to the standby-db-server:

barman recover --target-time "Begin time"  --remote-ssh-command "ssh postgres@standby-db-server-ip"   main-db-server   backup-id   /var/lib/pgsql/9.4/data

There are quite a few options, arguments, and variables here, so let's explain them.

--target-time "Begin time": Use the begin time from the show-backup command

In the latest copy of the manual it states:

Point in time recovery
Barman wraps PostgreSQL’s Point-in-Time Recovery (PITR), allowing you to specify a recovery
target, either as a timestamp, as a restore label, or as a transaction ID.

IMPORTANT: The earliest PITR for a given backup is the end of the base backup itself.
If you want to recover at any point in time between the start and the end of a backup,
you must use the previous backup. From Barman 2.3 you can exit recovery when consistency
is reached by using --target-immediate option (available only for PostgreSQL 9.4 and
newer).

In other words

barman recover --target-time "Begin time" ....

would not work per the manual but

barman recover --target-time "End time" ....

would.

Hello!

When I do: barman show-server barman | grep incoming_wals_directory

Features: ERROR: Unknown server ‘barman’

My file barman.conf:

; Barman, Backup and Recovery Manager for PostgreSQL ; http://www.pgbarman.org/ - http://www.2ndQuadrant.com/ ; ; Main configuration file

[barman] ; System user barman_user = barman

; Directory of configuration files. Place your sections in separate files with .conf extension ; For example place the ‘main’ server section in /etc/barman.d/main.conf configuration_files_directory = /etc/barman.d

;Main directory barman_home = /var/lib/barman

; Locks directory - default: %(barman_home)s ;barman_lock_directory = /var/run/barman

; Log location log_file = /var/log/barman/barman.log ; Log level (see https://docs.python.org/3/library/logging.html#levels) log_level = INFO

; Default compression level: possible values are None (default), bzip2, gzip, pigz, pygzip or pybzip2 compression = gzip reuse_backup = link

; Pre/post backup hook scripts ;pre_backup_script = env | grep ^BARMAN ;pre_backup_retry_script = env | grep ^BARMAN ;post_backup_retry_script = env | grep ^BARMAN ;post_backup_script = env | grep ^BARMAN

; Pre/post archive hook scripts ;pre_archive_script = env | grep ^BARMAN ;pre_archive_retry_script = env | grep ^BARMAN ;post_archive_retry_script = env | grep ^BARMAN ;post_archive_script = env | grep ^BARMAN

; Global retention policy (REDUNDANCY or RECOVERY WINDOW) - default empty ;retention_policy =

; Global bandwidth limit in KBPS - default 0 (meaning no limit) ;bandwidth_limit = 4000

; Number of parallel jobs for backup and recovery via rsync (default 1) ;parallel_jobs = 1

; Immediate checkpoint for backup command - default false immediate_checkpoint = true

; Enable network compression for data transfers - default false ;network_compression = false

; Number of retries of data copy during base backup after an error - default 0 basebackup_retry_times = 3

; Number of seconds of wait after a failed copy, before retrying - default 30 basebackup_retry_sleep = 30

; Maximum execution time, in seconds, per server ; for a barman check command - default 30 ;check_timeout = 30

; Time frame that must contain the latest backup date. ; If the latest backup is older than the time frame, barman check ; command will report an error to the user. ; If empty, the latest backup is always considered valid. ; Syntax for this option is: “i (DAYS | WEEKS | MONTHS)” where i is an ; integer > 0 which identifies the number of days | weeks | months of ; validity of the latest backup for this check. Also known as ‘smelly backup’. last_backup_maximum_age = 1 DAYS

; 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

[barman] description = “barman” ssh_command = ssh postgres@10.0.0.4 conninfo = host=10.0.0.4 user=postgres retention_policy_mode = auto retention_policy = RECOVERY WINDOW OF 7 days wal_retention_policy = main archiver = on

Hi,

Thanks for the tutorial, I am using barman 2.3 , Everything works fine for me except the size of the incremental backup size . I am using rsync and link method to take incremental backup of the DB. My DB is size is 20 GB and executing barman every 10 minutes . but the size of the folder getting created under /var/lib/barman/base ranges from 300MB to 6 GB . Not sure what is wrong with my configuration.

I am attaching my configuration here

[labsdb] description = “Database - 10.30.30.99 (via Ssh)” ssh_command = ssh postgres@labsdb conninfo = host=labsdb user=postgres password=XXXX dbname=postgres port=5432 backup_method = rsync reuse_backup = link archiver = on wal_retention_policy = main retention_policy_mode = auto

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

for me it was the solution : run on barman server : barman switch-xlog --force --archive “Main DB Server”

Digital Ocean guys make me always like Linux and open source:

Thank you very much for a detailed tutorial. I have followed the steps up and until barman check main-db-server but i have an error. So can’t proceed.

Using Google cloud (Debian GNU/linux 8). Like others have raised, I have scanned through but still cannot find the solution to my problem. Using three servers, main, standby and barman server.

barman@barman-backup-server:~/main-db-server/incoming$ barman list-server WARNING: No archiver enabled for server ‘main-db-server’. Please turn on ‘archiver’, ‘streaming_archiver’ or both WARNING: Forcing ‘archiver = on’ main-db-server - Main DB Server barman@barman-backup-server:~/main-db-server/incoming$ barman check main-db-server WARNING: No archiver enabled for server ‘main-db-server’. Please turn on ‘archiver’, ‘streaming_archiver’ or both WARNING: Forcing ‘archiver = on’ Server main-db-server: WAL archive: FAILED (please make sure WAL shipping is setup) PostgreSQL: FAILED 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 archiver errors: FAILED (unknown: 1) barman@barman-backup-server:~/main-db-server/incoming$

Any direction would be really appreciated as I have to setup backup.

Very Nice Blog , Could you please also create a blog for setting barman using pg_receivexlog / streaming protocol .

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel