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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter19.Managing Databases</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="admin.html" title="PartIII.Server Administration">
<link rel="prev" href="perm-functions.html" title="18.5.Functions and Triggers">
<link rel="next" href="manage-ag-createdb.html" title="19.2.Creating a Database">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter" lang="en" id="managing-databases">
<div class="titlepage"><div><div><h2 class="title">
<a name="managing-databases"></a>Chapter19.Managing Databases</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="managing-databases.html#manage-ag-overview">19.1. Overview</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-createdb.html">19.2. Creating a Database</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-templatedbs.html">19.3. Template Databases</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-config.html">19.4. Database Configuration</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-dropdb.html">19.5. Destroying a Database</a></span></dt>
<dt><span class="sect1"><a href="manage-ag-tablespaces.html">19.6. Tablespaces</a></span></dt>
</dl>
</div>
<a name="id658709"></a><p> Every instance of a running <span class="productname">PostgreSQL</span>
server manages one or more databases. Databases are therefore the
topmost hierarchical level for organizing <acronym class="acronym">SQL</acronym>
objects (“<span class="quote">database objects</span>”). This chapter describes
the properties of databases, and how to create, manage, and destroy
them.
</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="manage-ag-overview"></a>19.1.Overview</h2></div></div></div>
<a name="id658751"></a><p> A database is a named collection of <acronym class="acronym">SQL</acronym> objects
(“<span class="quote">database objects</span>”). Generally, every database
object (tables, functions, etc.) belongs to one and only one
database. (But there are a few system catalogs, for example
<code class="literal">pg_database</code>, that belong to a whole cluster and
are accessible from each database within the cluster.) 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 some other kind of object,
such as a function).
</p>
<p> When connecting to the database server, a client must specify 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.) Databases are
physically separated and access control is managed at the
connection level. If one <span class="productname">PostgreSQL</span> 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 database, but possibly
into separate schemas. Schemas are a purely logical structure and who can
access what is managed by the privilege system. More information about
managing schemas is in <a href="ddl-schemas.html" title="5.7.Schemas">Section5.7, “Schemas”</a>.
</p>
<p> Databases are created with the <code class="command">CREATE DATABASE</code> command
(see <a href="manage-ag-createdb.html" title="19.2.Creating a Database">Section19.2, “Creating a Database”</a>) and destroyed with the
<code class="command">DROP DATABASE</code> command
(see <a href="manage-ag-dropdb.html" title="19.5.Destroying a Database">Section19.5, “Destroying a Database”</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 href="app-psql.html" title="psql"><span class="refentrytitle"><a name="app-psql-title"></a><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" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> The <acronym class="acronym">SQL</acronym> standard calls databases “<span class="quote">catalogs</span>”, but there
is no difference in practice.
</p>
</div>
</div>
</div></body>
</html>
|
