Tablespace Management

Scenarios

RDS for PostgreSQL allows you to manage PostgreSQL tablespaces as the root user. You can use either of the following methods:

• System-predefined tablespaces: Use the control_tablespace function.
• User-defined tablespaces: Use standard SQL statements.

System-predefined Tablespaces

1. Connect to the database as user root and create a tablespace. To ensure database performance, a maximum of 100 tablespaces can be created.

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

   Table 1 Parameter description

   Parameter	Description
   RDS_ADDRESS	IP address of the RDS instance.
   DB_PORT	Port of the RDS instance.
   DB_NAME	Database name.
   TABLESPACE_NAME	Tablespace name.

   Run the example command below to log in to database my_db and create tablespace tbspc1.

   # psql --host=192.168.6.141 --port=5432 --dbname=my_db --username=root -c "select control_tablespace('create', 'tbspc1');"

   Enter the password of the root user when prompted.

   Password for user root:
             control_tablespace          
   ------------------------------    
   create tablespace tbspc1 successfully.   
   (1 row)

   If the creation fails, view the instance's error logs for more details.

2. Connect to the database as the root user and grant tablespace usage permissions to a specific user.

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

   Table 2 Parameter description

   Parameter	Description
   RDS_ADDRESS	IP address of the RDS instance.
   DB_PORT	Port of the RDS instance.
   DB_NAME	Database name.
   TABLESPACE_NAME	Tablespace name.
   USER_NAME	Tablespace username.

   Run the example command below to log in to database my_db and grant the permission to use tablespace tbspc1.

   # psql --host=192.168.6.141 --port=5432 --dbname=my_db --username=root -c "select control_tablespace('alter', 'tbspc1', 'user1');"

   Enter the password of the root user when prompted.

   Password for user root:
            control_tablespace         
   ----------------------------    
   alter tablespace tbspc1 successfully.   
   (1 row)

   If the permission fails to be granted, view the instance's error logs for more details.

3. Connect to the database as the root user and delete the tablespace. Before deleting the tablespace, ensure that it is empty.

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

   Table 3 Parameter description

   Parameter	Description
   RDS_ADDRESS	IP address of the RDS instance.
   DB_PORT	Port of the RDS instance.
   DB_NAME	Database name.
   TABLESPACE_NAME	Tablespace name.

   Example:

   # psql --host=192.168.6.141 --port=8635 --dbname=my_db --username=root -c "select control_tablespace('drop', 'tbspc1');"

   Enter the password of the root user when prompted.

   Password for user root:
            control_tablespace         
   ----------------------------    
   drop tablespace tbspc1 successfully.   
   (1 row)

   If the deletion fails, view the instance's error logs for more details.

User-defined Tablespaces

RDS for PostgreSQL uses /var/lib/pgsql/selfspc/tbls as the base directory for user-defined tablespaces of the root user. You can use standard SQL statements to manage tablespaces based on this directory.

1. Connect to the database as the root user.

   # psql --host=<RDS_ADDRESS> --port=<DB_PORT> --dbname=<DB_NAME> --username=root

   Table 4 Parameter description

   Parameter	Description
   RDS_ADDRESS	IP address of the RDS instance.
   DB_PORT	Port of the RDS instance.
   DB_NAME	Database name.

   Run the example command below to log in to database my_db and enter the password of the root user as prompted.

   # psql --host=192.168.6.141 --port=5432 --dbname=my_db

2. Create a tablespace.

   CREATE TABLESPACE <TABLESPACE_NAME> LOCATION '<TABLESPACE_LOCATION>'; 

   Table 5 Parameter description

   Parameter	Description
   TABLESPACE_NAME	Tablespace name.
   TABLESPACE_LOCATION	Storage path of a tablespace. The value must start with a slash (/) and can contain only digits, letters, and special characters (./-_). The final tablespace path is a concatenation of the base directory and this specified value.

   Run the example command below to create a tablespace named user_tbspc in the /var/lib/pgsql/selfspc/tbls/data directory.

   CREATE TABLESPACE user_tbspc LOCATION '/data'; 

3. Query the tablespace information.

   SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace; 

4. Use the tablespace.

   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       name VARCHAR(50)
   ) TABLESPACE user_tbspc;

5. Delete the tablespace.

   DROP TABLESPACE if exists user_tbspc;