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

 615 total views,  2 views today