use pg_filedump dump block contents

2 minute read

背景

源码 :
http://pgfoundry.org/frs/?group_id=1000541&release_id=1767

9.0安装实例 :

tar -zxvf pg_filedump-9.0.0.tar.gz  
mv pg_filedump-9.0.0 postgresql-9.0.2/contrib  
cd postgresql-9.0.2/contrib/pg_filedump-9.0.0  
mv Makefile Makefile.old  
mv Makefile.contrib Makefile  
gmake  
su - root  
cd postgresql-9.0.2/contrib/pg_filedump-9.0.0  
. /home/postgres/.bash_profile  
gmake install  

使用帮助

postgres@db-172-16-3-33-> pg_filedump -h  
  
Version 9.0.0 (PostgreSQL 9.0)  Copyright (c) 2002-2010 Red Hat, Inc.  
  
Usage: pg_filedump [-abcdfhixy] [-R startblock [endblock]] [-S blocksize] file  
  
Display formatted contents of a PostgreSQL heap/index/control file  
 Defaults are: relative addressing, range of the entire file, block  
               size as listed on block 0 in the file  
  
The following options are valid for heap and index files:  
  -a  Display absolute addresses when formatting (Block header  
      information is always block relative)  
  -b  Display binary block images within a range (Option will turn  
      off all formatting options)  
  -d  Display formatted block content dump (Option will turn off  
      all other formatting options)  
  -f  Display formatted block content dump along with interpretation  
  -h  Display this information  
  -i  Display interpreted item details  
  -R  Display specific block ranges within the file (Blocks are  
      indexed from 0)  
        [startblock]: block to start at  
        [endblock]: block to end at  
      A startblock without an endblock will format the single block  
  -S  Force block size to [blocksize]  
  -x  Force interpreted formatting of block items as index items  
  -y  Force interpreted formatting of block items as heap items  
  
The following options are valid for control files:  
  -c  Interpret the file listed as a control file  
  -f  Display formatted content dump along with interpretation  
  -S  Force block size to [blocksize]  
  
Report bugs to <rhdb@sources.redhat.com>  

使用实例:

digoal=> create table tbl_user ( id int,first_name text,last_name text,corp text);  
CREATE TABLE  
digoal=> insert into tbl_user values (1,'zhou','digoal','sky-mobi');  
INSERT 0 1  
digoal=> insert into tbl_user values (2,'ZHOU','DIGOAL','MOBI');  
INSERT 0 1  

查找文件节点:

digoal=> select pg_relation_filepath('tbl_user'::regclass);  
              pg_relation_filepath                
------------------------------------------------  
 pg_tblspc/16401/PG_9.0_201008051/16402/2074455  
(1 row)  

检索文件块:

postgres@db-172-16-3-33-> pg_filedump -i $PGDATA/pg_tblspc/16401/PG_9.0_201008051/16402/2074455  
  
*******************************************************************  
* PostgreSQL File/Block Formatted Dump Utility - Version 9.0.0  
*  
* File: /database/pgdata/tbs1/pg_root/pg_tblspc/16401/PG_9.0_201008051/16402/2074455  
* Options used: -i   
*  
* Dump created on: Thu May 26 10:21:51 2011  
*******************************************************************  

没有结果,原因是bgwriter还没有把数据写到块里面。执行checkpoint之后就可以看到数据了.

digoal=> \c digoal postgres  
You are now connected to database "digoal" as user "postgres".  
digoal=# checkpoint;  
CHECKPOINT  
  
postgres@db-172-16-3-33-> pg_filedump -f $PGDATA/pg_tblspc/16401/PG_9.0_201008051/16402/2074455  
  
*******************************************************************  
* PostgreSQL File/Block Formatted Dump Utility - Version 9.0.0  
*  
* File: /database/pgdata/tbs1/pg_root/pg_tblspc/16401/PG_9.0_201008051/16402/2074455  
* Options used: -f   
*  
* Dump created on: Thu May 26 10:24:21 2011  
*******************************************************************  
  
Block    0 ********************************************************  
<Header> -----  
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)  
 Block: Size 8192  Version    4            Upper    8088 (0x1f98)  
 LSN:  logid     27 recoff 0x90035270      Special  8192 (0x2000)  
 Items:    2                      Free Space: 8056  
 TLI: 0x0006  Prune XID: 0x00000000  Flags: 0x0000 ()  
 Length (including item array): 32  
  
  0000: 1b000000 70520390 06000000 2000981f  ....pR...... ...  
  0010: 00200420 00000000 c89f6200 989f5a00  . . ......b...Z.  
  
<Data> ------   
 Item   1 -- Length:   49  Offset: 8136 (0x1fc8)  Flags: NORMAL  
  1fc8: 1c110000 00000000 00000000 00000000  ................  
  1fd8: 01000400 02091800 01000000 0b7a686f  .............zho  
  1fe8: 750f6469 676f616c 13736b79 2d6d6f62  u.digoal.sky-mob  
  1ff8: 69                                   i                 
  
 Item   2 -- Length:   45  Offset: 8088 (0x1f98)  Flags: NORMAL  
  1f98: 1d110000 00000000 00000000 00000000  ................  
  1fa8: 02000400 02091800 02000000 0b5a484f  .............ZHO  
  1fb8: 550f4449 474f414c 0b4d4f42 49        U.DIGOAL.MOBI     

检索出来刚好一个数据块。BLOCK 0

两条ITEM

长度分别49,45 bytes

内容等等。

数据库存储结构可以参考源码。

Flag Counter

digoal’s 大量PostgreSQL文章入口