script to get the tablespace location and owner of the tablespace in PostgreSQL

 script to get the tablespace location in PostgreSQL

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;

  spcname    |                         pg_tablespace_location

--------------+-------------------------------------------------------------------------

 pg_default   |

 pg_global    |

 testdb2_data | /rdsdbdata/db/base/tablespace/rdsdbdata/db/base/tablespace/testdb2_data

(3 rows)

******************create DB to specific tablespace in postgres SQL****************

postgres=# create database TEST tablespace TBSspace;

CREATE DATABASE

*****************************

testdb2_connect_prod=> select version();

                                                 version

---------------------------------------------------------------------------------------------------------

 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

(1 row)

postgres_db_dev=> \du db_admin

                        List of roles

 Role name |          Attributes           |    Member of

-----------+-------------------------------+-----------------

 db_admin  | Create role, Create DB       +| {rds_superuser}

           | Password valid until infinity |

postgres_db_dev=>  SELECT spcname,spcowner FROM pg_tablespace;

   spcname    | spcowner

--------------+----------

 pg_default   |       10

 pg_global    |       10

 testdb2_data |    16410

(3 rows)

postgres_db_dev=> select usename from pg_user where usesysid='16410';

    usename

---------------

 testdb2_admin

(1 row)

testdb2_workbench_dev=> select current_database();

   current_database

-----------------------

 testdb2_workbench_dev

(1 row)

postgres_db_dev=> SELECT current_user;

 current_user

---------------

 testdb2_admin

(1 row)