标签归档：pg_filedump批量恢复

PostgreSQL恢复系列:pg_filedump批量处理

发表于 2024 年 4 月 18 日 由 惜分飞

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:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

发表在 PostgreSQL恢复 | 标签为 , , | 留下评论

PostgreSQL恢复系列:pg_filedump恢复字典构造

发表于 2024 年 4 月 15 日 由 惜分飞

pg_filedump是在pg数据库极端情况下直接解析数据库文件的利器,但是由于是开源软件,本身难以实现批量处理,通过对底层基表分析,可以实现批量处理功能
分析PostgreSQL库中数据库信息

--数据库查询结果
postgres=# select oid,datname,datdba,dattablespace from pg_database;
  oid  |   datname   | datdba | dattablespace 
-------+-------------+--------+---------------
 14187 | postgres    |     10 |          1663
 16403 | db_xff      |     10 |          1663
     1 | template1   |     10 |          1663
 14186 | template0   |     10 |          1663
 16407 | db_xifenfei |  16405 |         16406
(5 rows)

--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    5 (0x0005)  Flags: REDIRECT
 Item   2 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   3 -- Length:  260  Offset: 7320 (0x1c98)  Flags: NORMAL
COPY: 14187     postgres
 Item   4 -- Length:  260  Offset: 7056 (0x1b90)  Flags: NORMAL
COPY: 16403     db_xff
 Item   5 -- Length:  297  Offset: 7888 (0x1ed0)  Flags: NORMAL
COPY: 1 template1
 Item   6 -- Length:  297  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 14186     template0
 Item   7 -- Length:  260  Offset: 6792 (0x1a88)  Flags: NORMAL
COPY: 16407     db_xifenfei

分析PostgreSQL 表空间信息

--sql查询表空间信息
postgres=# select * from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions 
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        | 
  1664 | pg_global    |       10 |        | 
 16406 | tbs_xifenfei |    16405 |        | 
