<!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.7 2003/06/27 14:45:25 petere Exp $
+doc/src/sgml/ref/alter_database.sgml
PostgreSQL documentation
-->
<refentry id="SQL-ALTERDATABASE">
+ <indexterm zone="sql-alterdatabase">
+ <primary>ALTER DATABASE</primary>
+ </indexterm>
+
<refmeta>
- <refentrytitle id="sql-alterdatabase-title">ALTER DATABASE</refentrytitle>
+ <refentrytitle>ALTER DATABASE</refentrytitle>
+ <manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refsynopsisdiv>
<synopsis>
-ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>variable</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
-ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>variable</replaceable>
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ]
+
+<phrase>where <replaceable class="PARAMETER">option</replaceable> can be:</phrase>
+
+ IS_TEMPLATE <replaceable class="PARAMETER">istemplate</replaceable>
+ ALLOW_CONNECTIONS <replaceable class="PARAMETER">allowconn</replaceable>
+ CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
+
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
+
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
-ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>newname</replaceable>
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
+
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET ALL
</synopsis>
</refsynopsisdiv>
<title>Description</title>
<para>
- <command>ALTER DATABASE</command> is used to change the attributes
+ <command>ALTER DATABASE</command> changes the attributes
of a database.
</para>
<para>
- The first two forms change the session default of a run-time
- configuration variable for a <productname>PostgreSQL</productname>
- database. Whenever a new session is subsequently started in that
- database, the specified value becomes the session default value.
- The database-specific default overrides whatever setting is present
- in <filename>postgresql.conf</> or has been received from the
- <command>postmaster</command> command line. Only the database
- owner or a superuser can change the session defaults for a
- database.
+ The first form changes certain per-database settings. (See below for
+ details.) Only the database owner or a superuser can change these settings.
</para>
<para>
- The third form changes the name of the database. Only the database
- owner can rename a database, and only if he has the
+ The second form changes the name of the database. Only the database
+ owner or a superuser can rename a database; non-superuser owners must
+ also have the
<literal>CREATEDB</literal> privilege. The current database cannot
be renamed. (Connect to a different database if you need to do
that.)
</para>
+
+ <para>
+ The third form changes the owner of the database.
+ To alter the owner, you must own the database and also be a direct or
+ indirect member of the new owning role, and you must have the
+ <literal>CREATEDB</literal> privilege.
+ (Note that superusers have all these privileges automatically.)
+ </para>
+
+ <para>
+ The fourth form changes the default tablespace of the database.
+ Only the database owner or a superuser can do this; you must also have
+ create privilege for the new tablespace.
+ This command physically moves any tables or indexes in the database's old
+ default tablespace to the new tablespace. Note that tables and indexes
+ in non-default tablespaces are not affected.
+ </para>
+
+ <para>
+ The remaining forms change the session default for a run-time
+ configuration variable for a <productname>PostgreSQL</productname>
+ database. Whenever a new session is subsequently started in that
+ database, the specified value becomes the session default value.
+ The database-specific default overrides whatever setting is present
+ in <filename>postgresql.conf</> or has been received from the
+ <command>postgres</command> command line. Only the database
+ owner or a superuser can change the session defaults for a
+ database. Certain variables cannot be set this way, or can only be
+ set by a superuser.
+ </para>
</refsect1>
<refsect1>
<term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
- The name of the database whose session defaults are to be altered.
+ The name of the database whose attributes are to be altered.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable>variable</replaceable></term>
- <term><replaceable>value</replaceable></term>
+ <term><replaceable class="parameter">istemplate</replaceable></term>
+ <listitem>
+ <para>
+ If true, then this database can be cloned by any user with CREATEDB
+ privileges; if false, then only superusers or the owner of the
+ database can clone it.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">allowconn</replaceable></term>
+ <listitem>
+ <para>
+ If false then no one can connect to this database.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">connlimit</replaceable></term>
<listitem>
<para>
- Set the session default for this database of the specified
- configuration variable to the given value. If
- <replaceable>value</replaceable> is <literal>DEFAULT</literal>
- or, equivalently, <literal>RESET</literal> is used, the
- database-specific variable setting is removed and the system-wide
- default
- setting will be inherited in new sessions. Use <literal>RESET
- ALL</literal> to clear all settings.
- </para>
-
- <para>
- See <xref linkend="sql-set" endterm="sql-set-title"> and <xref linkend="runtime-config">
- for more information about allowed variable names
- and values.
+ How many concurrent connections can be made
+ to this database. -1 means no limit.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable>newname</replaceable></term>
+ <term><replaceable>new_name</replaceable></term>
<listitem>
<para>
The new name of the database.
</para>
</listitem>
</varlistentry>
- </variablelist>
- </refsect1>
- <refsect1>
- <title>Diagnostics</title>
-
- <variablelist>
- <varlistentry>
- <term><computeroutput>ALTER DATABASE</computeroutput></term>
- <listitem>
- <para>
- Message returned if the alteration was successful.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><computeroutput>ERROR: database "dbname" does not exist</computeroutput></term>
- <listitem>
- <para>
- Error message returned if the specified database is not known
- to the system.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">new_owner</replaceable></term>
+ <listitem>
+ <para>
+ The new owner of the database.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_tablespace</replaceable></term>
+ <listitem>
+ <para>
+ The new default tablespace of the database.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>configuration_parameter</replaceable></term>
+ <term><replaceable>value</replaceable></term>
+ <listitem>
+ <para>
+ Set this database's session default for the specified configuration
+ parameter to the given value. If
+ <replaceable>value</replaceable> is <literal>DEFAULT</literal>
+ or, equivalently, <literal>RESET</literal> is used, the
+ database-specific setting is removed, so the system-wide default
+ setting will be inherited in new sessions. Use <literal>RESET
+ ALL</literal> to clear all database-specific settings.
+ <literal>SET FROM CURRENT</> saves the session's current value of
+ the parameter as the database-specific value.
+ </para>
+
+ <para>
+ See <xref linkend="sql-set"> and <xref linkend="runtime-config">
+ for more information about allowed parameter names
+ and values.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</refsect1>
<refsect1>
<title>Notes</title>
<para>
- Using <xref linkend="sql-alteruser" endterm="sql-alteruser-title">,
- it is also possible to tie a session default to a specific user
- rather than a database. User-specific settings override database-specific
+ It is also possible to tie a session default to a specific role
+ rather than to a database; see
+ <xref linkend="sql-alterrole">.
+ Role-specific settings override database-specific
ones if there is a conflict.
</para>
</refsect1>
<programlisting>
ALTER DATABASE test SET enable_indexscan TO off;
-</programlisting>
- </para>
+</programlisting></para>
</refsect1>
<refsect1>
<title>Compatibility</title>
-
+
<para>
The <command>ALTER DATABASE</command> statement is a
<productname>PostgreSQL</productname> extension.
<title>See Also</title>
<simplelist type="inline">
- <member><xref linkend="sql-alteruser" endterm="sql-alteruser-title"></member>
- <member><xref linkend="sql-createdatabase" endterm="sql-createdatabase-title"></member>
- <member><xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title"></member>
- <member><xref linkend="sql-set" endterm="sql-set-title"></member>
+ <member><xref linkend="sql-createdatabase"></member>
+ <member><xref linkend="sql-dropdatabase"></member>
+ <member><xref linkend="sql-set"></member>
+ <member><xref linkend="sql-createtablespace"></member>
</simplelist>
</refsect1>
</refentry>
-
-<!-- Keep this comment at the end of the file
-Local variables:
-mode: sgml
-sgml-omittag:nil
-sgml-shorttag:t
-sgml-minimize-attributes:nil
-sgml-always-quote-attributes:t
-sgml-indent-step:1
-sgml-indent-data:t
-sgml-parent-document:nil
-sgml-default-dtd-file:"../reference.ced"
-sgml-exposed-tags:nil
-sgml-local-catalogs:"/usr/lib/sgml/catalog"
-sgml-local-ecat-files:nil
-End:
--->
projects / postgresql / blobdiff