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

Commit0729c4a

Browse files
committed
Clean up treatment of creating/dropping databases in User's Guide and
Admin Guide. Move discussion of template databases out of footnotesin CREATE DATABASE ref page and into a section of the Admin Guide.Clean up various obsolete claims, do some copy-editing.
1 parentc9a85cb commit0729c4a

File tree

4 files changed

+188
-335
lines changed

4 files changed

+188
-335
lines changed

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

Lines changed: 135 additions & 126 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.17 2001/11/12 19:19:39 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.18 2001/11/18 00:38:00 tgl Exp $
33
-->
44

55
<chapter id="managing-databases">
@@ -9,9 +9,13 @@ $Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.17 2001/11/12 19:19:39 p
99

1010
<para>
1111
A database is a named collection of SQL objects (<quote>database
12-
objects</quote>); every database object (tables, function, etc.)
13-
belongs to one and only one database. An application that connects
14-
to the database server specifies with its connection request the
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
1519
name of the database it wants to connect to. It is not possible to
1620
access more than one database per connection. (But an application
1721
is not restricted in the number of connections it opens to the same
@@ -40,7 +44,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.17 2001/11/12 19:19:39 p
4044
<synopsis>
4145
CREATE DATABASE <replaceable>name</>
4246
</synopsis>
43-
where <replaceable>name</> can be chosen freely. (Depending on the
47+
where <replaceable>name</> follows the usual rules for SQL identifiers.
48+
(Depending on the
4449
current implementation, certain characters that are special to the
4550
underlying operating system might be prohibited. There will be
4651
run-time checks for that.) The current user automatically becomes
@@ -51,7 +56,7 @@ CREATE DATABASE <replaceable>name</>
5156

5257
<para>
5358
The creation of databases is a restricted operation. See <xref
54-
linkend="user-attributes"> how to grant permission.
59+
linkend="user-attributes">forhow to grant permission.
5560
</para>
5661

5762
<formalpara>
@@ -62,8 +67,8 @@ CREATE DATABASE <replaceable>name</>
6267
question remains how the <emphasis>first</> database at any given
6368
site can be created. The first database is always created by the
6469
<command>initdb</> command when the data storage area is
65-
initialized. (See <xref linkend="creating-cluster">.)This
66-
database is called <literal>template1</> and cannot be deleted. So
70+
initialized. (See <xref linkend="creating-cluster">.)By convention
71+
thisdatabase is called <literal>template1</>. So
6772
to create the first <quote>real</> database you can connect to
6873
<literal>template1</>.
6974
</para>
@@ -75,7 +80,8 @@ CREATE DATABASE <replaceable>name</>
7580
This means that any changes you make in <literal>template1</> are
7681
propagated to all subsequently created databases. This implies that
7782
you should not use the template database for real work, but when
78-
used judiciously this feature can be convenient.
83+
used judiciously this feature can be convenient. More details appear
84+
below.
7985
</para>
8086

8187
<para>
@@ -88,29 +94,130 @@ createdb <replaceable class="parameter">dbname</replaceable>
8894
</synopsis>
8995

9096
<command>createdb</> does no magic. It connects to the template1
91-
database andexecutes the <command>CREATE DATABASE</> command,
92-
exactly as described above. It uses <application>psql</> program
97+
database andissues the <command>CREATE DATABASE</> command,
98+
exactly as described above. It usesthe<application>psql</> program
9399
internally. The reference page on <command>createdb</> contains the invocation
94-
details.In particular, <command>createdb</> without any arguments will create
100+
details.Note that <command>createdb</> without any arguments will create
95101
a database with the current user name, which may or may not be what
96102
you want.
97103
</para>
98104

99-
<sect2>
105+
<sect2 id="manage-ag-templatedbs">
106+
<title>Template Databases</title>
107+
108+
<para>
109+
<command>CREATE DATABASE</> actually works by copying an existing
110+
database. By default, it copies the standard system database named
111+
<literal>template1</>. Thus that database is the <quote>template</>
112+
from which new databases are made. If you add objects to
113+
<literal>template1</>, these objects
114+
will be copied into subsequently created user databases. This
115+
behavior allows site-local modifications to the standard set of
116+
objects in databases. For example, if you install the procedural
117+
language <literal>plpgsql</> in <literal>template1</>, it will
118+
automatically be available in user databases without any extra action
119+
being taken when those databases are made.
120+
</para>
121+
122+
<para>
123+
There is a second standard system database named <literal>template0</>.
124+
This database contains the same data as the initial contents of
125+
<literal>template1</>, that is, only the standard objects predefined by
126+
your version of Postgres. <literal>template0</> should never be changed
127+
after <literal>initdb</>. By instructing <command>CREATE DATABASE</> to
128+
copy <literal>template0</> instead of <literal>template1</>, you can
129+
create a <quote>virgin</> user database that contains none of the
130+
site-local additions in <literal>template1</>. This is particularly
131+
handy when restoring a <literal>pg_dump</> dump: the dump script should
132+
be restored in a virgin database to ensure that one recreates the
133+
correct contents of the dumped database, without any conflicts with
134+
additions that may now be present in <literal>template1</>.
135+
</para>
136+
137+
<para>
138+
It is possible to create additional template databases, and indeed
139+
one might copy any database in an installation by specifying its name
140+
as the template for <command>CREATE DATABASE</>. It is important to
141+
understand, however, that this is not (yet) intended as
142+
a general-purpose COPY DATABASE facility. In particular, it is
143+
essential that the source database be idle (no data-altering transactions
144+
in progress)
145+
for the duration of the copying operation. <command>CREATE DATABASE</>
146+
will check
147+
that no backend processes (other than itself) are connected to
148+
the source database at the start of the operation, but this does not
149+
guarantee that changes cannot be made while the copy proceeds, which
150+
would result in an inconsistent copied database. Therefore,
151+
we recommend that databases used as templates be treated as read-only.
152+
</para>
153+
154+
<para>
155+
Two useful flags exist in <literal>pg_database</literal> for each
156+
database: <literal>datistemplate</literal> and
157+
<literal>datallowconn</literal>. <literal>datistemplate</literal>
158+
may be set to indicate that a database is intended as a template for
159+
<command>CREATE DATABASE</>. If this flag is set, the database may be
160+
cloned by
161+
any user with CREATEDB privileges; if it is not set, only superusers
162+
and the owner of the database may clone it.
163+
If <literal>datallowconn</literal> is false, then no new connections
164+
to that database will be allowed (but existing sessions are not killed
165+
simply by setting the flag false). The <literal>template0</literal>
166+
database is normally marked <literal>datallowconn</literal> =
167+
<literal>false</> to prevent modification of it.
168+
Both <literal>template0</literal> and <literal>template1</literal>
169+
should always be marked with <literal>datistemplate</literal> =
170+
<literal>true</>.
171+
</para>
172+
173+
<para>
174+
After preparing a template database, or making any changes to one,
175+
it is a good idea to perform
176+
<command>VACUUM FREEZE</> or <command>VACUUM FULL FREEZE</> in that
177+
database. If this is done when there are no other open transactions
178+
in the same database, then it is guaranteed that all tuples in the
179+
database are <quote>frozen</> and will not be subject to transaction
180+
ID wraparound problems. This is particularly important for a database
181+
that will have <literal>datallowconn</literal> set to false, since it
182+
will be impossible to do routine maintenance <command>VACUUM</>s on
183+
such a database.
184+
See <xref linkend="vacuum-for-wraparound"> for more information.
185+
</para>
186+
187+
<note>
188+
<para>
189+
<literal>template1</> and <literal>template0</> do not have any special
190+
status beyond the fact that the name <literal>template1</> is the default
191+
source database name for <command>CREATE DATABASE</> and the default
192+
database-to-connect-to for various scripts such as <literal>createdb</>.
193+
For example, one could drop <literal>template1</> and recreate it from
194+
<literal>template0</> without any ill effects. This course of action
195+
might be advisable if one has carelessly added a bunch of junk in
196+
<literal>template1</>.
197+
</para>
198+
</note>
199+
200+
</sect2>
201+
202+
<sect2 id="manage-ag-alternate-locs">
100203
<title>Alternative Locations</title>
101204

102205
<para>
103206
It is possible to create a database in a location other than the
104-
default. Remember that all database access occurs through the
105-
database server backend, so that any location specified must be
106-
accessible by the backend.
207+
default location for the installation. Remember that all database access
208+
occurs through the
209+
database server, so any location specified must be
210+
accessible by the server.
107211
</para>
108212

109213
<para>
110214
Alternative database locations are referenced by an environment
111215
variable which gives the absolute path to the intended storage
112-
location. This environment variable must have been defined before
113-
the backend was started. Any valid environment variable name may
216+
location. This environment variable must be present in the server's
217+
environment, so it must have been defined before the server
218+
was started. (Thus, the set of available alternative locations is
219+
under the site administrator's control; ordinary users can't
220+
change it.) Any valid environment variable name may
114221
be used to reference an alternative location, although using
115222
variable names with a prefix of <literal>PGDATA</> is recommended
116223
to avoid confusion and conflict with other variables.
@@ -144,7 +251,8 @@ setenv PGDATA2 /home/postgres/data
144251
<para>
145252
<indexterm><primary>initlocation</></>
146253
To create a data storage area in <envar>PGDATA2</>, ensure that
147-
<filename>/home/postgres</filename> already exists and is writable
254+
the containing directory (here, <filename>/home/postgres</filename>)
255+
already exists and is writable
148256
by the user account that runs the server (see <xref
149257
linkend="postgres-user">). Then from the command line, type
150258
<informalexample>
@@ -156,7 +264,7 @@ initlocation PGDATA2
156264
</para>
157265

158266
<para>
159-
To create a databaseat the new location, use the command
267+
To create a databasewithin the new location, use the command
160268
<synopsis>
161269
CREATE DATABASE <replaceable>name</> WITH LOCATION = '<replaceable>location</>'
162270
</synopsis>
@@ -166,7 +274,7 @@ CREATE DATABASE <replaceable>name</> WITH LOCATION = '<replaceable>location</>'
166274
</para>
167275

168276
<para>
169-
Database createdat alternative locations using this method can be
277+
Databases createdin alternative locations can be
170278
accessed and dropped like any other database.
171279
</para>
172280

@@ -188,107 +296,6 @@ gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all
188296
</sect2>
189297
</sect1>
190298

191-
<sect1 id="manage-ag-accessdb">
192-
<title>Accessing a Database</title>
193-
194-
<para>
195-
Once you have constructed a database, you can access it by:
196-
197-
<itemizedlist spacing="compact" mark="bullet">
198-
<listitem>
199-
<para>
200-
running the <productname>Postgres</productname> terminal monitor program
201-
(<application>psql</application>) which allows you to interactively
202-
enter, edit, and execute <acronym>SQL</acronym> commands.
203-
</para>
204-
</listitem>
205-
206-
<listitem>
207-
<para>
208-
writing a C program using the <literal>libpq</literal> subroutine
209-
library. This allows you to submit <acronym>SQL</acronym> commands
210-
from C and get answers and status messages back to
211-
your program. This interface is discussed further
212-
in the <citetitle>PostgreSQL Programmer's Guide</citetitle>.
213-
</para>
214-
</listitem>
215-
</itemizedlist>
216-
217-
You might want to start up <application>psql</application>,
218-
to try out the examples in this manual. It can be activated for the
219-
<replaceable class="parameter">dbname</replaceable> database by typing the command:
220-
221-
<programlisting>
222-
psql <replaceable class="parameter">dbname</replaceable>
223-
</programlisting>
224-
225-
You will be greeted with the following message:
226-
227-
<programlisting>
228-
Welcome to psql, the PostgreSQL interactive terminal.
229-
230-
Type: \copyright for distribution terms
231-
\h for help with SQL commands
232-
\? for help on internal slash commands
233-
\g or terminate with semicolon to execute query
234-
\q to quit
235-
236-
<replaceable>dbname</replaceable>=&gt;
237-
</programlisting>
238-
</para>
239-
240-
<para>
241-
This prompt indicates that the terminal monitor is listening
242-
to you and that you can type <acronym>SQL</acronym> queries into a
243-
workspace maintained by the terminal monitor.
244-
The <application>psql</application> program responds to escape
245-
codes that begin
246-
with the backslash character, <literal>\</literal>. For example, you
247-
can get help on the syntax of various
248-
<productname>Postgres</productname> <acronym>SQL</acronym> commands by typing:
249-
250-
<programlisting>
251-
<replaceable>dbname</replaceable>=> \h
252-
</programlisting>
253-
254-
Once you have finished entering your queries into the
255-
workspace, you can pass the contents of the workspace
256-
to the <productname>Postgres</productname> server by typing:
257-
258-
<programlisting>
259-
<replaceable>dbname</replaceable>=> \g
260-
</programlisting>
261-
262-
This tells the server to process the query. If you
263-
terminate your query with a semicolon, the backslash-g is not
264-
necessary. <application>psql</application> will automatically
265-
process semicolon terminated queries.
266-
To read queries from a file, instead of
267-
entering them interactively, type:
268-
269-
<programlisting>
270-
<replaceable>dbname</replaceable>=> \i <replaceable class="parameter">filename</replaceable>
271-
</programlisting>
272-
273-
To get out of <application>psql</application> and return to Unix, type
274-
275-
<programlisting>
276-
<replaceable>dbname</replaceable>=&gt; \q
277-
</programlisting>
278-
279-
and <application>psql</application> will quit and return
280-
you to your command shell. (For more escape codes, type
281-
backslash-h at the monitor prompt.)
282-
White space (i.e., spaces, tabs and newlines) may be
283-
used freely in <acronym>SQL</acronym> queries.
284-
Single-line comments are denoted by two dashes
285-
("<literal>--</literal>"). Everything after the dashes up to the end of the
286-
line is ignored. Multiple-line comments, and comments within a line,
287-
are denoted by <literal>/* ... */</literal>, a convention borrowed
288-
from <productname>Ingres</productname>.
289-
</para>
290-
</sect1>
291-
292299
<sect1 id="manage-ag-dropdb">
293300
<title>Destroying a Database</title>
294301

@@ -297,17 +304,19 @@ Type: \copyright for distribution terms
297304
<synopsis>
298305
DROP DATABASE <replaceable>name</>
299306
</synopsis>
300-
Only the owner of the database (i.e., the user that created it) can
301-
drop databases. Dropping a databases removes all objects that were
307+
Only the owner of the database (i.e., the user that created it), or
308+
a superuser, can drop a database. Dropping a database removes all objects
309+
that were
302310
contained within the database. The destruction of a database cannot
303311
be undone.
304312
</para>
305313

306314
<para>
307315
You cannot execute the <command>DROP DATABASE</command> command
308316
while connected to the victim database. You can, however, be
309-
connected to any other database, including the template1 database,
310-
which would be the only option for dropping the last database of a
317+
connected to any other database, including the <literal>template1</>
318+
database,
319+
which would be the only option for dropping the last user database of a
311320
given cluster.
312321
</para>
313322

0 commit comments

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