On Thu, May 3, 2012 at 6:52 PM, Siva Palanisamy
<siva_p@hcl.com> wrote:
Hi there! 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.
You first step should start from $PGDATA/base/, you find OID's as directories which are related to each database of your cluster. In OID's directory, you find all the objects ID's for Tables/indexes/view etc.,
To know the object id, you can use a system defined function.
postgres=# select pg_relation_filepath('foo');
pg_relation_filepath
----------------------
base/12780/16407
(1 row)
12780, is database OID.
For moving objects from one drive to other, you need to use tablespaces.
Also, can I able to see the contents by opening its files? I mean, can I able to access it? Please help me on this. Thanks!
You cannot know the contents in files, unless u are good hacker :)
---
Regards,
Raghavendra
EnterpriseDB Corporation