Tablespace in PostgreSQL Database

 Tablespace in PostgreSQL Database

Current tablespace in PostgreSQL DB

postgres=# select * from pg_tablespace;

 oid  |  spcname   | spcowner | spcacl | spcoptions

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

 1663 | pg_default |       10 |        |

 1664 | pg_global  |       10 |        |

(2 rows)

Create tablespace in PostgreSQL DB

postgres=# create tablespace data location 'D:\pg_data\tablespace';

or

postgres=# create tablespace data owner amit location 'D:\pg_data\tablespace';

CREATE TABLESPACE

postgres=#

postgres=# select * from pg_tablespace;

  oid  |  spcname   | spcowner | spcacl | spcoptions

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

  1663 | pg_default |       10 |        |

  1664 | pg_global  |       10 |        |

 49196 | data       |       10 |        |

(3 rows)

postgres=# create table test(sid int,serialname varchar(100)) tablespace data;

CREATE TABLE

postgres=# select * from pg_tables where tablename='test';

 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity

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

 public     | test      | postgres   | data       | f          | f        | f           | f

(1 row)

Drop Tablespace in PostgreSQL DB

postgres=# drop tablespace data;

ERROR:  tablespace "data" is not empty

postgres=# alter table test set tablespace pg_default;

ALTER TABLE

postgres=# drop tablespace data;

DROP TABLESPACE

postgres=# select pg_relation_filepath('test');

 pg_relation_filepath

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

 base/5/49200

(1 row)

postgres=# select * from pg_tablespace;

 oid  |  spcname   | spcowner | spcacl | spcoptions

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

 1663 | pg_default |       10 |        |

 1664 | pg_global  |       10 |        |

(2 rows)

Create Temp Tablespace in PostgreSQL DB

postgres=# create tablespace temp location 'D:\pg_data\tablespace';

CREATE TABLESPACE

postgres=#

postgres=# select * from pg_tablespace;

  oid  |  spcname   | spcowner | spcacl | spcoptions

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

  1663 | pg_default |       10 |        |

  1664 | pg_global  |       10 |        |

 49201 | temp       |       10 |        |

(3 rows)

set temp tablespace in postgresql configuration file.

postgres=# drop tablespace temp;

DROP TABLESPACE

postgres=#