postgresql 增量备份

转载

mb5fe559619e363 2019-09-19 10:25:00

文章标签 postgresql hive bash sql vim 文章分类 代码人生

介绍:

barman是postgresql备份还原的管理工具。

本文环境:

系统: centos6.6

PostgreSQL 9.3.9

barman-1.4.1-1.rhel6.noarch.rpm

主机如下:

1	`<span style=``"font-size: 12px;"``>192.168.33.30 pgserver<br>192.168.33.31 backup<br><``/span``>`

其中pgserver与backup安装相同版本的pg

postgresql的安装见:http://my.oschina.net/firxiao/blog/295027

安装与配置:

1	`yum` `install` `rsync` `-y`

在backup上安装并配置barman:

配置epel源:

1	`curl firxiao.com``/sh/chrepo``.sh\|``bash`

安装barman:

1	`yum` `install` `-y http:``//nchc``.dl.sourceforge.net``/project/pgbarman/1``.4.1``/barman-1``.4.1-1.rhel6.noarch.rpm`

配置barman:

1 2	`mv` `/etc/barman``.conf` `/etc/barman``.conf.bak` `vim` `/etc/barman``.conf`

添加如下内容:

1 2 3 4 5 6 7 8 9 10	`[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`

创建配置文件目录

1	`mkdir` `/etc/barman``.d`

创建名字为pgserver的备份配置

1	`vim` `/etc/barman``.d``/pgserver``.conf`

添加如下内容

1 2 3 4 5 6	`[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`

各个参数的定义详见

1	`man` `5 barman`

配置双机ssh信任登录

使用ssh-copy-id 需要用到pgserver上的postgre用户密码及backup上barman用户密码

使用passwd 给予即可

配置完毕后使用passwd -d 删除密码

在pgserver上配置

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35	`[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上配置

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28	`[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:

1	`[root@pgserver ~]``# vim /var/lib/pgsql/9.3/data/postgresql.conf`

添加如下内容

1 2 3	`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服务器上使用

1 2	`#barman show-server pgserver\|grep "incoming_wals_directory"` `incoming_wals_directory:` `/var/lib/barman/pgserver/incoming`

查看并更改为那个目录

所以替换后应该是:

1 2 3	`wal_level =` `'archive'` `# For PostgreSQL >= 9.0` `archive_mode = on` `archive_command =` `'rsync -a %p barman@backup:/var/lib/barman/pgserver/incoming/%f'`

接下来配置backup可以免密码登录

1	`vim` `/var/lib/pgsql/9``.3``/data/pg_hba``.conf`

添加

1	`host all postgres 192.168.33.31``/32` `trust`

注意其中的IP地址为backup的地址

验证配置:

在backup上检查是否配置成功

1 2 3 4 5	`#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配置

1 2 3 4 5 6 7 8 9 10 11	`[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上执行

开始备份:

1 2 3 4 5 6 7 8 9 10 11 12 13	`[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`

列出备份:

1 2 3 4 5	`[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`

删除备份:

1 2 3 4	`[root@backup ~]``# barman delete pgserver 20150914T142547` `Deleting backup 20150914T142547` `for` `server pgserver` `Delete associated WAL segments:` `Done`

恢复备份:

数据库误删除丢失的时候就需要恢复了

1 2 3 4 5 6 7 8 9 10 11 12 13	`[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.`

这个操作会将数据库启动所需的文件复制到指定的目录然后使用命令启动即可

1 2 3 4 5 6 7 8 9 10 11 12 13 14	`-``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`