RDBMS Information - PostgreSQL - How to find the linkage of schema and tablespace

Purpose

This page provides the information on how to find the linkage of schema and tablespace in a particular Postgres server. The query will provide the results of all the tables and the corresponding tablespaces under which the schemas have been created.

 For more information on CAST Storage Service 2 (CSS2), please refer to CAST Storage Service.

Applicable RDBMS

Details

After connecting to the postgres server in pgAdmin on which the linkage between schemas and their tablespaces are to be checked, run the following query:

select distinct schemaname, coalesce (tablespace, '<name of tablespace>') 
from pg_tables
union 
select distinct schemaname, coalesce (tablespace, '<name of tablespace>') 
from pg_indexes
order by 1

Most of the schemas that are created are under the ‘default’ tablespace, but the name of tablespace can be defined in the query.

For example, The above query when executed in pgAdmin for ‘default’ tablespace, will provide the following information from the connected postgres server:

select distinct schemaname, coalesce (tablespace, 'default') 
from pg_tables
union 
select distinct schemaname, coalesce (tablespace, 'default') 
from pg_indexes
order by 1

Query result example

“clientserver_central”;“default”

“clientserver_local”;“default”
“clientserver_mngt”;“default”
“clientservertest_measure”;“default”
“cs_central”;“default”
“cs_local”;“default”
“cs_measure”;“default”
“cs_mngt”;“default”
“diamondresort_mngt”;“default”
“flex_central”;“default”
“flex_local”;“default”
“flex_mngt”;“default”

This query will list down all the tables and their tablespaces under the same postgres server.

Notes/comments

Ticket  # 5912

Related Pages