PostgreSql备份恢复工具之pg_rman详解（支持PITR恢复）

更多信息，请到墨天轮查阅：

墨天轮地址

链接二维码如下：

PGFans社区：

一、Pg_rman功能概述：

1.1、pg_rman功能：

pg_rman是一个专为PostgreSQL数据库设计的备份与恢复工具，它以高效、可靠和易于管理的方式，帮助数据库管理员应对数据保护的各种挑战。pg_rman支持全量、增量、差异备份，并支持在线备份，这意味着在备份过程中不影响数据库的正常运行。此外，pg_rman还提供了验证备份完整性和快速恢复的功能，确保数据安全性。

1.2、pg_rman的主要功能包括：

· 备份策略：支持三种备份模式：完整备份、增量备份和差异备份。

· 在线备份：利用PostgreSQL的逻辑复制功能实现在线备份，无需停止服务。

· 备份验证：提供备份验证功能，用于检查备份文件是否完整且可恢复。

· 快速恢复：利用归档日志和时间点恢复技术，pg_rman可以将数据库恢复到任何指定的时间点。

1.3、pg_rman的安装和使用

· pg_rman可以通过源码包安装

· 也可以通过RPM包安装。

· 安装后，需要设置环境变量，并初始化备份目录。

· 使用pg_rman时，可以执行全量备份、增量备份、恢复数据库等操作。

· pg_rman还支持备份验证和删除备份文件等功能。

1.4、功能一览图

二、pg_rman备份过程

2.1、部署pg_rman

2.1.1、下载pg_rman

下载地址：pg_rman工具下载地址

2.1.2、解压pg_rman压缩文件

代码如下：

[postgres@PostgreSQL_Beta install]$

[postgres@PostgreSQL_Beta install]$ tar -xvf pg_rman-1.3.16.tar.gz

Archive:  pg_rman-1.3.16.zip

