分类目录归档：数据库

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 | datlastsysoi
d | datfrozenxid | datminmxid | dattablespace 
-------+-------------+--------+----------+-------------+-------------+---------------+--------------+--------------+-------------
--+--------------+------------+---------------
 14187 | postgres    |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |          1663
 16403 | db_xff      |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |          1663
     1 | template1   |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         1418
6 |          479 |          1 |          1663
 14186 | template0   |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         1418
6 |          479 |          1 |          1663
 16407 | db_xifenfei |  16405 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          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/data
Recover tables in database with oid: 14187
LOG: starting to process table tab_attribute
LOG: starting to process table tab_class
LOG: starting to process table tab_database
LOG: starting to process table tab_namespace
LOG: starting to process table tab_tablespace
LOG: starting to process table tab_type
LOG: starting to process table t_tbs
LOG: starting to process table t_xff
LOG: starting to process table t_xff2
LOG: starting to process table t_xff3
LOG: starting to process table t_xff4
LOG: starting to process table t_xifenfei
Check dumps in /data/recovery

参考数据恢复

[root@xifenfei tmp]# cd /data/recovery/
[root@xifenfei recovery]# ls -ltr
total 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.csv
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
[root@xifenfei recovery]# cat recovered-14187-tab_class.csv|wc -l
407
[root@xifenfei recovery]# cat recovered-14187-tab_tablespace.csv
1663    pg_default
1664    pg_global
16406   tbs_xifenfei

把pg_class恢复数据导入库中进行对比,证明恢复的数据完全正确

postgres=# COPY tab_class_new FROM '/data/recovery/recovered-14187-tab_class.csv';
COPY 407

postgres=# 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-# EXCEPT
postgres-# select * from tab_class;
 oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel
allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind 
-----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+----
-----------+---------------+-------------+-------------+----------------+---------
(0 rows)

postgres=# select * from tab_class
postgres-# EXCEPT
postgres-# select * from tab_class_new;
 oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel
allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind 
-----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+----
-----------+---------------+-------------+-------------+----------------+---------
(0 rows)

通过上述操作证明:
1. 在没有人工列出列类型的情况下实现批量pg_filedump恢复功能
2. 在pg库没有启动的情况下直接解析字典实现恢复功能
3. 实现pg数据库的批量恢复
如果有PostgreSQL的数据库故障,自行无法解决,请联系我们提供专业数据库恢复技术支持:
电话/微信:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com