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
