PostgreSQL pg_rman备份恢复工具使用步骤（详细）

原创于 2025-10-05 17:46:03 发布 · 1.1k 阅读

16 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#postgresql #数据库 #sql #运维 #centos

数据库同时被 3 个专栏收录

105 篇文章

订阅专栏

PostgresSQL

103 篇文章

订阅专栏

PgSQL

101 篇文章

订阅专栏

pg_rman是Postgresql的⼀款插件，⽤于备份和还原PostgreSQL数据库；它需要对整个数据库集群，存档WAL和服务器日志进行物理在线备份。pg_rman⽀持使⽤PostgreSQL9.0之后从备用站点获取备份，还支持存储快照备份。

pg_rman的特点

仅使⽤⼀个命令即可对整个数据库（包括表空间）进⾏备份。只需⼀个命令即可从备份中恢复。
⽀持增量备份和备份⽂件压缩，以便占⽤更少的磁盘空间。
管理备份版本并显示备份⽬录。
⽀持存储快照。

pg_rman⽀持的命令

init --初始化备份⽬录。
backup --进⾏在线备份。
restore --还原。
show --显示备份历史记录。详细信息选项显示每个备份的附加信息。validate --验证备份⽂件。未经验证的备份不能⽤于还原和增量备份。delete --删除备份⽂件。
purge --从备份⽬录中删除已删除的备份。

## 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         location of the database storage area
  -A, --arclog-path=PATH    location of archive WAL storage area
  -S, --srvlog-path=PATH    location of server log storage area
  -B, --backup-path=PATH    location of the backup storage area
  -G, --pgconf-path=PATH    location of the configuration storage area
  -c, --check               show what would have been done
  -v, --verbose             show what detail messages
  -P, --progress            show progress of processed files

Backup options:
  -b, --backup-mode=MODE    full, incremental, or archive
  -s, --with-serverlog      also backup server log files
  -Z, --compress-data       compress data backup with zlib
  -C, --smooth-checkpoint   do smooth checkpoint before backup
  -F, --full-backup-on-error   switch to full backup mode
                               if pg_rman cannot find validate full backup
                               on current timeline
      NOTE: this option is only used in --backup-mode=incremental or archive.
  --keep-data-generations=NUM keep NUM generations of full data backup
  --keep-data-days=NUM        keep enough data backup to recover to N days ago
  --keep-arclog-files=NUM   keep NUM of archived WAL
  --keep-arclog-days=DAY    keep archived WAL modified in DAY days
  --keep-srvlog-files=NUM   keep NUM of serverlogs
  --keep-srvlog-days=DAY    keep serverlog modified in DAY days
  --standby-host=HOSTNAME   standby host when taking backup from standby
  --standby-port=PORT       standby port when taking backup from standby

Restore options:
  --recovery-target-time    time stamp up to which recovery will proceed
  --recovery-target-xid     transaction ID up to which recovery will proceed
  --recovery-target-inclusive whether we stop just after the recovery target
  --recovery-target-timeline  recovering into a particular timeline
  --recovery-target-action    action the server should take once the recovery target is reached
  --hard-copy                 copying archivelog not symbolic link

Catalog options:
  -a, --show-all            show deleted backup too

Delete options:
  -f, --force               forcibly delete backup older than given DATE

Connection options:
  -d, --dbname=DBNAME       database to connect
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

Generic options:
  -q, --quiet               don't show any INFO or DEBUG messages
  --debug                   show DEBUG messages
  --help                    show this help, then exit
  --version                 output version information, then exit

《本测试环境软件版本为pg15.14、pg_rman 3.15 / 3.16》

pg_rman安装

可以从以下链接下载对应版本的pg_rman：

https://github.com/ossc-db/pg_rman/releases

安装⽅法1：

–从源码编译安装，缺包编译报错需要下载对应包任何重试
$ cd pg_rman
$ make
$ make install
–将⽣成的pg_rman拷⻉到/opt/pgsql/15/bin、/usr/bin

