Обсуждение: Which table stored in which file in PGDATA/base/[db-oid]

Поиск
Список
Период
Сортировка

Which table stored in which file in PGDATA/base/[db-oid]

От
Soroosh Sardari
Дата:
Dear Hackers

I've created a new DB, and  a bunch of files created in base/12054, 12054 is oid of the new DB.
I want to find what table stored in each file.
BTW, I read this
http://www.postgresql.org/docs/9.2/interactive/storage-file-layout.html
I have 156 files with numerical names, vm and fsm file are ignored.
107 files are same as pg_class.reltoastrelid,so I have 49 files that I do not know what tables stored in them.
Any idea to find ?

Thanks,
Soroosh

Re: Which table stored in which file in PGDATA/base/[db-oid]

От
Martijn van Oosterhout
Дата:
On Sat, Jun 01, 2013 at 03:11:50PM +0430, Soroosh Sardari wrote:
> Dear Hackers
>
> I've created a new DB, and  a bunch of files created in base/12054, 12054
> is oid of the new DB.
> I want to find what table stored in each file.
> BTW, I read this
> http://www.postgresql.org/docs/9.2/interactive/storage-file-layout.html
> I have 156 files with numerical names, vm and fsm file are ignored.
> 107 files are same as pg_class.reltoastrelid,so I have 49 files that I do
> not know what tables stored in them.
> Any idea to find ?

From that page:

Each table and index is stored in a separate file. For ordinary
relations, these files are named after the table or index's filenode
number, which can be found in pg_class.relfilenode.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.  -- Arthur Schopenhauer

Re: Which table stored in which file in PGDATA/base/[db-oid]

От
Soroosh Sardari
Дата:
<div dir="ltr"><br /><div class="gmail_extra">Yes, I have some files which is not in pg_class.relfilenode of any table
orindex.<br /></div><div class="gmail_extra">I want to know which table or index stored in such files.<br /></div><div
class="gmail_extra"><br/><div class="gmail_quote"><br /><blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1pxsolid rgb(204,204,204);padding-left:1ex"><div class=""><div class="h5"><br /></div></div>From that
page:<br/><br /> Each table and index is stored in a separate file. For ordinary<br /> relations, these files are named
afterthe table or index's filenode<br /> number, which can be found in pg_class.relfilenode.<br /><br /> Have a nice
day,<br/><span class=""><font color="#888888">--<br /> Martijn van Oosterhout   <<a
href="mailto:kleptog@svana.org">kleptog@svana.org</a>>  <a href="http://svana.org/kleptog/"
target="_blank">http://svana.org/kleptog/</a><br/> > He who writes carelessly confesses thereby at the very outset
thathe does<br /> > not attach much importance to his own thoughts.<br />    -- Arthur Schopenhauer<br
/></font></span><br/>-----BEGIN PGP SIGNATURE-----<br /> Version: GnuPG v1.4.10 (GNU/Linux)<br /><br />
iQIVAwUBUanSAkvt++dL5i1EAQhn6hAAg9eiZEz2eV6Z/5f8ae56MNGwM5L1P6nU<br/>
y2pN49PoSz0FkO3lBwcShH3/O0s+SgNy8kh6Klm1qDlwvX9HFGeRVd9guX7/fFil<br/>
eu+Ueg5nVzXA4fb/NwjS+Hh1B+/NdJQnklddP6K4Pm0VW51wqaaFA3hn/CfNMiO2<br/>
07i8L/NFjlngc5wstQLGcxuE5bl69c1qGhl8RHoOPLRhFgMSzkxSR9TglTDPaniu<br/>
rptpWvHgfRYdorANBaSI3SByw8WeSPbrTHusX4XC5zVkIk7GZQiogQlQVRA7yBT6<br/>
YpdjqB4thWDctR4VLv0yvBRJ5g7M9GkhWSOmpDoRBWCB2EFFPwrBhyrxt/e/aPCn<br/>
+Nt1nFxtKGV4/tPW7cI9b4bv2OZctmOaoDByqAZUuB891eOebVjif9MsQeG5IWFb<br/>
5KOnQcQ+TxlmCkF7zot5Tv8ndMTtJN8eKAkhay+xmLjON/2tGl+ArKbVAqck2oIb<br/>
xGSavSLg6HZ/FmMNkbHVSo6/Z7Nmup2GGYsWWJhHvoO0hbGHCnxobAsWQGPUsC7l<br/>
6osFCcBokvZtIERLttznP1S8RvmLP6EuByxNNQY4MV1GJm55P1PHZeWRGCYMEDil<br/>
Fs73My0YxHBtnjI/LbgJ4GhKzINsQqviHJPFraKq8NdW/+B3Pte6bmtlRFa8Z/t+<br/> J6hjI9Wgky0=<br /> =68cp<br /> -----END PGP
SIGNATURE-----<br/><br /></blockquote></div><br /></div></div> 

Re: Which table stored in which file in PGDATA/base/[db-oid]

От
Martijn van Oosterhout
Дата:
On Sat, Jun 01, 2013 at 03:27:40PM +0430, Soroosh Sardari wrote:
> Yes, I have some files which is not in pg_class.relfilenode of any table or
> index.
> I want to know which table or index stored in such files.

That shouldn't happen. Are you sure you're looking in the right
database? Kan you list the filenames?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.  -- Arthur Schopenhauer

Re: Which table stored in which file in PGDATA/base/[db-oid]

