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 <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

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.

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>

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

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

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 1, 2013 at 3:57 PM, Andres Freund <andres@2ndquadrant.com>wrote:

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

- 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>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers