Where does PostgreSQL datafile get stored?
If we talk about database , at high level all databases are simply data store and stored as a set of files on disk.
In PostgreSQL inside the data directory , you will see many files and probably not able to recognize where your database reside in first glace
Datafiles in PostgreSQL reside in inside data directory for the particular PostgreSQL server .

Now , how to find the data directory of any postgresql
simply login to the database server and query
-bash-4.2$ psql Password: psql.bin (10.13) Type "help" for help. postgres=# postgres=# postgres=# show data_directory ; data_directory /opt/newclusterdb (1 row)
Once you go to data directory (My PostgreSQL data_dir is /opt/newclusterdb ) you will see lots of files including:
-bash-4.2$ ls -ltr total 64 -rw------- 1 postgres postgres 0 Apr 9 18:17 tablespace_map -rw------- 1 postgres postgres 234 Apr 9 18:17 backup_label -rw------- 1 postgres postgres 3 Apr 9 18:17 PG_VERSION -rw------- 1 postgres postgres 208 Apr 9 18:17 backup_label.old drwx------ 9 postgres postgres 93 Apr 9 18:17 base -rw------- 1 postgres postgres 44 Apr 9 18:17 current_logfiles -rw------- 1 postgres postgres 1636 Apr 9 18:17 pg_ident.conf -rw------- 1 postgres postgres 4398 Apr 9 18:17 pg_hba.conf -bash-4.2$ pwd /opt/newclusterdb
Once your inside the data_dir , you will see a directory called base . That’s where all datafiles of your table get stored . This is where all the database data for your whole cluster resides.
Inside base , if you go then you will see many directories which were names as some numbers . each numbered directories represents single database in your cluster
-bash-4.2$ ls -ltr total 72 drwx------ 2 postgres postgres 8192 Aug 2 2020 1 drwx------ 2 postgres postgres 8192 Aug 2 2020 13916 drwx------ 2 postgres postgres 8192 Aug 2 2020 16405 drwx------ 2 postgres postgres 8192 Aug 3 2020 16429 drwx------ 2 postgres postgres 8192 Apr 8 21:13 16422 drwx------ 2 postgres postgres 8192 Apr 8 21:41 13917 -bash-4.2$ pwd /opt/newclusterdb/base
check which base directory belong to which database in postgres
postgres=# SELECT oid as object_id, datname as database_name FROM pg_database; object_id | database_name -----------+--------------- 13917 | postgres 16405 | newdb 1 | template1 13916 | template0 16422 | standby_sync 16429 | test (6 rows)
Now you can see which database corresponds to which directory , from above output we can say 16405 in base dir belongs to newdb database .
If you want to verify all from os prompt, there is OS utilities called oid2name
-bash-4.2$ oid2name Password: All databases: Oid Database Name Tablespace 16405 newdb pg_default 24640 pitr_demo pg_default 13917 postgres pg_default 16422 standby_sync pg_default 13916 template0 pg_default 1 template1 pg_default 16429 test pg_default
In this standby_sync database I have a table called checkstdby .
If you want to see which file actually contains the table checkstdby data you can do the following:
Start up psql and: Check which table data files are stored where standby_sync=# \dt List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | checkstdby | table | postgres (1 row) standby_sync=# SELECT pg_relation_filepath('checkstdby') ; pg_relation_filepath base/16422/16423 (1 row) Check the table data files -bash-4.2$ ls -ltr 16423* -rw------- 1 postgres postgres 8192 Aug 20 2020 16423 -bash-4.2$ pwd /opt/newclusterdb/base/16422
For the above task also you can os utility
-bash-4.2$ oid2name -d standby_sync -t checkstdby
Password:
From database "standby_sync":
Filenode Table Name
16423 checkstdby
404 total views, 2 views today