barman_setup.md

PostgreSQL Barman Backups

Install PostgreSQL and the Barman dependencies

On the Barman server

sudo apt-get install build-essential postgresql-9.1 postgresql-server-dev-9.1 python-dev python-pip vim
sudo pip install argh psycopg2 python-dateutil==1.5

Setup the Barman user

sudo adduser barman
sudo gpasswd -a barman sudo

Download and install Barman

su - barman
wget --trust-server-name http://sourceforge.net/projects/pgbarman/files/1.0.0/barman-1.0.0.tar.gz/download
tar zxvf barman-1.0.0.tar.gz
cd barman-1.0.0/
./setup.py build
sudo ./setup.py install

Create a passwordless login sytem

Do not use passphrases

Connect to db servers and on each:

sudo passwd postgres

Connect to barman server as barman

cd ~
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]
ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]

Test passwordless connect on barman

ssh [email protected]

Test remote postgres connect

psql -c 'SELECT version()' -U postgres -h db.example.com

Tell barman not to freak out of host key changes because db.example.com was pointed somewhere else

Add to ~/.ssh/config

Host db.example.com
  StrictHostKeyChecking no
  UserKnownHostsFile=/dev/nul

Connect to each postgres server and create and copy keys

sudo su postgres
cd ~
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]

Test you are able to connect

ssh [email protected]

Setup Barman

Connect to barman server as barman

Setup directories and permissions

sudo mkdir /var/lib/barman
sudo chown barman:barman /var/lib/barman

sudo mkdir /var/log/barman
sudo chown root:barman /var/log/barman/
sudo chmod g+w /var/log/barman/

sudo cp barman-1.0.0/doc/barman.conf /etc/
sudo chown barman:barman /etc/barman.conf

Edit the (/etc/barman.conf) config

[barman]
; Main directory
barman_home = /var/lib/barman

; System user
barman_user = barman

; Log location
log_file = /var/log/barman/barman.log

; Default compression level: possible values are None (default), bzip2, gzip or custom
compression = gzip

; 'main' PostgreSQL Server configuration

[main]
; Human readable description
description =  "Main PostgreSQL Database"

; SSH options
ssh_command = ssh [email protected]

; PostgreSQL connection string
conninfo = host=db.example.com user=postgres password=protoss

Run barman for the new main server we created. make sure there are no errors in the config.

barman show-server main

Run barman and check that main is setup right

barman check main

Note

Note, you may need to turn on archving on master if you have not done so already.

If wal_level is already hot_standy, leave it the same, as hot_standby is a superset of archive.

If you need to make these changes:

Modify /etc/postgresql/9.1/main/postgresql.conf

• change
  • archive_mode = on
  • archive_command = 'rsync -a %p [email protected]:/var/lib/barman/main/incoming/%f'
    • ^^^ backup directory taken from barman show-server main
  • wal_level = archive sudo service postgresql restart

Run barman again and check that main is all OK

barman check main

Start your backup:

This is also what goes in cron (see below)

barman backup main

Check primary postgres server for errors

sudo less /var/log/postgresql/postgresql-9.1-main.log 

Recovery

Barman gives you two options for recovery.

Local recovery is good for restoring your database (possibly point in time) to get a pg_dump of some data that was accidentally deleted.

Remote recovery is good for when you want to get everything in the state it was at a certain point in time.

Remote:

barman recover --remote-ssh-command="ssh postgres@main" main 20120920T173752 /var/lib/postgresql/9.1/main

PG Dump

As root

mkdir /var/lib/barman/dumps
chown barman:barman /var/lib/barman/dumps

As postgres user on postgres servers

mkdir /var/lib/postgresql/backups

Create a file called /var/lib/postgresql/backup.sh with a+x

#!/bin/sh

# Backup all databases and then scp them to dbbackup

[email protected]
remotedir=/var/lib/barman/dumps
date=$(date +"%Y%m%dT%H%M%S")
outdir=/var/lib/postgresql/backups

psql -AtU postgres -c "SELECT datname FROM pg_database \
                          WHERE NOT datistemplate"| \
while read db; do
  outfile="${outdir}/${db}_${date}.dump"
  pg_dump -Fc $db > $outfile;
done

files=$outdir/*
for f in $files; do
  scp $f $remote:$remotedir 2>&1 > /dev/null && rm -f $f
done

Create a file on barman /home/barman/backup.sh

ssh [email protected] 'screen -S backup -d -m /var/lib/postgresql/backup.sh'

Cron

Cron on barman should look like this

25 2 * * 0 /usr/local/bin/barman backup main
0 5 * * * /home/barman/backup.sh