Redrock Postgres Documentation
Home Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage
  1. Redrock Postgres Documentation
  2. /
  3. Features
  4. /
  5. Databases
Edit page

Databases

Every instance of a running PostgreSQL server manages one or more databases. Databases are therefore the topmost hierarchical level for organizing SQL objects (“database objects”).

Database Structures

Databases are defined at the instance level, stored in the pg_database catalog and in the pg_global tablespace. Inside the instance are multiple databases, which are isolated from each other. Inside each database are multiple schemas, which contain objects like tables and functions. So the full hierarchy is: instance, database, schema, table (or some other kind of object, such as a function).

Here’s an instance of Redrock Postgres, it has 3 system databases (template0, template1, and postgres) and 3 user databases.

Figure 1 A Redrock Postgres Instance
Figure 1 A Redrock Postgres Instance

In this instance, there is a database called erp, the database has some system roles (postgres and some built-in roles that begin with pg_) and 3 user roles, 2 system tablespaces(pg_global and pg_default) and 3 user tablespaces, 3 system schemas(information_schema, pg_catalog, and pg_recyclebin) and 3 user schemas.

Figure 2 erp Database
Figure 2 erp Database

In the database erp, there is a schema called hr, the schema has tables, functions, and views.

Figure 3 hr Schema
Figure 3 hr Schema

Accessing Databases

When connecting to the database server, a client must specify the database name in its connection request. It is not possible to access more than one database per connection. However, clients can open multiple connections to the same database, or different databases. Database-level security has two components: access control (see Section 20.1), managed at the connection level, and authorization control (see Section 5.7), managed via the grant system. Foreign data wrappers (see postgres_fdw) allow for objects within one database to act as proxies for objects in other database or instances. The dblink module provides a similar capability. In Redrock Postgres, users are database-level objects, every user can only connect to database the user belongs to.

Managing Databases

If one PostgreSQL server instance is planned to serve unrelated applications or users that should be, for the most part, unaware of each other, it is recommended to put them into separate databases and adjust authorizations and access controls accordingly. If the applications or users are interrelated, and thus should be able to use each other’s resources, they should be put in the same database but probably into separate schemas; this provides a modular structure with namespace isolation and authorization control. More information about managing schemas is in Section 5.9.

While multiple databases can be created within a single instance, it is advised to consider carefully whether the benefits outweigh the risks and limitations. In particular, the impact that having a shared WAL (see Chapter 29) has on backup and recovery options. While individual databases in the instance are isolated when considered from the user’s perspective, they are closely bound from the database administrator’s point-of-view.

Databases are created with the CREATE DATABASE command and destroyed with the DROP DATABASE command. To determine the set of existing databases, examine the pg_database system catalog, for example

SELECT datname FROM pg_database;

The psql program’s \l meta-command and -l command-line option are also useful for listing the existing databases.

The SQL standard calls databases “catalogs”, but there is no difference in practice.