bgerm/barman_setup.md

## barman_setup.md

      
    Raw
  

              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 postgres@db1.example.com
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@db2.example.com

Test passwordless connect on barman

ssh postgres@db.example.com

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 barman@dbbackup.example.com

Test you are able to connect

ssh barman@dbbackup.example.com

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 postgres@db.example.com

; 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 barman@dbbackup.example.com:/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

remote=barman@dbbackup.example.com
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 postgres@db.example.com '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