PG物理备份恢复之 pg_rman 使用

原创

岳麓丹枫 2022-07-12 12:00:20 博主文章分类：PostgreSQL ©著作权

文章标签 postgresql 数据库 database sql hive 文章分类 OpenStack 云计算

©著作权归作者所有：来自51CTO博客作者岳麓丹枫的原创作品，请联系作者获取转载授权，否则将追究法律责任

简介

类似oracle 的 rman, 用于 postgres的备份与恢复

下载

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

安装

tar zxvf pg_rman*.tar.gz
chown postgres.postgres -R pg_rman*
su - postgres
cd xxx/pg_rman*
make && make install

使用

-A, --arclog-path=PATH    location of archive WAL storage area 归档文件存储目录   archive_log
-S, --srvlog-path=PATH    location of server log storage area  服务器日志存储目录 pg_log
-B, --backup-path=PATH    location of the backup storage area  数据备份存储目录   backups
mkdir -p /postgresql/pgsql/archive_log
mkdir -p /postgresql/pgsql/pg_log 
mkdir -p /postgresql/pgsql/pg_rman_backups

echo  'export BACKUP_PATH=/postgresql/pgsql/pg_rman_backups' >>~/.bash_profile
source ~/.bash_profile

开启归档

[postgres@node_206 /postgresql/pgsql/archive_log]$tail  -3 /postgresql/pgsql/data/postgresql.conf  
#for pg_Rman
archive_mode = on   # enables archiving; off, on, or always
archive_command = 'test ! -f /postgresql/pgsql/archive_log/%f && cp %p /postgresql/pgsql/archive_log/%f'

重启 PG

pg_ctl restart -m fast

初始化

pg_rman init -B /postgresql/pgsql/pg_rman_backups

全量备份

pg_rman backup --backup-mode=full -B /postgresql/pgsql/pg_rman_backups

实例

[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$psql -d test
psql (12.3)
Type "help" for help.

test=# \dt
Did not find any relations.
test=# create table test(id int, info text);
CREATE TABLE
test=# insert into test select n, n||'info' from generate_series(1,1000000) n;

INSERT 0 1000000
test=# checkpoint;
CHECKPOINT
test=# 
test=# 
test=# \q
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman init
INFO: ARCLOG_PATH is set to '/postgresql/pgsql/archive_log'
INFO: SRVLOG_PATH is set to '/postgresql/pgsql/pg_log'
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman backup -bf
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.
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman validate
INFO: validate: "2021-04-03 23:32:58" backup and archive log files by CRC
INFO: backup "2021-04-03 23:32:58" is valid
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2021-04-03 23:32:58  2021-04-03 23:33:00  FULL   175MB    15  OK
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$psql -d test
psql (12.3)
Type "help" for help.

test=# create table test2(id int, info text);
CREATE TABLE
test=# insert into test2 select n, n||'info' from generate_series(1,10000) n;
INSERT 0 10000
test=# checkpoint;
CHECKPOINT
test=# 
test=# 
test=# \q
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman backup -bi
INFO: copying database files
INFO: copying archived WAL files
^PINFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman validate
^P^R
INFO: validate: "2021-04-03 23:33:19" backup and archive log files by CRC
INFO: backup "2021-04-03 23:33:19" is valid
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2021-04-03 23:33:19  2021-04-03 23:33:22  INCR   111MB    15  OK
2021-04-03 23:32:58  2021-04-03 23:33:00  FULL   175MB    15  OK
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$psql -d test
psql (12.3)
Type "help" for help.

test=# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test  | table | postgres
 public | test2 | table | postgres
(2 rows)

test=# drop table test2;
DROP TABLE
test=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)

