pg_rman部署及使用

阿楠不会敲代码

已于 2024-03-26 16:41:17 修改

阅读量177

点赞数 2

文章标签： postgresql

于 2024-02-27 22:21:37 首次发布

本文链接：https://blog.csdn.net/ananhenku/article/details/136333376

版权

PG_RMAN部署及使用

PGSQL推出开源备份工具pg_rman，类似于oracle的rman备份策略，实现了全量、增量和归档等多重备份方式，可以很灵活的管理PGSQL数据库的备份，支持在线和基于PITR的备份恢复方式。

1、使用postgres用户部署

[postgres@pg14 ~]$ tar xvf pg_rman-1.3.15-pg14.tar.gz
[postgres@pg14 ~]$ cd pg_rman-1.3.15-pg14.tar.gz
[postgres@pg14 ~]$ make install

2、初始化环境

# 设置备份目录，再用户配置文件里添加以下信息：
export BACKUP_PATH=/home/postgres/pg_rman_bk

# 使环境变量生效
[postgres@pg14 ~]$ source .bash_profile

# 初始化备份目录，验证归档路径，日志目录，同时在备份路径下产生目标库相关文件
[postgres@pg14 ~]$ pg_rman init
WARNING: ARCLOG_PATH is not set yet
DETAIL: The archive_command is not set in postgresql.conf.
HINT: Please set ARCLOG_PATH in pg_rman.ini or environmental variable.
INFO: SRVLOG_PATH is set to '/opt/pgdata/log'

# 发现未配置归档命令，在postgresql.conf添加
archive_command = 'cp %p /home/postgres/archives/%f'
archive_mode = 'on'

