<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.19 2006/09/16 00:30:16 momjian 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>
<refpurpose>change a database</refpurpose>
</refnamediv>
- <indexterm zone="sql-alterdatabase">
- <primary>ALTER DATABASE</primary>
- </indexterm>
-
<refsynopsisdiv>
<synopsis>
ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ]
-where <replaceable class="PARAMETER">option</replaceable> can be:
+<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> SET <replaceable>parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
-ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>parameter</replaceable>
-
-ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>newname</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> 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>
<para>
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 second and third 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>
<para>
- The fourth form changes the name of the database. Only the database
+ 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
</para>
<para>
- The fifth form changes the owner of the database.
+ 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>
</varlistentry>
<varlistentry>
+ <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>
to this database. -1 means no limit.
</para>
</listitem>
- </varlistentry>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>new_name</replaceable></term>
+ <listitem>
+ <para>
+ The new name of the database.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <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>parameter</replaceable></term>
+ <term><replaceable>configuration_parameter</replaceable></term>
<term><replaceable>value</replaceable></term>
<listitem>
<para>
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" endterm="sql-set-title"> and <xref linkend="runtime-config">
+ See <xref linkend="sql-set"> and <xref linkend="runtime-config">
for more information about allowed parameter names
and values.
</para>
</listitem>
</varlistentry>
-
- <varlistentry>
- <term><replaceable>newname</replaceable></term>
- <listitem>
- <para>
- The new name of the database.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">new_owner</replaceable></term>
- <listitem>
- <para>
- The new owner of the database.
- </para>
- </listitem>
- </varlistentry>
</variablelist>
</refsect1>
<title>Notes</title>
<para>
- It is also possible to tie a session default to a specific user
+ It is also possible to tie a session default to a specific role
rather than to a database; see
- <xref linkend="sql-alteruser" endterm="sql-alteruser-title">.
- User-specific settings override database-specific
+ <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-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>
projects / postgresql / blobdiff