Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc889c9c

Browse files
committed
Enhance the description of user and database management. Reduce the
number of forward references in the admin guide.
1 parent8a6fab4 commitc889c9c

File tree

7 files changed

+221
-326
lines changed

7 files changed

+221
-326
lines changed

‎doc/src/sgml/admin.sgml‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.37 2002/06/13 05:15:22 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.38 2002/09/25 21:16:10 petere Exp $
33
-->
44

55
<book id="admin">
@@ -25,10 +25,10 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.37 2002/06/13 05:15:22
2525
&installation;
2626
&installw;
2727
&runtime;
28+
&user-manag;
29+
&manage-ag;
2830
&client-auth;
2931
&charset;
30-
&manage-ag;
31-
&user-manag;
3232
&maintenance;
3333
&backup;
3434
&monitoring;

‎doc/src/sgml/filelist.sgml‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/filelist.sgml,v 1.22 2002/08/22 03:17:37 momjian Exp $ -->
1+
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/filelist.sgml,v 1.23 2002/09/25 21:16:10 petere Exp $ -->
22

33
<!entity history SYSTEM "history.sgml">
44
<!entity info SYSTEM "info.sgml">
@@ -29,7 +29,6 @@
2929
<!entity func SYSTEM "func.sgml">
3030
<!entity indices SYSTEM "indices.sgml">
3131
<!entity keywords SYSTEM "keywords.sgml">
32-
<!entity manage SYSTEM "manage.sgml">
3332
<!entity mvcc SYSTEM "mvcc.sgml">
3433
<!entity perform SYSTEM "perform.sgml">
3534
<!entity queries SYSTEM "queries.sgml">

‎doc/src/sgml/manage-ag.sgml‎

Lines changed: 141 additions & 57 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.20 2001/11/28 20:49:10 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.21 2002/09/25 21:16:10 petere Exp $
33
-->
44

55
<chapter id="managing-databases">
@@ -8,36 +8,65 @@ $Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.20 2001/11/28 20:49:10 p
88
<indexterm zone="managing-databases"><primary>database</></>
99

1010
<para>
11-
A database is a named collection of SQL objects (<quote>database
12-
objects</quote>). Generally, every database object (tables, functions,
13-
etc.) belongs to one and only one database. (But there are a few system
14-
catalogs, for example <literal>pg_database</>, that belong to a whole
15-
installation and are accessible from each database within the
16-
installation.)
17-
An application that connects
18-
to the database server specifies in its connection request the
19-
name of the database it wants to connect to. It is not possible to
20-
access more than one database per connection. (But an application
21-
is not restricted in the number of connections it opens to the same
22-
or other databases.)
11+
Every instance of a running PostgreSQL server manages one or more
12+
databases. Databases are therefore the topmost hierarchical level
13+
for organizing SQL objects (<quote>database objects</quote>). This
14+
chapter describes the properties of databases, and how to create,
15+
manage, and destroy them.
2316
</para>
2417

25-
<note>
18+
<sect1>
19+
<title>Overview</title>
20+
2621
<para>
27-
<acronym>SQL</> calls databases <quote>catalogs</>, but there is no
28-
difference in practice.
22+
A database is a named collection of SQL objects (<quote>database
23+
objects</quote>). Generally, every database object (tables,
24+
functions, etc.) belongs to one and only one database. (But there
25+
are a few system catalogs, for example <literal>pg_database</>,
26+
that belong to a whole installation and are accessible from each
27+
database within the installation.) More accurately, a database is
28+
a collection of schemas and the schemas contain the tables,
29+
functions, etc. So the full hierarchy is:
30+
server-database-schema-table (or something else instead of a
31+
table).
2932
</para>
30-
</note>
3133

32-
<para>
33-
In order to create or drop databases, the <productname>PostgreSQL</>
34-
<application>postmaster</> must be up and running (see <xref
35-
linkend="postmaster-start">).
36-
</para>
34+
<para>
35+
An application that connects to the database server specifies in
36+
its connection request the name of the database it wants to connect
37+
to. It is not possible to access more than one database per
38+
connection. (But an application is not restricted in the number of
39+
connections it opens to the same or other databases.) It is
40+
possible, however, to access more than one schema from the same
41+
connection. Schemas are a purely logical structure and who can
42+
access what is managed by the privilege system. Databases are
43+
physically separated and access control is managed at the
44+
connection level. If one PostgreSQL server instance is to house
45+
projects or users that should be separate and for the most part
46+
unaware of each other, it is therefore recommendable to put them
47+
into separate databases. If the projects or users are interrelated
48+
and should be able to use each other's resources they should be put
49+
in the same databases but possibly into separate schemas. More
50+
information about managing schemas is in &cite-user;.
51+
</para>
52+
53+
<note>
54+
<para>
55+
<acronym>SQL</> calls databases <quote>catalogs</>, but there is no
56+
difference in practice.
57+
</para>
58+
</note>
59+
</sect1>
3760

