PostgreSQL tablespace creation and deletion

postgres=# \h create tablespaceCommand: CREATE TABLESPACEDescription: define a new tablespaceSyntax:CREATE TABLESPACE tablespace_name[OWNER user_name]LOCATION’directory’[WITH (tablespace_option = value [, …])]
[root@localhost ~]# mkdir /usr/local/pgdata[root@localhost ~]# chown postgres:postgres /usr/local/pgdata/
postgres=# create tablespace tbs_test owner postgres location ‘/usr/local/pgdata’;CREATE TABLESPACE
[root@localhost ~]# ll /mnt/syncdata/pgsql941/data/pg_tblspc/total 0lrwxrwxrwx. 1 postgres postgres 17 Aug 30 02:06 51276 -> /usr/local/pgdata[root@localhost ~]# ll /usr/local/pgdata/total 4drwx — — — . 2 postgres postgres 4096 Aug 30 02:06 PG_9.4_201409291
postgres=# create table test(a int) tablespace tbs_test;CREATE TABLE
[root@localhost ~]# ll /usr/local/pgdata/PG_9.4_201409291/13003/51277-rw — — — -. 1 postgres postgres 0 Aug 30 02:15 /usr/local/pgdata/PG_9.4_201409291 / 13003/51277
postgres=# select oid,datname from pg_database where datname = ‘postgres’;oid | datname — — — -+ — — — — — 13003 | postgres(1 row)postgres=# select relname,relfilenode from pg_class where relname=’test’;relname | relfilenode — — — — -+ — — — — — — -test | 51277(1 row)
postgres=# \h drop tablespaceCommand: DROP TABLESPACEDescription: remove a tablespaceSyntax:DROP TABLESPACE [IF EXISTS] name
postgres=# drop tablespace if exists tbs_test;ERROR: tablespace “tbs_test” is not empty
postgres=# drop table if exists test;DROP TABLEpostgres=# drop tablespace if exists tbs_test;DROP TABLESPACE
CREATE TABLE Teacher (ID int,Name varchar(20))TABLESPACE tbs;
CREATE INDEX idx_teacher on Teacher(ID) TABLESPACE tbs;
ALTER TABLE Teacher ADD UNIQUE(ID) USING INDEX TABLESPACE tbs;
ALTER TABLE Teacher ADD PRIMARY KEY(ID) USING INDEX TABLESPACE tbs;
ALTER TABLE Teacher set TABLESPACE pg_default;

--

--

--

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

The District Weekly — November 7th

HTS Hosting: High Performance, Low-Priced Web Hosting Service Provider

Kotlin Delegates Check

Elearning Software: All Features you Need to Know | Expertplus LMS

Getting started with PostgreSQL

Things every software developer should know

Things every software/developer should know

Clone For H&M India’s website

Deploying TLS certificates for local development and production using Kubernetes, cert-manager…

Love and locks, ironic isn’t it?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

More from Medium

How to use GQLAlchemy query builder?

Postgres replica conflicts: Part 2

Finding Curve Inflection Points in PostGIS

Dockerize Flask Application-AWS ECS+Flask