Postgresql Backup via Barman backup at Muthoot group

Environment

1.Live PG server - 192.168.1.133

2.Backup Server - 192.168.1.132

Software Used

Barman Backup Software for PostgreSql - http://www.pgbarman.org/

Document Refered - http://docs.pgbarman.org/

Installation process

please install barman on backup server i.e 192.168.1.132 Download the Rpm pakages from Sourceforge

wget http://downloads.sourceforge.net/project/pgbarman/1.1.1/barman-1.1.1-1.rhel6.noarch.rpm
wget http://downloads.sourceforge.net/project/pgbarman/rhel6-deps/python-argh-0.15.0-1.rhel6.noarch.rpm

Satisfy it dependecies

yum install python-argh
yum install python-argparse
yum install python-psycopg2

Install the Rpms

rpm -Uvh python-argh-0.15.0-1.rhel6.noarch.rpm barman-1.1.1-1.rhel6.noarch.rpm

Build Password less setup

SSH Password less bi-direction setup for postgres ( 192.168.1.133 ) and barman( 192.168.1.132 ) user You should be able to login on ssh from .133 with user postgres to .132 on barman user without password and Visa-Versa

Set the following in /etc/barman.conf

[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
; Pre/post backup hook scripts
;pre_backup_script = env | grep ^BARMAN
;post_backup_script = env | grep ^BARMAN
;; ; 'main' PostgreSQL Server configuration
[main]
;; ; Human readable description
description =  "Main PostgreSQL Database"
;;
;; ; SSH options
ssh_command = ssh postgres@192.168.1.133
;;
;; ; PostgreSQL connection string
conninfo = host=192.168.1.133 user=postgres

Also Ensure that .132 is trusted on main postgresql server .133 by adding the line in pg_hba.conf

host    all             all             192.168.1.132/32          trust

and Reload the config via

pg_ctl reload 

Make archieve on postgres server (.133) in postgresql.conf

archive_mode = on
archive_command = 'rsync -a %p barman@backup:INCOMING_WALS_DIRECTORY/%f'

Make sure you change the INCOMING_WALS_DIRECTORY placeholder with the value returned by the barman show-server main command (.132 via barman user) above.

In Our case it is /var/lib/barman/main/incoming/

Restart the Postgresql Server ( reloading will not help )

Now Check the status on barman server (.132)

barman show-server main

It should show something like below

Server main:
active: true
description: Main PostgreSQL Database
ssh_command: ssh postgres@192.168.1.133
conninfo: host=192.168.1.133 user=postgres
backup_directory: /var/lib/barman/main
basebackups_directory: /var/lib/barman/main/base
wals_directory: /var/lib/barman/main/wals
incoming_wals_directory: /var/lib/barman/main/incoming
lock_file: /var/lib/barman/main/main.lock
compression: gzip
custom_compression_filter: None
custom_decompression_filter: None
retention_policy: None
wal_retention_policy: None
pre_backup_script: None
post_backup_script: None
current_xlog: 00000001000000010000000E
last_shipped_wal: None
archive_command: rsync -a %p barman@192.168.1.132:/var/lib/barman/main/incoming/%f
server_txt_version: 8.4.13
data_directory: /var/lib/pgsql/data
archive_mode: on
config_file: /var/lib/pgsql/data/postgresql.conf
hba_file: /var/lib/pgsql/data/pg_hba.conf
ident_file: /var/lib/pgsql/data/pg_ident.conf

Checking the barman server

barman check main
Server main:
ssh: OK
PostgreSQL: OK
archive_mode: OK
archive_command: OK
directories: OK
compression settings: OK

In our case we have linked the main diecrtory of /var/lib/barman to /home/postreg_barman_backup/main as the home partion is very big to hold the backups

ln -s  /home/postreg_barman_backup/main /var/lib/barman/

Backup Process

As barman User on .132 server , running following command will take the backup

barman backup main

Crontab entry

To have backup at 1:00 , please have cron entry done via barman user for barman backup main command

Restoration

To restore a whole server issue the following command:

barman@backup$ barman recover main 20110920T185953 /path/to/recover/directory

where 20110920T185953 is the ID of the backup to be restored. When this command completes succesfully, /path/to/recover/directory contains a complete data directory ready to be started as a PostgreSQL database server.

For Remote Recovery

1. barman recover main 20110920T185953 --remote-ssh-command postgres@server-ip:/tmp/recover

Here is an example of a command that starts the server:

barman@backup$ pg_ctl -D /path/to/recover/directory start

1. pg_ctl Stop
2. pg_ctl -D /tmp/recover start

Important Commands

You can display the list of active servers that have been configured for your backup system with:

barman list-server

You can show the configuration parameters for a given server with: we are using main as the server name , you can have multiple servers too .

barman show-server main

You can list the catalogue of available backups for a given server with:

barman list-backup main

You can check if the connection to a given server is properly working with:

barman check main

Checking of the barman postgresql Backup

Login in 192.168.1.132 (the backup server of Muthoot)

su - barman 
barman list-backup main 

main 20121225T010001 - Tue Dec 25 01:04:37 2012 - Size: 2.0 GiB - WAL Size: 6.0 MiB
main 20121224T010001 - Mon Dec 24 01:04:34 2012 - Size: 2.0 GiB - WAL Size: 52.0 MiB
main 20121223T010002 - Sun Dec 23 01:04:35 2012 - Size: 2.0 GiB - WAL Size: 12.0 MiB
main 20121222T010001 - Sat Dec 22 01:04:33 2012 - Size: 2.0 GiB - WAL Size: 68.0 MiB
main 20121221T010002 - Fri Dec 21 01:04:33 2012 - Size: 2.0 GiB - WAL Size: 47.0 MiB
main 20121220T010001 - Thu Dec 20 01:04:31 2012 - Size: 2.0 GiB - WAL Size: 43.0 MiB
main 20121219T010001 - Wed Dec 19 01:04:31 2012 - Size: 2.0 GiB - WAL Size: 58.0 MiB
main 20121218T010001 - Tue Dec 18 01:04:31 2012 - Size: 2.0 GiB - WAL Size: 27.0 MiB

it should show the last few backups , else raise the alarm

Delete Last Barman Backup ( as barman user)

Script for auto deletion (delete-last-backup.sh)

#!/bin/bash
VALUE=`barman list-backup main | tail -1 | cut -d" " -f 2`
barman delete main $VALUE
exit

Make cron entry as barman user

Crontab -l 

0 1 * * * barman backup main
0 6 * * * /var/lib/barman/delete-last-backup.sh