阅读量:1
文章目录
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) 