Skip to content

Commit 2cf48ca

Browse files
tglsfdctglsfdc
committed
Extend CREATE DATABASE to allow selection of a template database to be
cloned, rather than always cloning template1. Modify initdb to generate two identical databases rather than one, template0 and template1. Connections to template0 are disallowed, so that it will always remain in its virgin as-initdb'd state. pg_dumpall now dumps databases with restore commands that say CREATE DATABASE foo WITH TEMPLATE = template0. This allows proper behavior when there is user-added data in template1. initdb forced!
1 parent 8a9315c commit 2cf48ca

File tree

23 files changed

+514
-310
lines changed

23 files changed

+514
-310
lines changed

‎doc/src/sgml/ref/create_database.sgml

Lines changed: 88 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.15 2000/10/05 19:48:17 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.16 2000/11/14 18:37:40 tgl Exp $
33
Postgres documentation
44
-->
55

@@ -23,7 +23,10 @@ Postgres documentation
2323
<date>1999-12-11</date>
2424
</refsynopsisdivinfo>
2525
<synopsis>
26-
CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATION = '<replaceable class="parameter">dbpath</replaceable>' ]
26+
CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
27+
[ WITH [ LOCATION = '<replaceable class="parameter">dbpath</replaceable>' ]
28+
[ TEMPLATE = <replaceable class="parameter">template</replaceable> ]
29+
[ ENCODING = <replaceable class="parameter">encoding</replaceable> ] ]
2730
</synopsis>
2831

2932
<refsect2 id="R2-SQL-CREATEDATABASE-1">
@@ -48,8 +51,30 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
4851
<term><replaceable class="parameter">dbpath</replaceable></term>
4952
<listitem>
5053
<para>
51-
An alternate location where to store the new database in the filesystem.
52-
See below for caveats.
54+
An alternate filesystem location in which to store the new database,
55+
specified as a string literal;
56+
or <literal>DEFAULT</literal> to use the default location.
57+
</para>
58+
</listitem>
59+
</varlistentry>
60+
<varlistentry>
61+
<term><replaceable class="parameter">template</replaceable></term>
62+
<listitem>
63+
<para>
64+
Name of template from which to create the new database,
65+
or <literal>DEFAULT</literal> to use the default template
66+
(<literal>template1</literal>).
67+
</para>
68+
</listitem>
69+
</varlistentry>
70+
<varlistentry>
71+
<term><replaceable class="parameter">encoding</replaceable></term>
72+
<listitem>
73+
<para>
74+
Multibyte encoding method to use in the new database. Specify
75+
a string literal name (e.g., <literal>'SQL_ASCII'</literal>),
76+
or an integer encoding number, or <literal>DEFAULT</literal>
77+
to use the default encoding.
5378
</para>
5479
</listitem>
5580
</varlistentry>
@@ -98,11 +123,10 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
98123
</varlistentry>
99124

100125
<varlistentry>
101-
<term><computeroutput>ERROR: Single quotes are not allowed in database names.</computeroutput></term>
102-
<term><computeroutput>ERROR: Single quotes are not allowed in database paths.</computeroutput></term>
126+
<term><computeroutput>ERROR: database path may not contain single quotes</computeroutput></term>
103127
<listitem>
104128
<para>
105-
The database <replaceable class="parameter">name</replaceable> and
129+
The database location
106130
<replaceable class="parameter">dbpath</replaceable> cannot contain
107131
single quotes. This is required so that the shell commands that
108132
create the database directory can execute safely.
@@ -111,18 +135,7 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
111135
</varlistentry>
112136

113137
<varlistentry>
114-
<term><computeroutput>ERROR: The path 'xxx' is invalid.</computeroutput></term>
115-
<listitem>
116-
<para>
117-
The expansion of the specified <replaceable class="parameter">dbpath</replaceable>
118-
(see below) failed. Check the path you entered or make sure that the
119-
environment variable you are referencing does exist.
120-
</para>
121-
</listitem>
122-
</varlistentry>
123-
124-
<varlistentry>
125-
<term><computeroutput>ERROR: createdb: May not be called in a transaction block.</computeroutput></term>
138+
<term><computeroutput>ERROR: CREATE DATABASE: may not be called in a transaction block</computeroutput></term>
126139
<listitem>
127140
<para>
128141
If you have an explicit transaction block in progress you cannot call
@@ -133,6 +146,9 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
133146

