PostgreSQL学习实践：PG15使用pg_rman1.3进行备份恢复

开源软件联盟PostgreSQL分会2024年01月26日约 11 分钟

pg_rman 是一款用于备份和恢复 PostgreSQL 数据库的插件。其功能类似于 Oracle 的 RMAN 工具

支持以下三种备份类型：

完整备份：备份整个数据库集群。
增量备份：仅备份在同一时间线上次验证备份后修改的文件或页面。
归档 WAL备份：仅备份归档WAL文件。

部署安装：

下载地址：

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

安装：

#上传文件至/opt
cd /opt
#解压文件：
tar zxvf pg_rman-1.3.16-pg15.tar.gz
#解压完成后：
chown -R postgres.postgres /opt/pg_rman-1.3.16-pg15
su - postges
cd /opt/pg_rman-1.3.16-pg15
make
make install
#安装完成后校验版本
[postgres@wypg15 pg_rman-1.3.16-pg15]$ pg_rman --version
pg_rman 1.3.16
#安装位置
[postgres@wypg15 pg_rman-1.3.16-pg15]$ which pg_rman
/postgresql/pg15/bin/pg_rman
#使用帮助
[postgres@wypg15 pg_rman-1.3.16-pg15]$ 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
CommonOptions:
-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
Read the website for details. <http://github.com/ossc-db/pg_rman>
Report bugs to <http://github.com/ossc-db/pg_rman/issues>.

配置环境变量：

#使用root用户创建文件夹与授权：
mkdir -p /bak/{backup,arch_bak,srvlog}
chown -R postgres.postgres /bak/{backup,arch_bak,srvlog}
[root@wypg15 ~]# ll /bak
total 0
drwxr-xr-x.2 postgres postgres 6Jan921:48 arch_bak
drwxr-xr-x.2 postgres postgres 6Jan921:48 backup
drwxr-xr-x.2 postgres postgres 6Jan921:48 srvlog
#切换到postgres用户并输入cd命令到根目录
su - postgres
vi .bashrc
export PG_RMAN=/postgresql/pg15/bin
export SRVLOG_PATH=/bak/srvlog
export ARCLOG_PATH=/bak/arch_bak
export BACKUP_PATH=/bak/backup
source .bashrc

语法及说明：

pg_rman [ OPTIONS ]{ init |
                      backup |
                      restore |
                      show [ DATE | detail ]|
                      validate [ DATE ]|
delete DATE |
                      purge }
init 初始化备份目录。
backup 在线备份。
restore 恢复。
show 显示备份历史记录。详细信息选项显示每个备份的附加信息。
validate 验证备份文件。未经验证的备份不能用于恢复和增量备份。
delete删除备份文件。
purge 从备份目录中删除已删除的备份。

初始化备份目录：

[postgres@wypg15 ~]$  pg_rman init -B /bak/backup
INFO: ARCLOG_PATH isset to '/bak/arch_bak'
INFO: SRVLOG_PATH isset to '/bak/srvlog'
[postgres@wypg15 bak]$ cd /bak/backup
[postgres@wypg15 backup]$ ll
total 8
drwx------.4 postgres postgres 34Jan921:52 backup
-rw-rw-r--.1 postgres postgres 55Jan921:52 pg_rman.ini
-rw-rw-r--.1 postgres postgres 40Jan921:52 system_identifier
drwx------.2 postgres postgres  6Jan921:52 timeline_history

查看配置文件：

[postgres@wypg15 backup]$ cat pg_rman.ini
ARCLOG_PATH='/bak/arch_bak'
SRVLOG_PATH='/bak/srvlog'
#配置文件中可增加备份策略

完整备份：

[postgres@wypg15 backup]$ pg_rman backup --backup-mode=full --with-serverlog --progress
INFO: copying database files
Processed1608 of 1608 files, skipped 0
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@wypg15 backup]$ pg_rman  validate
INFO: validate:"2024-01-09 21:57:06" backup, archive log files and server log files by CRC
INFO: backup "2024-01-09 21:57:06"is valid
[postgres@wypg15 backup]$ pg_rman show
=====================================================================
StartTimeEndTimeModeSize   TLI  Status
=====================================================================
2024-01-0921:57:062024-01-0921:57:09  FULL    28MB1  OK

增量备份：

[postgres@wypg15 backup]$ pg_rman backup --backup-mode=incremental --progress --compress-data
INFO: copying database files
Processed1608 of 1608 files, skipped 1575
INFO: copying archived WAL files
INFO: backup complete
INFO:Please execute 'pg_rman validate' to verify the files are correctly copied.

验证及查看备份文件：

[postgres@wypg15 backup]$ pg_rman  validate
INFO: validate:"2024-01-09 22:00:19" backup and archive log files by CRC
INFO: backup "2024-01-09 22:00:19"is valid
[postgres@wypg15 backup]$ pg_rman show
=====================================================================
StartTimeEndTimeModeSize   TLI  Status
=====================================================================
2024-01-0922:00:192024-01-0922:00:22  INCR   1516B1  OK
2024-01-0921:57:062024-01-0921:57:09  FULL    28MB1  OK

归档备份：

[postgres@wypg15 backup]$ pg_rman show
=====================================================================
StartTimeEndTimeModeSize   TLI  Status
=====================================================================
2024-01-0922:00:192024-01-0922:00:22  INCR   1516B1  OK
2024-01-0921:57:062024-01-0921:57:09  FULL    28MB1  OK

验证及查看备份文件：

[postgres@wypg15 backup]$ pg_rman backup --backup-mode=archive --progress --compress-data
INFO: copying archived WAL files
INFO: backup complete
INFO:Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@wypg15 backup]$ pg_rman validate
INFO: validate:"2024-01-09 22:03:42" archive log files by CRC
INFO: backup "2024-01-09 22:03:42"is valid

