Postgresql - TablespaceStep 1 : Make a tablespace directory -bash-4.2$ pwd /var/lib/pgsql -bash-4.2$ mkdir tbspcStep 2 : Find the existing tablespace with size
Method 1 :
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 39 MB |
pg_global | postgres | | | | 559 kB |
Method 2 :
postgres=# select * from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
(2 rows)
Step 3 : Create tablespace and choose location as above created directory
postgres=# create tablespace tbs location '/var/lib/pgsql/tbspc';
CREATE TABLESPACE
postgres=# select * from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
57347 | tbs | 10 | |
(3 rows)
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 39 MB |
pg_global | postgres | | | | 559 kB |
tbs | postgres | /var/lib/pgsql/tbspc | | | 0 bytes |
(3 rows)
Step 4 : Show current tablespace
postgres=# show default_tablespace ; default_tablespaceStep 5 : Set the created tablespace as default (session specific) postgres=# set default_tablespace to tbs; SET postgres=# show default_tablespace ; default_tablespace -------------------- tbs (1 row)Step 6 : Now if we try to create another tablespace in same directory, it will not allow postgres=# create tablespace tbs1 location '/var/lib/pgsql/tbspc'; ERROR: directory "/var/lib/pgsql/tbspc/PG_13_202007201" already in use as a tablespaceStep 7 : To check the location where the created tablespace are stored -bash-4.2$ cd $PGDATA -bash-4.2$ pwd /var/lib/pgsql/13/data -bash-4.2$ cd pg_tblspc/ -bash-4.2$ ll total 0 lrwxrwxrwx. 1 postgres postgres 20 Dec 12 17:17 57347 -> /var/lib/pgsql/tbspcThe Highlighted number is the identifier of the created tablespace. Step 8 : Find Identifier from database level which will be same as above
postgres=# select oid,* from pg_tablespace;
oid | oid | spcname | spcowner | spcacl | spcoptions
-------+-------+------------+----------+--------+------------
1663 | 1663 | pg_default | 10 | |
1664 | 1664 | pg_global | 10 | |
57347 | 57347 | tbs | 10 | |
(3 rows)
Step 9 : Creating Table With Default Tablespace
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 24 MB |
pg_global | postgres | | | | 559 kB |
tbs | postgres | /var/lib/pgsql/tbspc | | | 0 bytes |
(3 rows)
postgres=# create table testtable(id int) tablespace tbs;
CREATE TABLE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------------------+-------------------+---------+----------+-------------
pg_default | postgres | | | | 24 MB |
pg_global | postgres | | | | 559 kB |
tbs | postgres | /var/lib/pgsql/tbspc | | | 19 bytes |
(3 rows)
Step 10 : To Know the exact location of database
-bash-4.2$ cd tbspc -bash-4.2$ ll total 0 drwx------. 3 postgres postgres 19 Dec 14 11:58 PG_13_202007201 -bash-4.2$ cd PG_13_202007201/ -bash-4.2$ ll total 0 drwx------. 2 postgres postgres 19 Dec 14 11:58 14175 -bash-4.2$ cd 14175/ -bash-4.2$ ll total 0 -rw-------. 1 postgres postgres 0 Dec 14 11:58 65541 -bash-4.2$ pwd /var/lib/pgsql/tbspc/PG_13_202007201/14175The highlighted values is the identifier of the database. This is because one tablespace can be assigned to multiple databases. postgres=# select oid,* from pg_database; -[ RECORD 1 ]-+------------------------------------ oid | 14175 oid | 14175 datname | postgres datdba | 10 encoding | 6 datcollate | en_US.UTF-8 datctype | en_US.UTF-8 datistemplate | f datallowconn | t datconnlimit | -1 datlastsysoid | 14174 datfrozenxid | 479 datminmxid | 1 dattablespace | 1663 dataclStep 11 : To Know Exact Location of Table -bash-4.2$ cd tbspc -bash-4.2$ ll total 0 drwx------. 3 postgres postgres 19 Dec 14 11:58 PG_13_202007201 -bash-4.2$ cd PG_13_202007201/ -bash-4.2$ ll total 0 drwx------. 2 postgres postgres 19 Dec 14 11:58 14175 -bash-4.2$ cd 14175/ -bash-4.2$ ll total 0 -rw-------. 1 postgres postgres 0 Dec 14 11:58 65541 -bash-4.2$ pwd /var/lib/pgsql/tbspc/PG_13_202007201/14175 postgres=# select oid, relname from pg_class where relname='testtable'; -[ RECORD 1 ]------ oid | 65541 relname | testtableStep 12 : Set Permanent tablespace postgres=# alter database demodb set tablespace tbs; ALTER DATABASEStep 13 : Rename tablespace
postgres=# alter tablespace tbs rename to tbs1;
ALTER TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 24 MB |
pg_global | postgres | | | | 559 kB |
tbs1 | postgres | /var/lib/pgsql/tbspc | | | 7977 kB |
Step 14 : Drop tablespace
postgres=# drop tablespace tbs1;
DROP TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 24 MB |
pg_global | postgres | | | | 559 kB |
« Previous Next Topic » (Postgresql - Views) |