Tablespace Management

Scenarios

TaurusDB for PostgreSQL provides the PostgreSQL tablespace management solution based on user root.

Tablespace Management

You can create and delete tablespaces, and grant tablespace permissions.

Connect to the database as user root and create a tablespace.

# psql --host=<TaurusDB_ADDRESS> --port=<DB_PORT> --dbname=<DB_NAME> --username=root -c "select control_tablespace ('create', '<TABLESPACE_NAME>');"

**Table 1** Parameter description
Parameter	Description
TaurusDB_ADDRESS	IP address of the TaurusDB for PostgreSQL instance
DB_PORT	Port of the TaurusDB for PostgreSQL instance
DB_NAME	Database name
TABLESPACE_NAME	Tablespace name

Enter the password of user root when prompted.

Log in to the my_db database and create the tbspc1 tablespace. Example:

# psql --host=192.168.6.141 --port=5432 --dbname=my_db --username=root -c "select control_tablespace('create', 'tbspc1');"
```
Password for user root:
          control_tablespace          
------------------------------    
create tablespace tbspc1 successfully.   
(1 row)
```
If the creation fails, view error logs of the DB instance.

To ensure performance, a maximum of 100 tablespaces can be created.

Connect to a database as user root and grant the tablespace usage permissions to specified users.

# psql --host=<TaurusDB_ADDRESS> --port=<DB_PORT> --dbname=<DB_NAME> --username=root -c "select control_tablespace ('alter', '<TABLESPACE_NAME>', '<USER_NAME>');"

**Table 2** Parameter description
Parameter	Description
TaurusDB_ADDRESS	IP address of the TaurusDB for PostgreSQL instance
DB_PORT	Port of the TaurusDB for PostgreSQL instance
DB_NAME	Database name
TABLESPACE_NAME	Tablespace name
USER_NAME	Tablespace username

Enter the password of user root when prompted.

Log in to the my_db database and grant permissions to tablespace tbspc1, for example:

# psql --host=192.168.6.141 --port=5432 --dbname=my_db --username=root -c "select control_tablespace('alter', 'tbspc1', 'user1');"
```
Password for user root:
         control_tablespace         
----------------------------    
alter tablespace tbspc1 successfully.   
(1 row)
```
If the permissions fail to be granted, view error logs of the DB instance.

Connect to a database as user root and delete a tablespace.

# psql --host=<TaurusDB_ADDRESS> --port=<DB_PORT> --username=root --dbname=<DB_NAME> -c "select control_tablespace('drop', '<TABLESPACE _NAME>');"

**Table 3** Parameter description
Parameter	Description
TaurusDB_ADDRESS	IP address of the TaurusDB for PostgreSQL instance
DB_PORT	Port of the TaurusDB for PostgreSQL instance
DB_NAME	Database name
TABLESPACE_NAME	Tablespace name

Enter the password of user root when prompted.

Example:

# psql --host=192.168.6.141 --port=8635 --dbname=my_db --username=root -c "select control_tablespace('drop', 'tbspc1');"
```
Password for user root:
         control_tablespace         
----------------------------    
drop tablespace tbspc1 successfully.   
(1 row)
```
Before deleting the tablespace, ensure that it is empty. If the deletion fails, view error logs of the DB instance.