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.rpmerror: 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.rpmwarning: postgresql93-libs-9.6.11-1PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEYPreparing... ########################################### [100%]1:postgresql96-libs ########################################### [100%][root@localhost tmp]$ rpm -ivh pg_rman-1.3.3-1.pg96.rhel6.x86_64.rpmPreparing... ########################################### [100%]1:pg_rman ########################################### [100%]安装完成后，pg_rman会安装在默认的/usr/psql-9.6/目录下切换用户到postgres用户下2、添加环境变量：export PGDATA=/opt/pgsql/dataexport PATH=/opt/psql-9.6/bin:$PATH:/usr/pgsql-9.6/bin/export LD_LIBRARY_PATH=/opt/psql-9.6/libexport BACKUP_PATH=/backups3、查看pg_rman的情况和参数：[postgres@localhost ]$ pg_rman -helppg_rman manage backup/recovery of PostgreSQL database.Usage:pg_rman OPTION initpg_rman OPTION backuppg_rman OPTION restorepg_rman OPTION show [DATE]pg_rman OPTION show detail [DATE]pg_rman OPTION validate [DATE]pg_rman OPTION delete DATEpg_rman OPTION purgeCommon 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 filesBackup 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 modeif pg_rman cannot find validate full backupon current timelineNOTE: 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 standbyRestore 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 linkCatalog options:-a, --show-all show deleted backup tooDelete options:-f, --force forcibly delete backup older than given DATEConnection 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 promptGeneric 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 exitRead 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.conflisten_addresses = '*'port = 5432wal_level = archive ------->>若为HOT STANDBY环境则此处设置为hot_standbyarchive_mode = onarchive_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 /backupsINFO: ARCLOG_PATH is set to '/arclog'INFO: SRVLOG_PATH is set to '/opt/pgsql/data/pg_log'[postgres@localhost backups]$ lltotal 20drwx------. 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_identifierdrwx------. 2 postgres postgres 4096 Dec 8 10:20 timeline_history[postgres@localhost ~]$ cat /backup/pg_rman.iniARCLOG_PATH='/arclog'SRVLOG_PATH='/opt/pgsql/data/pg_log'6、pg_rman 支持三种备份方式，全库、增量和归档6.1全库备份：[postgres@localhost ~]$ cat /backups/pg_rman.iniARCLOG_PATH='/arclog'SRVLOG_PATH='/opt/pgsql/data/pg_log'COMPRESS_DATA = YESKEEP_ARCLOG_FILES = 10KEEP_ARCLOG_DAYS = 10KEEP_DATA_GENERATIONS = 3KEEP_DATA_DAYS = 120KEEP_SRVLOG_FILES = 10KEEP_SRVLOG_DAYS = 10执行全库备份：[postgres@localhost ~]$ pg_rman backup --backup-mode=fullINFO: database backup startNOTICE: 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 validateINFO: 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 OK6.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# configurationBACKUP_MODE=FULLFULL_BACKUP_ON_ERROR=falseWITH_SERVERLOG=falseCOMPRESS_DATA=true# resultTIMELINEID=1START_LSN=0/0e000028STOP_LSN=0/0e0000f8START_TIME='2016-12-08 10:49:51'END_TIME='2016-12-08 10:49:55'RECOVERY_XID=1691RECOVERY_TIME='2016-12-08 10:49:54'TOTAL_DATA_BYTES=22892947READ_DATA_BYTES=22892792READ_ARCLOG_BYTES=33554741WRITE_BYTES=2655690BLOCK_SIZE=8192XLOG_BLOCK_SIZE=8192STATUS=OK8、恢复数据;在数据库建一张表并插入数据，postgres=# create table b (b int);CREATE TABLEpostgres=# insert into b values (1);INSERT 0 1postgres=# insert into b values (2);INSERT 0 1postgres=# insert into b values (3);INSERT 0 1postgres=# select * from b;b---123(3 rows)执行备份[postgres@localhost ~]$ pg_rman backup --backup-mode=incrementalINFO: database backup startNOTICE: 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.3restore_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 existWARNING: pg_controldata file "/opt/pgsql/data/global/pg_control" does not existINFO: the recovery target timeline ID is not givenINFO: use timeline ID of latest full backup as recovery target: 1INFO: calculating timeline branches to be used to recovery target pointINFO: searching latest full backup which can be used as restore start pointINFO: 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 filesINFO: clearing restore destinationINFO: validate: "2016-12-08 10:48:12" backup and archive log files by SIZEINFO: backup "2016-12-08 10:48:12" is validINFO: restoring database files from the full mode backup "2016-12-08 10:48:12"INFO: searching incremental backup to be restoredINFO: searching backup which contained archived WAL files to be restoredINFO: backup "2016-12-08 10:48:12" is validINFO: restoring WAL files from backup "2016-12-08 10:48:12"INFO: backup "2016-12-08 10:49:51" is validINFO: restoring WAL files from backup "2016-12-08 10:49:51"INFO: restoring online WAL files and server log filesINFO: generating recovery.confINFO: restore completeHINT: Recovery will start automatically when the PostgreSQL server is started.启动数据库：[postgres@localhost data]$ pg_ctl startserver starting[postgres@localhost data]$ LOG: database system was interrupted; last known up at 2016-12-08 10:48:13 CSTLOG: starting point-in-time recovery to 2016-12-08 10:49:51+08LOG: restored log file "00000001000000000000000C" from archiveLOG: redo starts at 0/C000028LOG: consistent recovery state reached at 0/C0000F8LOG: restored log file "00000001000000000000000D" from archiveLOG: restored log file "00000001000000000000000E" from archiveLOG: restored log file "00000001000000000000000F" from archiveLOG: recovery stopping before commit of transaction 1691, time 2016-12-08 10:49:54.650729+08LOG: redo done at 0/F000028LOG: last completed transaction was at log time 2016-12-08 10:49:46.739701+08LOG: restored log file "00000001000000000000000E" from archiveLOG: restored log file "00000002.history" from archiveLOG: restored log file "00000003.history" from archivecp: cannot stat `/archive_log/00000004.history': No such file or directoryLOG: selected new timeline ID: 4cp: cannot stat `/archive_log/00000001.history': No such file or directoryLOG: archive recovery completeLOG: MultiXact member wraparound protections are now enabledLOG: database system is ready to accept connectionsLOG: autovacuum launcher started[postgres@localhost data]$ psqlpsql (9.6.1)Type "help" for help.postgres=# \dList of relationsSchema | Name | Type | Owner--------+------+-------+----------public | b | table | postgres(1 row)postgres=# select * from b;b---123(3 rows)数据恢复成功！