3861
<sect1 id="manage-ag-createdb">
3962
<title>Creating a Database</title>
4063

64+
<para>
65+
In order to create a databases, the <productname>PostgreSQL</>
66+
server must be up and running (see <xref
67+
linkend="postmaster-start">).
68+
</para>
69+
4170
<para>
4271
Databases are created with the query language command
4372
<command>CREATE DATABASE</command>:
@@ -56,35 +85,32 @@ CREATE DATABASE <replaceable>name</>
5685
linkend="user-attributes"> for how to grant permission.
5786
</para>
5887

59-
<formalpara>
60-
<title>Bootstrapping:</title>
61-
<para>
62-
Since you need to be connected to the database server in order to
63-
execute the <command>CREATE DATABASE</command> command, the
64-
question remains how the <emphasis>first</> database at any given
65-
site can be created. The first database is always created by the
66-
<command>initdb</> command when the data storage area is
67-
initialized. (See <xref linkend="creating-cluster">.) By convention
68-
this database is called <literal>template1</>. So
69-
to create the first <quote>real</> database you can connect to
70-
<literal>template1</>.
71-
</para>
72-
</formalpara>
88+
<para>
89+
Since you need to be connected to the database server in order to
90+
execute the <command>CREATE DATABASE</command> command, the
91+
question remains how the <emphasis>first</> database at any given
92+
site can be created. The first database is always created by the
93+
<command>initdb</> command when the data storage area is
94+
initialized. (See <xref linkend="creating-cluster">.) By convention
95+
this database is called <literal>template1</>. So to create the
96+
first <quote>real</> database you can connect to
97+
<literal>template1</>.
98+
</para>
7399

74100
<para>
75101
The name <quote>template1</quote> is no accident: When a new
76102
database is created, the template database is essentially cloned.
77103
This means that any changes you make in <literal>template1</> are
78104
propagated to all subsequently created databases. This implies that
79105
you should not use the template database for real work, but when
80-
used judiciously this feature can be convenient. More details appear
81-
below.
106+
used judiciously this feature can be convenient. More details
107+
appear in <xref linkend="manage-ag-templatedbs">.
82108
</para>
83109

84110
<para>
85111
As an extra convenience, there is also a program that you can
86112
execute from the shell to create new databases,
87-
<filename>createdb</>.
113+
<command>createdb</>.
88114

89115
<synopsis>
90116
createdb <replaceable class="parameter">dbname</replaceable>
@@ -99,8 +125,32 @@ createdb <replaceable class="parameter">dbname</replaceable>
99125
you want.
100126
</para>
101127

102-
<sect2 id="manage-ag-templatedbs">
103-
<title>Template Databases</title>
128+
<note>
129+
<para>
130+
<xref linkend="client-authentication"> contains information about
131+
how to restrict who can connect to a given database.
132+
</para>
133+
</note>
134+
135+
<para>
136+
Sometimes you want to create a database for someone else. That
137+
user should become the owner of the new database, so he can
138+
configure and manage it himself. To achieve that, use one of the
139+
following commands:
140+
<programlisting>
141+
CREATE DATABASE <replaceable>dbname</> OWNER <replaceable>username</>;
142+
</programlisting>
143+
from the SQL environment, or
144+
<programlisting>
145+
createdb -O <replaceable>username</> <replaceable>dbname</>
146+
</programlisting>
147+
You must be a superuser to be allowed to create a database for
148+
someone else.
149+
</para>
150+
</sect1>
151+
152+
<sect1 id="manage-ag-templatedbs">
153+
<title>Template Databases</title>
104154

105155
<para>
106156
<command>CREATE DATABASE</> actually works by copying an existing
@@ -111,7 +161,7 @@ createdb <replaceable class="parameter">dbname</replaceable>
111161
will be copied into subsequently created user databases. This
112162
behavior allows site-local modifications to the standard set of
113163
objects in databases. For example, if you install the procedural
114-
language <literal>plpgsql</> in <literal>template1</>, it will
164+
language <application>PL/pgSQL</> in <literal>template1</>, it will
115165
automatically be available in user databases without any extra action
116166
being taken when those databases are made.
117167
</para>
@@ -132,12 +182,24 @@ createdb <replaceable class="parameter">dbname</replaceable>
132182
additions that may now be present in <literal>template1</>.
133183
</para>
134184