68c2e8e57c1be752a54146605391af9abc6d128f

   creating: pg_rman-1.3.16/

   creating: pg_rman-1.3.16/.github/

   creating: pg_rman-1.3.16/.github/workflows/

  inflating: pg_rman-1.3.16/.github/workflows/build.yml

  inflating: pg_rman-1.3.16/.gitignore

  inflating: pg_rman-1.3.16/COPYRIGHT

  inflating: pg_rman-1.3.16/Makefile

  inflating: pg_rman-1.3.16/README.md

  inflating: pg_rman-1.3.16/backup.c

  inflating: pg_rman-1.3.16/catalog.c

  inflating: pg_rman-1.3.16/data.c

  inflating: pg_rman-1.3.16/delete.c

  inflating: pg_rman-1.3.16/dir.c

   creating: pg_rman-1.3.16/docs/

  inflating: pg_rman-1.3.16/docs/index-ja.html

  inflating: pg_rman-1.3.16/docs/index.html

  inflating: pg_rman-1.3.16/docs/style.css

   creating: pg_rman-1.3.16/expected/

  inflating: pg_rman-1.3.16/expected/arc_srv_log_management.out

  inflating: pg_rman-1.3.16/expected/backup.out

  inflating: pg_rman-1.3.16/expected/backup_from_standby.out

  inflating: pg_rman-1.3.16/expected/backup_management.out

  inflating: pg_rman-1.3.16/expected/delete.out

  inflating: pg_rman-1.3.16/expected/init.out

  inflating: pg_rman-1.3.16/expected/option.out

  inflating: pg_rman-1.3.16/expected/purge.out

  inflating: pg_rman-1.3.16/expected/restore.out

  inflating: pg_rman-1.3.16/expected/restore_checksum.out

  inflating: pg_rman-1.3.16/expected/restore_checksum_1.out

  inflating: pg_rman-1.3.16/expected/show.out

  inflating: pg_rman-1.3.16/idxpagehdr.h

  inflating: pg_rman-1.3.16/init.c

  inflating: pg_rman-1.3.16/parray.c

  inflating: pg_rman-1.3.16/parray.h

  inflating: pg_rman-1.3.16/pg_rman.c

  inflating: pg_rman-1.3.16/pg_rman.h

   creating: pg_rman-1.3.16/pgsql_src/

  inflating: pg_rman-1.3.16/pgsql_src/COPYRIGHT.pgsql_src

  inflating: pg_rman-1.3.16/pgsql_src/pg_ctl.c

   creating: pg_rman-1.3.16/pgut/

  inflating: pg_rman-1.3.16/pgut/pgut-port.c

  inflating: pg_rman-1.3.16/pgut/pgut-port.h

  inflating: pg_rman-1.3.16/pgut/pgut.c

  inflating: pg_rman-1.3.16/pgut/pgut.h

  inflating: pg_rman-1.3.16/restore.c

   creating: pg_rman-1.3.16/script/

  inflating: pg_rman-1.3.16/script/snapshot_script_lvm.sh

  inflating: pg_rman-1.3.16/show.c

   creating: pg_rman-1.3.16/sql/

  inflating: pg_rman-1.3.16/sql/arc_srv_log_management.sh

 extracting: pg_rman-1.3.16/sql/arc_srv_log_management.sql

  inflating: pg_rman-1.3.16/sql/backup.sh

 extracting: pg_rman-1.3.16/sql/backup.sql

  inflating: pg_rman-1.3.16/sql/backup_from_standby.sh

 extracting: pg_rman-1.3.16/sql/backup_from_standby.sql

  inflating: pg_rman-1.3.16/sql/backup_management.sh

 extracting: pg_rman-1.3.16/sql/backup_management.sql

  inflating: pg_rman-1.3.16/sql/common.sh

  inflating: pg_rman-1.3.16/sql/delete.sh

 extracting: pg_rman-1.3.16/sql/delete.sql

  inflating: pg_rman-1.3.16/sql/init.sh

 extracting: pg_rman-1.3.16/sql/init.sql

  inflating: pg_rman-1.3.16/sql/option.sh

 extracting: pg_rman-1.3.16/sql/option.sql

  inflating: pg_rman-1.3.16/sql/purge.sh

 extracting: pg_rman-1.3.16/sql/purge.sql

  inflating: pg_rman-1.3.16/sql/restore.sh

 extracting: pg_rman-1.3.16/sql/restore.sql

 extracting: pg_rman-1.3.16/sql/restore_checksum.sql

  inflating: pg_rman-1.3.16/sql/show.sh

 extracting: pg_rman-1.3.16/sql/show.sql

  inflating: pg_rman-1.3.16/util.c

  inflating: pg_rman-1.3.16/validate.c

  inflating: pg_rman-1.3.16/xlog.c

[postgres@PostgreSQL_Beta install]$

2.1.3、下载pg_rman

说明：切换到pg_rman根目录

[postgres@PostgreSQL_Beta install]$ cd pg_rman-1.3.16

2.1.4、编译安装pg_rman

代码如下：

[postgres@PostgreSQL_Beta pg_rman-1.3.16]$ make install

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o backup.o backup.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o catalog.o catalog.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o data.o data.c

data.c: 在函数‘figure_out_segno’中:

data.c:1270:8: 警告：变量‘scanned’被设定但未被使用 [-Wunused-but-set-variable]

   int  scanned;

        ^

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o delete.o delete.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o dir.o dir.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o init.o init.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o parray.o parray.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o pg_rman.o pg_rman.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o restore.o restore.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o show.o show.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o util.o util.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o validate.o validate.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o xlog.o xlog.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o pgsql_src/pg_ctl.o pgsql_src/pg_ctl.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o pgut/pgut.o pgut/pgut.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/postgres/server/include -lm -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o pgut/pgut-port.o pgut/pgut-port.c

gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 backup.o catalog.o data.o delete.o dir.o init.o parray.o pg_rman.o restore.o show.o util.o validate.o xlog.o pgsql_src/pg_ctl.o pgut/pgut.o pgut/pgut-port.o  -L/postgres/server/lib   -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -L/postgres/server/lib -lpq -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm -o pg_rman

