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

Question

I am using postgres 9.2 on redhat 6

this should be simple but I can't find it anywhere. I am looking for the database table and column which stores the Location for a postgres 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?

thanks

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;

Answer 2

Another super easy command to list all table spaces

\db+

This will provide you all table space details very quick