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”).
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 (
postgres) and 3 user databases.
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_default) and 3 user tablespaces, 3 system schemas(
pg_recyclebin) and 3 user schemas.
In the database
erp, there is a schema called
hr, the schema has tables, functions, and views.
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.
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.
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.