pg_rman备份

pg_rman是一款专门为postgresql设计的在线备份恢复的工具。其支持在线和基于时间点备份方式，还可以通过创建backup catalog来维护DB cluster备份信息。

1. pg_rman使用的是pg_start_backup(), copy, pg_stop_backup()的备份模式。

2. pg_rman跑的不是流复制协议，而是文件拷贝，所以pg_rman必须和数据库节点跑在一起。

3. 如果在standby节点跑pg_rman，pg_rman则需要通过网络连接到主节点执行pg_start_backup和pg_stop_backup。

4. pg_rman只从postgresql.conf取log_directory和archive_command参数的值。

1、pg_rman特点：

· 使用简单.一个命令即可完成备份和恢复.

· 支持在线全备,增量备份，归档备份.

· 支持备份压缩.通过gzip工具实现页内压缩.

· 自动备份维护.自动删除过期的WAL备份文件.

· 支持备份验证.

· 恢复期间无事务丢失.支持基于PITR的配置文件生成器.

2、pg_rman的用法

pg_rman manage backup/recovery of PostgreSQL database.

Usage:

pg_rman OPTION init

pg_rman OPTION backup

pg_rman OPTION restore

pg_rman OPTION show [DATE]

pg_rman OPTION show detail [DATE]

pg_rman OPTION validate [DATE]

pg_rman OPTION delete DATE

pg_rman OPTION purge

Common Options:

-D, --pgdata=PATH location of the database storage area

-A, --arclog-path=PATH location of archive WAL storage area

-S, --srvlog-path=PATH location of server log storage area

-B, --backup-path=PATH location of the backup storage area

-c, --check show what would have been done

-v, --verbose show what detail messages

-P, --progress show progress of processed files

Backup options:

-b, --backup-mode=MODE full, incremental, or archive

-s, --with-serverlog also backup server log files

-Z, --compress-data compress data backup with zlib

-C, --smooth-checkpoint do smooth checkpoint before backup

-F, --full-backup-on-error switch to full backup mode

if pg_rman cannot find validate full backup

on current timeline

NOTE: this option is only used in --backup-mode=incremental or archive.

--keep-data-generations=NUM keep NUM generations of full data backup

--keep-data-days=NUM keep enough data backup to recover to N days ago

--keep-arclog-files=NUM keep NUM of archived WAL

--keep-arclog-days=DAY keep archived WAL modified in DAY days

--keep-srvlog-files=NUM keep NUM of serverlogs

--keep-srvlog-days=DAY keep serverlog modified in DAY days

--standby-host=HOSTNAME standby host when taking backup from standby

--standby-port=PORT standby port when taking backup from standby

Restore options:

--recovery-target-time time stamp up to which recovery will proceed

--recovery-target-xid transaction ID up to which recovery will proceed

--recovery-target-inclusive whether we stop just after the recovery target

--recovery-target-timeline recovering into a particular timeline

--hard-copy copying archivelog not symbolic link

Catalog options:

-a, --show-all show deleted backup too

Delete options:

-f, --force forcibly delete backup older than given DATE

Connection options:

-d, --dbname=DBNAME database to connect

-h, --host=HOSTNAME database server host or socket directory

-p, --port=PORT database server port

-U, --username=USERNAME user name to connect as

-w, --no-password never prompt for password

-W, --password force password prompt

Generic options:

-q, --quiet don't show any INFO or DEBUG messages

--debug show DEBUG messages

--help show this help, then exit

--version output version information, then exit

3、使用前提

1. 开启归档

2. 配置csvlog

4、安装

下载地址： https://github.com/ossc-db/pg_rman/releases https://yum.postgresql.org/9.6/redhat/rhel-6.5-x86_64/

[root@racnode1 ~]# rpm --force -ivh openssl10-libs-1.0.1e-1.ius.el6.x86_64.rpm

warning: openssl10-libs-1.0.1e-1.ius.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 9cd4953f: NOKEY

Preparing... ########################################### [100%]

1:openssl10-libs ########################################### [100%]

[root@racnode1 ~]# rpm -ivh postgresql96-libs-9.6.4-1PGDG.rhel6.x86_64.rpm

warning: postgresql96-libs-9.6.4-1PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

Preparing... ########################################### [100%]

1:postgresql96-libs ########################################### [100%]

[root@racnode1 ~]# rpm -ivh pg_rman-1.3.5-1.pg96.el6.x86_64.rpm

Preparing... ########################################### [100%]

1:pg_rman ########################################### [100%]

安装完成后，pg_rman会安装在默认的/usr/psql-9.6/目录下 切换用户到postgres用户下

5、配置环境变量

export PG_RMAN=/usr/pgsql-9.6

export PATH=$PATH:$HOME/bin:/opt/pgsql95/bin:$PG_RMAN/bin

export BACKUP_PATH=/data/pg_rman

6、初始化备份目录

