联系：手机/微信(+86 17813235971) QQ(107644445)
标题：PostgreSQL恢复系列:pg_filedump批量处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
pg_filedump工具使用起来比较麻烦,主要存在问题：
1. 需要人工一个个枚举各个列类型无法实现批量恢复,参考以前写的PostgreSQL恢复系列:pg_filedump基本使用
2. 特别是在pg库无法正常运行的情况下,如果没有业务提供表创建语句,恢复基本上无法正常进行.
基于这两个问题,在以前的文章中写过PostgreSQL恢复系列:pg_filedump恢复字典构造,为了解决上述的两个,弄了一个pg_filedump_batch脚本实现批量恢复需求
在测试的pg库中创建了一些测试表,并查看部分表数据,便于对比后续恢复效果
postgres=# \d List of relations Schema | Name | Type | Owner --------+----------------+-------+---------- public | t_tbs | table | postgres public | t_xff | table | postgres public | t_xff2 | table | postgres public | t_xff3 | table | postgres public | t_xff4 | table | postgres public | t_xifenfei | table | postgres public | tab_attribute | table | postgres public | tab_class | table | postgres public | tab_database | table | postgres public | tab_namespace | table | postgres public | tab_tablespace | table | postgres public | tab_type | table | postgres(12 rows)postgres=# select * from tab_database; oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace -------+-------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+--------------- 14187 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 14186 | 479 | 1 | 1663 16403 | db_xff | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 14186 | 479 | 1 | 1663 1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 14186 | 479 | 1 | 1663 14186 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 14186 | 479 | 1 | 1663 16407 | db_xifenfei | 16405 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 14186 | 479 | 1 | 16406(5 rows)postgres=# select count(1) from tab_class; count------- 407(1 row)postgres=# select *from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions -------+--------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 16406 | tbs_xifenfei | 16405 | | (3 rows) |
使用pg_filedump_bath脚本来实现批量恢复
[root@xifenfei tmp]# ./pg_filedump_batch recover --database-oid=14187 \ --output-directory=/data/recovery --pgdata=/var/lib/pgsql/12/dataRecover tables in database with oid: 14187LOG: starting to process table tab_attributeLOG: starting to process table tab_classLOG: starting to process table tab_databaseLOG: starting to process table tab_namespaceLOG: starting to process table tab_tablespaceLOG: starting to process table tab_typeLOG: starting to process table t_tbsLOG: starting to process table t_xffLOG: starting to process table t_xff2LOG: starting to process table t_xff3LOG: starting to process table t_xff4LOG: starting to process table t_xifenfeiCheck dumps in /data/recovery |
参考数据恢复
[root@xifenfei tmp]# cd /data/recovery/[root@xifenfei recovery]# ls -ltrtotal 156-rw-r--r-- 1 root root 82797 Apr 18 20:35 recovered-14187-tab_attribute.csv-rw-r--r-- 1 root root 31129 Apr 18 20:35 recovered-14187-tab_class.csv-rw-r--r-- 1 root root 343 Apr 18 20:35 recovered-14187-tab_database.csv-rw-r--r-- 1 root root 118 Apr 18 20:35 recovered-14187-tab_namespace.csv-rw-r--r-- 1 root root 50 Apr 18 20:35 recovered-14187-tab_tablespace.csv-rw-r--r-- 1 root root 7907 Apr 18 20:35 recovered-14187-tab_type.csv-rw-r--r-- 1 root root 0 Apr 18 20:35 recovered-14187-t_tbs.csv-rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff.csv-rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff2.csv-rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff3.csv-rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xff4.csv-rw-r--r-- 1 root root 38 Apr 18 20:35 recovered-14187-t_xifenfei.csv[root@xifenfei recovery]# cat recovered-14187-tab_database.csv14187 postgres 10 6 en_US.UTF-8 en_US.UTF-8 f t -1 14186 479 1 166316403 db_xff 10 6 en_US.UTF-8 en_US.UTF-8 f t -1 14186 479 1 16631 template1 10 6 en_US.UTF-8 en_US.UTF-8 t t -1 14186 479 1 166314186 template0 10 6 en_US.UTF-8 en_US.UTF-8 t f -1 14186 479 1 166316407 db_xifenfei 16405 6 en_US.UTF-8 en_US.UTF-8 f t -1 14186 479 1 16406[root@xifenfei recovery]# cat recovered-14187-tab_class.csv|wc -l407[root@xifenfei recovery]# cat recovered-14187-tab_tablespace.csv1663 pg_default1664 pg_global16406 tbs_xifenfei |
把pg_class恢复数据导入库中进行对比,证明恢复的数据完全正确
postgres=# COPY tab_class_new FROM '/data/recovery/recovered-14187-tab_class.csv';COPY 407postgres=# select count(1) from tab_class; count------- 407(1 row) count------- 407(1 row)postgres=# select count(1) from tab_class_new; count------- 407(1 row)postgres=# select * from tab_class_new postgres-# EXCEPTpostgres-# select * from tab_class; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind -----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------(0 rows)postgres=# select * from tab_classpostgres-# EXCEPTpostgres-# select * from tab_class_new; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind -----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------(0 rows) |
通过上述操作证明:
1. 在没有人工列出列类型的情况下实现批量pg_filedump恢复功能
2. 在pg库没有启动的情况下直接解析字典实现恢复功能
3. 实现pg数据库的批量恢复
如果有PostgreSQL的数据库故障,自行无法解决,请联系我们提供专业数据库恢复技术支持:
电话/微信:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
浙公网安备 33010602011771号