Where in the DB is the Location of a PostgreSQL tablespace stored

Question

I am using PostgreSQL 9.2 on RedHat 6. I am looking for the database table and column which stores the location for a PostgreSQL tablespace. I thought it would be in PG_TABLESPACE, but

select * from pg_tablespace

shows...

postgres=# select * from pg_tablespace;
     spcname     | spcowner | spcacl | spcoptions
-----------------+----------+--------+------------
 pg_default      |       10 |        |
 pg_global       |       10 |        |
 C_TBL_DB91SABIR |       10 |        |
(3 rows)

but no location, any ideas where the location is kept?

Answer 1

Use pg_tablespace_location(tablespace_oid)(PostgreSQL 9.2+) to get the path in the file system where the tablespace is located.

You'll get oid of tablespace from pg_tablespace, so the query should be

select spcname
      ,pg_tablespace_location(oid) 
from   pg_tablespace;