首先需要初始化一个backup catalog，实际上就是需要一个目录，这个目录将用于存放备份的文件。 同时这个目录也会存放一些元数据，例如备份的配置文件，数据库的systemid，时间线文件历史等等。 初始化命令需要两个参数，分别为备份目标目录，以及数据库的$PGDATA

[postgres@racnode1 ~]$ pg_rman init -B /data/pg_rman

INFO: ARCLOG_PATH is set to '/home/postgres/arch'

INFO: SRVLOG_PATH is set to '/home/postgres/data/pg_log'

[root@racnode1 pg_rman]# ll

total 16

drwx------. 4 postgres postgres 4096 Aug 30 00:06 backup

-rw-rw-r--. 1 postgres postgres 76 Aug 30 00:06 pg_rman.ini

-rw-rw-r--. 1 postgres postgres 40 Aug 30 00:06 system_identifier

drwx------. 2 postgres postgres 4096 Aug 30 00:06 timeline_history

你可以把将来要使用的配置写在这个配置文件中，或者写在pg_rman的命令行中。

[postgres@racnode1 pg_rman]$ cat pg_rman.ini

ARCLOG_PATH='/home/postgres/arch'

SRVLOG_PATH='/home/postgres/data/pg_log'

COMPRESS_DATA = YES

KEEP_ARCLOG_FILES = 10

KEEP_ARCLOG_DAYS = 10

KEEP_DATA_GENERATIONS = 3

KEEP_DATA_DAYS = 10

KEEP_SRVLOG_FILES = 10

KEEP_SRVLOG_DAYS = 10

7、备份操作

全量备份

[postgres@racnode1 ~]$ pg_rman backup -b full

INFO: copying database files

INFO: copying archived WAL files

INFO: backup complete

INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 10, keep days = 10)

INFO: the threshold timestamp calculated by keep days is "2017-08-20 00:00:00"

INFO: start deleting old backup (keep generations = 3 AND keep after = 2017-08-20 00:00:00)

INFO: does not include the backup just taken

WARNING: backup "2021-03-10 17:09:31" is not taken into account

DETAIL: This is not a valid backup.

增量备份

[postgres@racnode1 ~]$ psql

psql (9.6.0)

Type "help" for help.

postgres=# \c lottu lottu

You are now connected to database "lottu" as user "lottu".

lottu=# create table lottu as select generate_series(1,10) id;

SELECT 10

[postgres@racnode1 ~]$ pg_rman backup -b incremental

INFO: copying database files

INFO: copying archived WAL files

INFO: backup complete

INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

...

DETAIL: This is not valid backup.

8、备份集校验

每次备份完，必须要做一次校验，否则备份集不可用用来恢复，增量备份时也不会用它来做增量比较

[postgres@racnode1 ~]$ pg_rman validate

INFO: validate: "2021-03-10 17:34:18" backup and archive log files by CRC

INFO: backup "2021-03-10 17:34:18" is valid

INFO: validate: "2021-03-10 17:37:36" backup and archive log files by CRC

INFO: backup "2021-03-10 17:37:36" is valid

[postgres@racnode1 ~]$ pg_rman show

=====================================================================

StartTime EndTime Mode Size TLI Status

=====================================================================

2021-03-10 17:37:36 2021-03-10 17:37:45 FULL 3710kB 3 OK

2021-03-10 17:34:18 2021-03-10 17:34:27 FULL 3656kB 3 OK

2021-03-10 17:33:33 2021-03-10 17:33:45 FULL 3820kB 3 OK

2021-03-10 17:27:49 2021-03-10 17:27:58 FULL 3766kB 3 OK

2021-03-10 17:27:33 2021-03-10 17:27:43 FULL 3712kB 3 OK

9、查看备份集

[postgres@racnode1 ~]$ pg_rman show

=====================================================================

StartTime EndTime Mode Size TLI Status

=====================================================================

2021-03-10 17:37:36 2021-03-10 17:37:45 FULL 3710kB 3 OK

2021-03-10 17:34:18 2021-03-10 17:34:27 FULL 3656kB 3 OK

2021-03-10 17:33:33 2021-03-10 17:33:45 FULL 3820kB 3 OK

2021-03-10 17:27:49 2021-03-10 17:27:58 FULL 3766kB 3 OK

2021-03-10 17:27:33 2021-03-10 17:27:43 FULL 3712kB 3 OK

10、删除备份集

1. 按指定时间从catalog删除备份集

例如我只需要我的备份集能恢复到2021-03-10 17:27:49，在这个时间点以前，不需要用来恢复到这个时间点的备份全删掉。

[postgres@racnode1 ~]$ pg_rman delete "2021-03-10 17:27:49"

WARNING: cannot delete backup with start time "2021-03-10 17:27:49"

DETAIL: This is the latest full backup necessary for successful recovery.

INFO: delete the backup with start time: "2021-03-10 17:27:33"

2. 根据备份策略来删除备份集

KEEP_DATA_GENERATIONS = 3 -- 备份集冗余度是3

