使用pg_rman软件来实现postgresql数据库的备份恢复

使用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
  --version                 output version information, 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, --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


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是物理层面的恢复的，如果有备库的话，那么我们这个时候主备库同步的状态就失效了