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?

Beatty

possible duplicate of Where Postgres database files are saved in ubuntu? – Ingham

@Bruno: Not quite the same question, and clearly not the same answers. – Ornithine

@Catcall you're right indeed. – Ingham

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

Nematode

+1, because I didn't know about that function. But pg_relation_filepath() doesn't work for databases. For databases, I think you'd still need to look at the tablespace. – Ornithine

@Catcall: I added a bit to my answer to cover databases and point to the manual for the more complex situation involving tablespaces. – Nematode

Sorry, Erwin. I've already upvoted you once. If you want more points, you'll have to teach me something else I don't know. – Ornithine

@Catcall: It's the other way round, really: I am to seizing your suggestion to improve my answer. Targeted at the general public and the OP. Did not expect to find any more gaps in your extensive knowledge of PostgreSQL for today. ;) – Nematode

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.

Meyers

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.

Lilian