postgresql 增量备份
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了postgresql 增量备份，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9179字，纯文字阅读大概需要14分钟。
内容图文
介绍:
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
安装与配置:
在pgserver上安装rsync:
yum install rsync -y
在backup上安装并配置barman:
配置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信任登录:
使用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表示配置成功
配置pgserver中的postgresql:
[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 增量备份
标签：
本文系统来源：http://my.oschina.net/firxiao/blog/505951
内容总结
以上是互联网集市为您收集整理的postgresql 增量备份全部内容，希望文章能够帮你解决postgresql 增量备份所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】