Postgresql: tracking which tables belong to which tablespace.

Find out the physical location of the tablespace

VCDB=# \db+
                                     List of tablespaces
    Name    |  Owner   |              Location              | Access privileges | Description
------------+----------+------------------------------------+-------------------+-------------
alarm      | vc       | /storage/seat/vpostgres/alarmtblsp |                   |
event      | vc       | /storage/seat/vpostgres/eventtblsp |                   |
hs1        | vc       | /storage/seat/vpostgres/hs1tblsp   |                   |
hs2        | vc       | /storage/seat/vpostgres/hs2tblsp   |                   |
hs3        | vc       | /storage/seat/vpostgres/hs3tblsp   |                   |
hs4        | vc       | /storage/seat/vpostgres/hs4tblsp   |                   |
pg_default | postgres |                                    |                   |
pg_global  | postgres |                                    |                   |
task       | vc       | /storage/seat/vpostgres/tasktblsp  |                   |
(9 rows)

where clause of spcname 

SELECT spcname ,relname FROM pg_class c join pg_tablespace ts

ON (CASE WHEN c.reltablespace = 0 THEN

(SELECT dattablespace FROM pg_database

WHERE datname = current_database())

ELSE c.reltablespace END) = ts.oid

WHERE relname NOT LIKE 'pg_toast%'

AND relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE

nspname IN ('pg_catalog', 'information_schema')) and spcname='event';

Simpler form that includes the toast.

SELECT spcname ,relname FROM pg_class c join pg_tablespace ts

ON (CASE WHEN c.reltablespace = 0 THEN

(SELECT dattablespace FROM pg_database

WHERE datname = current_database())

ELSE c.reltablespace END) = ts.oid

WHERE spcname='event';