Re: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
- On Sat, Jun 1, 2013 at 3:57 PM, Andres Freund and...@2ndquadrant.comwrote: - - 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
On Sat, Jun 1, 2013 at 3:57 PM, Andres Freund and...@2ndquadrant.comwrote: 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 Dear Andres You are right, Some tables are mapped, and some other are global. The SQL query is really helpful. Thanks, Soroosh
[HACKERS] 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. 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: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
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 klep...@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 signature.asc Description: Digital signature
Re: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
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. 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 klep...@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 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) iQIVAwUBUanSAkvt++dL5i1EAQhn6hAAg9eiZEz2eV6Z/5f8ae56MNGwM5L1P6nU y2pN49PoSz0FkO3lBwcShH3/O0s+SgNy8kh6Klm1qDlwvX9HFGeRVd9guX7/fFil eu+Ueg5nVzXA4fb/NwjS+Hh1B+/NdJQnklddP6K4Pm0VW51wqaaFA3hn/CfNMiO2 07i8L/NFjlngc5wstQLGcxuE5bl69c1qGhl8RHoOPLRhFgMSzkxSR9TglTDPaniu rptpWvHgfRYdorANBaSI3SByw8WeSPbrTHusX4XC5zVkIk7GZQiogQlQVRA7yBT6 YpdjqB4thWDctR4VLv0yvBRJ5g7M9GkhWSOmpDoRBWCB2EFFPwrBhyrxt/e/aPCn +Nt1nFxtKGV4/tPW7cI9b4bv2OZctmOaoDByqAZUuB891eOebVjif9MsQeG5IWFb 5KOnQcQ+TxlmCkF7zot5Tv8ndMTtJN8eKAkhay+xmLjON/2tGl+ArKbVAqck2oIb xGSavSLg6HZ/FmMNkbHVSo6/Z7Nmup2GGYsWWJhHvoO0hbGHCnxobAsWQGPUsC7l 6osFCcBokvZtIERLttznP1S8RvmLP6EuByxNNQY4MV1GJm55P1PHZeWRGCYMEDil Fs73My0YxHBtnjI/LbgJ4GhKzINsQqviHJPFraKq8NdW/+B3Pte6bmtlRFa8Z/t+ J6hjI9Wgky0= =68cp -END PGP SIGNATURE-
Re: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
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 klep...@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 signature.asc Description: Digital signature
Re: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
2013/6/1 Martijn van Oosterhout klep...@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: http://www.postgresql.org/docs/current/interactive/storage-file-layout.html It is recommended to use pg_relation_filenode() function: http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION -- Victor Y. Yegorov
Re: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Which table stored in which file in PGDATA/base/[db-oid]
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 klep...@svana.orgwrote: 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, newfile.pg Description: Binary data filenode.pg Description: Binary data newfile-filenode.pg Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
