1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>22.1. Overview</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="managing-databases.html" title="Chapter 22. Managing Databases" /><link rel="next" href="manage-ag-createdb.html" title="22.2. Creating a Database" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">22.1. Overview</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="managing-databases.html" title="Chapter 22. Managing Databases">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="managing-databases.html" title="Chapter 22. Managing Databases">Up</a></td><th width="60%" align="center">Chapter 22. Managing Databases</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="manage-ag-createdb.html" title="22.2. Creating a Database">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="MANAGE-AG-OVERVIEW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">22.1. Overview</h2></div></div></div><a id="id-1.6.9.4.2" class="indexterm"></a><p>
A small number of objects, like role, database, and tablespace
names, are defined at the cluster level and stored in the
<code class="literal">pg_global</code> tablespace. Inside the cluster are
multiple databases, which are isolated from each other but can access
cluster-level objects. Inside each database are multiple schemas,
which contain objects like tables and functions. So the full hierarchy
is: cluster, database, schema, table (or some other kind of object,
such as a function).
</p><p>
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 <a class="xref" href="auth-pg-hba-conf.html" title="20.1. The pg_hba.conf File">Section 20.1</a>), managed at the
connection level, and authorization control
(see <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a>), managed via the grant system.
Foreign data wrappers (see <a class="xref" href="postgres-fdw.html" title="F.33. postgres_fdw">postgres_fdw</a>)
allow for objects within one database to act as proxies for objects in
other database or clusters.
The older dblink module (see <a class="xref" href="dblink.html" title="F.10. dblink">dblink</a>) provides a similar capability.
By default, all users can connect to all databases using all connection methods.
</p><p>
If one <span class="productname">PostgreSQL</span> server cluster is planned to contain
unrelated projects 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 projects 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 <a class="xref" href="ddl-schemas.html" title="5.9. Schemas">Section 5.9</a>.
</p><p>
While multiple databases can be created within a single cluster, it is advised
to consider carefully whether the benefits outweigh the risks and limitations.
In particular, the impact that having a shared WAL (see <a class="xref" href="wal.html" title="Chapter 29. Reliability and the Write-Ahead Log">Chapter 29</a>)
has on backup and recovery options. While individual databases in the cluster
are isolated when considered from the user's perspective, they are closely bound
from the database administrator's point-of-view.
</p><p>
Databases are created with the <code class="command">CREATE DATABASE</code> command
(see <a class="xref" href="manage-ag-createdb.html" title="22.2. Creating a Database">Section 22.2</a>) and destroyed with the
<code class="command">DROP DATABASE</code> command
(see <a class="xref" href="manage-ag-dropdb.html" title="22.5. Destroying a Database">Section 22.5</a>).
To determine the set of existing databases, examine the
<code class="structname">pg_database</code> system catalog, for example
</p><pre class="synopsis">
SELECT datname FROM pg_database;
</pre><p>
The <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> program's <code class="literal">\l</code> meta-command
and <code class="option">-l</code> command-line option are also useful for listing the
existing databases.
</p><div class="note"><h3 class="title">Note</h3><p>
The <acronym class="acronym">SQL</acronym> standard calls databases <span class="quote">“<span class="quote">catalogs</span>”</span>, but there
is no difference in practice.
</p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="managing-databases.html" title="Chapter 22. Managing Databases">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="managing-databases.html" title="Chapter 22. Managing Databases">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="manage-ag-createdb.html" title="22.2. Creating a Database">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 22. Managing Databases </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 22.2. Creating a Database</td></tr></table></div></body></html>
|
