pg_rman的安装、使用备份和恢复

1、安装pg_rman
操作系统：redhat 6.5
数据库版本：postgresql9.6.1
下载地址：https://github.com/ossc-db/pg_rman/releases
[root@localhost tmp]$ rpm -ivh pg_rman-1.3.3-1.pg96.rhel6.x86_64.rpm
error: Failed dependencies:
postgresql96-libs is needed by pg_rman-1.3.3-1.pg94.rhel6.x86_64
出现上面的报错后我们需要下载和安装依赖包：
下载地址：http://yum.postgresql.org/9.6/redhat/rhel-6.5-x86_64/
[root@localhost tmp]$ rpm -ivh postgresql96-libs-9.6.1-1PGDG.rhel6.x86_64.rpm
安装这两个包：
[root@localhost tmp]$ rpm -ivh postgresql96-libs-9.6.1-1PGDG.rhel6.x86_64.rpm
warning: postgresql93-libs-9.6.11-1PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ########################################### [100%]
1:postgresql96-libs ########################################### [100%]
[root@localhost tmp]$ rpm -ivh pg_rman-1.3.3-1.pg96.rhel6.x86_64.rpm
Preparing... ########################################### [100%]
1:pg_rman ########################################### [100%]

安装完成后，pg_rman会安装在默认的/usr/psql-9.6/目录下
切换用户到postgres用户下
2、添加环境变量：
export PGDATA=/opt/pgsql/data
export PATH=/opt/psql-9.6/bin:$PATH:/usr/pgsql-9.6/bin/
export LD_LIBRARY_PATH=/opt/psql-9.6/lib
export BACKUP_PATH=/backups

3、查看pg_rman的情况和参数：
[postgres@localhost ]$ pg_rman -help
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

Read the website for details. <http://github.com/ossc-db/pg_rman>
Report bugs to <http://github.com/ossc-db/pg_rman/issues>.

4、创建归档目录：

[root@localhost ~]# mkdir /archive_log
[root@localhost ~]# chown postgres:postgres /archive_log

[postgres@localhost data]$ vi postgresql.conf
listen_addresses = '*'
port = 5432
wal_level = archive ------->>若为HOT STANDBY环境则此处设置为hot_standby
archive_mode = on
archive_command = 'test ! -f /archive_log/%f && cp %p /archive_log/%f'


5、创建初始化目录：

[root@localhost ~]# mkdir /backups
[root@localhost ~]# chown postgres:postgres /backups/
[postgres@localhost ~]$ pg_rman init -B /backups
INFO: ARCLOG_PATH is set to '/arclog'
INFO: SRVLOG_PATH is set to '/opt/pgsql/data/pg_log'
[postgres@localhost backups]$ ll
total 20
drwx------. 4 postgres postgres 4096 Dec 8 10:20 backup
-rw-rw-r--. 1 postgres postgres 222 Dec 8 10:23 pg_rman.ini
-rw-rw-r--. 1 postgres postgres 40 Dec 8 10:20 system_identifier
drwx------. 2 postgres postgres 4096 Dec 8 10:20 timeline_history
[postgres@localhost ~]$ cat /backup/pg_rman.ini
ARCLOG_PATH='/arclog'
SRVLOG_PATH='/opt/pgsql/data/pg_log'



6、pg_rman 支持三种备份方式，全库、增量和归档

6.1全库备份：

[postgres@localhost ~]$ cat /backups/pg_rman.ini
ARCLOG_PATH='/arclog'
SRVLOG_PATH='/opt/pgsql/data/pg_log'

COMPRESS_DATA = YES
KEEP_ARCLOG_FILES = 10
KEEP_ARCLOG_DAYS = 10
KEEP_DATA_GENERATIONS = 3
KEEP_DATA_DAYS = 120
KEEP_SRVLOG_FILES = 10
KEEP_SRVLOG_DAYS = 10
执行全库备份：
[postgres@localhost ~]$ pg_rman backup --backup-mode=full
INFO: database backup start
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
查看备份集：
[postgres@localhost ~]$ pg_rman show
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2016-12-08 10:49:51 FULL 0m 2655kB 1 DONE
验证：
[postgres@localhost ~]$ pg_rman validate
INFO: validate: 2016-12-08 10:49:51 backup and archive log files by CRC
再次查看备份：
[postgres@localhost ~]$ pg_rman show
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2016-12-08 10:49:51 FULL 0m 2655kB 1 OK