185+
<para>
186+
To create a database by copying <literal>template0</literal>, use
187+
<programlisting>
188+
CREATE DATABASE <replaceable>dbname</> TEMPLATE template0;
189+
</programlisting>
190+
from the SQL environment, or
191+
<programlisting>
192+
createdb -T template0 <replaceable>dbname</>
193+
</programlisting>
194+
from the shell.
195+
</para>
196+
135197
<para>
136198
It is possible to create additional template databases, and indeed
137199
one might copy any database in an installation by specifying its name
138200
as the template for <command>CREATE DATABASE</>. It is important to
139201
understand, however, that this is not (yet) intended as
140-
a general-purpose <quote>COPY DATABASE</quote> facility. In particular, it is
202+
a general-purpose <quote><command>COPY DATABASE</command></quote> facility. In particular, it is
141203
essential that the source database be idle (no data-altering transactions
142204
in progress)
143205
for the duration of the copying operation. <command>CREATE DATABASE</>
@@ -151,7 +213,7 @@ createdb <replaceable class="parameter">dbname</replaceable>
151213

152214
<para>
153215
Two useful flags exist in <literal>pg_database</literal> for each
154-
database: <literal>datistemplate</literal> and
216+
database:the columns<literal>datistemplate</literal> and
155217
<literal>datallowconn</literal>. <literal>datistemplate</literal>
156218
may be set to indicate that a database is intended as a template for
157219
<command>CREATE DATABASE</>. If this flag is set, the database may be
@@ -187,18 +249,47 @@ createdb <replaceable class="parameter">dbname</replaceable>
187249
<literal>template1</> and <literal>template0</> do not have any special
188250
status beyond the fact that the name <literal>template1</> is the default
189251
source database name for <command>CREATE DATABASE</> and the default
190-
database-to-connect-to for variousscripts such as <literal>createdb</>.
252+
database-to-connect-to for variousprograms such as <command>createdb</>.
191253
For example, one could drop <literal>template1</> and recreate it from
192254
<literal>template0</> without any ill effects. This course of action
193255
might be advisable if one has carelessly added a bunch of junk in
194256
<literal>template1</>.
195257
</para>
196258
</note>
259+
</sect1>
260+
261+
<sect1 id="manage-ag-config">
262+
<title>Database Configuration</title>
263+
264+
<para>
265+
Recall from <xref linkend="runtime-config"> that the PostgreSQL
266+
server provides a large number of run-time configuration variables.
267+
You can set database-specific default values for many of these
268+
settings.
269+
</para>
197270

198-
</sect2>
271+
<para>
272+
For example, if for some reason you want to disable the
273+
<acronym>GEQO</acronym> optimizer for a given database, you'd
274+
ordinarily have to either disable it for all databases or make sure
275+
that every connecting client is careful to issue <literal>SET geqo
276+
TO off;</literal>. To make this setting the default you can
277+
execute the command
278+
<programlisting>
279+
ALTER DATABASE mydb SET geqo TO off;
280+
</programlisting>
281+
This will save the setting (but not set it immediately) and in
282+
subsequent connections it will appear as though <literal>SET geqo
283+
TO off;</literal> had been called right before the session started.
284+
Note that users can still alter this setting during the session; it
285+
will only be the default. To undo any such setting, use
286+
<literal>ALTER DATABASE <replaceable>dbname</> RESET
287+
<replaceable>varname</>;</literal>.
288+
</para>
289+
</sect1>
199290

200-
<sect2 id="manage-ag-alternate-locs">
201-
<title>Alternative Locations</title>
291+
<sect1 id="manage-ag-alternate-locs">
292+
<title>Alternative Locations</title>
202293

203294
<para>
204295
It is possible to create a database in a location other than the
@@ -227,18 +318,14 @@ createdb <replaceable class="parameter">dbname</replaceable>
227318
initialize the data area, and finally restart the server. (See
228319
<xref linkend="postmaster-shutdown"> and <xref
229320
linkend="postmaster-start">.) To set an environment variable, type
230-
<informalexample>
231321
<programlisting>
232322
PGDATA2=/home/postgres/data
233323
export PGDATA2
234324
</programlisting>
235-
</informalexample>
236325
in Bourne shells, or
237-
<informalexample>
238326
<programlisting>
239327
setenv PGDATA2 /home/postgres/data
240328
</programlisting>
241-
</informalexample>
242329
in <application>csh</> or <application>tcsh</>. You have to make sure that this environment
243330
variable is always defined in the server environment, otherwise
244331
you won't be able to access that database. Therefore you probably
@@ -253,12 +340,11 @@ setenv PGDATA2 /home/postgres/data
253340
already exists and is writable
254341
by the user account that runs the server (see <xref
255342
linkend="postgres-user">). Then from the command line, type
256-
<informalexample>
257343
<programlisting>
258344
initlocation PGDATA2
259345
</programlisting>
260-
</informalexample>
261-
Then you can restart the server.
346+
(<emphasis>not</emphasis> <literal>initlocation
347+
$PGDATA2</literal>).Then you can restart the server.
262348
</para>
263349

264350
<para>
@@ -290,8 +376,6 @@ gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all
290376
</programlisting>
291377
</para>
292378
</note>
293-
294-
</sect2>
295379
</sect1>
296380

297381
<sect1 id="manage-ag-dropdb">

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp