Debian Codegraph @

doc/src/sgml/ref/create_database.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_database.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createdatabase">
7 <indexterm zone="sql-createdatabase">
8 <primary>CREATE DATABASE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE DATABASE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE DATABASE</refname>
19 <refpurpose>create a new database</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE DATABASE <replaceable class="parameter">name</replaceable>
25 [ WITH ] [ OWNER [=] <replaceable class="parameter">user_name</replaceable> ]
26 [ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ]
27 [ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ]
28 [ STRATEGY [=] <replaceable class="parameter">strategy</replaceable> ]
29 [ LOCALE [=] <replaceable class="parameter">locale</replaceable> ]
30 [ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
31 [ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
32 [ BUILTIN_LOCALE [=] <replaceable class="parameter">builtin_locale</replaceable> ]
33 [ ICU_LOCALE [=] <replaceable class="parameter">icu_locale</replaceable> ]
34 [ ICU_RULES [=] <replaceable class="parameter">icu_rules</replaceable> ]
35 [ LOCALE_PROVIDER [=] <replaceable class="parameter">locale_provider</replaceable> ]
36 [ COLLATION_VERSION = <replaceable>collation_version</replaceable> ]
37 [ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
38 [ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
39 [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ]
40 [ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable> ]
41 [ OID [=] <replaceable class="parameter">oid</replaceable> ]
42 </synopsis>
43 </refsynopsisdiv>
44
45 <refsect1>
46 <title>Description</title>
47
48 <para>
49 <command>CREATE DATABASE</command> creates a new
50 <productname>PostgreSQL</productname> database.
51 </para>
52
53 <para>
54 To create a database, you must be a superuser or have the special
55 <literal>CREATEDB</literal> privilege.
56 See <xref linkend="sql-createrole"/>.
57 </para>
58
59 <para>
60 By default, the new database will be created by cloning the standard
61 system database <literal>template1</literal>. A different template can be
62 specified by writing <literal>TEMPLATE
63 <replaceable class="parameter">name</replaceable></literal>. In particular,
64 by writing <literal>TEMPLATE template0</literal>, you can create a pristine
65 database (one where no user-defined objects exist and where the system
66 objects have not been altered)
67 containing only the standard objects predefined by your
68 version of <productname>PostgreSQL</productname>. This is useful
69 if you wish to avoid copying
70 any installation-local objects that might have been added to
71 <literal>template1</literal>.
72 </para>
73 </refsect1>
74
75 <refsect1>
76 <title>Parameters</title>
77
78 <variablelist>
79 <varlistentry id="create-database-name">
80 <term><replaceable class="parameter">name</replaceable></term>
81 <listitem>
82 <para>
83 The name of a database to create.
84 </para>
85 </listitem>
86 </varlistentry>
87 <varlistentry id="create-database-user-name">
88 <term><replaceable class="parameter">user_name</replaceable></term>
89 <listitem>
90 <para>
91 The role name of the user who will own the new database,
92 or <literal>DEFAULT</literal> to use the default (namely, the
93 user executing the command). To create a database owned by another
94 role, you must be able to <literal>SET ROLE</literal> to that
95 role.
96 </para>
97 </listitem>
98 </varlistentry>
99 <varlistentry id="create-database-template">
100 <term><replaceable class="parameter">template</replaceable></term>
101 <listitem>
102 <para>
103 The name of the template from which to create the new database,
104 or <literal>DEFAULT</literal> to use the default template
105 (<literal>template1</literal>).
106 </para>
107 </listitem>
108 </varlistentry>
109 <varlistentry id="create-database-encoding">
110 <term><replaceable class="parameter">encoding</replaceable></term>
111 <listitem>
112 <para>
113 Character set encoding to use in the new database. Specify
114 a string constant (e.g., <literal>'SQL_ASCII'</literal>),
115 or an integer encoding number, or <literal>DEFAULT</literal>
116 to use the default encoding (namely, the encoding of the
117 template database). The character sets supported by the
118 <productname>PostgreSQL</productname> server are described in
119 <xref linkend="multibyte-charset-supported"/>. See below for
120 additional restrictions.
121 </para>
122 </listitem>
123 </varlistentry>
124 <varlistentry id="create-database-strategy" xreflabel="CREATE DATABASE STRATEGY">
125 <term><replaceable class="parameter">strategy</replaceable></term>
126 <listitem>
127 <para>
128 Strategy to be used in creating the new database. If
129 the <literal>WAL_LOG</literal> strategy is used, the database will be
130 copied block by block and each block will be separately written
131 to the write-ahead log. This is the most efficient strategy in
132 cases where the template database is small, and therefore it is the
133 default. The older <literal>FILE_COPY</literal> strategy is also
134 available. This strategy writes a small record to the write-ahead log
135 for each tablespace used by the target database. Each such record
136 represents copying an entire directory to a new location at the
137 filesystem level. While this does reduce the write-ahead
138 log volume substantially, especially if the template database is large,
139 it also forces the system to perform a checkpoint both before and
140 after the creation of the new database. In some situations, this may
141 have a noticeable negative impact on overall system performance. The
142 <literal>FILE_COPY</literal> strategy is affected by the <xref
143 linkend="guc-file-copy-method"/> setting.
144 </para>
145 </listitem>
146 </varlistentry>
147 <varlistentry id="create-database-locale">
148 <term><replaceable class="parameter">locale</replaceable></term>
149 <listitem>
150 <para>
151 Sets the default collation order and character classification in the
152 new database. Collation affects the sort order applied to strings,
153 e.g., in queries with <literal>ORDER BY</literal>, as well as the order used in indexes
154 on text columns. Character classification affects the categorization
155 of characters, e.g., lower, upper, and digit. Also sets the
156 associated aspects of the operating system environment,
157 <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>. The
158 default is the same setting as the template database. See <xref
159 linkend="collation-managing-create-libc"/> and <xref
160 linkend="collation-managing-create-icu"/> for details.
161 </para>
162 <para>
163 Can be overridden by setting <xref
164 linkend="create-database-lc-collate"/>, <xref
165 linkend="create-database-lc-ctype"/>, <xref
166 linkend="create-database-builtin-locale"/>, or <xref
167 linkend="create-database-icu-locale"/> individually.
168 </para>
169 <para>
170 If <xref linkend="create-database-locale-provider"/> is
171 <literal>builtin</literal>, then <replaceable>locale</replaceable> or
172 <replaceable>builtin_locale</replaceable> must be specified and set to
173 either <literal>C</literal>, <literal>C.UTF-8</literal>, or
174 <literal>PG_UNICODE_FAST</literal>.
175 </para>
176 <tip>
177 <para>
178 The other locale settings <xref linkend="guc-lc-messages"/>, <xref
179 linkend="guc-lc-monetary"/>, <xref linkend="guc-lc-numeric"/>, and
180 <xref linkend="guc-lc-time"/> are not fixed per database and are not
181 set by this command. If you want to make them the default for a
182 specific database, you can use <literal>ALTER DATABASE
183 ... SET</literal>.
184 </para>
185 </tip>
186 </listitem>
187 </varlistentry>
188 <varlistentry id="create-database-lc-collate">
189 <term><replaceable class="parameter">lc_collate</replaceable></term>
190 <listitem>
191 <para>
192 Sets <literal>LC_COLLATE</literal> in the database server's operating
193 system environment. The default is the setting of <xref
194 linkend="create-database-locale"/> if specified, otherwise the same
195 setting as the template database. See below for additional
196 restrictions.
197 </para>
198 <para>
199 If <xref linkend="create-database-locale-provider"/> is
200 <literal>libc</literal>, also sets the default collation order to use
201 in the new database, overriding the setting <xref
202 linkend="create-database-locale"/>.
203 </para>
204 </listitem>
205 </varlistentry>
206 <varlistentry id="create-database-lc-ctype">
207 <term><replaceable class="parameter">lc_ctype</replaceable></term>
208 <listitem>
209 <para>
210 Sets <literal>LC_CTYPE</literal> in the database server's operating
211 system environment. The default is the setting of <xref
212 linkend="create-database-locale"/> if specified, otherwise the same
213 setting as the template database. See below for additional
214 restrictions.
215 </para>
216 <para>
217 If <xref linkend="create-database-locale-provider"/> is
218 <literal>libc</literal>, also sets the default character
219 classification to use in the new database, overriding the setting
220 <xref linkend="create-database-locale"/>.
221 </para>
222 </listitem>
223 </varlistentry>
224
225 <varlistentry id="create-database-builtin-locale">
226 <term><replaceable class="parameter">builtin_locale</replaceable></term>
227 <listitem>
228 <para>
229 Specifies the builtin provider locale for the database default
230 collation order and character classification, overriding the setting
231 <xref linkend="create-database-locale"/>. The <link
232 linkend="create-database-locale-provider">locale provider</link> must
233 be <literal>builtin</literal>. The default is the setting of <xref
234 linkend="create-database-locale"/> if specified; otherwise the same
235 setting as the template database.
236 </para>
237 <para>
238 The locales available for the <literal>builtin</literal> provider are
239 <literal>C</literal>, <literal>C.UTF-8</literal> and
240 <literal>PG_UNICODE_FAST</literal>.
241 </para>
242 </listitem>
243 </varlistentry>
244
245 <varlistentry id="create-database-icu-locale">
246 <term><replaceable class="parameter">icu_locale</replaceable></term>
247 <listitem>
248 <para>
249 Specifies the ICU locale (see <xref
250 linkend="collation-managing-create-icu"/>) for the database default
251 collation order and character classification, overriding the setting
252 <xref linkend="create-database-locale"/>. The <link
253 linkend="create-database-locale-provider">locale provider</link> must be ICU. The default
254 is the setting of <xref linkend="create-database-locale"/> if
255 specified; otherwise the same setting as the template database.
256 </para>
257 </listitem>
258 </varlistentry>
259
260 <varlistentry id="create-database-icu-rules">
261 <term><replaceable class="parameter">icu_rules</replaceable></term>
262 <listitem>
263 <para>
264 Specifies additional collation rules to customize the behavior of the
265 default collation of this database. This is supported for ICU only.
266 See <xref linkend="icu-tailoring-rules"/> for details.
267 </para>
268 </listitem>
269 </varlistentry>
270
271 <varlistentry id="create-database-locale-provider">
272 <term><replaceable>locale_provider</replaceable></term>
273
274 <listitem>
275 <para>
276 Specifies the provider to use for the default collation in this
277 database. Possible values are <literal>builtin</literal>,
278 <literal>icu</literal><indexterm><primary>ICU</primary></indexterm>
279 (if the server was built with ICU support) or <literal>libc</literal>.
280 By default, the provider is the same as that of the <xref
281 linkend="create-database-template"/>. See <xref
282 linkend="locale-providers"/> for details.
283 </para>
284 </listitem>
285 </varlistentry>
286
287 <varlistentry id="create-database-collation-version">
288 <term><replaceable>collation_version</replaceable></term>
289
290 <listitem>
291 <para>
292 Specifies the collation version string to store with the database.
293 Normally, this should be omitted, which will cause the version to be
294 computed from the actual version of the database collation as provided
295 by the operating system. This option is intended to be used by
296 <command>pg_upgrade</command> for copying the version from an existing
297 installation.
298 </para>
299
300 <para>
301 See also <xref linkend="sql-alterdatabase"/> for how to handle
302 database collation version mismatches.
303 </para>
304 </listitem>
305 </varlistentry>
306 <varlistentry id="create-database-tablespace-name">
307 <term><replaceable class="parameter">tablespace_name</replaceable></term>
308 <listitem>
309 <para>
310 The name of the tablespace that will be associated with the
311 new database, or <literal>DEFAULT</literal> to use the
312 template database's tablespace. This
313 tablespace will be the default tablespace used for objects
314 created in this database. See
315 <xref linkend="sql-createtablespace"/>
316 for more information.
317 </para>
318 </listitem>
319 </varlistentry>
320
321 <varlistentry id="create-database-allowconn">
322 <term><replaceable class="parameter">allowconn</replaceable></term>
323 <listitem>
324 <para>
325 If false then no one can connect to this database. The default is
326 true, allowing connections (except as restricted by other mechanisms,
327 such as <literal>GRANT</literal>/<literal>REVOKE CONNECT</literal>).
328 </para>
329 </listitem>
330 </varlistentry>
331
332 <varlistentry id="create-database-connlimit">
333 <term><replaceable class="parameter">connlimit</replaceable></term>
334 <listitem>
335 <para>
336 How many concurrent connections can be made
337 to this database. -1 (the default) means no limit.
338 </para>
339 </listitem>
340 </varlistentry>
341
342 <varlistentry id="create-database-istemplate">
343 <term><replaceable class="parameter">istemplate</replaceable></term>
344 <listitem>
345 <para>
346 If true, then this database can be cloned by any user with <literal>CREATEDB</literal>
347 privileges; if false (the default), then only superusers or the owner
348 of the database can clone it.
349 </para>
350 </listitem>
351 </varlistentry>
352
353 <varlistentry id="create-database-oid">
354 <term><replaceable class="parameter">oid</replaceable></term>
355 <listitem>
356 <para>
357 The object identifier to be used for the new database. If this
358 parameter is not specified, <productname>PostgreSQL</productname>
359 will choose a suitable OID automatically. This parameter is primarily
360 intended for internal use by <application>pg_upgrade</application>,
361 and only <application>pg_upgrade</application> can specify a value
362 less than 16384.
363 </para>
364 </listitem>
365 </varlistentry>
366
367 </variablelist>
368
369 <para>
370 Optional parameters can be written in any order, not only the order
371 illustrated above.
372 </para>
373 </refsect1>
374
375 <refsect1>
376 <title>Notes</title>
377
378 <para>
379 <command>CREATE DATABASE</command> cannot be executed inside a transaction
380 block.
381 </para>
382
383 <para>
384 Errors along the line of <quote>could not initialize database directory</quote>
385 are most likely related to insufficient permissions on the data
386 directory, a full disk, or other file system problems.
387 </para>
388
389 <para>
390 Use <link linkend="sql-dropdatabase"><command>DROP DATABASE</command></link> to remove a database.
391 </para>
392
393 <para>
394 The program <xref linkend="app-createdb"/> is a
395 wrapper program around this command, provided for convenience.
396 </para>
397
398 <para>
399 Database-level configuration parameters (set via <link
400 linkend="sql-alterdatabase"><command>ALTER DATABASE</command></link>) and database-level permissions (set via
401 <link linkend="sql-grant"><command>GRANT</command></link>) are not copied from the template database.
402 </para>
403
404 <para>
405 Although it is possible to copy a database other than <literal>template1</literal>
406 by specifying its name as the template, this is not (yet) intended as
407 a general-purpose <quote><command>COPY DATABASE</command></quote> facility.
408 The principal limitation is that no other sessions can be connected to
409 the template database while it is being copied. <command>CREATE
410 DATABASE</command> will fail if any other connection exists when it starts;
411 otherwise, new connections to the template database are locked out
412 until <command>CREATE DATABASE</command> completes.
413 See <xref linkend="manage-ag-templatedbs"/> for more information.
414 </para>
415
416 <para>
417 The character set encoding specified for the new database must be
418 compatible with the chosen locale settings (<literal>LC_COLLATE</literal> and
419 <literal>LC_CTYPE</literal>). If the locale is <literal>C</literal> (or equivalently
420 <literal>POSIX</literal>), then all encodings are allowed, but for other
421 locale settings there is only one encoding that will work properly.
422 (On Windows, however, UTF-8 encoding can be used with any locale.)
423 <command>CREATE DATABASE</command> will allow superusers to specify
424 <literal>SQL_ASCII</literal> encoding regardless of the locale settings,
425 but this choice is deprecated and may result in misbehavior of
426 character-string functions if data that is not encoding-compatible
427 with the locale is stored in the database.
428 </para>
429
430 <para>
431 The encoding and locale settings must match those of the template database,
432 except when <literal>template0</literal> is used as template. This is because
433 other databases might contain data that does not match the specified
434 encoding, or might contain indexes whose sort ordering is affected by
435 <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>. Copying such data would
436 result in a database that is corrupt according to the new settings.
437 <literal>template0</literal>, however, is known to not contain any data or
438 indexes that would be affected.
439 </para>
440
441 <para>
442 There is currently no option to use a database locale with nondeterministic
443 comparisons (see <link linkend="sql-createcollation"><command>CREATE
444 COLLATION</command></link> for an explanation). If this is needed, then
445 per-column collations would need to be used.
446 </para>
447
448 <para>
449 The <literal>CONNECTION LIMIT</literal> option is only enforced approximately;
450 if two new sessions start at about the same time when just one
451 connection <quote>slot</quote> remains for the database, it is possible that
452 both will fail. Also, the limit is not enforced against superusers or
453 background worker processes.
454 </para>
455 </refsect1>
456
457 <refsect1>
458 <title>Examples</title>
459
460 <para>
461 To create a new database:
462
463 <programlisting>
464 CREATE DATABASE lusiadas;
465 </programlisting>
466 </para>
467
468 <para>
469 To create a database <literal>sales</literal> owned by user <literal>salesapp</literal>
470 with a default tablespace of <literal>salesspace</literal>:
471
472 <programlisting>
473 CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
474 </programlisting>
475 </para>
476
477 <para>
478 To create a database <literal>music</literal> with a different locale:
479 <programlisting>
480 CREATE DATABASE music
481 LOCALE 'sv_SE.utf8'
482 TEMPLATE template0;
483 </programlisting>
484 In this example, the <literal>TEMPLATE template0</literal> clause is required if
485 the specified locale is different from the one in <literal>template1</literal>.
486 (If it is not, then specifying the locale explicitly is redundant.)
487 </para>
488
489 <para>
490 To create a database <literal>music2</literal> with a different locale and a
491 different character set encoding:
492 <programlisting>
493 CREATE DATABASE music2
494 LOCALE 'sv_SE.iso885915'
495 ENCODING LATIN9
496 TEMPLATE template0;
497 </programlisting>
498 The specified locale and encoding settings must match, or an error will be
499 reported.
500 </para>
501
502 <para>
503 Note that locale names are specific to the operating system, so that the
504 above commands might not work in the same way everywhere.
505 </para>
506 </refsect1>
507
508 <refsect1>
509 <title>Compatibility</title>
510
511 <para>
512 There is no <command>CREATE DATABASE</command> statement in the SQL
513 standard. Databases are equivalent to catalogs, whose creation is
514 implementation-defined.
515 </para>
516 </refsect1>
517
518 <refsect1>
519 <title>See Also</title>
520
521 <simplelist type="inline">
522 <member><xref linkend="sql-alterdatabase"/></member>
523 <member><xref linkend="sql-dropdatabase"/></member>
524 </simplelist>
525 </refsect1>
526
527 </refentry>