Databases

Overview

A database is the topmost hierarchical level for organizing SQL objects (database objects). The full hierarchy is cluster → database → schema → table and other objects (functions, views, operators).

Logical layer of ADPG cluster
Logical layer of ADPG cluster
Logical layer of ADPG cluster
Logical layer of ADPG cluster

An ADPG cluster can contain multiple databases, but it is possible to establish a connection to only one database. However, you can open multiple connections to different databases or a single database.

Databases are physically separated. By default, ADPG places the database files at the following path: /pg_data1/adpg14. ADPG creates the adpg14 directory at the path specified in the Data directory parameter (see Configuration parameters). The default value of this parameter is /pg_data1.

After installation, an ADPG cluster contains three databases described in the table below.

Template and default databases
Name Description

postgres

This is a default database for initial connections. It is a copy of template1 and can be dropped and recreated if necessary

template0

This is a template database. It does not accept connections and should not be changed. You can specify this template in the CREATE DATABASE command to create an initial database without changes made in template1

template1

This is a template database. It contains the same data as template0, but you can change it. By default, template1 is used for each new database that you create with the CREATE DATABASE command. You can add objects that should be in all new databases to this template. For example, add tables or install procedural languages

View existing databases

You can use the \l psql meta-command or -l command-line option to view existing databases:

$ psql

\l
$ psql -l

The result:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

Alternatively, select data from the pg_database catalog:

SELECT oid, datname, datistemplate, datallowconn FROM pg_database;

The result:

  oid  |  datname  | datistemplate | datallowconn
-------+-----------+---------------+--------------
 14486 | postgres  | f             | t
     1 | template1 | t             | t
 14485 | template0 | t             | f

In the datallowconn column, you can see that connections to template0 are not allowed.

The following command shows the database you connected to:

SELECT current_database();

To determine the database size, use the pg_size_pretty and pg_database_size functions as follows:

SELECT pg_size_pretty(pg_database_size('postgres'));

Create a database

To create your first database, connect to the postgres database. The psql utility connects to the postgres database by default. You should also have the correct privileges for this operation or be a superuser.

Execute the CREATE DATABASE command to create a database. The following command creates the book_store database:

CREATE DATABASE book_store;

The current role automatically becomes the owner of the new database. It is also possible to specify another database owner. The command below makes role1 the owner of the newly created book_store database:

CREATE DATABASE book_store OWNER role1;

You can also specify the template for a new database. The following code creates the book_store database by copying template0:

CREATE DATABASE book_store TEMPLATE template0;

Delete a database

Only the database owner or superuser can drop a database. This operation deletes all objects contained in the database. Deleting a database is an irreversible operation.

Use the DROP DATABASE command to delete a database:

DROP DATABASE book_store;

It is not possible to execute the DROP DATABASE command while there is at least one connection to the specified database. However, you can connect to any other, including template1. The template1 database may be the only option if you need to drop the last user database of the cluster.

Found a mistake? Seleсt text and press Ctrl+Enter to report it