[PostgreSQL/PPAS] Barman - Backup and Recovery Manager

1.Barman:
Braman (Backup & Recovery Manager) - 是PostgreSQL資料庫伺服器非常方便的備份與還原工具，它可以遠端執行備份、集中管理備份及允許由一個備份還原資料庫。
它的原理是利用PostgreSQL的PITR來達到的，對PITR有興趣的"捧油"，可以到這裡去看看喔!!

2.環境說明:

172.16.1.89 - PostgreSQL Backup Server
172.16.1.90 - Zabbix Server
172.16.1.91 - PostgreSQL 8.2
172.16.1.92 - PostgreSQL 8.3
172.16.1.93 - PostgreSQL 8.4
172.16.1.94 - PostgreSQL 9.0
172.16.1.95 - PostgreSQL 9.1
172.16.1.96 - EDB PPAS 9.2

3.安裝:
先到Barman的官網 http://www.pgbarman.org 下載安裝包到 PostgreSQL Backup Server。
3.1 解壓縮。
# tar -zxvf barman-1.2.3.tar.gz
3.1 cd到 barman-1.2.3，開始build and install
# cd barman-1.2.3
# ./setup.py build
# ./setup.py install
3.3 由於barman是利用python來實作的，所以它有相依的python lib如下:
a). python-argh     b). python-dateutil    c). python-psycopg2
安裝步驟如下:
# wget http://mirror-fpt-telecom.fpt.net/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
# rpm -ivh epel-release-6-8.noarch.rpm     (optional)  選一個
# yum install epel-release-6-8.noarch.rpm  (optional)  選一個
# yum repolist
# yum -y install python-pip
# yum -y install python-argh python-dateutil python-psycopg2
# pip install argcomplete