# 然后删除pg_rman_bk里的内容
[postgres@pg14 ~]$ rm -rf pg_rman_bk/*

# 重新初始化备份目录
[postgres@pg14 ~]$ pg_rman init
INFO: ARCLOG_PATH is set to '/home/postgres/archives'
INFO: SRVLOG_PATH is set to '/opt/pgdata/log'
[postgres@pg14 pg_rman_bk]$ ll
总用量 8
drwx------ 4 postgres dba 34 2月  27 20:57 backup
-rw-r--r-- 1 postgres dba 69 2月  27 20:57 pg_rman.ini
-rw-r--r-- 1 postgres dba 40 2月  27 20:57 system_identifier
drwx------ 2 postgres dba  6 2月  27 20:57 timeline_history

3、备份命令

[postgres@pg14 pg_rman_bk]$ 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 validate [DATE]	#验证备份
  pg_rman OPTION delete DATE		#删除备份信息
  pg_rman OPTION purge				#删除实际的备份文件

4、建立测试数据

postgres=# create table test(id serial,insert_time timestamp without time zone);
CREATE TABLE
postgres=# insert into test(id,insert_time) select i,now() from generate_series(1,200) as i;
INSERT 0 200
postgres=# select count(*) from test;
 count 
-------
   200
(1 row)

postgres=# select max(insert_time) from test;
            max             
----------------------------
 2024-03-26 14:52:41.336287
(1 row)

postgres=#

5、进行全量备份

# 进行全量备份
[postgres@pg14 ~]$ pg_rman backup --backup-mode=full -C
INFO: copying database files
Processed 1282 of 1282 files, skipped 0
INFO: copying archived WAL files
Processed 3 of 3 files, skipped 0
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

# 验证备份有效性
[postgres@pg14 ~]$ pg_rman validate
INFO: validate: "2024-03-26 14:55:53" backup and archive log files by CRC
INFO: backup "2024-03-26 14:55:53" is valid

# 查看备份集
[postgres@pg14 ~]$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-03-26 14:55:53  2024-03-26 14:55:55  FULL    58MB     1  OK

6、新增测试数据

postgres=# insert into test(id,insert_time) select i,now() from generate_series(1,10) as i;
INSERT 0 10
postgres=# select count(*) from test;
 count 
-------
   210
(1 row)

7、增量备份

# 进行增量备份
[postgres@pg14 ~]$ pg_rman backup --backup-mode=incremental -C
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@pg14 ~]$ pg_rman validate
INFO: validate: "2024-03-26 15:06:40" backup and archive log files by CRC
INFO: backup "2024-03-26 15:06:40" is valid

# 查看备份集
[postgres@pg14 ~]$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-03-26 15:06:40  2024-03-26 15:06:42  INCR    33MB     1  OK
2024-03-26 14:55:53  2024-03-26 14:55:55  FULL    58MB     1  OK

8、删除测试表数据,停止数据库

# 删除测试表数据
postgres=# \dt+
                                  List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description 
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | test | table | postgres | permanent   | heap          | 40 kB | 
(1 row)

postgres=# drop table test;
DROP TABLE
postgres=# select count(*) from test;
2024-03-26 15:08:10.390 CST [2999] ERROR:  relation "test" does not exist at character 22
2024-03-26 15:08:10.390 CST [2999] STATEMENT:  select count(*) from test;
ERROR:  relation "test" does not exist
LINE 1: select count(*) from test;
                             ^
postgres=# \q

# 停止数据库
[postgres@pg14 ~]$ pg_ctl -D /opt/pgdata stop
waiting for server to shut down....2024-03-26 15:09:27.923 CST [2290] LOG:  received fast shutdown request
2024-03-26 15:09:27.925 CST [2290] LOG:  aborting any active transactions
2024-03-26 15:09:27.930 CST [2290] LOG:  background worker "logical replication launcher" (PID 2298) exited with exit code 1
2024-03-26 15:09:27.931 CST [2292] LOG:  shutting down
2024-03-26 15:09:28.091 CST [2290] LOG:  database system is shut down
 done
server stopped
[postgres@pg14 ~]$

9、全量恢复

# 进行全量备份恢复
[postgres@pg14 ~]$ pg_rman restore --recovery-target-time "2024-03-26 14:55:55" --hard-copy
INFO: the recovery target timeline ID is not 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 as base in recovery: "2024-03-26 14:55:53"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2024-03-26 14:55:53" backup and archive log files by SIZE
INFO: backup "2024-03-26 14:55:53" is valid
INFO: restoring database files from the full mode backup "2024-03-26 14:55:53"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2024-03-26 14:55:53" is valid
INFO: restoring WAL files from backup "2024-03-26 14:55:53"
INFO: backup "2024-03-26 15:06:40" is valid
INFO: restoring WAL files from backup "2024-03-26 15:06:40"
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 is done, we recommend to remove recovery-related parameters configured by pg_rman.

# 启动数据库
[postgres@pg14 ~]$ pg_ctl -D /opt/pgdata start
waiting for server to start....2024-03-26 15:11:49.892 CST [3084] LOG:  starting PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-03-26 15:11:49.897 CST [3084] LOG:  listening on IPv6 address "::1", port 5432
2024-03-26 15:11:49.897 CST [3084] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-03-26 15:11:49.898 CST [3084] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-03-26 15:11:49.902 CST [3085] LOG:  database system was interrupted; last known up at 2024-03-26 14:55:53 CST
2024-03-26 15:11:49.914 CST [3085] LOG:  starting point-in-time recovery to 2024-03-26 14:55:55+08
2024-03-26 15:11:49.994 CST [3085] LOG:  restored log file "000000010000000000000017" from archive
2024-03-26 15:11:49.999 CST [3085] LOG:  redo starts at 0/17000028
2024-03-26 15:11:50.000 CST [3085] LOG:  consistent recovery state reached at 0/17000100
2024-03-26 15:11:50.000 CST [3084] LOG:  database system is ready to accept read-only connections
 done
server started
2024-03-26 15:11:50.076 CST [3085] LOG:  restored log file "000000010000000000000018" from archive
2024-03-26 15:11:50.079 CST [3085] LOG:  recovery stopping before commit of transaction 812, time 2024-03-26 14:55:55.045663+08
2024-03-26 15:11:50.079 CST [3085] LOG:  pausing at the end of recovery
2024-03-26 15:11:50.079 CST [3085] HINT:  Execute pg_wal_replay_resume() to promote.

# 验证全量恢复结果
[postgres@pg14 ~]$ psql
psql (14.6)
Type "help" for help.

postgres=# \dt+
                                  List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description 
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | test | table | postgres | permanent   | heap          | 40 kB | 
(1 row)

postgres=# select count(*) from test;
 count 
-------
   200
(1 row)
postgres=# \q

# 关闭数据库
[postgres@pg14 ~]$ pg_ctl -D /opt/pgdata stop
waiting for server to shut down....2024-03-26 15:13:41.706 CST [3084] LOG:  received fast shutdown request
2024-03-26 15:13:41.706 CST [3084] LOG:  aborting any active transactions
2024-03-26 15:13:42.253 CST [3087] LOG:  shutting down
2024-03-26 15:13:42.277 CST [3084] LOG:  database system is shut down
 done
server stopped
[postgres@pg14 ~]$

10、增量恢复

# 进行增量恢复
[postgres@pg14 ~]$ pg_rman restore --recovery-target-time "2024-03-26 15:06:42" --hard-copy
INFO: the recovery target timeline ID is not 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 as base in recovery: "2024-03-26 14:55:53"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2024-03-26 14:55:53" backup and archive log files by SIZE
INFO: backup "2024-03-26 14:55:53" is valid
INFO: restoring database files from the full mode backup "2024-03-26 14:55:53"
INFO: searching incremental backup to be restored
INFO: validate: "2024-03-26 15:06:40" backup and archive log files by SIZE
INFO: backup "2024-03-26 15:06:40" is valid
INFO: restoring database files from the incremental mode backup "2024-03-26 15:06:40"
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2024-03-26 15:06:40" is valid
INFO: restoring WAL files from backup "2024-03-26 15:06:40"
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 is done, we recommend to remove recovery-related parameters configured by pg_rman.

# 启动数据库
[postgres@pg14 ~]$ pg_ctl -D /opt/pgdata start
waiting for server to start....2024-03-26 15:14:25.787 CST [3184] LOG:  starting PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-03-26 15:14:25.788 CST [3184] LOG:  listening on IPv6 address "::1", port 5432
2024-03-26 15:14:25.788 CST [3184] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-03-26 15:14:25.789 CST [3184] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-03-26 15:14:25.793 CST [3185] LOG:  database system was interrupted; last known up at 2024-03-26 15:06:40 CST
2024-03-26 15:14:25.803 CST [3185] LOG:  starting point-in-time recovery to 2024-03-26 15:06:42+08
2024-03-26 15:14:25.855 CST [3185] LOG:  restored log file "000000010000000000000019" from archive
2024-03-26 15:14:25.874 CST [3185] LOG:  redo starts at 0/19000028
2024-03-26 15:14:25.874 CST [3185] LOG:  consistent recovery state reached at 0/19000100
2024-03-26 15:14:25.875 CST [3184] LOG:  database system is ready to accept read-only connections
2024-03-26 15:14:25.925 CST [3185] LOG:  restored log file "00000001000000000000001A" from archive
2024-03-26 15:14:25.929 CST [3185] LOG:  recovery stopping before commit of transaction 814, time 2024-03-26 15:06:42.153967+08
2024-03-26 15:14:25.929 CST [3185] LOG:  pausing at the end of recovery
2024-03-26 15:14:25.929 CST [3185] HINT:  Execute pg_wal_replay_resume() to promote.
 done
server started

# 验证增量恢复结果
[postgres@pg14 ~]$ psql
psql (14.6)
Type "help" for help.

postgres=# \dt+
                                  List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description 
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | test | table | postgres | permanent   | heap          | 40 kB | 
(1 row)

postgres=# select count(*) from test;
 count 
-------
   210
(1 row)