6.2增量备份：
[postgres@localhost ~]$ pg_rman backup --backup-mode=incremental --with-serverlog
查看备份集以及验证同上全库备份
6.3归档备份：
[postgres@localhost ~]$ pg_rman backup --backup-mode=archive --with-serverlog
查看备份集以及验证同上全库备份

7、删除备份
pg_rman delete "2016-12-08 10:49:51"

查看某个备份集的详细信息：
[postgres@localhost ~]$ pg_rman show 2016-12-08 10:49:51
# configuration
BACKUP_MODE=FULL
FULL_BACKUP_ON_ERROR=false
WITH_SERVERLOG=false
COMPRESS_DATA=true
# result
TIMELINEID=1
START_LSN=0/0e000028
STOP_LSN=0/0e0000f8
START_TIME='2016-12-08 10:49:51'
END_TIME='2016-12-08 10:49:55'
RECOVERY_XID=1691
RECOVERY_TIME='2016-12-08 10:49:54'
TOTAL_DATA_BYTES=22892947
READ_DATA_BYTES=22892792
READ_ARCLOG_BYTES=33554741
WRITE_BYTES=2655690
BLOCK_SIZE=8192
XLOG_BLOCK_SIZE=8192
STATUS=OK

8、恢复数据;
在数据库建一张表并插入数据，
postgres=# create table b (b int);
CREATE TABLE
postgres=# insert into b values (1);
INSERT 0 1
postgres=# insert into b values (2);
INSERT 0 1
postgres=# insert into b values (3);
INSERT 0 1
postgres=# select * from b;
b
---
1
2
3
(3 rows)

执行备份
[postgres@localhost ~]$ pg_rman backup --backup-mode=incremental
INFO: database backup start
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
[postgres@localhost ~]$ pg_rman show
==========================================================
StartTime Mode Duration Size TLI Status
==========================================================
2016-12-08 10:49:51 FULL 0m 2655kB 1 OK
模拟崩溃：
[postgres@localhost~]$ killall -9 postgres
[postgres@localhost ~]$ rm -rf /opt/pgsql/data/* 
（建议不删除data目录而是改名字在新建一个data目录）

建恢复文件：
[postgres@localhost data]$ vi recovery.conf

[postgres@localhost data]$ cat /opt/pgsql/data/recovery.conf

# recovery.conf generated by pg_rman 1.3.3
restore_command = 'cp /archive_log/%f %p'
recovery_target_time = '2016-12-08 10:49:51'
recovery_target_timeline = '1'
恢复：
[postgres@localhost data]$ pg_rman restore --recovery-target-time "2016-12-08 10:49:51"
WARNING: pg_controldata file "/opt/pgsql/data/global/pg_control" does not exist
WARNING: pg_controldata file "/opt/pgsql/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
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: "2016-12-08 10:48:12"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2016-12-08 10:48:12" backup and archive log files by SIZE
INFO: backup "2016-12-08 10:48:12" is valid
INFO: restoring database files from the full mode backup "2016-12-08 10:48:12"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2016-12-08 10:48:12" is valid
INFO: restoring WAL files from backup "2016-12-08 10:48:12"
INFO: backup "2016-12-08 10:49:51" is valid
INFO: restoring WAL files from backup "2016-12-08 10:49:51"
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.

启动数据库：

[postgres@localhost data]$ pg_ctl start
server starting
[postgres@localhost data]$ LOG: database system was interrupted; last known up at 2016-12-08 10:48:13 CST
LOG: starting point-in-time recovery to 2016-12-08 10:49:51+08
LOG: restored log file "00000001000000000000000C" from archive
LOG: redo starts at 0/C000028
LOG: consistent recovery state reached at 0/C0000F8
LOG: restored log file "00000001000000000000000D" from archive
LOG: restored log file "00000001000000000000000E" from archive
LOG: restored log file "00000001000000000000000F" from archive
LOG: recovery stopping before commit of transaction 1691, time 2016-12-08 10:49:54.650729+08
LOG: redo done at 0/F000028
LOG: last completed transaction was at log time 2016-12-08 10:49:46.739701+08
LOG: restored log file "00000001000000000000000E" from archive
LOG: restored log file "00000002.history" from archive
LOG: restored log file "00000003.history" from archive
cp: cannot stat `/archive_log/00000004.history': No such file or directory
LOG: selected new timeline ID: 4
cp: cannot stat `/archive_log/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

[postgres@localhost data]$ psql
psql (9.6.1)
Type "help" for help.

postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | b | table | postgres
(1 row)

postgres=# select * from b;
b
---
1
2
3
(3 rows)

数据恢复成功！