安裝好後檢查一下argh的版本, 只要大於0.21.2就可以囉~~
# python
----------------------
Python 2.6.6 (r266:84292, Jun 18 2012, 14:18:47)
[GCC 4.4.6 20110731 (Red Hat 4.4.6-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import argh
>>> argh.__version__
'0.23.2'
----------------------

4.組態Backup Server及各個PG Server
4.1 新增User for Barman，這裡最好用postgres及enterprisedb，一個用來備postgreSQL一個用來備EnterpriseDB PPAS
# useradd postgres
# passwd postgres
# useradd enterprisedb
# passwd enterprisedb
# visudo  <-- p="" postgres="" root="">----------------------------------------------------------------------
root    ALL=(ALL)       ALL
postgres        ALL=(ALL)       ALL
enterprisedb    ALL=(ALL)       ALL
----------------------------------------------------------------------
4.2 su成該User，並設定barman所需的config檔，這裡只寫postgres，PPAS的自己發揮創意囉!!
# su - postgres
# cp barman-1.2.3/doc/barman.conf ~/.barman.conf
# vi ~/.barman.conf
----------------------------------------------------------------------
[barman]
barman_home = /home/postgres/barman
;barman_user = enterprisedb
barman_user = postgres
log_file = /home/postgres/barman_log/barman.log

[pg82]
description = "PostgreSQL 8.2 for TEST"
ssh_command = ssh postgres@172.16.1.91
conninfo = host=172.16.1.91 user=postgres dbname=postgres
minimum_redundancy = 1

[pg83]
description = "PostgreSQL 8.3 for TEST"
ssh_command = ssh postgres@172.16.1.92
conninfo = host=172.16.1.92 user=postgres dbname=postgres
minimum_redundancy = 1

[pg84]
description = "PostgreSQL 8.4 for TEST"
ssh_command = ssh postgres@172.16.1.93
conninfo = host=172.16.1.93 user=postgres dbname=postgres
minimum_redundancy = 1

[pg90]
description = "PostgreSQL 9.0 for TEST"
ssh_command = ssh postgres@172.16.1.94
conninfo = host=172.16.1.94 user=postgres dbname=postgres
minimum_redundancy = 1

[pg91]
description = "PostgreSQL 9.1 for TEST"
ssh_command = ssh postgres@172.16.1.95
conninfo = host=172.16.1.95 user=postgres dbname=postgres
minimum_redundancy = 1

----------------------------------------------------------------------
4.3 到各PG Server去開啟Archive  (這裡面的archive_command內的路徑會對齊下面5.2裡面的incoming path喔)，各版本的pg對archive各有不同的設定，請依對應的pg版本設定。
# vi /usr/local/pgsql/data/postgresql.conf
----------------------------------------------------------------------
wal_level = archive
archive_mode = on
archive_command = 'rsync -a %p postgres@backup_server:/home/postgres/barman/pgxx/incoming/%f'
----------------------------------------------------------------------

5.操作
5.1看目前加入備份的Server
# barman -c /home/postgres/.barman.conf list-server
----------------------------------------------------------------------
pg82 - PostgreSQL 8.2 for TEST
pg83 - PostgreSQL 8.3 for TEST
pg84 - PostgreSQL 8.4 for TEST
pg90 - PostgreSQL 9.0 for TEST
pg91 - PostgreSQL 9.1 for TEST
----------------------------------------------------------------------

5.2 Show所有或某個Server的狀態
# barman -c /home/postgres/.barman.conf show-server all
# barman -c /home/postgres/.barman.conf show-server server_name
----------------------------------------------------------------------
這裡面可以看到備份的目錄(red)，記得要mkdir喔
#mkdir -p /home/osdba/barman/pgxx/base
#mkdir -p /home/osdba/barman/pgxx/incoming
#mkdir -p /home/osdba/barman/pgxx/wals
Server pg91:
active: true
description: PostgreSQL 9.1 for TEST
ssh_command: ssh postgres@172.16.1.95
conninfo: host=172.16.1.95 user=postgres dbname=postgres
backup_directory: /home/postgres/barman/pg91
basebackups_directory: /home/postgres/barman/pg91/base
wals_directory: /home/postgres/barman/pg91/wals
incoming_wals_directory: /home/postgres/barman/pg91/incoming
lock_file: /home/postgres/barman/pg91/pg91.lock
compression: None
custom_compression_filter: None
custom_decompression_filter: None
retention_policy_mode: auto
retention_policy: None
wal_retention_policy: main
pre_backup_script: None
post_backup_script: None
minimum_redundancy: 1
bandwidth_limit: None
tablespace_bandwidth_limit: None
current_xlog: 000000010000000000000009
last_shipped_wal: 000000010000000000000008
archive_command: rsync -a %p postgres@172.16.1.89:/home/postgres/barman/pg91/incoming/%f
server_txt_version: 9.1.10
data_directory: /usr/local/pgsql/data
archive_mode: on
config_file: /usr/local/pgsql/data/postgresql.conf
hba_file: /usr/local/pgsql/data/pg_hba.conf
ident_file: /usr/local/pgsql/data/pg_ident.conf
----------------------------------------------------------------------

5.3 執行Backup
#  barman -c /home/postgres/.barman.conf backup all
#  barman -c /home/postgres/.barman.conf backup server_name
----------------------------------------------------------------------
Starting backup for server pg91 in /home/postgres/barman/pg91/base/20131021T095711
Backup start at xlog location: 0/5000020 (000000010000000000000005, 00000020)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup end at xlog location: 0/50000A0 (000000010000000000000005, 000000A0)
Backup completed
----------------------------------------------------------------------

5.4 列出所有的或某Server的備份
# barman -c /home/postgres/.barman.conf list-backup all
# barman -c /home/postgres/.barman.conf list-backup server_name
----------------------------------------------------------------------
#barman -c /home/postgres/.barman.conf list-backup pg91
pg91 20131021T100605 - Mon Oct 21 10:06:06 2013 - Size: 24.0 MiB - WAL Size: 0 B
pg91 20131021T100507 - Mon Oct 21 10:05:19 2013 - Size: 24.0 MiB - WAL Size: 0 B
pg91 20131021T095711 - Mon Oct 21 09:57:18 2013 - Size: 24.0 MiB - WAL Size: 0 B
pg91 20131018T143509 - Fri Oct 18 14:35:11 2013 - Size: 24.0 MiB - WAL Size: 0 B
----------------------------------------------------------------------

5.5 列出備份細節
# barman -c /home/postgres/.barman.conf show-backup server_name backup_id
----------------------------------------------------------------------
#barman -c /home/postgres/.barman.conf show-backup pg91 20131021T100605
Backup 20131021T100605:
  Server Name       : pg91
  Status            : DONE
  PostgreSQL Version: 90110
  PGDATA directory  : /usr/local/pgsql/data

  Base backup information:
    Disk usage      : 24.0 MiB
    Timeline        : 1
    Begin WAL       : 000000010000000000000008
    End WAL         : 000000010000000000000008
    WAL number      : 0
    Begin time      : 2013-10-21 10:06:05.382415
    End time        : 2013-10-21 10:06:06.972259
    Begin Offset    : 32
    End Offset      : 160
    Begin XLOG      : 0/8000020
    End XLOG        : 0/80000A0

  WAL information:
    No of files     : 0
    Disk usage      : 0 B
    Last available  : None

  Catalog information:
    Retention Policy: not enforced
    Previous Backup : 20131021T100507
    Next Backup     : - (this is the latest base backup)
----------------------------------------------------------------------

5.6 執行還原
# barman -c /home/postgres/.barman.conf recover server_name backup_id /path/to/your/pgdatadir
----------------------------------------------------------------------
#barman -c /home/postgres/.barman.conf recover pg91 20131021T100605 /home/postgres/testrecover
Processing xlog segments for pg91
000000010000000000000001
000000010000000000000002
000000010000000000000003
000000010000000000000003.00000020.backup
000000010000000000000004
000000010000000000000005
000000010000000000000005.00000020.backup
000000010000000000000006
000000010000000000000007
000000010000000000000007.00000020.backup
000000010000000000000008
000000010000000000000008.00000020.backup
Starting local restore for server pg91 using backup 20131021T100605
Destination directory: /home/postgres/testrecover
Copying the base backup.
Copying required wal segments.
The archive_command was set to 'false' to prevent data losses.

Your PostgreSQL server has been successfully prepared for recovery!

Please review network and archive related settings in the PostgreSQL
configuration file before starting the just recovered instance.
----------------------------------------------------------------------

6.排程Backup,每天零晨三點發動備份!!
# crontab -e
# 0 3 * * * barman -c /home/postgres/.barman.conf backup all

補充:
1. 因為Backup Server 要能做 full backup及 base backup，所以Backup Server到各PostgreSQL Server需能ssh連進，並且對於PostgreSQL的Data folder要有讀取權限，且能在DB內有執行start_base_backup()的權限。
基於以上原因，所以建議使用postgres來進行。
1.開通ssh無需pass
$$Backup Server$$
# su - postgres
# ssh-keygen -t rsa   (全部按enter)
# scp .ssh/id_rsa.pub postgres@pgxx_server:/tmp/

$$PG or EDB Server$$
# su - postgres
# ssh-keygen -t rsa   (全部按enter)
# scp .ssh/id_rsa.pub postgres@backup_server:/tmp/

$$Backup Server$$
cat /tmp/id_rsa.pub >> ~/.ssh/authorized_keys

$$PG or EDB Server$$
cat /tmp/id_rsa.pub >> ~/.ssh/authorized_keys