时间:2021-07-01 10:21:17 帮助过:9人阅读
barman是postgresql备份还原的管理工具。官网: http://www.pgbarman.org/
系统: centos6.6
PostgreSQL 9.3.9
barman-1.4.1-1.rhel6.noarch.rpm
主机如下:
- 192.168.33.30 pgserver
- 192.168.33.31 backup
其中pgserver与backup安装相同版本的pg
postgresql的安装见:http://my.oschina.net/firxiao/blog/295027
- yum install rsync -y
配置epel源:
- curl firxiao.com/sh/chrepo.sh|bash
安装barman:
- yum install -y http://nchc.dl.sourceforge.net/project/pgbarman/1.4.1/barman-1.4.1-1.rhel6.noarch.rpm
配置barman:
- mv /etc/barman.conf /etc/barman.conf.bak
- vim /etc/barman.conf
添加如下内容:
- [barman]
- barman_home = /var/lib/barman
- barman_user = barman
- log_file = /var/log/barman/barman.log
- configuration_files_directory = /etc/barman.d
- compression = gzip
- reuse_backup = link
- bandwidth_limit = 4000
- basebackup_retry_times = 3
- basebackup_retry_sleep = 30
创建配置文件目录
- mkdir /etc/barman.d
创建名字为pgserver的备份配置
- vim /etc/barman.d/pgserver.conf
添加如下内容
- [pgserver]
- description = "The pgserver PostgreSQL Database"
- ssh_command = ssh postgres@pgserver
- conninfo = host=pgserver user=postgres
- minimum_redundancy = 1
- retention_policy = RECOVERY WINDOW OF 4 WEEKS
各个参数的定义详见
- man 5 barman
使用ssh-copy-id 需要用到pgserver上的postgre用户密码及backup上barman用户密码
使用passwd 给予即可
配置完毕后使用passwd -d 删除密码
在pgserver上配置
- [root@pgserver ~]# mkdir /var/lib/pgsql/.ssh
- [root@pgserver ~]# chown postgres /var/lib/pgsql/.ssh
- [root@pgserver ~]# su - postgres
- -bash-4.1$ ssh-keygen
- Generating public/private rsa key pair.
- Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
- Enter passphrase (empty for no passphrase):
- Enter same passphrase again:
- Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
- Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
- The key fingerprint is:
- 5b:bf:25:02:1c:36:e3:b9:98:42:5a:85:27:d4:03:92 postgres@pgserver
- The key‘s randomart image is:
- +--[ RSA 2048]----+
- | ..oo |
- | Eo .o |
- | o o.= |
- | + + = |
- | o S . |
- | + o = . |
- | . . o o . o . |
- | . . + |
- | . |
- +-----------------+
- -bash-4.1$ ssh-copy-id barman@backup
- The authenticity of host ‘backup (192.168.33.31)‘ can‘t be established.
- RSA key fingerprint is f3:48:30:89:03:76:cb:04:19:7a:fe:8d:6c:90:e4:fe.
- Are you sure you want to continue connecting (yes/no)? yes
- Warning: Permanently added ‘backup,192.168.33.31‘ (RSA) to the list of known hosts.
- barman@backup‘s password:
- Now try logging into the machine, with "ssh ‘barman@backup‘", and check in:
- .ssh/authorized_keys
- to make sure we haven‘t added extra keys that you weren‘t expecting.
- -bash-4.1$ ssh barman@backup
不输入密码即可登录backup表示配置成功
在backup上配置
- [root@backup ~]# su - barman
- -bash-4.1$ ssh-keygen
- Generating public/private rsa key pair.
- Enter file in which to save the key (/var/lib/barman/.ssh/id_rsa):
- Enter passphrase (empty for no passphrase):
- Enter same passphrase again:
- Your identification has been saved in /var/lib/barman/.ssh/id_rsa.
- Your public key has been saved in /var/lib/barman/.ssh/id_rsa.pub.
- The key fingerprint is:
- 59:f3:8a:23:56:b5:00:b2:40:e2:a9:fb:ea:95:1d:b8 barman@backup
- The key‘s randomart image is:
- +--[ RSA 2048]----+
- |..o . . |
- |.... o . |
- | o . . + |
- |. . = + |
- |. . . S . . |
- | . + .. . . |
- |. E .o o . |
- | .. . . . |
- |oo. |
- +-----------------+
- -bash-4.1$ ssh-copy-id postgres@pgserver
- postgres@pgserver‘s password:
- Now try logging into the machine, with "ssh ‘postgres@pgserver‘", and check in:
- .ssh/authorized_keys
- to make sure we haven‘t added extra keys that you weren‘t expecting.
- -bash-4.1$ ssh postgres@pgserver
不输入密码即可登录pgserver表示配置成功
- [root@pgserver ~]# vim /var/lib/pgsql/9.3/data/postgresql.conf
添加如下内容
- wal_level = ‘archive‘ # For PostgreSQL >= 9.0
- archive_mode = on
- archive_command = ‘rsync -a %p barman@backup:INCOMING_WALS_DIRECTORY/%f‘
其中INCOMING_WALS_DIRECTORY 可以在backup服务器上使用
- #barman show-server pgserver|grep "incoming_wals_directory"
- incoming_wals_directory: /var/lib/barman/pgserver/incoming
查看 并更改为那个目录
所以替换后应该是:
- wal_level = ‘archive‘ # For PostgreSQL >= 9.0
- archive_mode = on
- archive_command = ‘rsync -a %p barman@backup:/var/lib/barman/pgserver/incoming/%f‘
接下来配置backup可以免密码登录
- vim /var/lib/pgsql/9.3/data/pg_hba.conf
添加
- host all postgres 192.168.33.31/32 trust
注意其中的IP地址为backup的地址
在backup上检查是否配置成功
- #psql -c ‘SELECT version()‘ -U postgres -h pgserver
- version
- ---------------------------------------------------------------------------------------------------------------
- PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
- (1 行记录)
验证barman配置
- [root@backup ~]# barman check pgserver
- Server pgserver:
- ssh: OK
- PostgreSQL: OK
- archive_mode: OK
- archive_command: OK
- directories: OK
- retention policy settings: OK
- backup maximum age: OK (no last_backup_maximum_age provided)
- compression settings: OK
- minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
最后一行为检查最小保存备份数,因为还没有备份所以为0,其他的都为ok就可以了
以下操作均在backup上执行
- [root@backup ~]# barman backup pgserver
- Starting backup for server pgserver in /var/lib/barman/pgserver/base/20150914T151454
- Backup start at xlog location: 0/6000028 (000000010000000000000006, 00000028)
- Copying files.
- Copy done.
- Backup size: 32.1 MiB. Actual size on disk: 141.8 KiB (-99.57% deduplication ratio).
- Asking PostgreSQL server to finalize the backup.
- Backup end at xlog location: 0/60000B8 (000000010000000000000006, 000000B8)
- Backup completed
- Processing xlog segments for pgserver
- 000000010000000000000005
- 000000010000000000000006
- 000000010000000000000006.00000028.backup
- [root@backup ~]# barman list-backup pgserver
- pgserver 20150914T151454 - Mon Sep 14 15:14:55 2015 - Size: 32.1 MiB - WAL Size: 0 B
- pgserver 20150914T143210 - Mon Sep 14 14:32:11 2015 - Size: 32.1 MiB - WAL Size: 32.1 KiB
- pgserver 20150914T142931 - Mon Sep 14 14:29:41 2015 - Size: 32.1 MiB - WAL Size: 1.9 MiB
- pgserver 20150914T142547 - FAILED
- [root@backup ~]# barman delete pgserver 20150914T142547
- Deleting backup 20150914T142547 for server pgserver
- Delete associated WAL segments:
- Done
数据库误删除 丢失的时候 就需要恢复了
- [root@backup ~]# su - barman
- -bash-4.1$ barman recover pgserver 20150914T142931 /tmp/pgserver_recover
- Starting local restore for server pgserver using backup 20150914T142931
- Destination directory: /tmp/pgserver_recover
- Copying the base backup.
- Copying required wal segments.
- Generating archive status files
- Disabling dangerous settings in destination directory.
- 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.
- Recovery completed successful.
这个操作会将数据库启动所需的文件复制到指定的目录然后使用命令启动即可
- -bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl -D /tmp/pg_recover/ start
- server starting
- -bash-4.1$ < 2015-09-14 15:52:06.384 CST >LOG: redirecting log output to logging collector process
- < 2015-09-14 15:52:06.384 CST >HINT: Future log output will appear in directory "pg_log".
- -bash-4.1$ ps -ef|grep postgres
- barman 4547 1 0 15:52 pts/0 00:00:00 /usr/pgsql-9.3/bin/postgres -D /tmp/pg_recover
- barman 4548 4547 0 15:52 ? 00:00:00 postgres: logger process
- barman 4550 4547 0 15:52 ? 00:00:00 postgres: checkpointer process
- barman 4551 4547 0 15:52 ? 00:00:00 postgres: writer process
- barman 4552 4547 0 15:52 ? 00:00:00 postgres: wal writer process
- barman 4553 4547 0 15:52 ? 00:00:00 postgres: autovacuum launcher process
- barman 4554 4547 0 15:52 ? 00:00:00 postgres: archiver process failed on 000000010000000000000009
- barman 4555 4547 0 15:52 ? 00:00:00 postgres: stats collector process
- barman 4560 4434 0 15:52 pts/0 00:00:00 grep postgres
连上上这个数据库,从中找出你丢失的数据 然后恢复至pgserver中吧
停掉数据库:
- -bash-4.1$ /usr/pgsql-9.3/bin/pg_ctl -D /tmp/pg_recover/ stop
- waiting for server to shut down....... done
- server stopped
利用cron实现定时备份
- #crontab -e
添加如下信息即可:
- */5 * * * * /usr/bin/barman backup pgserver >/dev/null 2>&1
(5分钟备份一次)
到此已经完成了barman的基本配置及备份还原。
安装及配置: http://docs.pgbarman.org/#installation
更多barman配置及使用详见官方文档: http://docs.pgbarman.org/
postgresql 增量备份
标签: