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).
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.
| Name | Description |
|---|---|
postgres |
This is a default database for initial connections. It is a copy of |
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 |
This is a template database. It contains the same data as |
View existing databases
You can use the \l psql meta-command or -l command-line option to view existing databases:
$ psql
\l$ psql -lThe 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.