众所周知，Oracle下可以使用rman进行物理备份，支持数据库的全量、增量、归档的备份模式;而PostgreSQL作为开源数据库，也推出了开源功工具pg_rman，同样具备了oracle rman大部分的功能。本文写的很早，近期才翻出来，挂到博客上，希望能帮助需要的朋友。
1、下载安装软件
1[root@mydb01 ~]# wget https://github.com/ossc-db/pg_rman/releases/download/V1.3.6/pg_rman-1.3.6-1.pg10.rhel7.x86_64.rpm 2[root@mydb01 ~]# yum -y localinstall pg_rman-1.3.6-1.pg10.rhel7.x86_64.rpm
2、配置PG参数
首先设置postgres用户的profile，加入以下内容：
1-bash-4.2$ vi .bash_profile 2export PATH=/usr/pgsql-10/bin:$PATH 3export LD_LIBRARY_PATH=/usr/pgsql-10/lib 4export BACKUP_PATH=/u02/backups 5-bash-4.2$ vi pg_hba.conf 6host all all 0.0.0.0/0 md5 7-bash-4.2$ vi postgresql.conf 8listen_addresses = '*' 9port = 5432 10wal_level = archive ------->>若为HOT STANDBY环境则此处设置为hot_standby 11archive_mode = on 12archive_command = 'test ! -f /u02/archivelog/%f && cp %p /u02/archivelog/%f' 13[root@mydb01 ~]# systemctl restart postgresql10.service
3、初始化备份环境
1[root@mydb01 ~]# su - postgres 2-bash-4.2$ pg_rman init
4、全备份
1-bash-4.2$ pg_rman backup --backup-mode=full 2INFO: copying database files 3INFO: copying archived WAL files 4INFO: backup complete 5INFO: Please execute 'pg_rman validate' to verify the files are correctly copied. 6INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 10, keep days = 10) 7INFO: the threshold timestamp calculated by keep days is "2018-03-18 00:00:00" 8INFO: start deleting old backup (keep generations = 3 AND keep after = 2017-11-28 00:00:00) 9INFO: does not include the backup just tak
备份完成后，可以使用下面的命令进行验证：
1-bash-4.2$ pg_rman validate 2INFO: validate: "2018-03-28 16:21:34" backup and archive log files by CRC 3INFO: backup "2018-03-28 16:21:34" is valid 4-bash-4.2$ pg_rman show 5===================================================================== 6 StartTime EndTime Mode Size TLI Status 7===================================================================== 82018-03-28 16:21:34 2018-03-28 16:21:36 FULL 4465kB 1 OK
5、增量备份
1-bash-4.2$ pg_rman backup --backup-mode=incremental --with-serverlog 2INFO: copying database files 3INFO: copying archived WAL files 4INFO: copying server log files 5INFO: backup complete 6INFO: Please execute 'pg_rman validate' to verify the files are correctly copied. 7INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 10, keep days = 10) 8INFO: the threshold timestamp calculated by keep days is "2018-03-18 00:00:00" 9INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 10) 10INFO: the threshold timestamp calculated by keep days is "2018-03-18 00:00:00" 11INFO: start deleting old backup (keep generations = 3 AND keep after = 2017-11-28 00:00:00) 12INFO: does not include the backup just taken 13INFO: backup "2018-03-28 16:21:34" should be kept 14DETAIL: This is the 1st latest full backup. 15-bash-4.2$ pg_rman validate 16INFO: validate: "2018-03-28 16:27:57" backup, archive log files and server log files by CRC 17INFO: backup "2018-03-28 16:27:57" is valid 18-bash-4.2$ pg_rman show 19===================================================================== 20 StartTime EndTime Mode Size TLI Status 21===================================================================== 222018-03-28 16:27:57 2018-03-28 16:27:59 INCR 992kB 1 OK 232018-03-28 16:21:34 2018-03-28 16:21:36 FULL 4465kB 1 OK
6、归档备份
1-bash-4.2$ pg_rman backup --backup-mode=archive --with-serverlog 2INFO: copying archived WAL files 3INFO: copying server log files 4INFO: backup complete 5INFO: Please execute 'pg_rman validate' to verify the files are correctly copied. 6INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 10, keep days = 10) 7INFO: the threshold timestamp calculated by keep days is "2018-03-18 00:00:00" 8INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 10) 9INFO: the threshold timestamp calculated by keep days is "2018-03-18 00:00:00" 10INFO: start deleting old backup (keep generations = 3 AND keep after = 2017-11-28 00:00:00) 11INFO: does not include the backup just taken 12INFO: backup "2018-03-28 16:27:57" should be kept 13DETAIL: This belongs to the 1st latest full backup. 14INFO: backup "2018-03-28 16:21:34" should be kept 15DETAIL: This is the 1st latest full backup. 16-bash-4.2$ pg_rman show 17===================================================================== 18 StartTime EndTime Mode Size TLI Status 19===================================================================== 202018-03-28 16:28:51 2018-03-28 16:28:52 ARCH 26kB 1 DONE 212018-03-28 16:27:57 2018-03-28 16:27:59 INCR 992kB 1 OK 222018-03-28 16:21:34 2018-03-28 16:21:36 FULL 4465kB 1 OK
7、查看备份集信息
1-bash-4.2$ pg_rman show 2018-03-28 16:21:34 2# configuration 3BACKUP_MODE=FULL 4FULL_BACKUP_ON_ERROR=false 5WITH_SERVERLOG=false 6COMPRESS_DATA=true 7# result 8TIMELINEID=1 9START_LSN=0/02000028 10STOP_LSN=0/02000130 11START_TIME='2018-03-28 16:21:34' 12END_TIME='2018-03-28 16:21:36' 13RECOVERY_XID=555 14RECOVERY_TIME='2018-03-28 16:21:36' 15TOTAL_DATA_BYTES=24535637 16READ_DATA_BYTES=24535483 17READ_ARCLOG_BYTES=33554741 18WRITE_BYTES=4465406 19BLOCK_SIZE=8192 20XLOG_BLOCK_SIZE=8192 21STATUS=OK 22-bash-4.2$ pg_rman show 2018-03-28 16:27:57 23# configuration 24BACKUP_MODE=INCREMENTAL 25FULL_BACKUP_ON_ERROR=false 26WITH_SERVERLOG=true 27COMPRESS_DATA=true 28# result 29TIMELINEID=1 30START_LSN=0/04000028 31STOP_LSN=0/040000f8 32START_TIME='2018-03-28 16:27:57' 33END_TIME='2018-03-28 16:27:59' 34RECOVERY_XID=561 35RECOVERY_TIME='2018-03-28 16:27:59' 36TOTAL_DATA_BYTES=32585789 37READ_DATA_BYTES=8186187 38READ_ARCLOG_BYTES=33554741 39READ_SRVLOG_BYTES=518 40WRITE_BYTES=992901 41BLOCK_SIZE=8192 42XLOG_BLOCK_SIZE=8192 43STATUS=OK
8、删除备份
1-bash-4.2$ pg_rman delete "2018-03-28 16:28:51" 2WARNING: cannot delete backup with start time "2018-03-28 16:28:51" 3DETAIL: This is the archive backup necessary for successful recovery. 4WARNING: cannot delete backup with start time "2018-03-28 16:27:57" 5DETAIL: This is the incremental backup necessary for successful recovery. 6WARNING: cannot delete backup with start time "2018-03-28 16:21:34" 7DETAIL: This is the latest full backup necessary for successful recovery.
9、恢复数据库
删除/var/lib/pgsql/10/data文件夹或者清空，然后进行恢复操作。如果data文件夹是新建的，则必须注意data目录的属性为700，否则在恢复的时候会报错。
1-bash-4.2$ pg_rman restore --recovery-target-time "2018-03-29 14:35:53" 2WARNING: pg_controldata file "/var/lib/pgsql/10/data/global/pg_control" does not exist 3INFO: backup "2018-03-29 14:30:11" is valid 4INFO: the recovery target timeline ID is not given 5INFO: use timeline ID of latest full backup as recovery target: 1 6INFO: calculating timeline branches to be used to recovery target point 7INFO: searching latest full backup which can be used as restore start point 8INFO: found the full backup can be used as base in recovery: "2018-03-29 14:30:11" 9INFO: copying online WAL files and server log files 10INFO: clearing restore destination 11INFO: validate: "2018-03-29 14:30:11" backup and archive log files by SIZE 12INFO: backup "2018-03-29 14:30:11" is valid 13INFO: restoring database files from the full mode backup "2018-03-29 14:30:11" 14INFO: searching incremental backup to be restored 15INFO: searching backup which contained archived WAL files to be restored 16INFO: backup "2018-03-29 14:30:11" is valid 17INFO: restoring WAL files from backup "2018-03-29 14:30:11" 18INFO: restoring online WAL files and server log files 19INFO: generating recovery.conf 20INFO: restore complete 21HINT: Recovery will start automatically when the PostgreSQL server is started. 22-bash-4.2$ pg_ctl start 23waiting for server to start....2018-03-29 14:43:47.613 CST [1465] LOG: listening on IPv4 address "0.0.0.0", port 5432 242018-03-29 14:43:47.613 CST [1465] LOG: listening on IPv6 address "::", port 5432 252018-03-29 14:43:47.619 CST [1465] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 262018-03-29 14:43:47.637 CST [1465] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 272018-03-29 14:43:47.650 CST [1465] LOG: redirecting log output to logging collector process 282018-03-29 14:43:47.650 CST [1465] HINT: Future log output will appear in directory "log". 29. done 30server started
更详细的pg_rman用法，请参考官方手册。