Table of Contents
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)
最后修改时间：2024-03-16 20:05:13
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
