Re: How to get the physical locations of tables, views, functions etc of Postgresql in Windows & Linux?

On Thu, May 3, 2012 at 6:52 PM, Siva Palanisamy <siva_p(at)hcl(dot)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.

http://www.postgresql.org/docs/9.1/static/sql-createtablespace.html

> ****
>
> 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
Blog: http://raghavt.blogspot.com/