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

Not the exact answer : `SHOW data_directory;` will show you the location of current DB — Vivek S., Feb 05 '16 at 10:36
hi @wingedpanther, that shows the PGDATA location, but i'm looking for the directory of the tablespace, i want to query it so that i can script a removal of the physical folder when a tablespace is dropped. — davegreen100, Feb 05 '16 at 10:38
Try this `select spcname, pg_tablespace_location(oid) from pg_tablespace; ` — Vivek S., Feb 05 '16 at 10:45

Vivek S. · Accepted Answer · 2017-12-12T10:05:11.273

34

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;

edited Dec 12 '17 at 10:05

answered Feb 05 '16 at 10:47

Vivek S.

19,945
7
68
85

1

both this sql query and psql command \db+ is relevant with user defined or so called non system tablespaces. e.g default pgroot is defined by environment variable PGROOT on some cases – Mehme Sep 07 '22 at 08:21

score 5 · Answer 2 · answered Aug 15 '21 at 14:22

5

Another super easy command to list all table spaces

\db+

This will provide you all table space details very quick

answered Aug 15 '21 at 14:22

Jerry

7,863
2
26
35

score -4 · Answer 3 · answered Oct 23 '19 at 11:19

-4

Use below query to get tablespace name tablespace location.

postgres=# select spcname ,pg_tablespace_location(oid) from pg_tablespace;
  spcname   |    pg_tablespace_location     
------------+-------------------------------
 pg_default | 
 pg_global  | 
 fastspace  | /var/lib/postgresql/data/base
 demotbs    | /var/lib/postgresql/data

answered Oct 23 '19 at 11:19

Srikant Patra

399
4
5

3

What's the point in duplicating the already existing accepted answer? – Washington Guedes Oct 25 '19 at 13:29

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

3 Answers3