通过pg_rman对postgresql数据库进行备份还原操作

postgresql本身提供了pg_basebackup、pg_dump和pg_restore等工具对数据库进行备份还原操作。我在平时工作中还使用到另外一款开源软件：pg_rman。

一、软件获取

下载地址：https://github.com/ossc-db/pg_rman/releases
有在redhat平台上编译好的rpm包和源码包。如果是centos7上安装的postgresql 12版本，则下载pg_rman-1.3.9-1.pg12.rhel7.x86_64.rpm

二、使用pg_rman备份pg数据库

注：backup-mode可用参数为full（全备）, incremental（增量备份）, or archive（只备归档）。backup-path是指定要将备份文件保存在哪个位置，注意该目录要给postgres用户读写权限。

–先初始化备份环境，会在备份目录下生成一些文件和目录

$ pg_rman init --backup-mode=full --backup-path=/pgdata/12/backups --arclog-path=/pgdata/12/archive_wals
INFO: ARCLOG_PATH is set to '/pgdata/12/archive_wals'
INFO: SRVLOG_PATH is set to '/pgdata/12/data/log'

–然后执行全备

$ pg_rman backup --backup-mode=full --backup-path=/pgdata/12/backups
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

–检查备份是否有效

$ pg_rman validate --backup-path=/pgdata/12/backups
INFO: validate: "2021-12-24 15:13:51" backup and archive log files by CRC
INFO: backup "2021-12-24 15:13:51" is valid

–查看备份记录

$ pg_rman show --backup-path=/pgdata/12/backups
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2021-12-24 15:13:51  2021-12-24 15:13:54  FULL   189MB     1  OK

三、使用pg_rman还原数据库

–停库，删除数据文件所有目录

pg_ctl stop -m fast
rm -rf /pgdata/12/data/*

–使用pg_rman恢复数据库

$ pg_rman restore --backup-path=/pgdata/12/backups
WARNING: pg_controldata file "/pgdata/12/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup 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: "2021-12-24 15:13:51"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2021-12-24 15:13:51" backup and archive log files by SIZE
INFO: backup "2021-12-24 15:13:51" is valid
INFO: restoring database files from the full mode backup "2021-12-24 15:13:51"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2021-12-24 15:13:51" is valid
INFO: restoring WAL files from backup "2021-12-24 15:13:51"
INFO: restoring online WAL files and server log files
INFO: add recovery related options to postgresql.conf
INFO: generating recovery.signal
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.

–启动pg

$ pg_ctl start
waiting for server to start....2021-12-24 15:36:08.658 CST [4555] LOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-12-24 15:36:08.658 CST [4555] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-12-24 15:36:08.659 CST [4555] LOG:  listening on IPv6 address "::", port 5432
2021-12-24 15:36:08.665 CST [4555] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-12-24 15:36:08.734 CST [4555] LOG:  redirecting log output to logging collector process
2021-12-24 15:36:08.734 CST [4555] HINT:  Future log output will appear in directory "log".
. done
server started

–登录测试

$ psql
psql (12.2)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 pguser    | Replication                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repuser   | Replication                                               +| {}
           | 5 connections                                              | 

关于pg_rman的更多使用参数，可使用如下命令获取：

pg_rman --help