/usr/bin/mkdir -p '/postgres/server/bin'

/usr/bin/install -c  pg_rman '/postgres/server/bin'

[postgres@PostgreSQL_Beta pg_rman-1.3.16]$

2.2、设置pg_rman的环境变量

代码如下：

[postgres@PostgreSQLBeta ~]$

[postgres@PostgreSQLBeta ~]$ mkdir -p /postgres/rman/srvlog

[postgres@PostgreSQLBeta ~]$ mkdir -p /postgres/rman/arclog

[postgres@PostgreSQLBeta ~]$ chown -R postgres:postgres /postgres/

[postgres@PostgreSQLBeta ~]$

[postgres@PostgreSQLBeta ~]$ chmod -R 777 /postgres/rman

[postgres@PostgreSQLBeta ~]$

[postgres@PostgreSQLBeta ~]$ mkdir -p /postgres/rman/backup

[postgres@PostgreSQLBeta ~]$

[postgres@PostgreSQLBeta ~]$ chmod -R 777 /postgres/rman

[postgres@PostgreSQLBeta ~]$ chown -R postgres:postgres /postgres/

2.2.1.编辑/home/postgres/.bashrc

代码如下：

[postgres@node1 ~]$  vi  /home/postgres/.bashrc

[postgres@node1 ~]$

2.2.2.增加pg_rman备份路径

代码如下：

# .bashrc

# Source global definitions

if [ -f /etc/bashrc ]; then

        . /etc/bashrc

fi

# User specific environment

if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]]

then

    PATH="$HOME/.local/bin:$HOME/bin:$PATH"

fi

export PATH

# Uncomment the following line if you don't like systemctl's auto-paging feature:

# export SYSTEMD_PAGER=

# User specific aliases and functions

export PGPORT=5432

export PGUSER=postgres

export PGHOME=/postgres/server

export PGDATA=/postgres/data

export PATH=$PGHOME/bin:$PATH

export PG_RMAN=/postgres/server/bin/pg_rman/bin

export SRVLOG_PATH=/postgres/rman/srvlog

export ARCLOG_PATH=/postgres/rman/arclog

export BACKUP_PATH=/postgres/rman/backup

2.2.3.使环境变量生效

代码如下：

[postgres@node1 ~]$ source /home/postgres/.bashrc

[postgres@node1 ~]$

2.3、开启归档

代码如下：

[postgres@node1 ~]$  vi $PGDATA/postgresql.conf

[postgres@node1 ~]$

wal_level='replica'

 archive_mode = on

archive_command ='test ! -f /postgres/archive/%f && cp %p /postgres/archive/%f'

restore_command='cp /postgres/archive/%f %p'

max_wal_senders=10

2.4、初始化pg_rman工具

代码如下：

[postgres@PostgreSQLBeta pg_rman-1.3.16]$ pg_rman init -B /postgres/rman/backup

INFO: ARCLOG_PATH is set to '/postgres/rman/arclog'

INFO: SRVLOG_PATH is set to '/postgres/rman/srvlog'

[postgres@PostgreSQLBeta pg_rman-1.3.16]$

2.5、查看pg_rman帮助

代码如下：

[postgres@node1 ~]$ 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 detail [DATE]        #验证备份

  pg_rman OPTION validate [DATE]            #删除备份信息

  pg_rman OPTION delete DATE               #删除实际的备份文件

  pg_rman OPTION purge                   #取消备份计划

Common Options:

  -D, --pgdata=PATH         数据库存放位置

  -A, --arclog-path=PATH      归档WAL日志存放位置

  -S, --srvlog-path=PATH    服务器日志存放位置

  -B, --backup-path=PATH    备份文件存放位置

  -G, --pgconf-path=PATH    配置文件存放位置

  -c, --check               显示正在进行什么操作

  -v, --verbose             显示详细信息

  -P, --progress           显示已处理文件的进度

