PostgreSQL Tablespace

                
                 Tablespace in PostgreSQL is an additional data area outside the base directory. It is available on PostgreSQL version 8.0. It allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

This is useful in at least two ways.

1.    The partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured. 
2.    Tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance.

PostgreSQL comes with two default tablespaces:

•  pg_default          -           it stores all user data.
• pg_global                -            it stores all global data.  

 

Step 1: Make a tablespace directory.

[root@s2 /]# mkdir tablespace

[root@s2 /]# chown -R postgres:postgres /tablespace/

[root@s2 /]# chmod 700 /tablespace/

Step 2: Create tablespace and choose location.

postgres=# create tablespace tbs location '/tablespace/';

postgres=# show default_tablespace ;             //show current tablespace

 default_tablespace

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

postgres=# set default_tablespace TO tbs;          //assign current session only

postgres=# show default_tablespace ;

 default_tablespace

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

 tbs

postgres=# create table siva (id int,name text);

postgres=# select pg_relation_filepath('siva');

            pg_relation_filepath            

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

 pg_tblspc/32882/PG_10_201707211/13451/32883

postgres=# alter system set default_tablespace =tbs;  //assign all sessions & must server restart

postgres=# create table pearl (id int,name text) tablespace tbs;  //create table & assign tablespace

postgres=# create database pearl tablespace tbs;  //create database & assign tablespace

How to List of all tablespaces

postgres=# \db+             //only psql tool

postgres=# select * from pg_tablespace ;

postgres=# alter tablespace tbs rename to sbt; //Rename tablespace

postgres=# alter tablespace sbt owner to muthu ;            //chane tablespace owner

postgres=# alter table pearl set tablespace sbt ;               //assign tablespace on table

postgres=# alter database db tablespace sbt ;             //assign tablespace on database

postgres=# select * from pg_tables where tablespace='sbt'; //list particular tablespace tables

postgres=# drop tablespace sbt ;              //drop tablespace

=============================================================================