134147
<varlistentry>
135148
<term><computeroutput>ERROR: Unable to create database directory '<replaceable>path</replaceable>'.</computeroutput></term>
149+
</varlistentry>
150+
151+
<varlistentry>
136152
<term><computeroutput>ERROR: Could not initialize database directory.</computeroutput></term>
137153
<listitem>
138154
<para>
@@ -169,10 +185,10 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
169185
command.
170186
</para>
171187
<para>
172-
If the path contains a slash, the leading part is interpreted
173-
as an environment variable, which must be known to the
188+
If the path name does not contain a slash, it is interpreted
189+
as an environment variable name, which must be known to the
174190
server process. This way the database administrator can
175-
exercise control over at which locations databases can be created.
191+
exercise control over locations in which databases can be created.
176192
(A customary choice is, e.g., '<envar>PGDATA2</envar>'.)
177193
If the server is compiled with <literal>ALLOW_ABSOLUTE_DBPATHS</literal>
178194
(not so by default), absolute path names, as identified by
@@ -181,6 +197,29 @@ CREATE DATABASE <replaceable class="PARAMETER">name</replaceable> [ WITH LOCATIO
181197
are allowed as well.
182198
</para>
183199

200+
<para>
201+
By default, the new database will be created by cloning the standard
202+
system database <literal>template1</>. A different template can be
203+
specified by writing <literal>TEMPLATE =</>
204+
<replaceable class="parameter">name</replaceable>. In particular,
205+
by writing <literal>TEMPLATE = template0</>, you can create a virgin
206+
database containing only the standard objects predefined by your
207+
version of Postgres. This is useful if you wish to avoid copying
208+
any installation-local objects that may have been added to template1.
209+
</para>
210+
211+
<para>
212+
The optional encoding parameter allows selection of the database encoding,
213+
if your server was compiled with multibyte encoding support. When not
214+
specified, it defaults to the encoding used by the selected template
215+
database.
216+
</para>
217+
218+
<para>
219+
Optional parameters can be written in any order, not only the order
220+
illustrated above.
221+
</para>
222+
184223
<refsect2 id="R2-SQL-CREATEDATABASE-3">
185224
<refsect2info>
186225
<date>1999-12-11</date>
@@ -221,6 +260,33 @@ comment from Olly; response from Thomas...
221260
Not sure if the dump/reload would guarantee that
222261
the alternate data area gets refreshed though...
223262
-->
263+
264+
<para>
265+
Although it is possible to copy a database other than template1 by
266+
specifying its name as the template, this is not (yet) intended as
267+
a general-purpose COPY DATABASE facility. In particular, it is
268+
essential that the source database be idle (no data-altering transactions
269+
in progress)
270+
for the duration of the copying operation. CREATE DATABASE will check
271+
that no backend processes (other than itself) are connected to
272+
the source database at the start of the operation, but this does not
273+
guarantee that changes cannot be made while the copy proceeds. Therefore,
274+
we recommend that databases used as templates be treated as read-only.
275+
</para>
276+
277+
<para>
278+
Two useful flags exist in <literal>pg_database</literal> for each
279+
database: <literal>datistemplate</literal> and
280+
<literal>datallowconn</literal>. <literal>datistemplate</literal>
281+
may be set to indicate that a database is intended as a template for
282+
CREATE DATABASE. If this flag is set, the database may be cloned by
283+
any user with CREATEDB privileges; if it is not set, only superusers
284+
and the owner of the database may clone it.
285+
If <literal>datallowconn</literal> is false, then no new connections
286+
to that database will be allowed (but existing sessions are not killed
287+
simply by setting the flag false). The <literal>template0</literal>
288+
database is normally marked this way to prevent modification of it.
289+
</para>
224290
</refsect2>
225291
</refsect1>
226292

0 commit comments

Comments
 (0)