Backup options:

  -b, --backup-mode=MODE    full, incremental, or archive：完整、增量或归档

  -s, --with-serverlog      还备份服务器日志文件

  -Z, --compress-data       用zlib压缩数据备份

  -C, --smooth-checkpoint   备份前先做检查点checkpoint

  -F, --full-backup-on-error   切换到完全备份模式

                               if pg_rman cannot find validate full backup

                               on current timeline

      NOTE: this option is only used in --backup-mode=incremental or archive.

如果pg_rman在当前时间线上找不到验证完整备份，就会切换到完全备份模式

注意：此选项仅用于--backup mode=incremental或archive。

  --keep-data-generations=NUM  保留NUM份完整数据备份

  --keep-data-days=NUM        保留足够的数据备份以恢复到N天前

  --keep-arclog-files=NUM   保留存档WAL的NUM

  --keep-arclog-days=DAY    保持存档的WAL在N天修改

  --keep-srvlog-files=NUM   保留NUM天服务器日志

  --keep-srvlog-days=DAY    在DAY天内修改服务器日志

  --standby-host=HOSTNAME  从待机状态进行备份时的待机主机

  --standby-port=PORT       从待机状态进行备份时的待机端口

Restore options:

  --recovery-target-time    将进行恢复的时间戳

  --recovery-target-xid     将继续恢复的事务ID

  --recovery-target-inclusive  是否在恢复目标后立即停止

  --recovery-target-timeline  恢复到特定时间线

  --recovery-target-action    action the server should take once the recovery target is reached 达到恢复目标后服务器应采取的操作

  --hard-copy               复制archivelog非符号链接

Catalog options:

  -a, --show-all            显示已删除的备份

Delete options:

  -f, --force               强制删除早于给定日期的备份

Connection options:

  -d, --dbname=DBNAME      数据库名称

  -h, --host=HOSTNAME       主机名称或者IP地址

  -p, --port=PORT           端口

  -U, --username=USERNAME  用户名

  -w, --no-password         从不提示输入密码

  -W, --password            强制密码提示

Generic options:

  -q, --quiet               不显示任何INFO或DEBUG消息

  --debug                  显示调试信息

  --help                   显示帮助，然后退出

  --version                 输出版本信息，然后退出

Read the website for details. <http://github.com/ossc-db/pg_rman>

Report bugs to <http://github.com/ossc-db/pg_rman/issues>.

[postgres@node1 ~]$

2.6、查看pg_rman备份文件

代码如下：

[postgres@PostgreSQLBeta pg_rman-1.3.16]$  ls /postgres/rman/

arclog  backup  srvlog

三、问题

3.1、全量备份：

代码如下：

[postgres@PostgreSQLBeta pg_rman-1.3.16]$ pg_rman backup --backup-mode=full -C

INFO: copying database files

2024-06-19 10:53:48.162 CST [41625] LOG:  checkpoint starting: force wait

2024-06-19 10:53:48.169 CST [41625] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.002 s, total=0.007 s; sync files=2, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=16384 kB; lsn=0/A000080, redo lsn=0/A000028

INFO: copying archived WAL files

INFO: backup complete

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

[postgres@PostgreSQLBeta pg_rman-1.3.16]$ pg_rman validate

INFO: validate: "2024-06-19 10:53:48" backup and archive log files by CRC

INFO: backup "2024-06-19 10:53:48" is valid

[postgres@PostgreSQLBeta pg_rman-1.3.16]$

3.2、增量备份：

代码如下：

postgres=# create table t1(id int);

CREATE TABLE

postgres=# insert into t1 values (generate_series(1,100));

INSERT 0 100

postgres=#

[postgres@node1 ~]$ pg_rman backup --backup-mode=incremental -C

INFO: copying database files

2024-06-13 05:17:40.088 CST [1759] LOG:  checkpoint starting: force wait

2024-06-13 05:17:40.113 CST [1759] LOG:  checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.001 s, sync=0.001 s, total=0.025 s; sync files=1, longest=0.001 s, average=0.001 s; distance=32768 kB, estimate=32768 kB; lsn=0/18000060, redo lsn=0/18000028

INFO: copying archived WAL files

INFO: backup complete

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

[postgres@node1 ~]$

