看下postgre中的rman：pg_rman备份恢复

1、pg_rman安装
1.1、安装依赖
yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc

1.2、工具下载地址
https://github.com/ossc-db/pg_rman/releases

1.3、编译安装
[root@node2 ~]# tar -zxvf pg_rman-1.3.14-pg13.tar.gz

[root@node2 ~]# cd pg_rman-1.3.14-pg13

[root@node2 pg_rman-1.3.14-pg13]# make && make install

...

...

/usr/bin/mkdir -p '/home/postgresql/bin'

/usr/bin/install -c pg_rman '/home/postgresql/bin'

1.4、创建备份目录
mkdir /pg_rman_backup/{fullbackup,walbackup,pg_srvlog} -p

chown -R postgres:postgres /pg_rman_backup

1.5、修改环境变量
vi /etc/profile

export BACKUP_PATH=/pg_rman_backup/fullbackup

export ARCLOG_PATH=/pg_rman_backup/walbackup

export SRVLOG_PATH=/pg_rman_backup/pg_srvlog

source /etc/profile

1.6、开启归档模式
vi $PGDATA/postgresql.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'

1.7、初始化pg_rman
[postgres@node2 ~]$ pg_rman -B /pg_rman_backup/fullbackup/ -D /home/postgresql/data -A /pg_rman_backup/walbackup/ init

INFO: ARCLOG_PATH is set to '/pg_rman_backup/walbackup/'

INFO: SRVLOG_PATH is set to '/pg_rman_backup/pg_srvlog'

2、备份
2.1、测试数据
repmgr=# create table t1(id int);

CREATE TABLE

repmgr=# insert into t1 values(1);

INSERT 0 1

2.2、全备
[postgres@node2 ~]$ pg_rman --backup-path /pg_rman_backup/fullbackup/ --pgdata /home/postgresql/data --arclog-path /pg_rman_backup/walbackup/ backup -b full -s --srvlog-path=/pg_rman_backup/pg_srvlog/ -U postgres -d repmgr

INFO: copying database files

INFO: copying archived WAL files

INFO: copying server log files

INFO: backup complete

INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

[postgres@node2 ~]$ pg_rman validate

INFO: validate: "2023-02-01 14:51:48" backup, archive log files and server log files by CRC

INFO: backup "2023-02-01 14:51:48" is valid

[postgres@node2 ~]$ pg_rman show

=====================================================================

StartTime EndTime Mode Size TLI Status

=====================================================================

2023-02-01 14:51:48 2023-02-01 14:51:59 FULL 89MB 2 OK

[postgres@node2 ~]$

备份后未校验（pg_rman validate）状态（status）显示DONE，校验后状态显示OK

2.3、增备
repmgr=# insert into t1 values(2);

INSERT 0 1

[postgres@node2 ~]$ pg_rman --backup-path /pg_rman_backup/fullbackup/ --pgdata /home/postgresql/data --arclog-path /pg_rman_backup/walbackup/ backup --backup-mode incremental --progress --compress-data -U postgres -d repmgr

INFO: copying database files

Processed 1268 of 1268 files, skipped 1239

INFO: copying archived WAL files

Processed 9 of 9 files, skipped 6

INFO: backup complete

INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

[postgres@node2 ~]$ pg_rman validate

INFO: validate: "2023-02-01 14:55:47" backup and archive log files by CRC

INFO: backup "2023-02-01 14:55:47" is valid

[postgres@node2 ~]$ pg_rman show

=====================================================================

StartTime EndTime Mode Size TLI Status

=====================================================================

2023-02-01 14:55:47 2023-02-01 14:55:50 INCR 33kB 2 OK

2023-02-01 14:51:48 2023-02-01 14:51:59 FULL 89MB 2 OK

[postgres@node2 ~]$

3、恢复
3.1、删除数据，停止数据库
repmgr=# drop table t1;

DROP TABLE

repmgr=# \d

Did not find any relations.

repmgr=# \q

[postgres@node2 ~]$ pg_ctl -D /home/postgresql/data -l /home/postgresql/data/logfile stop

waiting for server to shut down.... done

server stopped

3.2、数据恢复
[postgres@node2 ~]$ pg_rman restore -B /pg_rman_backup/fullbackup/ -D /home/postgresql/data --recovery-target-time "2023-02-01 14:51:59" --hard-copy

INFO: the recovery target timeline ID is not given

INFO: use timeline ID of current database cluster as recovery target: 2

INFO: calculating timeline branches to be used to recovery target point

INFO: searching latest full backup which can be used as restore start point

INFO: found the full backup can be used as base in recovery: "2023-02-01 14:51:48"

INFO: copying online WAL files and server log files

INFO: clearing restore destination

