PG物理备份恢复之 pg_rman 使用

PG物理备份恢复之 pg_rman 使用

简介

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

-

zxvf pg_rman*.tar.gzchown postgres.postgres -R pg_rman*su - postgrescd 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 数据备份存储目录 backupsmkdir -p /postgresql/pgsql/archive_logmkdir -p /postgresql/pgsql/pg_log mkdir -p /postgresql/pgsql/pg_rman_backupsecho 'export BACKUP_PATH=/postgresql/pgsql/pg_rman_backups' >>~/.bash_profilesource ~/.bash_profile

开启归档

[postgres@node_206 /postgresql/pgsql/archive_log]$tail -3 /postgresql/pgsql/data/postgresql.conf #for pg_Rmanarchive_mode = on # enables archiving; off, on, or alwaysarchive_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 testpsql (12.3)Type "help" for help.test=# \dtDid not find any relations.test=# create table test(id int, info text);CREATE TABLEtest=# insert into test select n, n||'info' from generate_series(1,1000000) n;INSERT 0 1000000test=# checkpoint;CHECKPOINTtest=# test=# test=# \q[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman initINFO: 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 -bfINFO: copying database filesINFO: copying archived WAL filesINFO: backup completeINFO: Please execute 'pg_rman validate' to verify the files are correctly copied.[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman validateINFO: validate: "2021-04-03 23:32:58" backup and archive log files by CRCINFO: 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 testpsql (12.3)Type "help" for help.test=# create table test2(id int, info text);CREATE TABLEtest=# insert into test2 select n, n||'info' from generate_series(1,10000) n;INSERT 0 10000test=# checkpoint;CHECKPOINTtest=# test=# test=# \q[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman backup -biINFO: copying database filesINFO: copying archived WAL files^PINFO: backup completeINFO: Please execute 'pg_rman validate' to verify the files are correctly copied.[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_rman validate^P^RINFO: validate: "2021-04-03 23:33:19" backup and archive log files by CRCINFO: 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 OK2021-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 testpsql (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 TABLEtest=# \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 stopwaiting for server to shut down.... doneserver 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 OK2021-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 givenINFO: use timeline ID of current database cluster as recovery target: 15INFO: calculating timeline branches to be used to recovery target pointINFO: searching latest full backup which can be used as restore start pointINFO: 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 filesINFO: clearing restore destinationINFO: validate: "2021-04-03 23:32:58" backup and archive log files by SIZEINFO: backup "2021-04-03 23:32:58" is validINFO: restoring database files from the full mode backup "2021-04-03 23:32:58"INFO: searching incremental backup to be restoredINFO: searching backup which contained archived WAL files to be restoredINFO: backup "2021-04-03 23:32:58" is validINFO: restoring WAL files from backup "2021-04-03 23:32:58"INFO: backup "2021-04-03 23:33:19" is validINFO: restoring WAL files from backup "2021-04-03 23:33:19"INFO: restoring online WAL files and server log filesINFO: add recovery related options to postgresql.confINFO: generating recovery.signalINFO: restore completeHINT: Recovery will start automatically when the PostgreSQL server is started.[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$pg_ctl startwaiting 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-bit2021-04-03 11:34:21.548 EDT [94026] LOCATION: PostmasterMain, postmaster.c:9982021-04-03 11:34:21.549 EDT [94026] LOG: 00000: listening on IPv4 address "0.0.0.0", port 54322021-04-03 11:34:21.549 EDT [94026] LOCATION: StreamServerPort, pqcomm.c:5932021-04-03 11:34:21.549 EDT [94026] LOG: 00000: listening on IPv6 address "::", port 54322021-04-03 11:34:21.549 EDT [94026] LOCATION: StreamServerPort, pqcomm.c:5932021-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:5872021-04-03 11:34:21.577 EDT [94026] LOG: 00000: redirecting log output to logging collector process2021-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 doneserver started[postgres@node_206 /postgresql/pgsql/pg_rman_backups]$psql -d testpsql (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=2KEEP_DATA_DAYS=14KEEP_ARCLOG_DAYS=15KEEP_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个月。

建议备份时添加参数 -C -Z建议恢复时添加参数–hard-copy如果没有指定这个参数，pg_rman实际上是把在归档目录中建一个软链接 指向恢复中要用到的WAL日志文件。如果指定了这个参数，则执行真的拷贝。

参考

​

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。