3.3、归档WAL备份：

代码如下：

 [postgres@node1 ~]$ pg_rman backup --backup-mode=archive

INFO: copying archived WAL files

INFO: backup complete

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

[postgres@node1 ~]$

3.4、压缩备份：

代码如下：

[postgres@node1 ~]$ pg_rman backup --backup-mode=full -Z

INFO: copying database files

2024-06-17 03:01:13.986 CST [1855] LOG:  checkpoint starting: immediate force wait

2024-06-17 03:01:13.993 CST [1855] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.008 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16383 kB, estimate=16383 kB; lsn=0/1C000060, redo lsn=0/1C000028

INFO: copying archived WAL files

INFO: backup complete

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

[postgres@node1 ~]$

3.5、基于PITR备份恢复：

代码如下：

1、添加测试数据

postgres=# create table t2(id int);

CREATE TABLE

postgres=# insert into t2 values (generate_series(1,100));

INSERT 0 100

postgres=# \q

2、模拟误删数据：

[postgres@PostgreSQL_Beta ~]$ psql

psql (17beta1)

Type "help" for help.

postgres=# drop table t2;

DROP TABLE

postgres=# \q

3、停止PostgreSQL服务

[postgres@node1 ~]$

[postgres@node1 ~]$ pg_ctl stop

waiting for server to shut down....2024-06-17 05:00:28.174 CST [3255] LOG:  received fast shutdown request

2024-06-17 05:00:28.177 CST [3255] LOG:  aborting any active transactions

2024-06-17 05:00:28.602 CST [3256] LOG:  shutting down

2024-06-17 05:00:28.602 CST [3256] LOG:  restartpoint starting: shutdown immediate

