PostgreSQL恢复系列:pg_filedump恢复字典构造---惜分飞-易微帮

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%';

-------+------------+--------------+---------+-----------+----------+-------+-------------+---------------

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;

----------+----------+----------+---------------+--------+--------

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恢复系列:pg_filedump恢复字典构造---惜分飞

网站公告

今日签到

热门文章

最新发布