От
Victor Yegorov
Дата:
2013/6/1 Martijn van Oosterhout <kleptog@svana.org>
On Sat, Jun 01, 2013 at 03:27:40PM +0430, Soroosh Sardari wrote:
> Yes, I have some files which is not in pg_class.relfilenode of any table or
> index.
> I want to know which table or index stored in such files.

That shouldn't happen. Are you sure you're looking in the right
database? Kan you list the filenames?

According to the docs, it is possible if there had been
operations on the table that change filenode, like TRUNCATE.
Also, some some relations has relfilenode being 0, like pg_class catalogue table.

Check more here:

It is recommended to use pg_relation_filenode() function:


--
Victor Y. Yegorov

Re: Which table stored in which file in PGDATA/base/[db-oid]

От
Andres Freund
Дата:
On 2013-06-01 13:04:55 +0200, Martijn van Oosterhout wrote:
> On Sat, Jun 01, 2013 at 03:27:40PM +0430, Soroosh Sardari wrote:
> > Yes, I have some files which is not in pg_class.relfilenode of any table or
> > index.
> > I want to know which table or index stored in such files.
> 
> That shouldn't happen. Are you sure you're looking in the right
> database? Kan you list the filenames?

It's actually entirely normal. For some system tables the actual
relfilenode isn't stored in the system catalog but in the relation
mapper. Those are
a) tables needed to access the catalogs themselves like pg_class,  pg_attribute, pg_proc, ..
b) shared tables where we otherwise couldn't change the relfilenode from  another database

To get the actual relfilenode you actually need to do something like:
SELECT relname, pg_relation_filenode(pg_class.oid) FROM pg_class;


Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services



Re: Which table stored in which file in PGDATA/base/[db-oid]

От
Soroosh Sardari
Дата:
Yes, I'm sure.

Difference of filenodes and new files changed w.r.t my first mail, because I added a table.
I attached 3 files,
newfile.pg : list of numerical files in base/[db-oid], ls | grep '[[:digit:]]\>'
filenode.pg : select distinct relfilenode from pg_class
newfile-filenode.pg : Set of oids which exists in newfile.pg and does not in filenode.pg


On Sat, Jun 1, 2013 at 3:34 PM, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Sat, Jun 01, 2013 at 03:27:40PM +0430, Soroosh Sardari wrote:
> Yes, I have some files which is not in pg_class.relfilenode of any table or
> index.
> I want to know which table or index stored in such files.

That shouldn't happen. Are you sure you're looking in the right
database? Kan you list the filenames?

Have a nice day,

Вложения

Re: Which table stored in which file in PGDATA/base/[db-oid]

От
Soroosh Sardari
Дата:
<div dir="ltr"><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Sat, Jun 1, 2013 at 3:57 PM,
AndresFreund <span dir="ltr"><<a href="mailto:andres@2ndquadrant.com"
target="_blank">andres@2ndquadrant.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="im">On 2013-06-01 13:04:55 +0200, Martijn van Oosterhout
wrote:<br/> > On Sat, Jun 01, 2013 at 03:27:40PM +0430, Soroosh Sardari wrote:<br /> > > Yes, I have some
fileswhich is not in pg_class.relfilenode of any table or<br /> > > index.<br /> > > I want to know which
tableor index stored in such files.<br /> ><br /> > That shouldn't happen. Are you sure you're looking in the
right<br/> > database? Kan you list the filenames?<br /><br /></div>It's actually entirely normal. For some system
tablesthe actual<br /> relfilenode isn't stored in the system catalog but in the relation<br /> mapper. Those are<br />
a)tables needed to access the catalogs themselves like pg_class,<br />    pg_attribute, pg_proc, ..<br /> b) shared
tableswhere we otherwise couldn't change the relfilenode from<br />    another database<br /><br /> To get the actual
relfilenodeyou actually need to do something like:<br /> SELECT relname, pg_relation_filenode(pg_class.oid) FROM
pg_class;<br/><br /><br /> Greetings,<br /><br /> Andres Freund<br /><span class="HOEnZb"><font color="#888888"><br />
--<br/>  Andres Freund                     <a href="http://www.2ndQuadrant.com/"
target="_blank">http://www.2ndQuadrant.com/</a><br/>  PostgreSQL Development, 24x7 Support, Training & Services<br
/></font></span></blockquote></div><br/><br /></div><div class="gmail_extra">Dear Andres<br /><br /></div><div
class="gmail_extra">Youare right, Some tables are mapped, and some other are global.<br /></div><div
class="gmail_extra">The SQL query is really helpful.<br /><br /></div><div class="gmail_extra">Thanks,<br />Soroosh<br
/></div></div>

Re: Which table stored in which file in PGDATA/base/[db-oid]

От
David Kerr
Дата:
- On Sat, Jun 1, 2013 at 3:57 PM, Andres Freund <andres@2ndquadrant.com>wrote:
- 
- > On 2013-06-01 13:04:55 +0200, Martijn van Oosterhout wrote:
- > To get the actual relfilenode you actually need to do something like:
- > SELECT relname, pg_relation_filenode(pg_class.oid) FROM pg_class;
- 
- Dear Andres
- 
- You are right, Some tables are mapped, and some other are global.
- The SQL query is really helpful.

Another option, is to use oid2name
http://www.postgresql.org/docs/devel/static/oid2name.html

oid2name -S -d <database> -f <filename>