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

Storming your Development Team

Benefits of Using Flutter In 2022

flutter benefits

Financial Reporting Semantics

How to Use PyScript Within VS Code

PyScript is a framework that allows users to create rich Python applications in the browser using HTML’s interface. PyScript aims to give users a first-class programming language that has consistent styling rules, is more expressive, and is easier to learn.

ZooToken — Community Update / Voice Chat Recap— 7.5.21

CS373 Spring 2021: Final Entry

The installation guide for Redis

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

RADIUS — Remote Authentication Dial-In User Service CONFIGURATION

Container Monitoring Guide: Using Cadvisor, Prometheus And Grafana For Easy Docker Container…

Kubernetes Capsule-3

Sitecore 10 Docker installation : Solr-init is unhealthy error