pg_rman在恢复服务器上恢复源库的备份

文章目录

说明
恢复笔记
- - 待恢复的数据库文件所在目录与文件层级如下
  - 检查事项
  - 具体操作
- 修改3: mv recovery.conf recovery.done
- 需要重置 wal 日志然后启库
- 检查效果
补充笔记(2024-02-07更新)

pg_rman 安装与使用参考:
pg_rman 编译安装与使用

说明

需求是能启库就行, 可以接受数据丢失, 恢复步骤有偷懒, 本文仅供个人记录笔记使用, 请不要完全照抄
规范合理的步骤是需要使用 recovery.conf 文件, 写清楚目标环境上的归档日志路径(当前提前得将源库中的wal日志拷贝到目标环境归档路径下) 来启动并恢复

恢复笔记

以 db_test 为例:

待恢复的数据库文件所在目录与文件层级如下

/data/restore/pg_restore/backups/db_test20231216/014025/backup.ini

检查事项

0.目录级别与生产环境保持一致,如果不一致, 需要自己手动创建确保一致,
1.最上级目录(20231216同级目录)存在 pg_rman.ini
2.20231216/014025下存在 file_database.txt
3.文件 20231216/014025/backup.ini 中的 STATUS=OK

	-bash-4.2$ cat backup.ini
	# configuration
	BACKUP_MODE=FULL
	WITH_SERVERLOG=false
	COMPRESS_DATA=true
	# result
	TIMELINEID=5
	START_LSN=4bb/4e000028
	STOP_LSN=4bb/7e81c420
	START_TIME='2023-02-15 01:35:03'
	END_TIME='2023-02-15 07:42:53'
	RECOVERY_XID=181372900
	RECOVERY_TIME='2023-02-15 06:04:45'
	TOTAL_DATA_BYTES=233395485133
	READ_DATA_BYTES=233408059346
	READ_ARCLOG_BYTES=32782681011
	WRITE_BYTES=35758753390
	BLOCK_SIZE=8192
	XLOG_BLOCK_SIZE=8192
	STATUS=OK

具体操作

创建恢复目录(你要将数据库备份集恢复到哪里去), 相当于是执行了 pg_rman -B $BACKUP_PATH init

mkdir -p   /data/restore/2023_pg_restore/db_test/data

检查待恢复的数据库文件集合是否正常, 如果显示 OK 则可以继续操作, 如果显示 RUNNING或者ERROR 则说明备份文件集合有问题, 需要重新弄一份新的
pg_rman -B /data/restore/pg_restore/backups/db_test show
具体恢复命令 (-D 指向你要将数据库文件集合恢复到哪里去)
pg_rman -B /data/restore/pg_restore/backups/db_test restore -D /data/restore/2023_pg_restore/db_test/data
这里如果报 ARC_PATH 相关的错误(ERROR), 需要手动创建该目录 ,如果只是报 warning 可以先忽略
比如:
mkdir -p /data/restore/2023_pg_restore/db_test/arc_log
mkdir -p /data/restore/2023_pg_restore/db_test/srv_log
更新 /data/restore/pg_restore/backups/db_test/pg_rman.ini 文件
修改配置文件 /data/restore/2023_pg_restore/db_test/data/postgresql.conf 与 /data/restore/2023_pg_restore/db_test/data/backup_label(如果存在的话)
修改1: 禁用 archive_mode
修改2: 修改 log_directory 与 data_directory 与 hba_file 配置项中的 data 值与 /data/restore/2023_pg_restore/db_test/data 保持一致

修改3: mv recovery.conf recovery.done

修改4: mv backup_label backup_label_lxm
修改5: 将 listen_addresses= ‘10.33.45.110’ 改为 listen_addresses = ‘*’

手动启动数据库 (-D 启动哪个目录对应的数据库实例 )
pg_ctl start -D /data/restore/2023_pg_restore/db_test/data

如果还报错, 查看日志文件

-bash-4.2$ tailf /data/restore/2023_pg_restore/db_test/data/pg_log/postgresql-25.*csv
2023-12-25 15:44:37.909 HKT,,,119321,,658932e5.1d219,4,,2023-12-25 15:44:37 HKT,,0,FATAL,XX000,"could not locate required checkpoint record",,"If you are not restoring from a backup, try removing the file ""/data/restore/2023_pg_restore/db_test/data/backup_label"".",,,,,,,""
2023-12-25 15:44:37.910 HKT,,,119317,,658932e5.1d215,1,,2023-12-25 15:44:37 HKT,,0,LOG,00000,"startup process (PID 119321) exited with exit code 1",,,,,,,,,""
2023-12-25 15:44:37.910 HKT,,,119317,,658932e5.1d215,2,,2023-12-25 15:44:37 HKT,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,,""
2023-12-25 15:49:10.374 HKT,,,136964,,658933f6.21704,1,,2023-12-25 15:49:10 HKT,,0,LOG,00000,"database system was interrupted; last known up at 2023-12-16 02:17:39 HKT",,,,,,,,,""
2023-12-25 15:49:10.375 HKT,,,136964,,658933f6.21704,2,,2023-12-25 15:49:10 HKT,,0,LOG,00000,"could not open tablespace directory ""pg_tblspc/0/PG_9.4_201409291"": No such file or directory",,,,,,,,,""
2023-12-25 15:49:10.459 HKT,,,136964,,658933f6.21704,3,,2023-12-25 15:49:10 HKT,,0,LOG,00000,"invalid primary checkpoint record",,,,,,,,,""
2023-12-25 15:49:10.459 HKT,,,136964,,658933f6.21704,4,,2023-12-25 15:49:10 HKT,,0,LOG,00000,"invalid secondary checkpoint record",,,,,,,,,""