###　安装⽅法2：
–RPM包安装【本测试环境软件版本为pg15.14、pg_rman 3.15/3.16】

rpm -ivh pg_rman-x.x.xx-x.pgxx.rhelx.x86_64.rpm

–默认⽣成在/usr/pgxxx下⾯
–将⽣成的pg_rman拷⻉到/opt/pgsql/15/bin、/usr/bin

使⽤pg_rman 前期准备创建备份和归档⽬录:

建议：备份和归档的⽬录需要单独挂盘，不能跟数据⽬录放⼀起，因为会影响在线系统IO，推荐挂NFS远程盘进⾏远程备份
创建备份⽬录

mkdir /home/backup/var/lib/pgsql/15/backupfull/pg_rman

创建wal归档⽬录

mkdir /home/backup/var/lib/pgsql/15/archive

配置环境变量

echo 'export BACKUP_PATH=/var/lib/pgsql/15/backupfull/pg_rman'>>~/.bash_profile
echo 'export ARCLOG_PATH=/var/lib/pgsql/15/archive'>>~/.bash_profile

–修改postgresql.conf配置

archive_mode=on #开启归档
archive_command= 'test ! -f /var/lib/pgsql/15/archive/%f && cp %p /var/lib/pgsql/15/archive/%f' #shell脚本
archive_timeout=900 #强制wal⽇志切换时间

–重启PG

systemctl restart postgresql-15.service

pg_rman 初始化

初始化

/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v init

vim /var/lib/pgsql/15/backupfull/pg_rman/pg_rman.ini 配置⽂件，配置备份、归档保存天数：

ARCLOG_PATH='/var/lib/pgsql/15/archive'
SRVLOG_PATH='/var/lib/pgsql/15/data/log'
#配置压缩参数
COMPRESS_DATA=YES
#归档⽇志保留天数
KEEP_ARCLOG_DAYS=14
#KEEP_ARCLOG_FILES=1000 --归档保留个数
#备份保留天数
KEEP_DATA_DAYS=14

设置备份策略

全库备份策略

整个数据库备份和存档备份

–可以配置到crontab中每周⽇凌晨⼀点执⾏⼀次

0 1 * * 0 postgres sh pg_fullback.sh

–脚本pg_fullback.sh，备份完必须执⾏validate，否则备份不能⽤于还原

/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v -b full backup
# 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

增量备份策略

增量备份和存档备份

可以配置到crontab中每⽇晚上执⾏⼀次

0 23 * * * postgres sh inrback.sh

–脚本inrback.sh

/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data-B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v -b incremental backup
# 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

wal归档备份

可以配置到crontab中每两小时执⾏⼀次

0 */2 * * *  postgres sh walback.sh

–脚本walback.sh

# wal备份命令
/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -s -v -b archive backup
# 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

查看备份情况

pg_rman show -B /var/lib/pgsql/15/backupfull/pg_rman
pg_rman show detail -B /var/lib/pgsql/15/backupfull/pg_rman

还原操作

任何数据库恢复操作，操作前都需要对原$PGDATA⽬录进⾏备份；

确认实例已经停掉

systemctl stop postgresql-15.service

–备份数据

cp -r /var/lib/pgsql/15/data /var/lib/pgsql/15/data_bak-$(date +%F)
cp -r /var/lib/pgsql/15/archive /var/lib/pgsql/15/archive_bak-$(date +%F)

还原到指定时间点or最新

恢复到指定时间点

/usr/pgsql-15/bin/pg_rman -D $PGDATA -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v restore --recovery-target-time '2020-12-02 03:04:54'

或

恢复到最新时间点：

/usr/pgsql-15/bin/pg_rman -D $PGDATA -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v restore

备份恢复测试案例

备份恢复【只做当前数据的全量备份和恢复】

一、

手动切换归档

select pg_switch_wal();

1、 – 检查是否有未归档的WAL 【必须要做，关系到数据恢复是否成功】

