Creating and Managing Tablespaces

    A tablespace requires a file system location to store its database files. In Greenplum Database, the master and each segment (primary and mirror) require a distinct storage location. The collection of file system locations for all components in a Greenplum system is a filespace. Filespaces can be used by one or more tablespaces.

    Parent topic: Defining Database Objects

    A filespace sets aside storage for your Greenplum system. A filespace is a symbolic storage identifier that maps onto a set of locations in your Greenplum hosts’ file systems. To create a filespace, prepare the logical file systems on all of your Greenplum hosts, then use the utility to define the filespace. You must be a database superuser to create a filespace.

    Note: Greenplum Database is not directly aware of the file system boundaries on your underlying systems. It stores files in the directories that you tell it to use. You cannot control the location on disk of individual files within a logical file system.

    1. Log in to the Greenplum Database master as the gpadmin user.

    2. Create a filespace configuration file:

      1. $ gpfilespace -o gpfilespace_config

    3. gpfilespace creates a configuration file. Examine the file to verify that the gpfilespace configuration is correct.

    4. Run gpfilespace again to create the filespace based on the configuration file:

    Moving the Location of Temporary or Transaction Files

    You can move temporary or transaction files to a specific filespace to improve database performance when running queries, creating backups, and to store data more sequentially.

    The dedicated filespace for temporary and transaction files is tracked in two separate flat files called gp_temporary_files_filespace and . These are located in the pg_system directory on each primary and mirror segment, and on master and standby. You must be a superuser to move temporary or transaction files. Only the gpfilespace utility can write to this file.

    About Temporary and Transaction Files

    Unless otherwise specified, temporary and transaction files are stored together with all user data. The default location of temporary files, <filespace_directory>/<tablespace_oid>/<database_oid>/pgsql_tmp is changed when you use gpfilespace --movetempfiles for the first time.

    Also note the following information about temporary or transaction files:

    • You can dedicate only one filespace for temporary or transaction files, although you can use the same filespace to store other types of files.
    • You cannot drop a filespace if it used by temporary files.
    • You must create the filespace in advance. See .

    To move temporary files using gpfilespace

    1. Check that the filespace exists and is different from the filespace used to store all other user data.

    2. Bring Greenplum Database online with no active session and run the following command:

      1. gpfilespace --movetempfilespace filespace_name

      The location of the temporary files is stored in the segment configuration shared memory (PMModuleState) and used whenever temporary files are created, opened, or dropped.

    After you create a filespace, use the CREATE TABLESPACE command to define a tablespace that uses that filespace. For example:

    1. =# CREATE TABLESPACE fastspace FILESPACE fastdisk;

    Database superusers define tablespaces and grant access to database users with the GRANT CREATE command. For example:

    Using a Tablespace to Store Database Objects

    1. CREATE TABLE tablename(options) TABLESPACE spacename

    For example, the following command creates a table in the tablespace space1:

    1. CREATE TABLE foo(i int) TABLESPACE space1;

    You can also use the default_tablespace parameter to specify the default tablespace for and CREATE INDEX commands that do not specify a tablespace:

    The tablespace associated with a database stores that database’s system catalogs, temporary files created by server processes using that database, and is the default tablespace selected for tables and indexes created within the database, if no TABLESPACE is specified when the objects are created. If you do not specify a tablespace when you create a database, the database uses the same tablespace used by its template database.

    You can use a tablespace from any database if you have appropriate privileges.

    Every Greenplum Database system has the following default tablespaces.

    • pg_global for shared system catalogs.
    • pg_default, the default tablespace. Used by the template1 and template0 databases.

    These tablespaces use the system default filespace, pg_system, the data directory location created at system initialization.

    To see filespace information, look in the pg_filespace and pg_filespace_entry catalog tables. You can join these tables with pg_tablespace to see the full definition of a tablespace. For example:

    1. =# SELECT spcname as tblspc, fsname as filespc, 
    2.           fsedbid as seg_dbid, fselocation as datadir 
    3.    FROM   pg_tablespace pgts, pg_filespace pgfs, 
    4.           pg_filespace_entry pgfse 
    5.    WHERE  pgts.spcfsoid=pgfse.fsefsoid 
    6.    ORDER BY tblspc, seg_dbid;

    Dropping Tablespaces and Filespaces

    To drop a tablespace, you must be the tablespace owner or a superuser. You cannot drop a tablespace until all objects in all databases using the tablespace are removed.

    Only a superuser can drop a filespace. A filespace cannot be dropped until all tablespaces using that filespace are removed.

    The command removes an empty tablespace.

    Note: You cannot drop a filespace if it stores temporary or transaction files.