Обсуждение: Which table stored in which file in PGDATA/base/[db-oid]
Dear Hackers
I've created a new DB, and a bunch of files created in base/12054, 12054 is oid of the new DB.http://www.postgresql.org/docs/9.2/interactive/storage-file-layout.html
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
<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>
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
2013/6/1 Martijn van Oosterhout <kleptog@svana.org>
--
Victor Y. Yegorov
On Sat, Jun 01, 2013 at 03:27:40PM +0430, Soroosh Sardari wrote:That shouldn't happen. Are you sure you're looking in the right
> 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.
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
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
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
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:That shouldn't happen. Are you sure you're looking in the right
> 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.
database? Kan you list the filenames?
Have a nice day,
Вложения
<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>
- 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>