Physical location of objects in a PostgreSQL database?

Question

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?

Answer 1

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().

Answer 2

The query show data_directory; will show you the main data directory. But that doesn't necessarily tell you where things are stored.

PostgreSQL lets you define new tablespaces. A tablespace is a named directory in the filesystem. PostgreSQL lets you store individual tables, indexes, and entire databases in any permissible tablespace. So if a database were created in a specific tablespace, I believe none of its objects would appear in the data directory.

For solid run-time information about where things are stored on disk, you'll probably need to query pg_database, pg_tablespace, or pg_tables from the system catalogs. Tablespace information might also be available in the information_schema views.

But for merging or copying to your hard disk, using these files is almost certainly a Bad Thing. For that kind of work, pg_dump is your friend.

Answer 3

If you're talking about copying the disk files as a form of backup, you should probably read this, especially the section on Continuous Archiving and Point-in-Time Recovery (PITR):

http://www.postgresql.org/docs/current/interactive/backup.html

If you're thinking about trying to directly access and interpret data in the disk files, bypassing the database management system, that is a very bad idea for a lot of reasons. For one, the storage scheme is very complex. For another, it tends to change in every new major release (issued once per year). Thirdly, the ghost of E.F. Codd will probably haunt you; see rules 8, 9, 11, and 12 of Codd's 12 rules.