如何删除备份集：

pg_rman删除备份集
pg_rman delete-f "starttime"
清除备份集
pg_rman purge

恢复：

pg_rman 将备份的数据恢复到目标数据库集群路径中。

恢复前应停止 PostgreSQL 服务器。另外，不要删除原始数据库集群，因为pg_rman必须从中检查时间线ID或数据校验和状态。恢复命令将保存未归档的事务日志并删除所有数据库文件。您可以重试恢复，直到创建新的备份。恢复文件后，pg_rman 在中创建recovery.conf $PGDATA.conf 文件包含恢复参数，如果需要，您也可以修改该文件。

pg_rman 恢复时配置恢复相关的guc参数。配置文件取决于PostgreSQL的版本和pg_rman的版本。如果需要，请手动修改文件后启动服务器并执行 PITR。

PostgreSQL的版本低于12：pg_rman创建并配置$PGDATA/recovery.conf
PostgreSQL 的版本为 12 或更高，pg_rman 的版本为 1.3.12 或更低：pg_rman 将与恢复相关的配置附加到$PGDATA/postgresql.conf并创建 $PGDATA/recovery.signal.
PostgreSQL 的版本为 12 或更高，并且 pg_rman 的版本高于 1.3.12：pg_rman 创建并配置$PGDATA/pg_rman_recovery.conf，并将include 指令附加到$PGDATA/postgresql.conf. 如果include过去恢复 pg_rman 时添加了指令，请将其删除。它创建了$PGDATA/recovery.signal.

恢复选项：

--recovery-target-timeline TIMELINE
#如果不指定时间线，则使用$PGDATA/global/pg_control，如果没有$PGDATA/global/pg_control，则使用最新的全量备份集的时间线
--recovery-target-time TIMESTAMP
#此参数指定恢复将继续进行的时间戳。如果未指定，则继续恢复到最新时间。
--recovery-target-xid XID
此参数指定恢复将继续进行的事务 ID。如果未指定，则继续恢复到最新的xid。
--recovery-target-inclusive
#是否在指定的恢复目标（true）之后停止，默认为true，如果指定false意识是在恢复目标之前停止
--hard-copy 
#是否使用硬链接复制archive log，如果不指定使用符号连接（软连接）的方式。

恢复实验：

全备数据：

[postgres@wypg15 ~]$ pg_rman backup --backup-mode=full --with-serverlog --progress
INFO: copying database files
Processed1608 of 1608 files, skipped 0
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@wypg15 backup]$ psql
psql (15.3)
Type"help"for help.
postgres=# \l
List of databases
Name|Owner|Encoding|Collate|Ctype| ICU Locale|LocaleProvider|Access privileges
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |
 template0 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |=c/postgres          +
||||||| postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |=c/postgres          +
||||||| postgres=CTc/postgres
 wydb01    | wy01     | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |
 wydb03    | wy03     | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |
(5 rows)

模拟故障：

pg_ctl stop
rm -rf /pgdata/data
[postgres@wypg15 data]$ psql
could not identify current directory:No such file or directory
psql: error: connection to server on socket "/tmp/.s.PGSQL.6543" failed: FATAL:  could not open file "global/pg_filenode.map":No such file or directory

恢复：

[postgres@wypg15 arch]$  pg_rman restore -B /bak/backup -A  /arch  -hard_copy  -D /pgdata/data
INFO: the recovery target timeline ID isnot 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 asbasein recovery:"2024-01-09 22:33:57"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate:"2024-01-09 22:33:57" backup, archive log files and server log files by SIZE
INFO: backup "2024-01-09 22:33:57"is valid
INFO: restoring database files from the full mode backup "2024-01-09 22:33:57"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2024-01-09 22:33:57"is valid
INFO: restoring WAL files from backup "2024-01-09 22:33:57"
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 isdone, we recommend to remove recovery-related parameters configured by pg_rman.
[postgres@wypg15 arch]$ pg_ctl start
waiting for server to start....2024-01-0914:43:08.637 GMT [33403] LOG:00000: redirecting log output to logging collector process
2024-01-0914:43:08.637 GMT [33403] HINT:Future log output will appear in directory "log".
2024-01-0914:43:08.637 GMT [33403] LOCATION:SysLogger_Start, syslogger.c:715
done
server started
[postgres@wypg15 arch]$ psql
psql (15.3)
Type"help"for help.
postgres=# \l
List of databases
Name|Owner|Encoding|Collate|Ctype| ICU Locale|LocaleProvider|Access privileges
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |
 template0 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |=c/postgres          +
||||||| postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |=c/postgres          +
||||||| postgres=CTc/postgres
 wydb01    | wy01     | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |
 wydb03    | wy03     | UTF8     | zh_CN.utf8 | zh_CN.utf8 || libc            |
(5 rows)

部署安装：

下载地址：

安装：

配置环境变量：

语法及说明：

初始化备份目录：

查看配置文件：

完整备份：

验证及查看备份文件：

增量备份：

验证及查看备份文件：

归档备份：

验证及查看备份文件：

如何删除备份集：

恢复：

恢复选项：

恢复实验：

全备数据：

查看数据库：

模拟故障：

恢复：

来自同一个声音

活动报名｜PostgreSQL & IvorySQL 技术交流 Meetup · 南京站

PPT下载来了 | 数据库开源发展联盟走进西安邮电大学活动回顾

PG 30 周年系列直播活动第二期！本周三晚与你相约！

【献礼西邮校园行】当开源数据库“遇见”空间天气：从底层数据仓库走向“空间智能基础设施”的跨界演进