Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 496 insertions, 0 deletions

diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
new file mode 100644
index 0000000..ce7317f
--- /dev/null
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -0,0 +1,496 @@
+<!--
+doc/src/sgml/ref/create_database.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createdatabase">
+ <indexterm zone="sql-createdatabase">
+  <primary>CREATE DATABASE</primary>
+ </indexterm>
+
+ <refmeta>
+  <refentrytitle>CREATE DATABASE</refentrytitle>
+  <manvolnum>7</manvolnum>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>CREATE DATABASE</refname>
+  <refpurpose>create a new database</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE DATABASE <replaceable class="parameter">name</replaceable>
+    [ WITH ] [ OWNER [=] <replaceable class="parameter">user_name</replaceable> ]
+           [ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ]
+           [ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ]
+           [ STRATEGY [=] <replaceable class="parameter">strategy</replaceable> ] ]
+           [ LOCALE [=] <replaceable class="parameter">locale</replaceable> ]
+           [ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
+           [ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
+           [ ICU_LOCALE [=] <replaceable class="parameter">icu_locale</replaceable> ]
+           [ ICU_RULES [=] <replaceable class="parameter">icu_rules</replaceable> ]
+           [ LOCALE_PROVIDER [=] <replaceable class="parameter">locale_provider</replaceable> ]
+           [ COLLATION_VERSION = <replaceable>collation_version</replaceable> ]
+           [ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
+           [ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
+           [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ]
+           [ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable> ]
+           [ OID [=] <replaceable class="parameter">oid</replaceable> ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <command>CREATE DATABASE</command> creates a new
+   <productname>PostgreSQL</productname> database.
+  </para>
+
+  <para>
+   To create a database, you must be a superuser or have the special
+   <literal>CREATEDB</literal> privilege.
+   See <xref linkend="sql-createrole"/>.
+  </para>
+
+  <para>
+   By default, the new database will be created by cloning the standard
+   system database <literal>template1</literal>.  A different template can be
+   specified by writing <literal>TEMPLATE
+   <replaceable class="parameter">name</replaceable></literal>.  In particular,
+   by writing <literal>TEMPLATE template0</literal>, you can create a pristine
+   database (one where no user-defined objects exist and where the system
+   objects have not been altered)
+   containing only the standard objects predefined by your
+   version of <productname>PostgreSQL</productname>.  This is useful
+   if you wish to avoid copying
+   any installation-local objects that might have been added to
+   <literal>template1</literal>.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Parameters</title>
+
+    <variablelist>
+     <varlistentry id="create-database-name">
+      <term><replaceable class="parameter">name</replaceable></term>
+      <listitem>
+       <para>
+        The name of a database to create.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry id="create-database-user-name">
+      <term><replaceable class="parameter">user_name</replaceable></term>
+      <listitem>
+       <para>
+        The role name of the user who will own the new database,
+        or <literal>DEFAULT</literal> to use the default (namely, the
+        user executing the command).  To create a database owned by another
+        role, you must be able to <literal>SET ROLE</literal> to that
+        role.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry id="create-database-template">
+      <term><replaceable class="parameter">template</replaceable></term>
+      <listitem>
+       <para>
+        The name of the template from which to create the new database,
+        or <literal>DEFAULT</literal> to use the default template
+        (<literal>template1</literal>).
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry id="create-database-encoding">
+      <term><replaceable class="parameter">encoding</replaceable></term>
+      <listitem>
+       <para>
+        Character set encoding to use in the new database.  Specify
+        a string constant (e.g., <literal>'SQL_ASCII'</literal>),
+        or an integer encoding number, or <literal>DEFAULT</literal>
+        to use the default encoding (namely, the encoding of the
+        template database). The character sets supported by the
+        <productname>PostgreSQL</productname> server are described in
+        <xref linkend="multibyte-charset-supported"/>. See below for
+        additional restrictions.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry id="create-database-strategy" xreflabel="CREATE DATABASE STRATEGY">
+      <term><replaceable class="parameter">strategy</replaceable></term>
+      <listitem>
+       <para>
+        Strategy to be used in creating the new database.  If
+        the <literal>WAL_LOG</literal> strategy is used, the database will be
+        copied block by block and each block will be separately written
+        to the write-ahead log. This is the most efficient strategy in
+        cases where the template database is small, and therefore it is the
+        default. The older <literal>FILE_COPY</literal> strategy is also
+        available. This strategy writes a small record to the write-ahead log
+        for each tablespace used by the target database. Each such record
+        represents copying an entire directory to a new location at the
+        filesystem level. While this does reduce the write-ahead
+        log volume substantially, especially if the template database is large,
+        it also forces the system to perform a checkpoint both before and
+        after the creation of the new database. In some situations, this may
+        have a noticeable negative impact on overall system performance.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry id="create-database-locale">
+      <term><replaceable class="parameter">locale</replaceable></term>
+      <listitem>
+       <para>
+        Sets the default collation order and character classification in the
+        new database.  Collation affects the sort order applied to strings,
+        e.g., in queries with <literal>ORDER BY</literal>, as well as the order used in indexes
+        on text columns.  Character classification affects the categorization
+        of characters, e.g., lower, upper, and digit.  Also sets the
+        associated aspects of the operating system environment,
+        <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>.  The
+        default is the same setting as the template database.  See <xref
+        linkend="collation-managing-create-libc"/> and <xref
+        linkend="collation-managing-create-icu"/> for details.
+       </para>
+       <para>
+        Can be overridden by setting <xref
+        linkend="create-database-lc-collate"/>, <xref
+        linkend="create-database-lc-ctype"/>, or <xref
+        linkend="create-database-icu-locale"/> individually.
+       </para>
+       <tip>
+        <para>
+         The other locale settings <xref linkend="guc-lc-messages"/>, <xref
+         linkend="guc-lc-monetary"/>, <xref linkend="guc-lc-numeric"/>, and
+         <xref linkend="guc-lc-time"/> are not fixed per database and are not
+         set by this command.  If you want to make them the default for a
+         specific database, you can use <literal>ALTER DATABASE
+         ... SET</literal>.
+        </para>
+       </tip>
+      </listitem>
+     </varlistentry>
+     <varlistentry id="create-database-lc-collate">
+      <term><replaceable class="parameter">lc_collate</replaceable></term>
+      <listitem>
+       <para>
+        Sets <literal>LC_COLLATE</literal> in the database server's operating
+        system environment.  The default is the setting of <xref
+        linkend="create-database-locale"/> if specified, otherwise the same
+        setting as the template database.  See below for additional
+        restrictions.
+       </para>
+       <para>
+        If <xref linkend="create-database-locale-provider"/> is
+        <literal>libc</literal>, also sets the default collation order to use
+        in the new database, overriding the setting <xref
+        linkend="create-database-locale"/>.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry id="create-database-lc-ctype">
+      <term><replaceable class="parameter">lc_ctype</replaceable></term>
+      <listitem>
+       <para>
+        Sets <literal>LC_CTYPE</literal> in the database server's operating
+        system environment.  The default is the setting of <xref
+        linkend="create-database-locale"/> if specified, otherwise the same
+        setting as the template database.  See below for additional
+        restrictions.
+       </para>
+       <para>
+        If <xref linkend="create-database-locale-provider"/> is
+        <literal>libc</literal>, also sets the default character
+        classification to use in the new database, overriding the setting
+        <xref linkend="create-database-locale"/>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="create-database-icu-locale">
+      <term><replaceable class="parameter">icu_locale</replaceable></term>
+      <listitem>
+       <para>
+        Specifies the ICU locale (see <xref
+        linkend="collation-managing-create-icu"/>) for the database default
+        collation order and character classification, overriding the setting
+        <xref linkend="create-database-locale"/>.  The <link
+        linkend="create-database-locale-provider">locale provider</link> must be ICU.  The default
+        is the setting of <xref linkend="create-database-locale"/> if
+        specified; otherwise the same setting as the template database.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="create-database-icu-rules">
+      <term><replaceable class="parameter">icu_rules</replaceable></term>
+      <listitem>
+       <para>
+        Specifies additional collation rules to customize the behavior of the
+        default collation of this database.  This is supported for ICU only.
+        See <xref linkend="icu-tailoring-rules"/> for details.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="create-database-locale-provider">
+      <term><replaceable>locale_provider</replaceable></term>
+
+      <listitem>
+       <para>
+        Specifies the provider to use for the default collation in this
+        database.  Possible values are
+        <literal>icu</literal><indexterm><primary>ICU</primary></indexterm>
+        (if the server was built with ICU support) or <literal>libc</literal>.
+        By default, the provider is the same as that of the <xref
+        linkend="create-database-template"/>. See <xref
+        linkend="locale-providers"/> for details.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="create-database-collation-version">
+      <term><replaceable>collation_version</replaceable></term>
+
+      <listitem>
+       <para>
+        Specifies the collation version string to store with the database.
+        Normally, this should be omitted, which will cause the version to be
+        computed from the actual version of the database collation as provided
+        by the operating system.  This option is intended to be used by
+        <command>pg_upgrade</command> for copying the version from an existing
+        installation.
+       </para>
+
+       <para>
+        See also <xref linkend="sql-alterdatabase"/> for how to handle
+        database collation version mismatches.
+       </para>
+     </listitem>
+    </varlistentry>
+     <varlistentry id="create-database-tablespace-name">
+      <term><replaceable class="parameter">tablespace_name</replaceable></term>
+      <listitem>
+       <para>
+        The name of the tablespace that will be associated with the
+        new database, or <literal>DEFAULT</literal> to use the
+        template database's tablespace. This
+        tablespace will be the default tablespace used for objects
+        created in this database. See
+        <xref linkend="sql-createtablespace"/>
+        for more information.
+       </para>
+      </listitem>
+     </varlistentry>
+
+      <varlistentry id="create-database-allowconn">
+       <term><replaceable class="parameter">allowconn</replaceable></term>
+       <listitem>
+        <para>
+         If false then no one can connect to this database.  The default is
+         true, allowing connections (except as restricted by other mechanisms,
+         such as <literal>GRANT</literal>/<literal>REVOKE CONNECT</literal>).
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry id="create-database-connlimit">
+      <term><replaceable class="parameter">connlimit</replaceable></term>
+      <listitem>
+       <para>
+        How many concurrent connections can be made
+        to this database.  -1 (the default) means no limit.
+       </para>
+      </listitem>
+     </varlistentry>
+
+      <varlistentry id="create-database-istemplate">
+       <term><replaceable class="parameter">istemplate</replaceable></term>
+       <listitem>
+        <para>
+         If true, then this database can be cloned by any user with <literal>CREATEDB</literal>
+         privileges; if false (the default), then only superusers or the owner
+         of the database can clone it.
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry id="create-database-oid">
+       <term><replaceable class="parameter">oid</replaceable></term>
+       <listitem>
+        <para>
+         The object identifier to be used for the new database. If this
+         parameter is not specified, <productname>PostgreSQL</productname>
+         will choose a suitable OID automatically. This parameter is primarily
+         intended for internal use by <application>pg_upgrade</application>,
+         and only <application>pg_upgrade</application> can specify a value
+         less than 16384.
+        </para>
+       </listitem>
+      </varlistentry>
+
+    </variablelist>
+
+  <para>
+   Optional parameters can be written in any order, not only the order
+   illustrated above.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+   <para>
+    <command>CREATE DATABASE</command> cannot be executed inside a transaction
+    block.
+   </para>
+
+   <para>
+    Errors along the line of <quote>could not initialize database directory</quote>
+    are most likely related to insufficient permissions on the data
+    directory, a full disk, or other file system problems.
+   </para>
+
+   <para>
+    Use <link linkend="sql-dropdatabase"><command>DROP DATABASE</command></link> to remove a database.
+   </para>
+
+   <para>
+    The program <xref linkend="app-createdb"/> is a
+    wrapper program around this command, provided for convenience.
+   </para>
+
+   <para>
+    Database-level configuration parameters (set via <link
+    linkend="sql-alterdatabase"><command>ALTER DATABASE</command></link>) and database-level permissions (set via
+    <link linkend="sql-grant"><command>GRANT</command></link>) are not copied from the template database.
+   </para>
+
+  <para>
+   Although it is possible to copy a database other than <literal>template1</literal>
+   by specifying its name as the template, this is not (yet) intended as
+   a general-purpose <quote><command>COPY DATABASE</command></quote> facility.
+   The principal limitation is that no other sessions can be connected to
+   the template database while it is being copied.  <command>CREATE
+   DATABASE</command> will fail if any other connection exists when it starts;
+   otherwise, new connections to the template database are locked out
+   until <command>CREATE DATABASE</command> completes.
+   See <xref linkend="manage-ag-templatedbs"/> for more information.
+  </para>
+
+  <para>
+   The character set encoding specified for the new database must be
+   compatible with the chosen locale settings (<literal>LC_COLLATE</literal> and
+   <literal>LC_CTYPE</literal>).  If the locale is <literal>C</literal> (or equivalently
+   <literal>POSIX</literal>), then all encodings are allowed, but for other
+   locale settings there is only one encoding that will work properly.
+   (On Windows, however, UTF-8 encoding can be used with any locale.)
+   <command>CREATE DATABASE</command> will allow superusers to specify
+   <literal>SQL_ASCII</literal> encoding regardless of the locale settings,
+   but this choice is deprecated and may result in misbehavior of
+   character-string functions if data that is not encoding-compatible
+   with the locale is stored in the database.
+  </para>
+
+  <para>
+   The encoding and locale settings must match those of the template database,
+   except when <literal>template0</literal> is used as template.  This is because
+   other databases might contain data that does not match the specified
+   encoding, or might contain indexes whose sort ordering is affected by
+   <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>.  Copying such data would
+   result in a database that is corrupt according to the new settings.
+   <literal>template0</literal>, however, is known to not contain any data or
+   indexes that would be affected.
+  </para>
+
+  <para>
+   There is currently no option to use a database locale with nondeterministic
+   comparisons (see <link linkend="sql-createcollation"><command>CREATE
+   COLLATION</command></link> for an explanation).  If this is needed, then
+   per-column collations would need to be used.
+  </para>
+
+  <para>
+   The <literal>CONNECTION LIMIT</literal> option is only enforced approximately;
+   if two new sessions start at about the same time when just one
+   connection <quote>slot</quote> remains for the database, it is possible that
+   both will fail.  Also, the limit is not enforced against superusers or
+   background worker processes.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   To create a new database:
+
+<programlisting>
+CREATE DATABASE lusiadas;
+</programlisting>
+  </para>
+
+  <para>
+   To create a database <literal>sales</literal> owned by user <literal>salesapp</literal>
+   with a default tablespace of <literal>salesspace</literal>:
+
+<programlisting>
+CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
+</programlisting>
+  </para>
+
+  <para>
+   To create a database <literal>music</literal> with a different locale:
+<programlisting>
+CREATE DATABASE music
+    LOCALE 'sv_SE.utf8'
+    TEMPLATE template0;
+</programlisting>
+    In this example, the <literal>TEMPLATE template0</literal> clause is required if
+    the specified locale is different from the one in <literal>template1</literal>.
+    (If it is not, then specifying the locale explicitly is redundant.)
+  </para>
+
+  <para>
+   To create a database <literal>music2</literal> with a different locale and a
+   different character set encoding:
+<programlisting>
+CREATE DATABASE music2
+    LOCALE 'sv_SE.iso885915'
+    ENCODING LATIN9
+    TEMPLATE template0;
+</programlisting>
+   The specified locale and encoding settings must match, or an error will be
+   reported.
+  </para>
+
+  <para>
+   Note that locale names are specific to the operating system, so that the
+   above commands might not work in the same way everywhere.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+
+  <para>
+   There is no <command>CREATE DATABASE</command> statement in the SQL
+   standard.  Databases are equivalent to catalogs, whose creation is
+   implementation-defined.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-alterdatabase"/></member>
+   <member><xref linkend="sql-dropdatabase"/></member>
+  </simplelist>
+ </refsect1>
+
+</refentry>