pg_rman 实现对postgresql数据库的备份，恢复与管理

--下载软件https://github.com/ossc-db/pg_rman--安装 unzip pg_rman-master.zip  --下载安装postgresql93-libshttp://yum.postgresql.org/9.3/redhat/rhel-6.4-x86_64/ [root@rudy tools]# rpm -ivh postgresql93-libs-9.3.10-1PGDG.rhel6.x86_64.rpm warning: postgresql93-libs-9.3.10-1PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEYPreparing...                ########################################### [100%]   1:postgresql93-libs      ########################################### [100%]--下载pg_rman,注意与其对应的数据库版本    https://github.com/ossc-db/pg_rman/releases  [root@rudy tools]# rpm -ivh pg_rman-1.3.1-1.pg93.rhel6.x86_64.rpm    Preparing...                ########################################### [100%]   1:pg_rman                ########################################### [100%]      --注意默认pg_rman安装在 /usr/pgsql-9.3 目录下   --注意pg_rman需要一个备份目录export BACKUP_PATH=/tmp/pg_rman--初始化备份目录mkdir pg_rmanpg_rman init -B /tmp/pg_rman--查看备份集 pg_rman show  --删除备份  pg_rman delete 2015-11-19 15:10:34    --pg_rman 支持增量备份和压缩备份  --pg_rman必须要有一个全量的备份做增量 pg_rman backup --backup-mode=full --progress  --基于全库备份的增量备份  pg_rman backup -b incremental -P -Z        --pg_rman 的备份必须都是经过验证过的，否则不能进行恢复和增量备份 Backups without validation cannot be used for restore and incremental backup   --执行完备份要执行一次validate,因为备份后的状态是done，还不能进行恢复 pg_rman validate  $ pg_rman show detail========================================================== StartTime           Mode  Duration    Size   TLI  Status==========================================================2015-07-30 13:36:38  FULL        0m    15MB    15  DONEThe status of the backup we have just taken is DONE. This is because we does not do validate yet. So, do validate command next.$ pg_rman validateINFO: validate: "2015-07-30 13:36:38" backup, archive log files and server log files by CRCINFO: backup "2015-07-30 13:36:38" is valid$ pg_rman show========================================================== StartTime           Mode  Duration    Size   TLI  Status==========================================================2015-07-30 13:36:38  FULL        0m    15MB    15  OK--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. --删除指定时间点之前的数据 pg_rman delete '2015-12-28 14:40:00' --注意pg_rman至少要保存一个完整的备份及其增量 WARNING: cannot delete backup with start time "2015-12-28 14:29:22" DETAIL: This is the incremental backup necessary for successful recovery. WARNING: cannot delete backup with start time "2015-12-28 14:26:42" DETAIL: This is the latest full backup necessary for successful recovery.  --如果实在想删除可以指定 -f 参数  pg_rman delete -f '2015-12-28 14:39:51'        -- pg_rman 的delete只是删除其文件，但对于purge操作是删除备份目录中的备份信息Though delete command removes actual data from file system, there remains some catalog information of deleted backups. In order to remove this, execute purge command-- pg_rman 可用于初始化一个standby 数据库pg_rman backup --pgdata=/home/postgres/pgdata_sby --backup-mode=full --host=master --standby-host=localhost --standby-port=5432--pg_rman 的增量备份是基于文件系统的update time时间线When taking an incremental backup, pg_rman check the timeline ID of the target database whether it is the same with the one of the full backup in backup list. But, pg_rman does not check whether the data itself is same with the full backup in backup list. So, you can take an incremental backup over the full backup against the database which has the same timeline ID but has different data--对于pg_rman，如果备份的数据库全部丢失，包括 pg_xlog目录内的文件，其只能恢复到备份时的时间点--模拟数据丢失mv data data_bak--使用pg_rman进行恢复，注意如果归档目录不存在，则需要手动创建一个归档目录pg_rman restore--恢复完成之后，由于xlog丢失，启动数据库失败，错误如下2015-12-27 19:08:35.848 PST,,,6422,,5680a7b3.1916,2,,2015-12-27 19:08:35 PST,,0,LOG,00000,"invalid primary checkpoint record",,,,,,,,"ReadCheckpointRecord, xlog.c:6411",""2015-12-27 19:08:35.848 PST,,,6422,,5680a7b3.1916,3,,2015-12-27 19:08:35 PST,,0,LOG,00000,"invalid secondary checkpoint record",,,,,,,,"ReadCheckpointRecord, xlog.c:6415",""2015-12-27 19:08:35.848 PST,,,6422,,5680a7b3.1916,4,,2015-12-27 19:08:35 PST,,0,PANIC,XX000,"could not locate a valid checkpoint record",,,,,,,,"StartupXLOG, xlog.c:5121",""--此时只能重置xlog，并取消恢复模式pg_resetxlog -f /usr/local/postgresql/9.3.4/datamv recovery.conf recovery.done--对于pg_rman，如果备份的数据库全部丢失,但xlog日志存在，此时可以恢复到数据库崩溃的时间点 rm -rf data/pg_xlog/ --拷贝原有的xlog到恢复的数据库目录中 cp -r data_bak/pg_xlog/ data --删除archive_status文件目录的文件 rm -rf data/pg_xlog/archive_status/*  --注释掉recovery.conf的恢复点 #recovery_target_timeline = '2'--启动数据库 pg_ctl start