Databases
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.
| 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 |
template1 |
This database is used as a template for each new database you create (via the |
|
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:
-
Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name
gpadmin:$ sudo su - gpadmin -
List all ADB databases:
$ psql -lThe 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:
-
Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name
gpadmin:$ sudo su - gpadmin -
Connect to the database via psql (or any other client program):
$ psql adbThe output is:
psql (9.4.24) Type "help" for help.
-
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_storeThe 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. |