管理Oracle数据库备份恢复工作的DBA,对RMAN可谓是再熟悉不过了,所幸,postgresql数据库也有一款类似的工具,称为:pg_rman
pg_rman是一款专门为postgresql设计的在线备份恢复的工具。其支持在线和基于时间点备份方式,还可以通过创建backup catalog来维护DB cluster备份信息。
下面是其使用的初步尝试:
使用前提
开启归档
配置log_destination=csvlog
下载安装
下载地址:
https://github.com/ossc-db/pg_rman/releases
https://yum.postgresql.org/11/redhat/rhel-7-x86_64/
[root@vwattjps2008001 ~]# rpm -ivh pg_rman-1.3.9-1.pg11.rhel7.x86_64.rpm postgresql11-libs-11.7-1PGDG.rhel7.x86_64.rpm
warning: postgresql11-libs-11.7-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql11-libs-11.7-1PGDG.rhel################################# [ 50%]
2:pg_rman-1.3.9-1.pg11.rhel7 ################################# [100%]
一定要找对操作系统版本,pg数据库版本
安装完成后,pg_rman会安装在默认的/usr/pgsql-11目录下
切换用户到postgres用户下
配置环境变量
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PGHOME=/usr/local/postgresql
export PGDATA=/pgdata/data
export BACKUP_PATH=/backup/pgrman
export ARCLOG_PATH=/archive_wals
export SRVLOG_PATH=/pgdata/data/pg_log
export PG_RMAN=/usr/pgsql-11
export PATH=$PATH:$HOME/bin:$PG_RMAN/bin
初始化备份目录
首先需要初始化一个backup catalog,实际上就是需要一个目录,这个目录将用于存放备份的文件。同时这个目录也会存放一些元数据,例如备份的配置文件,数据库的systemid,时间线文件历史等等。初始化命令需要两个参数,分别为备份目标目录,以及数据库的$PGDATA
[postgres@vwattjps2008001 ~]$ pg_rman init -B /backup/pgrman
WARNING: ARCLOG_PATH is not set yet
DETAIL: The archive_command is not set in postgresql.conf.
HINT: Please set ARCLOG_PATH in pg_rman.ini or environmental variable.
INFO: SRVLOG_PATH is set to '/pgdata/data/pg_log'
[postgres@vwattjps2008001 pgrman]$ cat pg_rman.ini
SRVLOG_PATH='/pgdata/data/pg_log'
你可以把将来要使用的配置写在这个配置文件中,或者写在pg_rman的命令行中。
[postgres@vwattjps2008001 pgrman]$ cat pg_rman.ini
SRVLOG_PATH='/pgdata/data/pg_log'
备份操作
全量备份:
[postgres@vwattjps2008001 ~]$ pg_rman -h 127.0.0.1 -p 5432 -U postgres backup -b full
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
增量备份
[postgres@vwattjps2008001 ~]$ pg_rman -h 127.0.0.1 -p 5432 -U postgres backup -b incremental
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
备份集校验
每次备份完,必须要做一次校验,否则备份集不可用用来恢复,增量备份时也不会用它来做增量比较
[postgres@vwattjps2008001 ~]$ pg_rman validate
INFO: validate: "2020-10-19 13:21:34" backup and archive log files by CRC
INFO: backup "2020-10-19 13:21:34" is valid
[postgres@vwattjps2008001 ~]$ pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2020-10-19 13:21:34 2020-10-19 13:21:36 INCR 33MB 16 OK
2020-10-19 13:20:02 2020-10-19 13:20:07 FULL 1917MB 16 OK
部署备份脚本
[root@vwattjps2008001 ~]# crontab -l
0 22 * * * su - postgres -c /backup/pgbackup.sh >>/var/message 2>&1
30 22 * * * su - postgres -c /backup/pg_rman.sh >>/var/message 2>&1
[root@vwattjps2008001 ~]# cat /backup/pg_rman.sh
#!/bin/bash
source /home/postgres/.bash_profile
DATE=`date +%Y%m%d`;
PGHOME=/usr/local/postgresql
BACK_LOG=/backup/pgrman/log/pg_rman_${DATE}.log
#START BACKUP
echo "Postgresql Start Backup............................................" > $BACK_LOG
#Execute backup command
pg_rman -h 172.24.140.77 -p 5000 -U postgres backup -b full >> $BACK_LOG
#Backup set verification
pg_rman validate >> $BACK_LOG
#Check that the backup was successful
error_num=`pg_rman show | awk 'BEGIN{n=0}{if(NR > 3 && $8 != "OK")n++}END{print n}'`
if [ $error_num > 0 ];then
echo "Backup unsuccessful!"
fi
#Clean up invalid backup sets
pg_rman purge >> $BACK_LOG
echo "Backup End............................................" >> $BACK_LOG
说明:其中172.24.140.77为高可用集群的VIP。
更多信息可以访问:https://github.com/ossc-db/pg_rman
下次咱们聊聊pg_rman如何实现基于时间点的数据库恢复。