1<!-- 2doc/src/sgml/ref/alter_database.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="SQL-ALTERDATABASE"> 7 <indexterm zone="sql-alterdatabase"> 8 <primary>ALTER DATABASE</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle>ALTER DATABASE</refentrytitle> 13 <manvolnum>7</manvolnum> 14 <refmiscinfo>SQL - Language Statements</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname>ALTER DATABASE</refname> 19 <refpurpose>change a database</refpurpose> 20 </refnamediv> 21 22 <refsynopsisdiv> 23<synopsis> 24ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ] 25 26<phrase>where <replaceable class="PARAMETER">option</replaceable> can be:</phrase> 27 28 ALLOW_CONNECTIONS <replaceable class="PARAMETER">allowconn</replaceable> 29 CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable> 30 IS_TEMPLATE <replaceable class="PARAMETER">istemplate</replaceable> 31 32ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable> 33 34ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_USER | SESSION_USER } 35 36ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> 37 38ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT } 39ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT 40ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>configuration_parameter</replaceable> 41ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET ALL 42</synopsis> 43 </refsynopsisdiv> 44 45 <refsect1> 46 <title>Description</title> 47 48 <para> 49 <command>ALTER DATABASE</command> changes the attributes 50 of a database. 51 </para> 52 53 <para> 54 The first form changes certain per-database settings. (See below for 55 details.) Only the database owner or a superuser can change these settings. 56 </para> 57 58 <para> 59 The second form changes the name of the database. Only the database 60 owner or a superuser can rename a database; non-superuser owners must 61 also have the 62 <literal>CREATEDB</literal> privilege. The current database cannot 63 be renamed. (Connect to a different database if you need to do 64 that.) 65 </para> 66 67 <para> 68 The third form changes the owner of the database. 69 To alter the owner, you must own the database and also be a direct or 70 indirect member of the new owning role, and you must have the 71 <literal>CREATEDB</literal> privilege. 72 (Note that superusers have all these privileges automatically.) 73 </para> 74 75 <para> 76 The fourth form changes the default tablespace of the database. 77 Only the database owner or a superuser can do this; you must also have 78 create privilege for the new tablespace. 79 This command physically moves any tables or indexes in the database's old 80 default tablespace to the new tablespace. The new default tablespace 81 must be empty for this database, and no one can be connected to 82 the database. Tables and indexes in non-default tablespaces are 83 unaffected. 84 </para> 85 86 <para> 87 The remaining forms change the session default for a run-time 88 configuration variable for a <productname>PostgreSQL</productname> 89 database. Whenever a new session is subsequently started in that 90 database, the specified value becomes the session default value. 91 The database-specific default overrides whatever setting is present 92 in <filename>postgresql.conf</> or has been received from the 93 <command>postgres</command> command line. Only the database 94 owner or a superuser can change the session defaults for a 95 database. Certain variables cannot be set this way, or can only be 96 set by a superuser. 97 </para> 98 </refsect1> 99 100 <refsect1> 101 <title>Parameters</title> 102 103 <variablelist> 104 <varlistentry> 105 <term><replaceable class="PARAMETER">name</replaceable></term> 106 <listitem> 107 <para> 108 The name of the database whose attributes are to be altered. 109 </para> 110 </listitem> 111 </varlistentry> 112 113 <varlistentry> 114 <term><replaceable class="parameter">allowconn</replaceable></term> 115 <listitem> 116 <para> 117 If false then no one can connect to this database. 118 </para> 119 </listitem> 120 </varlistentry> 121 122 <varlistentry> 123 <term><replaceable class="parameter">connlimit</replaceable></term> 124 <listitem> 125 <para> 126 How many concurrent connections can be made 127 to this database. -1 means no limit. 128 </para> 129 </listitem> 130 </varlistentry> 131 132 <varlistentry> 133 <term><replaceable class="parameter">istemplate</replaceable></term> 134 <listitem> 135 <para> 136 If true, then this database can be cloned by any user with <literal>CREATEDB</literal> 137 privileges; if false, then only superusers or the owner of the 138 database can clone it. 139 </para> 140 </listitem> 141 </varlistentry> 142 143 <varlistentry> 144 <term><replaceable>new_name</replaceable></term> 145 <listitem> 146 <para> 147 The new name of the database. 148 </para> 149 </listitem> 150 </varlistentry> 151 152 <varlistentry> 153 <term><replaceable class="parameter">new_owner</replaceable></term> 154 <listitem> 155 <para> 156 The new owner of the database. 157 </para> 158 </listitem> 159 </varlistentry> 160 161 <varlistentry> 162 <term><replaceable class="parameter">new_tablespace</replaceable></term> 163 <listitem> 164 <para> 165 The new default tablespace of the database. 166 </para> 167 168 <para> 169 This form of the command cannot be executed inside a transaction block. 170 </para> 171 </listitem> 172 </varlistentry> 173 174 <varlistentry> 175 <term><replaceable>configuration_parameter</replaceable></term> 176 <term><replaceable>value</replaceable></term> 177 <listitem> 178 <para> 179 Set this database's session default for the specified configuration 180 parameter to the given value. If 181 <replaceable>value</replaceable> is <literal>DEFAULT</literal> 182 or, equivalently, <literal>RESET</literal> is used, the 183 database-specific setting is removed, so the system-wide default 184 setting will be inherited in new sessions. Use <literal>RESET 185 ALL</literal> to clear all database-specific settings. 186 <literal>SET FROM CURRENT</> saves the session's current value of 187 the parameter as the database-specific value. 188 </para> 189 190 <para> 191 See <xref linkend="sql-set"> and <xref linkend="runtime-config"> 192 for more information about allowed parameter names 193 and values. 194 </para> 195 </listitem> 196 </varlistentry> 197 </variablelist> 198 </refsect1> 199 200 <refsect1> 201 <title>Notes</title> 202 203 <para> 204 It is also possible to tie a session default to a specific role 205 rather than to a database; see 206 <xref linkend="sql-alterrole">. 207 Role-specific settings override database-specific 208 ones if there is a conflict. 209 </para> 210 </refsect1> 211 212 <refsect1> 213 <title>Examples</title> 214 215 <para> 216 To disable index scans by default in the database 217 <literal>test</literal>: 218 219<programlisting> 220ALTER DATABASE test SET enable_indexscan TO off; 221</programlisting></para> 222 </refsect1> 223 224 <refsect1> 225 <title>Compatibility</title> 226 227 <para> 228 The <command>ALTER DATABASE</command> statement is a 229 <productname>PostgreSQL</productname> extension. 230 </para> 231 </refsect1> 232 233 <refsect1> 234 <title>See Also</title> 235 236 <simplelist type="inline"> 237 <member><xref linkend="sql-createdatabase"></member> 238 <member><xref linkend="sql-dropdatabase"></member> 239 <member><xref linkend="sql-set"></member> 240 <member><xref linkend="sql-createtablespace"></member> 241 </simplelist> 242 </refsect1> 243</refentry> 244