SELECT * FROM pg_stat_archiver WHERE last_failed_wal IS NOT NULL;
或
data/pg_wal/archive_status/xxxxxxxxxx.ready 【ready结尾的为未归档的wal文件 + 当前online wal文件，手动cp到archive下《1、cp data/pg_wal/* archive/ ；2、mv data/pg_wal/archive_status/*.ready data/pg_wal/archive_status/*.done》，再停库、恢复备份，否则全备会卡住】

查看归档延迟

SELECT 
    pg_walfile_name(pg_current_wal_lsn()) as current_wal,
    archived_count,
    last_archived_wal,
    last_archived_time
FROM pg_stat_archiver;

2、ready结尾未归档的wal文件 + 当前online wal文件，手动cp到archive下；并修改.ready为.done 【没有此步骤，则备份卡住】

1）cp data/pg_wal/* archive/ 
2）mv data/pg_wal/archive_status/*.ready data/pg_wal/archive_status/*.done

二、备份data和archive目录

cp -r data data-bak-$(date +%F)
cp -r archive archive-bak-$(date +%F)

三、全备

1、全备命令：

pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -s -v -b full backup
# 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

2、增量备份命令：

/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -s -v -b incremental backup
# 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

3、做一次wal备份，用于恢复wal备份的最近时间点or指定时间点

/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -s -v -b archive backup
# 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

四、停库

systemctl stop postgresql-15.service

五、恢复到wal备份的最近时间点or指定时间点

1、检查可用的时间范围
查看备份详情，确认：pg_rman show detail -B /var/lib/pgsql/15/backupfull/pg_rman 【EndTime为可恢复到的时间点】

======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2025-10-04 20:30:25  2025-10-04 20:39:05  FULL  4782MB  2315MB   363kB   805MB        true      10          9  OK

2、恢复到指定时间点

pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v --recovery-target-time '2025-10-04 15:30:12' restore

或

3、恢复到最近数据

pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v restore

【***生产上，停应用、把最近的全备、增备拷贝过来后，把online wal日志拷贝到archive目录后，再执行一次wal备份，停库、然后恢复wal备份的最近时间点or指定时间点】

恢复案例：【全备+增量+wal归档备份】

准备工作

–备份数据

cp -r /var/lib/pgsql/15/data /var/lib/pgsql/15/data_bak-$(date +%F)
cp -r /var/lib/pgsql/15/archive /var/lib/pgsql/15/archive_bak-$(date +%F)

1、日常全备

全备

- 全备命令
/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -s -v -b full backup 
- 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

2、每天增备

模仿日常写入数据

INSERT INTO author (id, person_info, book) 
VALUES 
(13, ('玉米', 27, true)::person, '张三的自传'),
(14, ('小麦', 36, false)::person, '张三的自传');

增量备份

- 增备命令
/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -s -v -b incremental backup 
- 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

3、每天定时备份wal归档备份

- wal备份命令
/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -s -v -b archive backup
- 验证数据
pg_rman validate -B /var/lib/pgsql/15/backupfull/pg_rman

4、恢复时可指定wal归档备份开始时间

检查备份时间：

pg_rman show detail -B /var/lib/pgsql/15/backupfull/pg_rman
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2025-10-04 22:08:21  2025-10-04 22:08:23  INCR   212kB    33MB   3652B    58kB        true      14         10  OK
2025-10-04 22:03:36  2025-10-04 22:06:03  FULL  4782MB  2717MB   3308B   805MB        true      14         10  OK

恢复数据

－停库：

systemctl stop postgresql-15.service

－清理data目录

rm -rf  /var/lib/pgsql/15/data/*

恢复到指定时间点

/usr/pgsql-15/bin/pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v  --recovery-target-time '2025-10-04 22:08:21' restore

或

恢复到最近时间点

pg_rman -D /var/lib/pgsql/15/data -B /var/lib/pgsql/15/backupfull/pg_rman -A /var/lib/pgsql/15/archive -P -v restore

【备注：用全备+增备，恢复数据到指定时间点or最近时间点，步骤一样，只是少了wal归档部分】