CREATE TABLESPACE

Description

Creates a tablespace in a database.

Precautions

The system administrator or a user who inherits permissions of the built-in role gs_role_tablespace can create a tablespace.
Do not run CREATE TABLESPACE in a transaction block.
If running CREATE TABLESPACE fails but the internal directory (or file) has been created, the directory (or file) will remain. You need to manually clear it before creating the tablespace again. If there are residual files of soft links for the tablespace in the data directory, delete the residual files, and then perform OM operations.
CREATE TABLESPACE cannot be used for two-phase transactions. If it fails on some nodes, the execution cannot be rolled back.
You are advised not to use user-defined tablespaces in the Huawei Cloud scenario. This is because user-defined tablespaces are usually used with storage media other than the main storage (storage device where the default tablespace is located, such as a disk) to isolate I/O resources that can be used by different services. Storage devices use standard configurations and do not have other available storage media in the Huawei Cloud scenario. If the user-defined tablespace is not properly used, the system cannot run stably for a long time and the overall performance is affected. Therefore, you are advised to use the default tablespace.

Syntax

CREATE TABLESPACE tablespace_name
    [ OWNER user_name ] [ RELATIVE ] LOCATION 'directory' [ MAXSIZE 'space_size' ]
    [with_option_clause];

with_option_clause for creating a general tablespace is as follows:

WITH ( {filesystem= { ' general ' | " general " | general } | address = { ' ip:port [, ... ] ' | " ip:port [, ... ] " } | cfgpath = { ' path ' | " path " } | storepath = { ' rootpath ' | " rootpath " } | random_page_cost = { ' value ' | " value " | value } | seq_page_cost = { ' value ' | " value " | value }}[, ... ])

Parameters

tablespace_name
Specifies name of a tablespace to be created.

The tablespace name must be distinct from the name of any existing tablespace in the database and cannot start with "pg", which are reserved for system catalog spaces.

Value range: a string. It must comply with the naming convention.
OWNER user_name
Specifies the name of the user who will own the tablespace. If omitted, the default owner is the current user.

Only system administrators can create tablespaces, but they can use the OWNER clause to assign ownership of tablespaces to non-system-administrators.

Value range: a string. It must be an existing user.
RELATIVE
If this parameter is specified, a relative path is used. The location directory is relative to the data directory on each database node.

Directory hierarchy: the relative path of the directory /pg_location/ A relative path contains a maximum of two levels.

If this parameter is not specified, the absolute tablespace path is used. The location directory must be an absolute path.
LOCATION directory
Specifies the directory for the table space. When creating an absolute tablespace path, ensure that the directory meets the following requirements:
- The system user must have the read and write permissions on the directory, and the directory must be empty. If the directory does not exist, the system automatically creates it.
- The directory must be an absolute path, and does not contain special characters, such as dollar sign ($) and greater-than sign (>).
- The directory cannot be specified under the database data directory.
- The directory must be a local path.
Value range: a string. It must be a valid directory.
MAXSIZE 'space_size'
Specifies the maximum value of the tablespace in a single database node.

Value range: a string consisting of a positive integer and unit. The unit can be KB, MB, GB, TB, or PB currently. The unit of parsed value is KB and cannot exceed the range that can be expressed in 8 bits, which is 1 KB to 9007199254740991 KB.
filesystem
Specifies the file system used by the tablespace.

Value range:

general: general file system.

hdfs: Hadoop distributed file system. The current version does not support this function.

Default value: general
random_page_cost
Specifies the time and resources required for randomly reading pages.

Value range: 0 to 1.79769e+308

Default value: value of the GUC parameter random_page_cost
seq_page_cost
Specifies the time and resources required for reading pages in sequence.

Value range: 0 to 1.79769e+308

Default value: value of GUC parameter seq_page_cost

Examples

-- Create a tablespace.
gaussdb=# CREATE TABLESPACE tbs_location1 RELATIVE LOCATION 'test_tablespace/test_tablespace_1';

-- Create a tablespace and specify the maximum value.
gaussdb=# CREATE TABLESPACE tbs_location2 RELATIVE LOCATION 'test_tablespace/test_tablespace_2' MAXSIZE '10G';

-- Query tablespace information.
gaussdb=# SELECT * FROM pg_tablespace WHERE spcname = 'tbs_location2';
    spcname    | spcowner | spcacl | spcoptions | spcmaxsize | relative 
---------------+----------+--------+------------+------------+----------
 tbs_location2 |       10 |        |            | 10485760 K | t
(1 row)

-- Create a user joe.
gaussdb=# CREATE ROLE joe IDENTIFIED BY '********';

-- Create a tablespace and set its owner to user joe.
gaussdb=# CREATE TABLESPACE tbs_location3 OWNER joe RELATIVE LOCATION 'test_tablespace/test_tablespace_3';

-- Delete tablespaces and users.
gaussdb=# DROP TABLESPACE tbs_location1;
gaussdb=# DROP TABLESPACE tbs_location2;
gaussdb=# DROP TABLESPACE tbs_location3;
gaussdb=# DROP ROLE joe;