Editing Postgresql Backup via Barman backup at Muthoot group

[[category:Tetra Clients]]
[[category:Installation]]
[[category:Muthoot group]]

== Backup Implemenation at Muthoot Group 's Live PostgreSql Server (RHEL 6) ==

==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

#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

#pg_ctl Stop
#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