• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Database Quick Start
  5. Creating and Managing Databases

Creating and Managing Databases

Prerequisites

Only database system administrators or users granted with database creation permissions can create a database. For details about how to grant database creation permissions to a user, see User and Permission Overview.

Context

  • DWS has two initial template databases template0 and template1 and a default user database postgres.
  • CREATE DATABASE creates a database by copying a template database (template1 by default). Do not use a client or any other tools to connect to or to perform operations on the template databases.
  • A maximum of 128 databases can be created in DWS.
  • A database system consists of multiple databases. A client can connect to only one database at a time. Users cannot query data across databases. If a database cluster contains multiple databases, set the -d parameter to specify the database instance to connect to.

Procedure

  • Create a database db_tpcds:
    CREATE DATABASE db_tpcds;

    If the following information is displayed, the database has been created.

    CREATE DATABASE

    As said in Context, the template database template1 is copied by default to create a database. Its encoding format is SQL_ASCII. If the name of an object created in this database contains multiple-byte characters (such as Chinese characters) and exceeds the name length limit (63 bytes), the system truncates the name from the last byte instead of the last character. As a result, characters may be incomplete.

    To resolve this problem, you need to:

    • Ensure that the name of the data object does not exceed the maximum length.
    • Exclude multi-byte characters from object names.

    If you fail to delete an object by specifying its name after truncation, specify its original name to delete it, or manually delete it from the system catalogs on each node.

    You can also let CREATE DATABASE use template0 to create a database and specify new encoding and locale, for example, use UTF-8 as the default database encoding (server_encoding). For details, see the syntax in CREATE DATABASE.

    You can run the show server_encoding command to view the current database encoding.

NOTE:
  • Database names must comply with the general naming convention of SQL identifiers. The current user automatically becomes the owner of this new database.
  • If a database system is used to support independent users and projects, you are advised to store them in different databases.
  • If the projects or users are associated with each other and share resources, store them in different schemas in the same database. A schema is a logical structure. The access permission of a schema is controlled by permissions listed in Table 1 Permission type.
  • View databases.
    • Run the \l meta-command to view the database list of the database system.
      \l
    • Run the following command to query the database list in the system catalog pg_database:
      SELECT datname FROM pg_database;
  • Modify databases.

    You can execute the ALTER DATABASE statement to modify database attributes, such as the owner, name, and default configuration attributes.

    • Run the following command to set the default search path for the database:
      ALTER DATABASE db_tpcds SET search_path TO pa_catalog,public;
      ALTER DATABASE
    • Run the following command to modify the database tablespaces:
      ALTER DATABASE db_tpcds SET TABLESPACE tpcds;
      ALTER DATABASE
    • Run the following command to rename the database:
      ALTER DATABASE db_tpcds RENAME TO human_tpcds;
      ALTER DATABASE
  • Delete databases:

    You can execute the DROP DATABASE statement to delete a database. This command deletes the system directory in the database, as well as the database directory on the disk that stores data. Only the database owner or the system administrator can delete a database. A database accessed by users cannot be deleted. You need to connect to another database before deleting this database.

    Run the following command to delete a database:
    DROP DATABASE human_tpcds;
    DROP DATABASE