(3 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 1663      pg_default
 Item   2 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 1664      pg_global
 Item   3 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 16406     tbs_xifenfei

分析PostgreSQL 对象id、name、path对应关系

--对象信息查询
postgres=# select oid ,relname,relnamespace,reltype,reloftype,relowner,relam,relfilenode,
           reltablespace from pg_class where relname like 't_t%' or relname like 't_x%';
  oid  |  relname   | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace 
-------+------------+--------------+---------+-----------+----------+-------+-------------+---------------
 16387 | t_xifenfei |         2200 |   16389 |         0 |       10 |     2 |       16390 |             0
 16391 | t_xff      |         2200 |   16393 |         0 |       10 |     2 |       16391 |             0
 16394 | t_xff2     |         2200 |   16396 |         0 |       10 |     2 |       16394 |             0
 16397 | t_xff3     |         2200 |   16399 |         0 |       10 |     2 |       16397 |             0
 16400 | t_xff4     |         2200 |   16402 |         0 |       10 |     2 |       16400 |             0
 16408 | t_tbs      |         2200 |   16410 |         0 |       10 |     2 |       16408 |         16406
(6 rows)

--通过dump 该文件解析数据（显示部分）
COPY: 16394     t_xff2  2200    16396   0       10      2       16394   0       0       0.000000000000  0       0       f       f       p       r
 Item  29 -- Length:    0  Offset:   31 (0x001f)  Flags: REDIRECT
 Item  30 -- Length:  172  Offset: 2592 (0x0a20)  Flags: NORMAL
COPY: 16397     t_xff3  2200    16399   0       10      2       16397   0       0       0.000000000000  0       0       f       f       p       r
 Item  31 -- Length:  205  Offset: 3376 (0x0d30)  Flags: NORMAL
COPY: 12093     pg_shadow       11      12094   0       10      0       0       0       0       0.000000000000  0       0       f       f       p       v
 Item  32 -- Length:  172  Offset: 2416 (0x0970)  Flags: NORMAL
COPY: 16400     t_xff4  2200    16402   0       10      2       16400   0       0       0.000000000000  0       0       f       f       p       r

这个里面获取到pg_class.reltablespace是表空间的id值,根据自定义表空间的规则:在pgdata/pg_tblspc创建link指向创建表空间时候的文件夹路径

-bash-4.2$ pwd
/var/lib/pgsql/12/data/pg_tblspc
-bash-4.2$ ls -ltr
total 0
lrwxrwxrwx 1 postgres postgres 30 Apr 15 20:13 16406 -> /var/lib/pgsql/12/data/tbs_xff

结合上述的pg_database,pg_tablespace,pg_class信息,可以获取到每个表对应实际的存储路径
分析PostgreSQL 模式信息

--sql查询模式信息
postgres=# select * from pg_namespace;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13887 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 16404 | u_xifenfei         |       10 | 
(7 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   2 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 99        pg_toast
 Item   3 -- Length:    0  Offset:    7 (0x0007)  Flags: REDIRECT
 Item   4 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 12314     pg_temp_1
 Item   5 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 12315     pg_toast_temp_1
 Item   6 -- Length:  141  Offset: 7760 (0x1e50)  Flags: NORMAL
COPY: 11        pg_catalog
 Item   7 -- Length:  141  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 2200      public
 Item   8 -- Length:    0  Offset:    9 (0x0009)  Flags: REDIRECT
 Item   9 -- Length:  141  Offset: 7472 (0x1d30)  Flags: NORMAL
COPY: 13887     information_schema
 Item  10 -- Length:   96  Offset: 7376 (0x1cd0)  Flags: NORMAL
COPY: 16404     u_xifenfei

通过pg_namespace，pg_class信息，可以获取到对象所属的模式关系，基于上述汇总,可以获取到某个模式下面,所有表id和实际存储路径,现在使用pg_filedump进行恢复,还缺少表的列类型信息,通过pg_type和pg_attribute来获取。

获取PostgreSQL表的列名称和类型[编号]信息

--sql查询列信息
postgres=# \d t_tbs
               Table "public.t_tbs"
  Column  | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 oid      | oid  |           |          | 
 spcname  | name |           |          | 
 spcowner | oid  |           |          | 
Tablespace: "tbs_xifenfei"

postgres=# select attrelid,attname,atttypid,attstattarget,attlen,attnum from pg_attribute where attrelid=16408;
 attrelid | attname  | atttypid | attstattarget | attlen | attnum 
----------+----------+----------+---------------+--------+--------
    16408 | tableoid |       26 |             0 |      4 |     -6
    16408 | cmax     |       29 |             0 |      4 |     -5
    16408 | xmax     |       28 |             0 |      4 |     -4
    16408 | cmin     |       29 |             0 |      4 |     -3
    16408 | xmin     |       28 |             0 |      4 |     -2
    16408 | ctid     |       27 |             0 |      6 |     -1
    16408 | oid      |       26 |            -1 |      4 |      1
    16408 | spcname  |       19 |            -1 |     64 |      2
    16408 | spcowner |       26 |            -1 |      4 |      3
(9 rows)

--dump 内容（截取部分）
 Item  11 -- Length:  144  Offset: 1424 (0x0590)  Flags: NORMAL
COPY: 16408     oid     26      -1      4       1
 Item  12 -- Length:  144  Offset: 1280 (0x0500)  Flags: NORMAL
COPY: 16408     spcname 19      -1      64      2
 Item  13 -- Length:  144  Offset: 1136 (0x0470)  Flags: NORMAL
COPY: 16408     spcowner        26      -1      4       3
 Item  14 -- Length:  144  Offset:  992 (0x03e0)  Flags: NORMAL
COPY: 16408     ctid    27      0       6       -1
 Item  15 -- Length:  144  Offset:  848 (0x0350)  Flags: NORMAL
COPY: 16408     xmin    28      0       4       -2
 Item  16 -- Length:  144  Offset:  704 (0x02c0)  Flags: NORMAL
COPY: 16408     cmin    29      0       4       -3
 Item  17 -- Length:  144  Offset:  560 (0x0230)  Flags: NORMAL
COPY: 16408     xmax    28      0       4       -4
 Item  18 -- Length:  144  Offset:  416 (0x01a0)  Flags: NORMAL
COPY: 16408     cmax    29      0       4       -5

PostgreSQL获取类型编号和实际类型名称对应关系

--查询类型编号和实际类型关系
postgres=# select oid,typname from pg_type;
  oid  |                typname                
-------+---------------------------------------
    16 | bool
    17 | bytea
    18 | char
    19 | name
    20 | int8
    21 | int2
    22 | int2vector
    23 | int4
    24 | regproc
    25 | text
    26 | oid
    27 | tid
    28 | xid
    29 | cid
……

--dump 内容（截取部分）
 Item   1 -- Length:  176  Offset: 8016 (0x1f50)  Flags: NORMAL
COPY: 16        bool
 Item   2 -- Length:  176  Offset: 7840 (0x1ea0)  Flags: NORMAL
COPY: 17        bytea
 Item   3 -- Length:  176  Offset: 7664 (0x1df0)  Flags: NORMAL
COPY: 18        char
 Item   4 -- Length:  176  Offset: 7488 (0x1d40)  Flags: NORMAL
COPY: 19        name
 Item   5 -- Length:  176  Offset: 7312 (0x1c90)  Flags: NORMAL
COPY: 20        int8
 Item   6 -- Length:  176  Offset: 7136 (0x1be0)  Flags: NORMAL
COPY: 21        int2
 Item   7 -- Length:  176  Offset: 6960 (0x1b30)  Flags: NORMAL
COPY: 22        int2vector
 Item   8 -- Length:  176  Offset: 6784 (0x1a80)  Flags: NORMAL
COPY: 23        int4
 Item   9 -- Length:  176  Offset: 6608 (0x19d0)  Flags: NORMAL
COPY: 24        regproc
 Item  10 -- Length:  176  Offset: 6432 (0x1920)  Flags: NORMAL
COPY: 25        text
 Item  11 -- Length:  176  Offset: 6256 (0x1870)  Flags: NORMAL
COPY: 26        oid
 Item  12 -- Length:  176  Offset: 6080 (0x17c0)  Flags: NORMAL
COPY: 27        tid
 Item  13 -- Length:  176  Offset: 5904 (0x1710)  Flags: NORMAL
COPY: 28        xid
 Item  14 -- Length:  176  Offset: 5728 (0x1660)  Flags: NORMAL
COPY: 29        cid
 Item  15 -- Length:  176  Offset: 5552 (0x15b0)  Flags: NORMAL
COPY: 30        oidvector

通过pg_class、pg_type和pg_attribute可以获取对象的表的列名称，数据类型等信息。通过以上几个对象，即可获取到pg_filedmp处理所需要的所有信息,然后可以实现批量处理

发表在 PostgreSQL恢复 | 标签为 , , | 留下评论