Обсуждение: How to determine which file contains which block
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/
Frank Way <fgw_three@yahoo.com> writes:
> 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
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Frank Way <fgw_three@yahoo.com> writes: > > 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 > Thanks Tom, your answer is MUCH appreciated..... frank ===== Frank Way E-Mail: fgw_three@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/