PostgreSQL - How to determine which files on disk correspond to tables in a database

Colleagues hello to all.

In today's article, we will talk about how you can find a specific file in the directory where you have your database installed, which refers to one of the tables in the PostgreSQL database.

Sometimes a database administrator's job is to determine which file on disk a table in the database corresponds to. By default, all database tables are stored in the path  $PGDATA/base.

The task of determining the file name by the name of table may arise in many cases, one of such cases may be you may see the following error in your log file:

ERROR:  could not read block 22342 of relation base/16384/16388: read only 0 of 8192 bytes

To solve this problem and find the relationship between a table in the database and a file on your server, we can use a special function that comes with the c PostgreSQL DBMS. The function is called pg_relation_filepath. The pg_relation_filepath function takes one parameter, which is the name of a table that exists in the database. If successful, the function will return the relative path to the file.

Before using the function, we need to connect to a specific database, and only then use the function.

psql> SELECT pg_relation_filepath('random_number');

As a result, we got all the files that belong to the table random_number.

Suddenly, if you want to find out the name of the table by the name of the file, then there is another function called pg_filenode_relation. The  pg_filenode_relation function takes two parameters, the first is the tablespace and the second is the filename.

psql> SELECT pg_filenode_relation(0,16388);

Thank you all, I hope that my article helped you in some way.