PostgreSQL tablespace creation and deletion

Tablespace: literally understood as the physical space where the table is stored, which actually includes the tables, indexes, and sequences of the database.

You can create tablespaces in different partitions on the server.

Syntax:

postgres=# \h create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
[OWNER user_name]
LOCATION’directory’
[WITH (tablespace_option = value [, …])]

The user must have access rights to the directory where the tablespace is located, so before creating the tablespace, you need to create the corresponding directory under the corresponding partition and assign permissions to it.

[root@localhost ~]# mkdir /usr/local/pgdata
[root@localhost ~]# chown postgres:postgres /usr/local/pgdata/

Example of creating tablespace:

postgres=# create tablespace tbs_test owner postgres location ‘/usr/local/pgdata’;
CREATE TABLESPACE

After creating the tablespace successfully, you can see a new directory pg_tblspc in the database cluster directory with a connection file 51276, pointing to /usr/local/pgdata

[root@localhost ~]# ll /mnt/syncdata/pgsql941/data/pg_tblspc/
total 0
lrwxrwxrwx. 1 postgres postgres 17 Aug 30 02:06 51276 -> /usr/local/pgdata
[root@localhost ~]# ll /usr/local/pgdata/
total 4
drwx — — — . 2 postgres postgres 4096 Aug 30 02:06 PG_9.4_201409291

Create a table in this tablespace:

postgres=# create table test(a int) tablespace tbs_test;
CREATE TABLE

Now a file corresponding to the test table will be added under the tablespace directory:

[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

Among them, 51277 corresponds to the relfilenode of the test table, and 13003 is the oid of the database postgres.

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)

Delete the tablespace:

postgres=# \h drop tablespace
Command: DROP TABLESPACE
Description: remove a tablespace
Syntax:
DROP TABLESPACE [IF EXISTS] name

Before deleting a tablespace, you must delete all database objects under the tablespace, otherwise, you cannot delete it.

Such as:

postgres=# drop tablespace if exists tbs_test;
ERROR: tablespace “tbs_test” is not empty

Delete the table test created in this tablespace, and then delete the tablespace.

postgres=# drop table if exists test;
DROP TABLE
postgres=# drop tablespace if exists tbs_test;
DROP TABLESPACE

Specify the tablespace when creating the table.

CREATE TABLE Teacher (ID int,Name varchar(20))TABLESPACE tbs;

Specify the tablespace when creating the index.

CREATE INDEX idx_teacher on Teacher(ID) TABLESPACE tbs;

Specify the tablespace of the constraint index when creating a unique constraint.

ALTER TABLE Teacher ADD UNIQUE(ID) USING INDEX TABLESPACE tbs;

When increasing the primary key, specify the tablespace of the primary key index.

ALTER TABLE Teacher ADD PRIMARY KEY(ID) USING INDEX TABLESPACE tbs;

Move the table from one tablespace to another tablespace.

ALTER TABLE Teacher set TABLESPACE pg_default;

The table will be locked when the table is moved. At this time, all operations on the table will be blocked, including query operations. When executing a move command, you need to consider the appropriate timing.

Thanks for reading this post, if you like, you can follow me up..pankajconnect.com