INFO: validate: "2023-02-01 14:51:48" backup, archive log files and server log files by SIZE

INFO: backup "2023-02-01 14:51:48" is valid

INFO: restoring database files from the full mode backup "2023-02-01 14:51:48"

INFO: searching incremental backup to be restored

INFO: searching backup which contained archived WAL files to be restored

INFO: backup "2023-02-01 14:51:48" is valid

INFO: restoring WAL files from backup "2023-02-01 14:51:48"

INFO: backup "2023-02-01 14:55:47" is valid

INFO: restoring WAL files from backup "2023-02-01 14:55:47"

INFO: restoring online WAL files and server log files

INFO: create pg_rman_recovery.conf for recovery-related parameters.

INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists

INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf

INFO: generating recovery.signal

INFO: removing standby.signal if exists to restore as primary

INFO: restore complete

HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.

3.3、查看数据
[postgres@node2 ~]$ pg_ctl -D /home/postgresql/data -l /home/postgresql/data/logfile start

waiting for server to start.... done

server started

[postgres@node2 ~]$ psql

psql (13.4)

Type "help" for help.

postgres=# \c repmgr

You are now connected to database "repmgr" as user "postgres".

repmgr=# \d

List of relations

Schema | Name | Type | Owner

--------+------+-------+----------

public | t1 | table | postgres

(1 row)

repmgr=# select * from t1;

id

----

1

(1 row)

repmgr=#

全备恢复成功

[postgres@node2 ~]$ pg_ctl -D /home/postgresql/data -l /home/postgresql/data/logfile stop

waiting for server to shut down.... done

server stopped

[postgres@node2 ~]$ pg_rman restore -B /pg_rman_backup/fullbackup/ -D /home/postgresql/data --recovery-target-time "2023-02-01 14:55:50" --hard-copy

INFO: the recovery target timeline ID is not given

INFO: use timeline ID of current database cluster as recovery target: 2

INFO: calculating timeline branches to be used to recovery target point

INFO: searching latest full backup which can be used as restore start point

INFO: found the full backup can be used as base in recovery: "2023-02-01 14:51:48"

INFO: copying online WAL files and server log files

INFO: clearing restore destination

INFO: validate: "2023-02-01 14:51:48" backup, archive log files and server log files by SIZE

INFO: backup "2023-02-01 14:51:48" is valid

INFO: restoring database files from the full mode backup "2023-02-01 14:51:48"

INFO: searching incremental backup to be restored

INFO: validate: "2023-02-01 14:55:47" backup and archive log files by SIZE

INFO: backup "2023-02-01 14:55:47" is valid

INFO: restoring database files from the incremental mode backup "2023-02-01 14:55:47"

INFO: searching backup which contained archived WAL files to be restored

INFO: backup "2023-02-01 14:55:47" is valid

INFO: restoring WAL files from backup "2023-02-01 14:55:47"

INFO: restoring online WAL files and server log files

INFO: create pg_rman_recovery.conf for recovery-related parameters.

INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists

INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf

INFO: generating recovery.signal

INFO: removing standby.signal if exists to restore as primary

INFO: restore complete

HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.

[postgres@node2 ~]$ pg_ctl -D /home/postgresql/data -l /home/postgresql/data/logfile start

waiting for server to start.... done

server started

[postgres@node2 ~]$ psql

psql (13.4)

Type "help" for help.

postgres=# \c repmgr

You are now connected to database "repmgr" as user "postgres".

repmgr=# select * from t1;

id

----

1

2

(2 rows)

repmgr=#

增备恢复成功

4、删除备份
[postgres@node2 ~]$ pg_rman show

=====================================================================

StartTime EndTime Mode Size TLI Status

=====================================================================

2023-02-01 15:40:23 2023-02-01 15:40:25 FULL 56MB 2 OK

2023-02-01 15:38:40 2023-02-01 15:38:42 FULL 56MB 2 OK

2023-02-01 15:36:06 2023-02-01 15:36:08 FULL 56MB 2 OK

删除规定时间之前更旧的备份

[postgres@node2 ~]$ pg_rman -B /pg_rman_backup/fullbackup/ -D /home/postgresql/data delete "2023-02-01 15:38:40"

WARNING: cannot delete backup with start time "2023-02-01 15:38:40"

DETAIL: This is the latest full backup necessary for successful recovery.

INFO: delete the backup with start time: "2023-02-01 15:36:06"

[postgres@node2 ~]$ pg_rman show

=====================================================================

StartTime EndTime Mode Size TLI Status

=====================================================================

2023-02-01 15:40:23 2023-02-01 15:40:25 FULL 56MB 2 OK

2023-02-01 15:38:40 2023-02-01 15:38:42 FULL 56MB 2 OK