Databases

Daria Barysheva

Overview

Database is a collection of data physically stored together. Every ADB cluster can contain multiple databases. However, client programs can connect to only one database at a time — cross-queries between different databases are impossible since the databases are physically separated.

There are several template and default databases in every ADB cluster. These databases are added after the DBMS is initialized. See their descriptions in the table below.

Template and default databases
Name Description

adb

This database can be used as default for client connections. All necessary database objects can be added to this database. It is created via ADCM — by applying the Create database action to the ADB cluster. However, you can also add other custom databases. Notice, that you can change the name of the default database before applying the Create database action — via configuration parameters of the ADB service (see the Name of default database parameter in the ADB → Main section)

diskquota

This database is used by the ADB extension Diskquota. The extension allows users to limit the disk space used by schemas and roles

postgres

This database is usually used as default for administrative connections. For example, it can be used by startup processes, the Global Deadlock Detector process, etc.

template0

This database template is used by the DBMS internally. You can use template0 to create a completely clean database that contains only the standard objects predefined by DBMS at initialization. Do not drop or modify the template0 database

template1

This database is used as a template for each new database you create (via the CREATE DATABASE command) — unless you specify another template. Creating objects in template1 is not recommended
CAUTION
It is not recommended to drop or modify the databases listed above.

View the list of databases

To view the list of available databases in the current ADB cluster, you can use several methods. Two of them are listed below.

Way 1 — psql

If you work in the psql client program, you can use the \l meta-command to show the list of databases:

  1. Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name gpadmin:

    $ sudo su - gpadmin

  2. List all ADB databases:

    $ psql -l

    The result can look like this:

                                   List of databases
   Name    |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges
-----------+---------+----------+------------+------------+---------------------
 adb       | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/gpadmin        +
           |         |          |            |            | gpadmin=CTc/gpadmin
 diskquota | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
(5 rows)

Way 2 — SQL query

After connecting to the specified database via psql (or any other client program), you can also run the special SQL query that gets the list of databases from the pg_catalog.pg_database system catalog table. To do this, run the following commands:

  1. Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name gpadmin:

    $ sudo su - gpadmin

  2. Connect to the database via psql (or any other client program):

    $ psql adb

    The output is:

    psql (9.4.24)
Type "help" for help.

  3. Run the following SQL query to list all ADB databases:

    SELECT * from pg_catalog.pg_database ORDER BY datname;

    The result can look like this:

      datname  | datdba | encoding | datcollate |  datctype  | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |              datacl
-----------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-----------------------------------
 adb       |     10 |        6 | en_US.utf8 | en_US.utf8 | f             | t            |           -1 |         12809 |          702 |          1 |          1663 | {=Tc/gpadmin,gpadmin=CTc/gpadmin}
 diskquota |     10 |        6 | en_US.utf8 | en_US.utf8 | f             | t            |           -1 |         12809 |          948 |          1 |          1663 |
 postgres  |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | t            |           -1 |         12809 |         1881 |          1 |          1663 |
 template0 |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | f            |           -1 |         12809 |          702 |          1 |          1663 | {=c/gpadmin,gpadmin=CTc/gpadmin}
 template1 |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | t            |           -1 |         12809 |         1799 |          1 |          1663 | {=c/gpadmin,gpadmin=CTc/gpadmin}
(5 rows)

Create a new database

To create a new database, it is necessary to use the CREATE DATABASE command and then write the database name.

The following example creates the new database books_store:

CREATE DATABASE books_store;

The output should be similar to:

CREATE DATABASE

To disconnect from the current database and switch to the new one, you can use the psql command \c:

\c books_store

The result is:

You are now connected to database "books_store" as user "gpadmin".
books_store=#
NOTE

To create a database, you should have the correct privileges for this operation or be a superuser. If you do not have such privileges, please, contact your database administrator to either give you the necessary rights or to create a database for you.
Found a mistake? Seleсt text and press Ctrl+Enter to report it