GitHub

Databases

Andrey Aksenov

A database is a named collection of objects used to store and manage data. Examples of such objects are schemas, tables, indexes, or views.

As a rule, a database object belongs to one database and is visible only within this database. Some objects, like database roles, belong to a whole cluster and are accessible from any database within the cluster.

A newly initialized Greengage DB cluster includes a predefined template and default databases. These databases are not intended to be modified. To store your data, create a new database and define the necessary database objects within it.

Template and default databases

The following template and default databases are available in a newly initialized Greengage DB cluster:

  • postgres

    postgres is the default database that can be used for administrative connections. It is also used by startup processes, the Global Deadlock Detector process, and so on.

  • template0

    The template0 database is used internally by Greengage DBMS. You can use template0 to create a clean database containing only the standard objects predefined by DBMS at initialization.

  • template1

    The template1 database is used by default as a template for creating new databases.

NOTE

You cannot drop template and default databases.

Connect to a database

To connect to your Greengage DB cluster for the first time, use the default postgres database. First, connect to the master host and switch to the gpadmin user. Then, connect to the postgres database locally using psql:

$ psql postgres

You can connect to new databases created manually in the same way. Learn more about connecting to databases locally or remotely from the Connect to Greengage DB via psql topic.

NOTE

You can connect to only one database at a time: cross-queries between different databases are not supported.

Create a new database

Creating new databases is available to roles with one of the following attributes:

  • SUPERUSER

  • CREATEDB

To create a new database, use the CREATE DATABASE command:

CREATE DATABASE books_store;

By default, a new database is created by cloning the predefined template1 database with all objects and data contained in this database. You can also use the TEMPLATE option to specify the database to be cloned explicitly.

Once the database is created, you can connect to it using the \c meta-command:

\c books_store

The output should look as follows:

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

You can now create database objects: schemas, tables, and so on.

To let end-users connect to the created database, use the database field in the pg_hba.conf file.

NOTE

You can use the createdb command-line utility to create databases.

Alter a database

The ALTER DATABASE command allows you to change various database attributes, such as the database owner, the maximum number of simultaneous connections, or default configuration attributes. In the example below, the maximum number of allowed simultaneous connections is set to 10:

ALTER DATABASE books_store WITH CONNECTION LIMIT 10;

This command sets the default schema search path for the specified database using the search_path configuration parameter:

ALTER DATABASE books_store SET search_path TO catalog, sales, public;

You can also set a default session-level configuration parameter for a specific role. If a parameter is set for both a role and a database, the role-specific setting takes priority.

View information about databases

List of databases

To show the list of all databases, use the \l meta-command:

\l

In the output, you can see the owner of each database, the encoding, and so on:

                                List of databases
    Name     |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges
-------------+---------+----------+------------+------------+---------------------
 books_store | 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
(4 rows)

You can also query the pg_catalog.pg_database system table to get information about all databases in the cluster:

SELECT *
from pg_catalog.pg_database
ORDER BY datname;

The output below shows that the books_store database allows up to 10 connections (the datconnlimit column) and cannot be used as a template for creating new databases (datistemplate is f):

   datname   | datdba | encoding | datcollate |  datctype  | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |              datacl
-------------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+----------------------------------
 books_store |     10 |        6 | en_US.utf8 | en_US.utf8 | f             | t            |           10 |         12809 |          703 |          1 |          1663 |
 postgres    |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | t            |           -1 |         12809 |          703 |          1 |          1663 |
 template0   |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | f            |           -1 |         12809 |          703 |          1 |          1663 | {=c/gpadmin,gpadmin=CTc/gpadmin}
 template1   |     10 |        6 | en_US.utf8 | en_US.utf8 | t             | t            |           -1 |         12809 |          703 |          1 |          1663 | {=c/gpadmin,gpadmin=CTc/gpadmin}
(4 rows)

Default settings

To get the default settings for combinations of roles and databases, use the \drds meta-command:

\drds

The result shows search_path specified for the books_store database:

                    List of settings
 Role |  Database   |              Settings
------+-------------+------------------------------------
      | books_store | search_path=catalog, sales, public

You can also use the pg_catalog.pg_db_role_setting system table to view the default values of server configuration settings for each role and database combination:

SELECT *
FROM pg_catalog.pg_db_role_setting;

Monitoring and statistics

Greengage DB provides various means by which you may monitor the created databases. Below are a few examples:

  • The pg_database_size() function allows you to get the database size:

    SELECT pg_size_pretty(pg_database_size('books_store'));

    The result might look like this:

     pg_size_pretty
----------------
 65 MB

  • The pg_catalog.pg_stat_database system view shows database-wide statistics:

    SELECT *
FROM pg_catalog.pg_stat_database;

    The result might look like this:

     datid  |   datname   | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_read_time | blk_write_time |          stats_reset
--------+-------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+---------------+----------------+-------------------------------
      1 | template1   |           0 |          69 |            12 |       274 |    43721 |        76827 |       58772 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 | 2025-03-10 14:22:37.336925+00
  12809 | template0   |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 |
  12812 | postgres    |           0 |          19 |             6 |       112 |     7935 |        13661 |       13056 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 | 2025-03-10 14:22:36.43759+00
  16385 | books_store |           1 |          14 |             0 |       102 |     5003 |         7158 |        7157 |            4 |           0 |           0 |         0 |          0 |          0 |         0 |             0 |              0 | 2025-03-10 14:46:21.281755+00
(4 rows)

Drop a database

To drop a database, use the DROP DATABASE command. Note the following:

  • Dropping a database cannot be undone.

  • The DROP DATABASE command is not transactional.

To drop the currently used database, switch to another database using the \c meta-command:

\c postgres

Then, use DROP DATABASE to drop the database:

DROP DATABASE books_store;

Note that active client connections can prevent dropping a database:

ERROR:  database "books_store" is being accessed by other users
DETAIL:  There is 1 other session using the database.

In this case, you need to close them as described in Stop client queries and processes.

NOTE

You can also use the dropdb command-line utility to drop databases.

Previous
Recover a failed master
Next
Tablespaces
Open search page