Simulate replication slots with Barman and PostgreSQL 9.3

Physical replication slots were introduced in PostgreSQL 9.4 where they are used to manage WAL retention. With older PostgreSQL versions Barman requires that WAL archiving be enabled with the barman archive_command even if streaming is active. However, in some environments, SSH connection from PostgreSQL to Barman ("push") is not allowed for security reasons, while the other way around ("pull") is. This article highlights a workaround to this problem.

IMPORTANT: this article applies only to a very specific use case, where:

• Barman is used to backup a PostgreSQL 9.3 (or 9.2) database instance
• SSH connection is allowed only from the Barman server (barman user) to the PostgreSQL server (postgres user). SSH connections from PostgreSQL to the Barman server are forbidden for security reasons.
• WAL streaming for Barman is configured and working (the streaming_barman user in PostgreSQL can connect for streaming replication purposes, with proper pg_hba.conf settings)
• PostgreSQL's archive_command cannot be used to ship WAL files directly to the Barman server, as this is forbidden for security reasons
• Base backup takes place with either rsync or postgres as copy_method

As pointed out in Barman's documentation, relying on WAL streaming without also using archiving is not entirely safe (check Barman's feature matrix). Indeed, in case of temporary network problems, PostgreSQL before 9.4 has no means to keep those WAL files that Barman needs to keep the stream of WALs continuous for that instance. On 9.4, physical replication slots meet this need, making it safe to use streaming alone.

By taking advantage of PostgreSQL's reliable WAL archiver's process and Barman's flexible hook scripts facility, we can deploy a quick workaround that enables us to simulate replication slots behaviour for a Barman WAL streamer.

IMPORTANT: Keep in mind that this is a workaround. We always suggest and encourage to upgrade PostgreSQL to the latest major version. Do not use this workaround on PostgreSQL 9.4 or higher, as Barman has native support for physical replication slots.

The main idea is to use the post WAL archive hook script to somehow inform PostgreSQL that the WAL file that has been received by Barman via streaming replication has been correctly archived. Specifically, via SSH we will be depositing in a folder (we will call it WAL safe directory) a file that clearly identifies the WAL file.

At the same time, we will instruct the archive_command to consider a WAL file correctly archived if the WAL safe directory contains a file with the same name. This way we can reliably ask PostgreSQL to keep all required WAL files by the Barman server.

This workaround requires that backup_options contains concurrent_backup. This is the default for copy_method = postgres. If you use rsync, you need to install the pgespresso extension. More information on concurrent backups with Barman is available on the documentation.

Let's proceed with the implementation.

In the PostgreSQL server, as postgres user create a directory where we will be keeping track of the files that have been correctly archived by Barman. For the sake of simplicity, we chose ~/.wal_safe_dir in the postgres user's home directory. You can choose another one, as long as it can be written by the postgres user.

mkdir ~/.wal_safe_dir
chmod 700 ~/.wal_safe_dir

Make sure that PostgreSQL's archive mode is enabled (this requires a restart) and that archive_command is set as below:

archive_mode = on
# Either wait for hook script to deposit a %f.done file or skip if '.backup' label file
# NOTE: if you prefer, you can create wrap the following command in a bash script
archive_command = '(test -f ~/.wal_safe_dir/%f.done && rm -f ~/.wal_safe_dir/%f.done) || (echo "%f" | grep -q ".backup")'

Once you reload PostgreSQL, the new archive_command will become effective and PostgreSQL will start keeping WAL files in its pg_xlog directory. We now need to configure Barman to notify the PostgreSQL archiver.

For the sake of simplicity, the server where PostgreSQL is installed is named pg, and we will be using it in the following examples.

We suggest to install in the Barman server the latest major version's client libraries for PostgreSQL (currently version 10). pg_receivewal 10 is compatible with PostgreSQL 9.3.

The content of the /etc/barman.d/pg.conf file looks like this:

[pg]
description = "PostgreSQL 9.3 Database"
conninfo = host=pg user=barman dbname=postgres
streaming_conninfo = host=pg user=streaming_barman
backup_options = concurrent_backup

# Remember that rsync requires 'pgespresso installed in the database pointed by conninfo
backup_method = rsync

# This is the retry hook script that allows us to 'trick' PostgreSQL's archive_command
post_archive_retry_script = ssh postgres@pg touch .wal_safe_dir/${BARMAN_SEGMENT}.done

# Use PostgreSQL 10 client
path_prefix = "/usr/pgsql-10/bin"

# Enable streaming archiver and a 'fake' archiver
archiver = on
streaming_archiver = on

Concurrent backup is required, therefore you need to install pgespresso in the PostgreSQL server. pgespresso is fully supported by 2ndQuadrant and it is available via community repositories. You can install it with yum or apt depending on the distribution you use.

All you need to do is connect to the database identified by the conninfo string in Barman and execute:

CREATE EXTENSION pgespresso;

Shortcut: You can automatically get the connection string using the show-server command and pass it to psql as follows:

psql "$(barman show-server pg | grep -P '^\tconninfo' | cut -f 2 -d ':')"