PostgreSQL插件——pg_rman

一、环境介绍

CentOS 7.6
PostgreSQL 14.6
pg_rman 1.3.15

二、安装pg_rman

1.安装依赖

yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc

2.安装软件

tar xf pg_rman-1.3.15-pg14.tar.gz
make && make install

3.创建备份目录

mkdir -p /pg_rman_backup/{fullbackup,walbackup,pg_srvlog}
chown -R pg14:pg14 /pg_rman_backup

4.修改环境变量

 vi .bash_profile 
export BACKUP_PATH=/pg_rman_backup/fullbackup
export ARCLOG_PATH=/pg_rman_backup/walbackup
export SRVLOG_PATH=/pg_rman_backup/pg_srvlog

5.数据库开启归档

vi postgresql.auto.conf
archive_mode = on
archive_command = 'DATE=`date +%Y%m%d`; DIR="/pg_rman_backup/walbackup/"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'

重启数据库

6.初始化pg_rman

[pg14@node1 ~]$ pg_rman init
INFO: ARCLOG_PATH is set to '/pg_rman_backup/walbackup'
INFO: SRVLOG_PATH is set to '/pg_rman_backup/pg_srvlog'

7.配置文件

[postgres@mxl pg_rman]$ cat pg_rman.ini 



ARCLOG_PATH=‘/home/postgres/arch’                        --归档目录
SRVLOG_PATH=‘/home/postgres/data/log’                   --数据库错误日志目录
COMPRESS_DATA = YES                                   --压缩数据
KEEP_ARCLOG_FILES = 10                                 --保存归档文件个数
KEEP_ARCLOG_DAYS = 10                                  --保存归档的天数
KEEP_DATA_GENERATIONS = 3                               --备份冗余度
KEEP_DATA_DAYS = 10                                   --保存备份集时间
KEEP_SRVLOG_FILES = 10                                 --保存日志文件个数
KEEP_SRVLOG_DAYS = 10                                  --保存日志文件天数



ARCLOG_PATH=‘/home/postgres/arch’
SRVLOG_PATH=‘/home/postgres/data/log’
COMPRESS_DATA = YES
KEEP_ARCLOG_FILES = 10
KEEP_ARCLOG_DAYS = 10
KEEP_DATA_GENERATIONS = 3
KEEP_DATA_DAYS = 10
KEEP_SRVLOG_FILES = 10
KEEP_SRVLOG_DAYS = 10

8.全库备份

 pg_rman backup --backup-mode=full --with-serverlog  -Upostgres -dpostgres  -h node1


 备份校验
 pg_rman validate

9.增量备份

 pg_rman backup --backup-mode=incremental  -Upostgres -dpostgres --progress --compress-data  -hnode1
  pg_rman validate

10.恢复测试

 drop database test;
 pg_ctl stop -D $PGDATA -l /tmp/logfile 
 pg_rman restore --recovery-target-time "2023-02-26 10:03:21" --hard-copy

11 恢复验证

pg_ctl start -D $PGDATA -l /tmp/logfile 
psql -Upostgres

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

postgres=# \c test;
You are now connected to database "test" as user "postgres".
test=# select * from t1;
 id | name 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
  4 | ddd
  5 | eee
  6 | fff
(6 rows)