Physical location of objects in a PostgreSQL database?
data-storage, database-design, postgresql
Solution
Kevin and Mike already provided pointers where to find the data directory. For the physical location of a table in the file system, use:
SELECT pg_relation_filepath('my_table');
Don't mess with the files directly unless you know exactly what you are doing.
A database as a whole is represented by a subdirectory in `PGDATA/base`:
If you use tablespaces it gets more complicated. Read details in the chapter Database File Layout in the manual:
For each database in the cluster there is a subdirectory within PGDATA/base, named after the database's OID in `pg_database`. This subdirectory is the default location for the database's files; in particular, its system catalogs are stored there.
...
Each table and index is stored in a separate file. For ordinary relations, these files are named after the table or index's filenode number, which can be found in `pg_class.relfilenode`.
...
The `pg_relation_filepath()` function shows the entire path (relative to PGDATA) of any relation.
Bold emphasis mine. The manual about the function `pg_relation_filepath()`.
Problem
I'm interested to get the physical locations of tables, views, functions, data/content available in the tables of PostgreSQL in Linux OS. I've a scenario that PostgreSQL could be installed in SD-Card facility and Hard-Disk. If I've tables, views, functions, data in SD, I want to get the physical locations of the same and merge/copy into my hard-disk whenever I wish to replace the storage space. I hope the storage of database should be in terms of plain files architecture. Also, is it possible to view the contents of the files? I mean, can I access them?