How to determine which file contains which block

[Frank Way]

Hi all,

I've recently had the opportunity to use pg_filedump to try and find
some corruption in one of my database tables (7.3.4).

If found the following description of how to find the block/tuple and
to use pg_filedump:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=20030922162322.E12708%40quartz.newn.cam.ac.uk&rnum=8&prev=/groups%3Fq%3Dpg_filedump%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D20030922162322.E12708%2540quartz.newn.cam.ac.uk%26rnum%3D8

It was quite good but left one gap that I've not been able to figure
out.

After finding the ctid in (block,tuple) format, how do you know which
file in all the subdirectories under /usr/local/pgsql/data/base
contains that block?

I'm a bit ashamed to say I found it through trial and error. There has
to be a better way <grin>.

I've looked through the documentation, but haven't found anything on
this subject.


Thanks for your help,
Frank Way


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Frank Way
After finding the ctid in (block,tuple) format, how do you know which
file in all the subdirectories under /usr/local/pgsql/data/base
contains that block?

The subdirectories are named after the OIDs of their databases
(pg_database.oid column). Individual files are named after the
relfilenodes of their tables/indexes (pg_class.relfilenode).
Note also that tables exceeding 1GB will be broken into gigabyte-size
segments named nnn, nnn.1, nnn.2, etc; if you are dealing with one
of these then you need to determine which segment to use from the
block number.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Frank Way]

Post by Tom Lane

Post by Frank Way
After finding the ctid in (block,tuple) format, how do you know

which

Post by Frank Way
file in all the subdirectories under /usr/local/pgsql/data/base
contains that block?

The subdirectories are named after the OIDs of their databases
(pg_database.oid column). Individual files are named after the
relfilenodes of their tables/indexes (pg_class.relfilenode).
Note also that tables exceeding 1GB will be broken into gigabyte-size
segments named nnn, nnn.1, nnn.2, etc; if you are dealing with one
of these then you need to determine which segment to use from the
block number.
regards, tom lane

Thanks Tom, your answer is MUCH appreciated.....

frank



=====
Frank Way
E-Mail: ***@yahoo.com

"Place guards on all the roads and keep the troops
from running to the rear..." MG John Buford, 1863

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org