使用pg_rman来进行备份恢复 pg_rman的安装软件 pg_rman-1.2.11-1.pg93.rhel6.x86_64.rpm 在安装这个rpm的时候，有一个依赖包需要我们去额外下载的 [09:33:24 root()@enmorep soft]# rpm -ivh pg_rman-1.2.11-1.pg94.rhel6.x86_64.rpm error: Failed dependencies: postgresql94-libs is needed by pg_rman-1.2.11-1.pg94.rhel6.x86_64 这个依赖包我们可以从这个网站上面下载到 http://yum.postgresql.org/9.3/redhat/rhel-6.5-x86_64/ 然后安装这两个包 [09:49:22 root()@enmorep soft]# rpm -ivh postgresql93-libs-9.3.11-1PGDG.rhel6.x86_64.rpm warning: postgresql93-libs-9.3.11-1PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature,key ID 442df0f8: NOKEY Preparing... ########################################### [100%] 1:postgresql93-libs ########################################### [100%] [09:49:38 root()@enmorep soft]# rpm -ivh pg_rman-1.2.11-1.pg93.rhel6.x86_64.rpm Preparing... ########################################### [100%] 1:pg_rman ########################################### [100%] --注意默认pg_rman安装在 /usr/pgsql-9.3 目录下 我们查看下pg_rman的帮助 [09:53:00 postgres()@enmorep ~]$ 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 timeline [DATE] pg_rman OPTION validate [DATE] pg_rman OPTION delete DATE Generic options: --help show this help,then exit --version output version information,then exit [09:53:07 postgres()@enmorep ~]$ 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 timeline [DATE] pg_rman OPTION validate [DATE] pg_rman OPTION delete DATE 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 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 --keep-data-generations=N keep GENERATION of full data backup NOTE: This number does not include the latest full backup in a count. --keep-data-days=DAY keep enough data backup to recover to DAY days age NOTE: Need to use the above two options together. Full backups older than both condition are deleted automatically. ex.) --keep-data-generations=10 --keep-data-days=100 --keep-arclog-files=NUM keep NUM of archived WAL --keep-arclog-days=DAY keep archived WAL modified in DAY days NOTE: Need to use the above two options together. Archive log files older than both condition are deleted automatically. ex.) --keep-arclog-files=100 --keep-arclog-days=10 --keep-srvlog-files=NUM keep NUM of serverlogs --keep-srvlog-days=DAY keep serverlog modified in DAY days NOTE: Need to use the above two options together. Server log files older than both condition are deleted automatically. ex.) --keep-arclog-files=100 --keep-arclog-days=10 --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 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 write any messages --debug debug mode --help show this help,then exit Read the website for details. <http://sourceforge.net/projects/pg-rman/> Report bugs to <http://sourceforge.net/p/pg-rman/tickets/>. --注意pg_rman需要一个备份目录 export BACKUP_PATH=/var/lib/pgsql/backups --初始化备份目录 pg_rman init -B /var/lib/pgsql/backups --查看备份集 pg_rman show --pg_rman 支持增量备份和压缩备份 --pg_rman必须要有一个全量的备份做增量 -D,--backup-path=PATH location of the backup storage area pg_rman backup --backup-mode=full -D /usr/local/pgsql/data -A /usr/local/pgsql/archive -c [10:50:55 postgres()@enmorep data]$ pg_rman show ============================================================================ Start Time Total Data WAL Log Backup Status ============================================================================ 2015-08-15 10:43:18 1m 239MB ---- 603MB ---- 839MB DONE --基于全库备份的增量备份 pg_rman backup -b incremental -D /usr/local/pgsql/data [11:00:38 postgres()@enmorep data]$ pg_rman backup -b incremental -D /usr/local/pgsql/data INFO: database backup start ERROR: There is no validated full backup with current timeline.Please take a full backup and validate it before doing an incremental backup. NOTICE: pg_stop_backup complete,all required WAL segments have been archived 这儿我们需要注意的是我们备份完的全备，并不是可以立即让人使用的，我们必须要先对全备进行validate，同样的道理，没有进行validate的备份也是不能够用来进行恢复的 [11:06:07 postgres()@enmorep data]$ pg_rman show ============================================================================ Start Time Total Data WAL Log Backup Status ============================================================================ 2015-08-15 10:43:18 1m 239MB ---- 603MB ---- 839MB OK 这个时候状态显示为ok，我们再来进行下增量备份 [11:07:23 postgres()@enmorep data]$ pg_rman show ============================================================================ Start Time Total Data WAL Log Backup Status ============================================================================ 2015-08-15 11:07:18 0m ---- 6976kB 67MB ---- 73MB DONE 2015-08-15 10:43:18 1m 239MB ---- 603MB ---- 839MB OK [11:07:31 postgres()@enmorep data]$ pg_rman validate INFO: validate: 2015-08-15 11:07:18 backup and archive log files by CRC [11:07:47 postgres()@enmorep data]$ pg_rman show ============================================================================ Start Time Total Data WAL Log Backup Status ============================================================================ 2015-08-15 11:07:18 0m ---- 6976kB 67MB ---- 73MB OK 2015-08-15 10:43:18 1m 239MB ---- 603MB ---- 839MB OK [11:08:48 postgres()@enmorep data]$ pg_rman show timeline ============================================================ Start Mode Current TLI Parent TLI Status ============================================================ 2015-08-15 11:07:18 INCR 2 1 OK 2015-08-15 10:43:18 FULL 2 1 OK --删除备份 pg_rman delete "2015-08-15 16:32:45" pg_rman restore --recovery-target-time "2015-08-15 16:32:45" [16:32:58 postgres()@enmorep ~]$ pg_rman show ============================================================================ Start Time Total Data WAL Log Backup Status ============================================================================ 2015-08-15 16:32:45 0m ---- 153kB 33MB ---- 33MB OK 2015-08-15 11:07:18 0m ---- 6976kB 67MB ---- 73MB OK 2015-08-15 10:43:18 1m 239MB ---- 603MB ---- 839MB OK test=# select count(*) from test; count --------- 1391776 (1 row) test=# drop table test; DROP TABLE [16:43:23 postgres()@enmorep ~]$ service postgresql stop Stopping Postgresql: Password: ok [16:41:18 postgres()@enmorep ~]$ pg_rman restore --recovery-target-time "2015-08-15 16:32:45" INFO: validate: 2015-08-15 10:43:18 backup and archive log files by SIZE INFO: validate: 2015-08-15 11:07:18 backup and archive log files by SIZE INFO: restore complete. Recovery starts automatically when the Postgresql server is started. 启动pg数据库 [16:50:12 root()@enmorep ~]# service postgresql start Starting Postgresql: ok 这个时候我们看下表，有没有恢复 test=# \d List of relations Schema | Name | Type | Owner --------+----------+-------+------- public | bonus | table | kiwi public | dept | table | kiwi public | emp | table | kiwi public | salgrade | table | kiwi public | test | table | kiwi public | test1 | table | kiwi public | test2 | table | kiwi public | test3 | table | kiwi (8 rows) test=# select count(*) from test; count --------- 1391776 (1 row) 可以看到表中的数据已经完全恢复了 这儿我们需要注意的是，pg_rman是物理层面的恢复的，如果有备库的话，那么我们这个时候主备库同步的状态就失效了 原文链接：https://www.f2er.com/postgresql/194648.html