【轉載】使用barman備份PostgreSQL

標籤：

什麼是barman

Barman (備份和復原管理員) 是 PostgreSQL 資料庫伺服器中非常方便的備份和恢複工具，允許遠程備份多個伺服器，允許從一個備份組中一個命令就恢複資料庫。同時還可以對多個備份組進行方便的管理.

環境說明

環境有兩台機器:

• 192.168.1.21 主要資料庫所在主機
• 192.168.1.22 為barman備份機

安裝barman

到barman的官方網站http://www.pgbarman.org/下載barman安裝包,把安裝包拷貝到192.168.1.22機器上,解壓:

tar xvf barman-1.2.1.tar.gz

然後進入目錄,進行安裝:

cd barman-1.2.1python setup.py buildsudo python setup.py install

安裝barman需要的附加的包:

sudo aptitude install python-arghsudo aptitude install python-psycopg2sudo aptitude install python-dateutilsudo pip install argcomplete

檢測argh的版本

[email protected]:~$ pythonPython 2.7.4 (default, Apr 19 2013, 18:28:01)[GCC 4.7.3] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import argh>>> argh.__version__‘0.17.2‘

barman要求argh >= 0.21.2,版本太低,升級版本:

[email protected]:~$ sudo pip install --upgrade arghDownloading/unpacking argh from https://pypi.python.org/packages/source/a/argh/argh-0.23.2.tar.gz#md5=0d026a85bdfb5dd895138b274a3fc665 Downloading argh-0.23.2.tar.gz Running setup.py egg_info for package arghInstalling collected packages: argh Found existing installation: argh 0.17.2   Uninstalling argh:     Successfully uninstalled argh Running setup.py install for arghSuccessfully installed arghCleaning up...[email protected]:~$ pythonPython 2.7.4 (default, Apr 19 2013, 18:28:01)[GCC 4.7.3] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import argh>>> argh.__version__‘0.23.2‘

配置barman配置要備份的主庫

到192.168.1.21配置主庫的postgresql.conf檔案,開啟主庫的歸檔模式,同時修改archive_command的配置,配置內容如下:

wal_level = archivearchive_mode = onarchive_command = ‘rsync -a %p [email protected]:/home/osdba/barman/main/incoming/%f‘

注意到上面的歸檔命令是把WAL日誌拷貝到192.168.1.22機器的/home/osdba/barman/main/incoming/目錄下,所以要先在備份機上建這個目錄:

mkdir -p /home/osdba/barman/main/incoming 

當然你也可以使用其它的目錄. 配置完後,需要重啟啟動主庫.

打通備份機與資料庫機器之間的ssh通道

barman需要打通兩台機器之間的ssh通道,假設我們的使用者是osdba,方法如下: 先進入192.168.1.21機器上:

[email protected]:~/$ cd .ssh[email protected]:~/.ssh$ cat id_rsa.pub >> authorized_keysscp authorized_keys 192.168.1.22:`pwd`

再進入192.168.1.22機器上:

[email protected]:~/$ cd .ssh[email protected]:~/.ssh$ cat id_rsa.pub >> authorized_keysscp authorized_keys 192.168.1.21:`pwd`/.

實際上上面的操作就是產生一個authroized_keys檔案,其中存有192.168.1.21和192.168.1.22兩台機器上id_rsa.pub檔案的內容.

把下面的內容加到這兩台機器的/etc/hosts檔案中:

192.168.1.21 pg192.168.1.22 backup

測試是否能互相ssh: 在192.168.1.21上:

[email protected]:~/.ssh$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/Last login: Sat Jun 22 16:02:07 2013 from pg [email protected]:~$ exitlogout Connection to backup closed.[email protected]:~/.ssh$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/45 packages can be updated. 13 updates are security updates.Last login: Sat Jun 22 16:01:53 2013 from pg[email protected]:~$ exitlogout Connection to pg closed.

在192.168.1.22上:

[email protected]:~$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/45 packages can be updated. 13 updates are security updates.Last login: Sat Jun 22 16:02:21 2013 from pg[email protected]:~$ exitlogout Connection to pg closed. [email protected]:~$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/Last login: Sat Jun 22 16:02:12 2013 from pg[email protected]:~$ exitlogout Connection to backup closed.

設定barman的設定檔

在192.168.1.22從模板中拷貝一個barman的設定檔,在此模板的基礎上進行修改:

[email protected]:~/src/barman-1.2.1/doc$ cp barman.conf ~/.barman.conf [email protected]:~/src/barman-1.2.1/doc$ vi ~/.barman.conf

