diff options
Diffstat (limited to 'doc/src/sgml/ref/create_database.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_database.sgml | 449 |
1 files changed, 449 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..ea38c64 --- /dev/null +++ b/doc/src/sgml/ref/create_database.sgml @@ -0,0 +1,449 @@ +<!-- +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> ] + [ 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> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + The name of a database to create. + </para> + </listitem> + </varlistentry> + <varlistentry> + <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 a direct or indirect member of that role, + or be a superuser. + </para> + </listitem> + </varlistentry> + <varlistentry> + <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> + <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> + <term><replaceable class="parameter">locale</replaceable></term> + <listitem> + <para> + This is a shortcut for setting <symbol>LC_COLLATE</symbol> + and <symbol>LC_CTYPE</symbol> at once. + </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> + <term><replaceable class="parameter">lc_collate</replaceable></term> + <listitem> + <para> + Collation order (<literal>LC_COLLATE</literal>) to use in the new database. + This affects the sort order applied to strings, e.g., in queries with + ORDER BY, as well as the order used in indexes on text columns. + The default is to use the collation order of the template database. + See below for additional restrictions. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="parameter">lc_ctype</replaceable></term> + <listitem> + <para> + Character classification (<literal>LC_CTYPE</literal>) to use in the new + database. This affects the categorization of characters, e.g., lower, + upper and digit. The default is to use the character classification of + the template database. See below for additional restrictions. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">icu_locale</replaceable></term> + <listitem> + <para> + Specifies the ICU locale ID if the ICU locale provider is used. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <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> + <literal>libc</literal>. <literal>libc</literal> is the default. The + available choices depend on the operating system and build options. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <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> + <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> + <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> + <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> + <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> + <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> |