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.

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.

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

barman@backup$ pg_ctl -D /path/to/recover/directory 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