KEEP_DATA_DAYS = 10 -- 备份集保留日期是10d

在备份时；假如备份时间是2021-03-10。两者条件是“和”

INFO: start deleting old backup (keep generations = 3 AND keep after = 2017-08-20 00:00:00)

11、清除备份集

物理删除已从catalog删除的备份集

上面从catalog删除的备份集;备份集文件夹并没有一起删除。只是把arclog/ database/ srvlog/删除了。

[postgres@racnode1 ~]$ pg_rman purge

INFO: DELETED backup "2021-03-10 17:27:33" is purged

INFO: DELETED backup "2021-03-10 17:26:22" is purged

INFO: DELETED backup "2021-03-10 17:15:26" is purged

12、恢复操作

pg_rman restore [options:]

命令的选项也很简单，甚至可以不指定任何option

Restore options:

The parameters which are started with –recovery are same as parameters in recovery.conf. See also “Recovery Configuration” for details.

接下来的几个配置，与recovery.conf的意思对齐。

如果不指定时间线，则使用$PGDATA/global/pg_control，如果没有$PGDATA/global/pg_control，则使用最新的全量备份集的时间线。

--recovery-target-timeline TIMELINE

Specifies recovering into a particular timeline. If not specified, the current timeline from ($PGDATA/global/pg_control) is used.

如果不指定，则恢复到最新时间

--recovery-target-time TIMESTAMP

This parameter specifies the time stamp up to which recovery will proceed. If not specified, continue recovery to the latest time.

如果不指定，则恢复到最新xid

--recovery-target-xid XID

This parameter specifies the transaction ID up to which recovery will proceed. If not specified, continue recovery to the latest xid.

如果不指定，则默认使用true，即恢复到包含恢复目标XID的commit record为止，或者第一笔commit record ts>指定ts的 commit redo record为止；

如果是false则不apply恢复目标XID的commit record，或者不apply第一笔commit record ts>=指定ts的 commit redo record。

--recovery-target-inclusive

Specifies whether we stop just after the specified recovery target (true), or just before the recovery target (false). Default is true.

是否使用硬链接复制archive log，而不需要拷贝文件

The following parameter determines the behavior of restore.

--hard-copy

The archive WAL are copied to archive WAL storage area. If not specified, pg_rman makes symbolic link to archive WAL where are in the backup catalog directory.

恢复时，需要注意，你可以选择原地恢复（覆盖式），或者使用新的$PGDATA作为恢复目标。

但是请注意，不管是哪种恢复方式，如果在本机恢复的话，pg_rman可能会覆盖原有的数据文件，arch, pg_xlog目录中的文件，所以，如果你要保留原数据，建议先将原数据目录重命名。

1. 原地恢复

2. 使用新的$PGDATA恢复

[postgres@racnode1 ~]$ pg_rman restore --recovery-target-time "2021-03-10 19:48:00";

INFO: the recovery target timeline ID is not given

INFO: use timeline ID of current database cluster as recovery target: 5

INFO: calculating timeline branches to be used to recovery target point

INFO: searching latest full backup which can be used as restore start point

INFO: found the full backup can be used as base in recovery: "2021-03-10 17:37:36"

INFO: copying online WAL files and server log files

INFO: clearing restore destination

INFO: validate: "2021-03-10 17:37:36" backup and archive log files by SIZE

INFO: backup "2021-03-10 17:37:36" is valid

INFO: restoring database files from the full mode backup "2021-03-10 17:37:36"

INFO: searching incremental backup to be restored

INFO: validate: "2021-03-10 17:59:34" backup and archive log files by SIZE

INFO: backup "2021-03-10 17:59:34" is valid

INFO: restoring database files from the incremental mode backup "2021-03-10 17:59:34"

INFO: searching backup which contained archived WAL files to be restored

INFO: backup "2021-03-10 17:59:34" is valid

INFO: restoring WAL files from backup "2021-03-10 17:59:34"

INFO: restoring online WAL files and server log files

INFO: generating recovery.conf

INFO: restore complete

HINT: Recovery will start automatically when the PostgreSQL server is started.

13、附上备份脚本

##===========================================================

## pg_rman.sh

## 2021/03/10

## usage: pg_rman.sh

##============================================================

#!/bin/bash

source /home/postgres/.bash_profile

DATE=`date +%Y%m%d`;

PG_HOME=/home/postgres

BACK_LOG=/home/postgres/log/pg_rman_${DATE}.log

#START BACKUP

echo "START BACKUP............................................" > $BACK_LOG

#执行备份命令

pg_rman backup -b full >> $BACK_LOG

#备份集校验

pg_rman validate >> $BACK_LOG

#检查备份是否成功

error_num=`pg_rman show | awk 'BEGIN{n=0}{if(NR > 3 && $8 != "OK")n++}END{print n}'`

if [ $error_num > 0 ];then

echo "0"

fi

#清理无效备份集

pg_rman purge >> $BACK_LOG

echo "BACKUP END............................................" >> $BACK_LOG