PostgreSQL学习随笔25 利用工具恢复误删除数据5之pg_filedump

postgres=# create table test(id int,name varchar(20));
CREATE TABLE
postgres=# insert into test values(1,'cui');
INSERT 0 1
postgres=# insert into test values(2,'peng');
INSERT 0 1
postgres=# select * from test;
 id | name 
----+------
  1 | cui
  2 | peng
(2 rows)


查看表对应的文件
postgres=# select pg_relation_filepath('test');
 pg_relation_filepath 
----------------------
 base/13593/16384
(1 row)


postgres@pgexp1-> ll base/13593/16384
-rw------- 1 postgres postgres 8.0K May 22 22:46 base/13593/16384


解析文件内容
postgres@pgexp1-> pg_filedump base/13593/16384


*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: base/13593/16384
* Options used: None
*******************************************************************
Notice: Block size determined from reading block 0 is zero, using default 8192 instead.
Hint: Use -S <size> to specify the size manually.


Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower       0 (0x0000)
 Block: Size    0  Version    0            Upper       0 (0x0000)
 LSN:  logid      0 recoff 0x00000000      Special     0 (0x0000)
 Items:    0                      Free Space:    0
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 24


 Error: Invalid header information.


<Data> -----
 Empty block - no items listed 


<Special Section> -----
 Error: Invalid special section encountered.


*** End of File Encountered. Last Block Read: 0 ***




-------------------------------------------------------------
postgres@pgexp1-> pg_filedump -D int,charn base/13593/16384  


*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: base/13593/16384
* Options used: -D int,charn
*******************************************************************


Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8120 (0x1fb8)
 LSN:  logid      0 recoff 0x0164ea10      Special  8192 (0x2000)
 Items:    2                      Free Space: 8088
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32


<Data> -----
 Item   1 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
COPY: 1 cui
 Item   2 -- Length:   33  Offset: 8120 (0x1fb8)  Flags: NORMAL
COPY: 2 peng
*** End of File Encountered. Last Block Read: 0 ***


生成可导入的csv文件
postgres@pgexp1-> pg_filedump -D int,charn base/13593/16384|grep COPY |awk '{$1=null;print $0}'>test
postgres@pgexp1-> cat test
 1 cui
 2 peng
 
 导入数据
postgres=# copy test from 'test'(DELIMITER ' ');
COPY 2
postgres=# select * from test;
 id | name 
----+------
  1 | cui
  2 | peng
(2 rows)