把.barman.conf檔案中內容修改為:

 [barman] ;  Main directory barman_home = /home/osdba/barman; System user barman_user = osdba; Log locationlog_file = /home/osdba/barman/log[main] ; ; Human readable description description = "Main PostgreSQL Database"; SSH options ; ssh_command = ssh [email protected] ssh_command = ssh [email protected]; PostgreSQL connection stringconninfo = host=pg user=osdba dbname=master; Minimum number of required backups (redundancy)minimum_redundancy = 1

配置完後,barman需要在192.168.1.22機器上串連到192.168.1.21上的資料庫,在設定檔中配置的串連串為: conninfo = host=pg user=osdba dbname=master, 一般我們還有密碼,為了不輸入密碼,我們把密碼配置到~/.pgpass檔案中:

[email protected]:~/src/barman-1.2.1/doc$ cat ~/.pgpasspg:5432:master:osdba:XXXXXX 

上面內容”XXXXXX”是資料庫使用者osdba的密碼,當然你也可以用其它的超用使用者串連PostgreSQL,這裡使用的是”osdba”使用者.

備份資料庫查看要備份的資料庫

使用barman show-server main查看要備份的資料庫的情況: [email protected]:~/barman$ barman show-server main

Server main:active: truedescription: Main PostgreSQL Databasessh_command: ssh [email protected]conninfo: host=pg user=osdba dbname=masterbackup_directory: /home/osdba/barman/mainbasebackups_directory: /home/osdba/barman/main/basewals_directory: /home/osdba/barman/main/walsincoming_wals_directory: /home/osdba/barman/main/incominglock_file: /home/osdba/barman/main/main.lockcompression: Nonecustom_compression_filter: Nonecustom_decompression_filter: Noneretention_policy_mode: autoretention_policy: Nonewal_retention_policy: mainpre_backup_script: Nonepost_backup_script: Noneminimum_redundancy: 1bandwidth_limit: Nonetablespace_bandwidth_limit: Nonecurrent_xlog: 000000010000000000000005last_shipped_wal: Nonearchive_command: rsync -a %p [email protected]:/home/osdba/barman/main/incoming/%fserver_txt_version: 9.2.3data_directory: /home/osdba/pgdataarchive_mode: onconfig_file: /home/osdba/pgdata/postgresql.confhba_file: /home/osdba/pgdata/pg_hba.confident_file: /home/osdba/pgdata/pg_ident.conf

執行備份

Starting backup for server main in /home/osdba/barman/main/base/20130622T120619Backup start at xlog location: 0/B000020 (00000001000000000000000B, 00000020)Copying files.Copy done.Asking PostgreSQL server to finalize the backup.Backup end at xlog location: 0/B0000A8 (00000001000000000000000B, 000000A8)Backup completed

查看備份結果

[email protected]:~$ barman list-backup mainmain 20130622T120619 - Sat Jun 22 12:06:25 2013 - Size: 25.0 MiB - WAL Size: 0 B 

恢複資料庫執行恢複

做恢複測試,把資料庫恢複到/home/osdba/pgd2目錄下:

[email protected]:~$ barman recover main 20130622T120619 /home/osdba/pgd2Processing xlog segments for main000000010000000000000005000000010000000000000006000000010000000000000006.00000020.backup000000010000000000000007000000010000000000000008000000010000000000000008.00000020.backup00000001000000000000000900000001000000000000000A00000001000000000000000A.00000020.backup00000001000000000000000B00000001000000000000000B.00000020.backupStarting local restore for server main using backup 20130622T120619Destination directory: /home/osdba/pgd2Copying 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 PostgreSQLconfiguration file before starting the just recovered instance.

啟動恢複的資料庫

[email protected]:~$ pg_ctl -D /home/osdba/pgd2 start server starting[email protected]:~$

檢查恢複的資料庫:

[email protected]:~$ psql postgrespsql (9.2.3)Type "help" for help.postgres=# \l                             List of databases  Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges-----------+-------+----------+-------------+-------------+-------------------master    | slony | UTF8     | en_US.UTF-8 | en_US.UTF-8 |postgres  | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 |template0 | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/osdba         +          |       |          |             |             | osdba=CTc/osdbatemplate1 | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/osdba         +          |       |          |             |             | osdba=CTc/osdba(4 rows)

停止恢複的資料庫

[email protected]:~$ pg_ctl -D /home/osdba/pgd2 stopwaiting for server to shut down....... doneserver stopped

【轉載】使用barman備份PostgreSQL