Thread: Which file belongs to which database?

Which file belongs to which database?

From
Thomas Kellerer
Date:
Hello all,

just out of curiosity: how can I find out which files in the PG_DATA directory
belong to which database/table?

I have looked through the documentation of the system catalogs, but couldn't
find any reference to that.

The field datpath in pg_database is empty in my system (7.2 on Windows and 7.3.1
on Linux)

Cheers
Thomas


Re: Which file belongs to which database?

From
"Shridhar Daithankar"
Date:
On 25 Jul 2003 at 8:45, Thomas Kellerer wrote:
> just out of curiosity: how can I find out which files in the PG_DATA directory
> belong to which database/table?

There is a contrib module oid2name. Use that.

You can just find the oid of the object from catalog and search for that file.
That's the principle.

Bye
 Shridhar

--
But Captain -- the engines can't take this much longer!


Re: Which file belongs to which database?

From
Karsten Hilbert
Date:
> just out of curiosity: how can I find out which files in the PG_DATA
> directory belong to which database/table?
>
> I have looked through the documentation of the system catalogs, but couldn't
you should also look through the mailing list archives...

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Which file belongs to which database?

From
Thomas Kellerer
Date:
Shridhar Daithankar schrieb:

> On 25 Jul 2003 at 8:45, Thomas Kellerer wrote:
>
>> just out of curiosity: how can I find out which files in the PG_DATA
>> directory belong to which database/table?
>
> There is a contrib module oid2name. Use that.
>
> You can just find the oid of the object from catalog and search for that
> file. That's the principle.
>
> Bye Shridhar
>

That easy ? :-)

Thanks for the information!

Cheers
Thomas



Re: Which file belongs to which database?

From
Tom Lane
Date:
Thomas Kellerer <spam_eater@gmx.net> writes:
> Shridhar Daithankar schrieb:
>> You can just find the oid of the object from catalog and search for that
>> file. That's the principle.

> That easy ? :-)

Actually you must look at pg_class.relfilenode; this is initially the
same as oid, but there are operations (such as cluster and reindex) that
assign new relfilenode values because they replace the underlying file.

At the database level, pg_database.oid is correct.

            regards, tom lane