use pg_filedump dump block contents
背景
源码 :
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
内容等等。
数据库存储结构可以参考源码。