YMatrix

Version Release History
Platform Requirements

YMatrix

Quick Start

Standard Cluster Deployment

Data Modeling

Connecting to The database

Data Writing

Data Migration

Data Query

Maintenance and Monitoring

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

scenario

CREATE TABLESPACE

Define a new tablespace.

Summary

CREATE TABLESPACE tablespace_name [OWNER username]  LOCATION '/path/to/dir' 
   [WITH (contentID_1='/path/to/dir1'[, contentID_2='/path/to/dir2' ... ], storage_degradation_parameter)]

describe

CREATE TABLESPACE Register and configure a new tablespace for the YMatrix database system. The tablespace name must be different from the name of any existing tablespace in the system. Tablespaces are YMatrix database system objects (global objects) and you can use tablespaces in any database if you have the appropriate privileges.

Tablespaces allow superusers to define the alternate host file system location where the data files containing database objects such as tables and indexes reside.

Users with appropriate privileges can pass tablespace names to CREATE DATABASE, CREATE DATABASE, or CREATE INDEX to store data files for these objects in the specified tablespace.

In the YMatrix database, file system locations must exist on all hosts, including hosts running Master, Standby, each Primary, and each Mirror.

Parameters

tablespacename

  • The name of the tablespace to be created. This name cannot start with pg or gp because such names are reserved for the system tablespace.

OWNER username

  • Username that owns the tablespace. If omitted, the default is the user executing the command. Only superusers can create tablespaces, but they can assign ownership of the tablespace to non-superusers.

LOCATION '/path/to/dir'

  • The absolute path to the directory (host system file location), which will be the root directory of the tablespace, e.g. '/home/mxadmin/test'. When registry space, the directory should be empty and the database administrator (mxadmin) must have access. This directory must be specified by the absolute pathname.
    For each Segment instance, you can specify a different directory for the tablespace in the WITH clause.

contentID_i='/path/to/dir_i'

  • The value ID_i is the content ID of the Segment instance. /path/to/dir_i is the absolute path to the host system file location of the Segment instance used as the root of the tablespace. You cannot specify the Content ID (-1) of the Master instance. You can specify the same directory for multiple segments.
    If the Segment instance is not listed in the WITH clause, the YMatrix database will use the directory specified by the LOCATION clause.
    When registry space, the directory should be empty and must be owned by the YMatrix database system user.

storage_degradation_parameter

  • The relevant tablespace parameters involved in the MARS3 table downgrade storage function. Including oss_accessid, oss_secretkey, oss_sessiontoken, oss_region, oss_endpoint, oss_bucket, oss_keyprefix, oss_cache_size. Such parameters are only configured when creating tablespaces.

Note

Tablespaces are supported only on systems that support symbolic links.

CREATE TABLESPACE cannot be executed within a transaction block.

When creating tablespaces, make sure that the file system location has sufficient I/O speed and available disk space.

CREATE TABLESPACE Creates a symbolic link from the pg_tblspc directory in the Master and Segment instance data directory to the directory specified in the command.

The system catalog table pg_tablespace stores tablespace information. This command displays the OID value, name, and owner of the tablespace.

SELECT oid, spcname, spcowner FROM pg_tablespace ;

YMatrix database built-in function gp_tablespace_location(tablespace_oid) displays the tablespace host system file locations for all Segment instances. This command lists the Segment database ID and host system file locations for tablespaces with OID 16385.

SELECT * FROM gp_tablespace_location(16385)


The downgrade storage parameters are described as follows:

| Field name | Unit | Default value | Value range | Description | |--|--|--|--|--|--| | oss_accessid | | | 0 ~ 2048 | Access Key | | oss_secretkey | | | 0 ~ 2048 | Private access key. This field will be encrypted by default, and the ciphertext is saved. The encryption function can be closed through the parameter mx_enable_oss_secretkey_encrypt | | oss_sessiontoken | | | | 0 ~ 2048 | STS mechanism token. This field will be encrypted by default, and the ciphertext is saved. The encryption function can be closed through the parameter mx_enable_oss_sessiontoken_encrypt | | oss_region | | cn-north-1 | 0 ~ 2048 | Object service area (no need to fill in the local environment) | | oss_endpoint | | | 0 ~ 2048 | Object service address | | oss_bucket | | | 0 ~ 2048 | Object bucket name | | oss_keyprefix | | | 0 ~ 2048 | Object bucket prefix. Object prefix is ​​similar to a subdirectory and requires that it must end with / | | oss_cache_size | MB | 2048 | 1 ~ INT_MAX | Specify the maximum file space that can be occupied in the table space when using file cache object storage |

Example

Create a new tablespace and specify the file system location for Master and all Segment instances:

CREATE TABLESPACE mytblspace LOCATION '/gpdbtspc/mytestspace' ;

Create a new tablespace and specify a location for the Segment instance with content IDs 0 and 1. For Master and Segment instances not listed in the WITH clause, specify the file system location for the tablespace in the LOCATION clause.

CREATE TABLESPACE mytblspace LOCATION '/gpdbtspc/mytestspace' WITH (content0='/temp/mytest', content1='/temp/mytest');

This example specifies the same location for both Segment instances. You can specify a different location for each segment.

Create a tablespace in the cluster for accessing the downgrade storage function:

CREATE TABLESPACE tbs LOCATION '/temp/mytest'
WITH(
    oss_accessid='XYwJgHLTWGlk2KmP9Ruc',
    oss_secretkey='fk160295luHpQsq2DiaIYaTBAzNCkjgoIMr1OHIT',
    oss_endpoint='http://localhost:9000',
    oss_bucket='test'
);

compatibility

CREATE TABLESPACE is a YMatrix database extension.

See also

ALTER TABLESPACE, DROP TABLESPACE