FATAL,XX000,"could not locate required checkpoint record",

需要重置 wal 日志然后启库

Report bugs to <pgsql-bugs@postgresql.org>.
-bash-4.2$ pg_resetxlog -f /data/restore/2023_pg_restore/db_test/data
Transaction log reset
-bash-4.2$ 
-bash-4.2$  pg_ctl start -D /data/restore/2023_pg_restore/db_test/data
server starting
-bash-4.2$ 2023-12-25 15:50:13 HKT::@:[138504]: LOG:  redirecting log output to logging collector process
2023-12-25 15:50:13 HKT::@:[138504]: HINT:  Future log output will appear in directory "/data/restore/2023_pg_restore/db_test/data/pg_log".

检查效果

-bash-4.2$ netstat -anp |grep 5472
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:5472            0.0.0.0:*               LISTEN      138504/postgres     
tcp6       0      0 :::5472                 ::: *                    LISTEN      138504/postgres     
unix  2      [ ACC ]     STREAM     LISTENING     2208435216 138504/postgres      /tmp/.s.PGSQL.5472
unix  3      [ ]         STREAM     CONNECTED     154721   -                    
unix  3      [ ]         STREAM     CONNECTED     154720   -  

-bash-4.2$ psql -p 5472
psql (9.4.14)
Type "help" for help.

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 db_test   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 69 GB   | pg_default | 
 repmgr    | repmgr   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 687 MB  | pg_default | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 6729 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8697 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |

补充笔记(2024-02-07更新)

1. 修改 /postgresql/hq/bk/restore/pg_restore/backups/iobs/pg_rman.ini 
将 ARCLOG_PATH 值改为一个新路径, 例如/postgresql/hq/bk/restore/2023_pg_restore/iobs_new_arc_log, 需要先手动创建该路径并授予 postgres的属主权限

2. 执行 pg_rman -B /postgresql/hq/bk/restore/pg_restore/backups/iobs/ restore -D /postgresql/hq/bk/restore/2023_pg_restore/iobs_new_lxm2 >> ./res.log 2>&1 &
进行恢复

3. 检查恢复结果
tail res.log
-bash-4.2$ tail res.log 
LOG: (16485/16492) 00000009000010B800000014
LOG: (16486/16492) 00000009000010B800000015
LOG: (16487/16492) 00000009000010B800000016
LOG: (16488/16492) 00000009000010B800000017
LOG: (16489/16492) 00000009000010B800000018
LOG: (16490/16492) 00000009000010B800000019
LOG: (16491/16492) 00000009000010B80000001A
LOG: (16492/16492) 00000009000010B80000001B
LOG: backup 2024-02-24 01:35:25 is valid
INFO: restore complete. Recovery starts automatically when the PostgreSQL server is started.

4.进入新的 data目录
 cd /postgresql/hq/bk/restore/2023_pg_restore/iobs_new_lxm2
 cp postgresql.conf postgresql.conf_bak_`date +%F`
 cp recovery.conf recovery.conf_bak_`date +%F`
 vim recovery.conf 
将 recovery_target_timeline 值改为 'latest'

vim postgresql.conf 修改里面的相关路径
包括如下配置
-bash-4.2$ diff postgresql.conf postgresql.conf_bak_20240311 
2,3c2,3
< data_directory				    ='/postgresql/hq/bk/restore/2023_pg_restore/iobs_new_lxm2'
< hba_file					    ='/postgresql/hq/bk/restore/2023_pg_restore/iobs_new_lxm2/pg_hba.conf'
---
> data_directory				    ='/postgresql/pg5472/data'
> hba_file					    ='/postgresql/pg5472/data/pg_hba.conf'
7c7
< listen_addresses				   = '*'
---
> listen_addresses				   = '10.33.45.110'
57,58c57,58
< #archive_command = 'test ! -f /postgresql/pgbackup/iobs/archlog/%f && cp %p /postgresql/pgbackup/iobs/archlog/%f'
< archive_command = 'test ! -f /postgresql/hq/bk/restore/2023_pg_restore/iobs_new_arc_log/%f && cp %p /postgresql/hq/bk/restore/2023_pg_restore/iobs_new_arc_log/%f'
---
> archive_command = 'test ! -f /postgresql/pgbackup/iobs/archlog/%f && cp %p /postgresql/pgbackup/iobs/archlog/%f'
> 
67c67
< log_directory = '/postgresql/hq/bk/restore/2023_pg_restore/iobs_new_lxm2/pg_log'
---
> log_directory = '/postgresql/pg5472/data/pg_log'
-bash-4.2$



5. 启动数据库
postmaster -D /postgresql/hq/bk/restore/2023_pg_restore/iobs_new_lxm2 &

6. 登录(根据配置文件中 listen_port 为 5472
-bash-4.2$ psql -p 5472
psql (9.4.14)
Type "help" for help.

postgres=# show data_directory ;
                  data_directory                   
---------------------------------------------------
 /postgresql/hq/bk/restore/2023_pg_restore/iobs_new_lxm2
(1 row)