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"). This
chapter describes the properties of databases, and how to create,
manage, and destroy them.
A database is a named collection of SQL objects ("database
objects"). Generally, every database object (tables,
functions, etc.) belongs to one and only one database. (But there
are a few system catalogs, for example pg_database,
that belong to a whole installation and are accessible from each
database within the installation.) More accurately, a database is
a collection of schemas and the schemas contain the tables,
functions, etc. So the full hierarchy is:
server, database, schema, table (or something else instead of a
table).
An application that connects to the database server specifies in
its connection request the name of the database it wants to connect
to. It is not possible to access more than one database per
connection. (But an application is not restricted in the number of
connections it opens to the same or other databases.) It is
possible, however, to access more than one schema from the same
connection. Schemas are a purely logical structure and who can
access what is managed by the privilege system. Databases are
physically separated and access control is managed at the
connection level. If one PostgreSQL server instance is to house
projects or users that should be separate and for the most part
unaware of each other, it is therefore recommendable to put them
into separate databases. If the projects or users are interrelated
and should be able to use each other's resources they should be put
in the same databases but possibly into separate schemas. More
information about managing schemas is in the PostgreSQL 7.3 User's Guide.
Note: SQL calls databases "catalogs", but there is no
difference in practice.