2024-06-17 05:00:28.621 CST [3256] LOG:  restartpoint complete: wrote 18 buffers (0.1%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.010 s, sync=0.001 s, total=0.020 s; sync files=16, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=16384 kB; lsn=0/1D0000C0, redo lsn=0/1D000088

2024-06-17 05:00:28.621 CST [3256] LOG:  recovery restart point at 0/1D000088

2024-06-17 05:00:28.621 CST [3256] DETAIL:  Last completed transaction was at log time 2024-06-17 03:01:23.812887+08.

2024-06-17 05:00:28.628 CST [3255] LOG:  database system is shut down

 done

server stopped

```

##### 4、并删除$PGDATA目录下的文件

```

[postgres@PostgreSQLBeta ~]$ rm -rf /postgres/data//*

[postgres@PostgreSQLBeta ~]$

4、并删除$PGDATA目录下的文件

[postgres@PostgreSQLBeta ~]$ rm -rf /postgres/data//*

[postgres@PostgreSQLBeta ~]$

5、恢复到2024-06-16 19:20

[postgres@node1 ~]$

[postgres@node1 ~]$ pg_rman restore -B /postgres/rman --recovery-target-time='2024-06-16 19:20:53' -A /postgres/rman/20240617/032820/arclog -S /postgres/rman/20240617/032820/srvlog

INFO: the recovery target timeline ID is not given

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

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: "2024-06-13 05:16:37"

INFO: copying online WAL files and server log files

INFO: clearing restore destination

INFO: validate: "2024-06-13 05:16:37" backup and archive log files by SIZE

INFO: backup "2024-06-13 05:16:37" is valid

INFO: restoring database files from the full mode backup "2024-06-13 05:16:37"

INFO: searching incremental backup to be restored

INFO: validate: "2024-06-13 05:17:29" backup and archive log files by SIZE

INFO: backup "2024-06-13 05:17:29" is valid

INFO: restoring database files from the incremental mode backup "2024-06-13 05:17:29"

INFO: validate: "2024-06-13 05:17:40" backup and archive log files by SIZE

INFO: backup "2024-06-13 05:17:40" is valid

INFO: restoring database files from the incremental mode backup "2024-06-13 05:17:40"

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

INFO: backup "2024-06-13 05:17:40" is valid

INFO: restoring WAL files from backup "2024-06-13 05:17:40"

INFO: validate: "2024-06-13 06:01:42" archive log files by SIZE

INFO: backup "2024-06-13 06:01:42" is valid

INFO: restoring WAL files from backup "2024-06-13 06:01:42"

INFO: backup "2024-06-17 03:01:13" is valid

INFO: restoring WAL files from backup "2024-06-17 03:01:13"

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.

6、启动PostgreSQL服务

[postgres@node1 ~]$ pg_ctl start

waiting for server to start....2024-06-17 05:00:40.905 CST [3375] LOG:  starting PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

2024-06-17 05:00:40.905 CST [3375] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2024-06-17 05:00:40.905 CST [3375] LOG:  listening on IPv6 address "::", port 5432

2024-06-17 05:00:40.913 CST [3375] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2024-06-17 05:00:40.919 CST [3378] LOG:  database system was interrupted; last known up at 2024-06-13 05:17:40 CST

2024-06-17 05:00:40.942 CST [3378] LOG:  starting point-in-time recovery to 2024-06-16 19:20:53+08

2024-06-17 05:00:40.944 CST [3378] LOG:  starting backup recovery with redo LSN 0/18000028, checkpoint LSN 0/18000060, on timeline ID 1

2024-06-17 05:00:41.025 CST [3378] LOG:  restored log file "000000010000000000000018" from archive

2024-06-17 05:00:41.032 CST [3378] LOG:  redo starts at 0/18000028

2024-06-17 05:00:41.089 CST [3378] LOG:  restored log file "000000010000000000000019" from archive

2024-06-17 05:00:41.161 CST [3378] LOG:  restored log file "00000001000000000000001A" from archive

2024-06-17 05:00:41.163 CST [3378] LOG:  completed backup recovery with redo LSN 0/18000028 and end LSN 0/18000100

2024-06-17 05:00:41.163 CST [3378] LOG:  consistent recovery state reached at 0/18000100

2024-06-17 05:00:41.163 CST [3375] LOG:  database system is ready to accept read-only connections

2024-06-17 05:00:41.230 CST [3378] LOG:  restored log file "00000001000000000000001B" from archive

 done

server started

7、检查恢复情况

[postgres@node1 ~]$ psql

psql (16.2)

Type "help" for help.

postgres=# \d

                     List of relations

 Schema |            Name            |   Type   |  Owner

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

 public | actor            | table | postgres

 public | address          | table | postgres

 public | category         | table | postgres

 public | city             | table | postgres

 public | country          | table | postgres

 public | customer         | table | postgres

 public | film             | table | postgres

 public | film_actor       | table | postgres

 public | film_category    | table | postgres

 public | inventory        | table | postgres

 public | language         | table | postgres

 public | payment          | table | postgres

 public | payment_p2007_01 | table | postgres

 public | payment_p2007_02 | table | postgres

 public | payment_p2007_03 | table | postgres

 public | payment_p2007_04 | table | postgres

 public | payment_p2007_05 | table | postgres

 public | payment_p2007_06 | table | postgres

 public | rental           | table | postgres

 public | staff            | table | postgres

 public | store            | table | postgres

 public | t1               | table | postgres

 public | t2               | table | postgres

(23 rows)

postgres=#

postgres=#

3.6、验证备份文件：

代码如下：

[postgres@PostgreSQLBeta ~]$ pg_rman validate

INFO: validate: "2024-06-22 17:46:24" backup and archive log files by CRC

INFO: backup "2024-06-22 17:46:24" is valid

INFO: validate: "2024-06-22 20:20:10" backup and archive log files by CRC

INFO: backup "2024-06-22 20:20:10" is valid

3.7、查看备份文件：

代码如下：

[postgres@PostgreSQL_Beta ~]$ pg_rman show detail

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

 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status

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

2024-06-17 17:13:08  2024-06-17 17:14:14  INCR  5733kB    83MB    ----    87MB       false       1          0  OK

2024-06-17 17:07:59  2024-06-17 17:08:01  FULL    31MB   201MB    ----   224MB       false       1          0  OK

2024-06-17 17:04:22  2024-06-17 17:04:22  INCR      0B    ----    ----      0B       false       1          0  ERROR

2024-06-17 17:04:03  2024-06-17 17:04:05  FULL    32MB   134MB    ----   157MB       false       1          0  OK

2024-06-17 17:01:27  2024-06-17 17:01:28  INCR      0B    ----    ----      0B       false       1          0  ERROR

2024-06-17 16:23:28  2024-06-17 16:23:30  FULL    32MB    33MB    ----    56MB       false       1          0  OK

3.8、删除过期的备份文件

代码如下：

[postgres@PostgreSQL_Beta ~]$ pg_rman delete -B /postgres/rman '2024-06-17 17:04:22'

INFO: delete the backup with start time: "2024-06-17 17:04:22"

WARNING: cannot delete backup with start time "2024-06-17 17:04:03"

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

INFO: delete the backup with start time: "2024-06-17 17:01:27"

INFO: delete the backup with start time: "2024-06-17 17:01:17"

INFO: delete the backup with start time: "2024-06-17 16:26:52"

INFO: delete the backup with start time: "2024-06-17 16:25:57"

INFO: delete the backup with start time: "2024-06-17 16:25:26"

INFO: delete the backup with start time: "2024-06-17 16:24:55"

INFO: delete the backup with start time: "2024-06-17 16:24:27"

INFO: delete the backup with start time: "2024-06-17 16:23:28"

[postgres@PostgreSQL_Beta ~]$

[postgres@PostgreSQL_Beta ~]$ pg_rman show detail

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

 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status

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

2024-06-17 17:13:08  2024-06-17 17:14:14  INCR  5733kB    83MB    ----    87MB       false       1          0  OK

2024-06-17 17:07:59  2024-06-17 17:08:01  FULL    31MB   201MB    ----   224MB       false       1          0  OK

2024-06-17 17:04:03  2024-06-17 17:04:05  FULL    32MB   134MB    ----   157MB       false       1          0  OK

[postgres@PostgreSQL_Beta ~]$

四、问题

1、问题：ERROR: could not remove directory "/postgres/wal/archive_status": 目录非空

· 说明：执行恢复数据操作时，需清除/postgres/wal/archive_status/的文件

[root@PostgreSQL_Beta ~]# rm -rf /postgres/wal/archive_status/*

[root@PostgreSQL_Beta ~]#

五、总结

5.1、pg_rman相比其他备份工具有哪些优势?

pg_rman是专为PostgreSQL设计的备份恢复工具，它具有以下几个显著优势：

· 支持多种备份模式：pg_rman支持全量备份、增量备份和归档备份，这使得它能够灵活地应对不同的备份需求，特别是对于大型数据库，增量备份可以大大减少备份所需的时间和空间。

· 备份压缩：pg_rman支持通过gzip工具实现备份数据的压缩，这样可以有效减少备份文件的大小，节省存储空间。

· 自动备份维护：pg_rman能够自动删除过期的WAL备份文件，保持备份目录的整洁，同时也减轻了管理员的工作负担。

· 备份验证：pg_rman提供了备份验证功能，确保备份的数据完整性和可用性，这对于数据安全至关重要。

· 恢复期间无事务丢失：pg_rman支持基于PITR（Point In Time Recovery）的配置文件生成器，允许用户恢复到特定时间点的数据状态，而且在恢复过程中不会丢失任何事务。

· 使用简单：pg_rman的操作界面相对简洁，通过单个命令即可完成备份和恢复的操作，降低了学习和使用的难度。

· 支持在线备份：pg_rman支持在线备份，即在数据库运行的情况下进行备份，无需停止数据库服务，这对于生产环境中非常重要。

5.2、使用pg_rman备份工具的注意事项

· 数据覆盖风险：备份与现有数据可能存在冲突，需谨慎操作防止数据丢失。

· 主备同步影响：备份过程可能影响主备数据库同步，确保备份策略不影响服务连续性。

· 恢复策略规划：制定详尽的恢复计划，考虑不同场景下的数据恢复顺序和目标。