Dropping a Database
The DROP DATABASE command drops (or deletes) a database. It removes the system catalog entries for the database and deletes the database directory on disk that contains the data. You must be the database owner or a superuser to drop a database, and you cannot drop a database while you or anyone else is connected to it. Connect to template1 (or another database) before dropping a database. For example:
=> \c template1
=> DROP DATABASE mydatabase;
You can also use the client program dropdb to drop a database. For example, the following command connects to Greenplum Database using the provided host name and port and drops the database mydatabase:
$ dropdb -h masterhost -p 5432 mydatabase
Warning: Dropping a database cannot be undone.
Creating and Managing Tablespaces
Tablespaces allow database administrators to have multiple file systems per machine and decide how to best use physical storage to store database objects. They are named locations within a filespace in which you can create objects. Tablespaces allow you to assign different storage for frequently and infrequently used database objects or to control the I/O performance on certain database objects. For example, place frequently-used tables on file systems that use high performance solid-state drives (SSD), and place other tables on standard hard drives.
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.
Creating a Filespace
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 gpfilespace 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.
To create a filespace using gpfilespace
1. Log in to the Greenplum Database master as the gpadmin user.
$ su - gpadmin
2. Create a filespace configuration file:
Creating and Managing Tablespaces 126
$ gpfilespace -o gpfilespace_config
3. At the prompt, enter a name for the filespace, the primary segment file system locations, the mirror segment file system locations, and a master file system location. For example, if your configuration has 2 primary and 2 mirror segments per host:
Enter a name for this filespace> fastdisk primary location 1> /gpfs1/seg1
primary location 2> /gpfs1/seg2 mirror location 1> /gpfs2/mir1 mirror location 2> /gpfs2/mir2 master location> /gpfs1/master
4. gpfilespace creates a configuration file. Examine the file to verify that the gpfilespace configuration is correct.
5. Run gpfilespace again to create the filespace based on the configuration file:
$ gpfilespace -c gpfilespace_config
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
gp_transaction_files_filespace. 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 “Creating a Filespace”.
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. Issue smart shutdown to bring the Greenplum Database offline.
Creating and Managing Tablespaces 127 Note: If any connections are still in progess,the gpfilespace
--movetempfiles utility will fail.
3. Bring Greenplum Database online with no active session and run the following command:
gpfilespace --movetempfilespace filespace_name
Note: 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.
To move transaction files using gpfilespace
1. Check that the filespace exists and is different from the filespace used to store all other user data.
2. Issue smart shutdown to bring the Greenplum Database offline.
Note: If any connections are still in progess,the gpfilespace --movetransfiles utility will fail.
3. Bring Greenplum Database online with no active session and run the following command:
gpfilespace --movetransfilespace filespace_name
Note: The location of the transaction files is stored in the segment configuration shared memory (PMModuleState) and used whenever transaction files are created, opened, or dropped.
Creating a Tablespace
After you create a filespace, use the CREATE TABLESPACE command to define a tablespace that uses that filespace. For example:
=# CREATE TABLESPACE fastspace FILESPACE fastdisk;
Database superusers define tablespaces and grant access to database users with the GRANTCREATE command. For example:
=# GRANT CREATE ON TABLESPACE fastspace TO admin;
Using a Tablespace to Store Database Objects
Users with the CREATE privilege on a tablespace can create database objects in that tablespace, such as tables, indexes, and databases. The command is:
CREATE TABLE tablename(options) TABLESPACE spacename For example, the following command creates a table in the tablespace space1:
CREATE TABLE foo(i int) TABLESPACE space1;
You can also use the default_tablespace parameter to specify the default tablespace for CREATE TABLE and CREATE INDEX commands that do not specify a tablespace:
SET default_tablespace = space1;
CREATE TABLE foo(i int);