how to find out the database and table a file path refers to on PostgreSQL database ?

how to find out the database and table a file path refers to on PostgreSQL database ?

There are three main patterns for paths:

• 1.For files in the default tablespace: base/database_oid/filenode id for the relation
• 2.For files in Non-default tablespace: pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id for the relation
• 3.For shared relations (see below): global/filenode id for the relation

1.For files in the default tablespace:

Tables’ file names aren’t necessarily the same as their oids in pg_class and  can change when VACUUM FULL, TRUNCATE, etc are run. 

For example:

billing_db=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | t1   | table | postgres | 0 bytes |
(1 row)

billing_db=# SELECT pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/18144/18146
(1 row)

billing_db=# insert into t1 values(2);
INSERT 0 1
billing_db=# insert into t1 values(2);
INSERT 0 1
billing_db=# insert into t1 values(2);
INSERT 0 1

billing_db=# SELECT pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/18144/18146
(1 row)

billing_db=# update t1 set id=1;
UPDATE 3
billing_db=# SELECT pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/18144/18146
(1 row)


billing_db=# vacuum t1;
VACUUM

billing_db=# SELECT pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/18144/18146
(1 row)

billing_db=# vacuum full t1;
VACUUM

billing_db=# SELECT pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/18144/18149
(1 row)

2.Non-default tablespace paths:

SELECT pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
pg_tblspc / 16709 / PG_9.3_201306121 / 16499/19401

So the filename pattern breaks down to:

• pg_tblspc: it’s in a non-default tablespace
• 16709: it’s in the tablespace with oid 16709
• PG_9.3_201306121: used by PostgreSQL 9.3 with catalog version 201306121.
• 16499: in the database with oid 16499
• 19401 the table with relfilenode id 19401