Backup via barman

Thux uses barman as the backup manager for PostgreSQL.

We have 2 main backup server, one for old 32bit architectures and one for 64bit.

Barman has a a very rich manual page and a complete man page for all the options.

In this page, I’ll just focus on the choice we’ve adopted and mainly on the restore command needed to get a working setup ASAP. You may read a description of our barman setup here.

Restore a database from backup

Remember that:

  • we need same architecture (32/64 bit)
  • we need same postgresql version (9.6, 10, …)
  • we backup and restore whole pg_clusters, i.e. set of databases handled by a single instance of postgres answering at a specified TCP port
  • we preferably use streaming wal backup, that allows us to reach almost RPO=0 (RPO is Recovery Point Objective (RPO), i.e.: “maximum targeted period in which data might be lost from an IT service due to a major incident”)

Each pgcluster backed-up by barman has a label used in any command. For our labels go and find the correct one in our internal project server

Let’s name remote_server the server where we want to restore a backup and my_label the barman label to name the cluster

We need to issues commands

  • on the backup server, to create the data directiory on the remote server
  • on the (new) remote server, to recreate the pg_cluster

Commands on the barman backup server

Here’s what you are supposed to issue as root on barman server:

barman recover <my_label> <backup_id> /path/to/recover/dir

A tipical command may include a point in time where we want to stop the recovery process and a remote command to rebuild the db remotely:

LABEL=my_label
DATE=$(date '+%Y-%m-%d %H:%M:%S')
OPTS='--remote-ssh-command "ssh postgres@remote_server"'
CLUSTER_NAME=bck_prod
DEST_DIR=/var/lib/postgresql/10/$CLUSTER_NAME
BCK_ID=last
barman recover $OPTS --target-time "$DATE" $LABEL $BCK_ID $DEST_DIR

Where DATE is the point in time where you want to stop the recovery, others methods are available

Note

choose DEST_DIR basename as the cluster’s name you want to create (main, prod, backup…)

Note

BCK_ID and --target-time must be choosen so that target time follows the begin_time of the backup with id BCK_ID

Due to a limitation of barman 2.3, we also need to manually copy the file that hold all streamed information that are not yet consolidated in a wal:

PARTIAL_WAL_FILE=$(find /var/lib/barman/$my_label/streaming -name \*partial)
DEST_WAL=/var/lib/postgresql/10/$my_label/barman_xlog/${partial_file%.partial}
scp $PARTIAL_WAL_FILE postgres@remote_server:$DEST_WAL

Commands on the (new) postgresql server

Remember you’re not restoring a database, but a pg_cluster, that is an instance of PostgreSQL running on a specified port identifien by a version (9.6, 10, …) and a name (main, prod…)

The choice we made to user backup_method = rsync (as opposed to postgres) reflects in the fact that the configuration of the old db (postgresql.conf, pg_hba.conf, ident.conf) are already copied in the new datadir, so that starting the cluster is as simple as:

CLUSTER_NAME=bck_prod
DEST_DIR=/var/lib/postgresql/10/$CLUSTER_NAME
PORT_OPT='-p 5434'
pg_createcluster --start $PORT_OPT -d $DEST_DIR

At this point the cluster starts replaying the logs according to the informations if find in the file recover.conf that was created by barman in the previous trep. According to dimentions of data and logs the recover phase may last very little or longer.