test=# \q
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2021-04-03 23:33:19  2021-04-03 23:33:22  INCR   111MB    15  OK
2021-04-03 23:32:58  2021-04-03 23:33:00  FULL   175MB    15  OK
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman restore --recovery-target-time='2021-04-03 23:33:19'
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 15
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-04-03 23:32:58"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2021-04-03 23:32:58" backup and archive log files by SIZE
INFO: backup "2021-04-03 23:32:58" is valid
INFO: restoring database files from the full mode backup "2021-04-03 23:32:58"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2021-04-03 23:32:58" is valid
INFO: restoring WAL files from backup "2021-04-03 23:32:58"
INFO: backup "2021-04-03 23:33:19" is valid
INFO: restoring WAL files from backup "2021-04-03 23:33:19"
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.
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_ctl start
waiting for server to start....2021-04-03 11:34:21.548 EDT [94026] LOG:  00000: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-03 11:34:21.548 EDT [94026] LOCATION:  PostmasterMain, postmaster.c:998
2021-04-03 11:34:21.549 EDT [94026] LOG:  00000: listening on IPv4 address "0.0.0.0", port 5432
2021-04-03 11:34:21.549 EDT [94026] LOCATION:  StreamServerPort, pqcomm.c:593
2021-04-03 11:34:21.549 EDT [94026] LOG:  00000: listening on IPv6 address "::", port 5432
2021-04-03 11:34:21.549 EDT [94026] LOCATION:  StreamServerPort, pqcomm.c:593
2021-04-03 11:34:21.551 EDT [94026] LOG:  00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-03 11:34:21.551 EDT [94026] LOCATION:  StreamServerPort, pqcomm.c:587
2021-04-03 11:34:21.577 EDT [94026] LOG:  00000: redirecting log output to logging collector process
2021-04-03 11:34:21.577 EDT [94026] HINT:  Future log output will appear in directory "/postgresql/pgsql/pg_log".
2021-04-03 11:34:21.577 EDT [94026] LOCATION:  SysLogger_Start, syslogger.c:675
 done
server started
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$psql -d test
psql (12.3)
Type "help" for help.

test=# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test  | table | postgres
 public | test2 | table | postgres
(2 rows)

test=# select count(*) from test2;
 count 
-------
 10000
(1 row)

test=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

test=# \df *resume*
                                 List of functions
   Schema   |         Name         | Result data type | Argument data types | Type 
------------+----------------------+------------------+---------------------+------
 pg_catalog | pg_wal_replay_resume | void             |                     | func
(1 row)

test=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

test=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

test=# \q
[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$

总结

在基于时间点恢复时, 如果之前做过恢复，那么此时DB与之前的备份已经不在同一时间线上。恢复默认只沿着基础备份建立时时间线恢复而不会切换到新的时间线，如果不做处理，恢复结果将和前面的完整恢复一模一样，恢复不出新插入的数据, 所以建议在恢复之后,可以接着做一个全库的备份
恢复之后, 数据库可能处于 read-only状态,此时可以用超户执行select pg_wal_replay_resume(); 或者在启动数据库实例前在postgresql.conf中添加recovery_target_action=‘promote’
pg_rman init 之后会生产 pg_rman.ini文件, 此时可以编辑该文件并添加备份策略,

我们以一个例子来说明：总共保留两周的数据，即14天的数据，每周进行一次全备，每周一和周三的2:00做一次增量备份，每天进行一次归档备份，这样我们需要对pg_rman.init中做如下配置：

KEEP_DATA_GENERATIONS=2
KEEP_DATA_DAYS=14
KEEP_ARCLOG_DAYS=15
KEEP_SRVLOG_DAYS=180
因为我们需要两个全备份，所以KEEP_DATA_GENERATIONS=2
两周的数据所以KEEP_DATA_DAYS=14，而可以把WAL日志多保留一天，所以KEEP_ARCLOG_DAYS=15
至少保留1000个WAL文件，每个WAL为16M，所以大约16G，占用空间不算大。
KEEP_SRVLOG_DAYS=180，通常程序日志不太，所以保留180天的日志，即3个月。