doc/src/sgml/ref/alter_system.sgml

   1 <!--
   2 doc/src/sgml/ref/alter_system.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-altersystem">
   7  <indexterm zone="sql-altersystem">
   8   <primary>ALTER SYSTEM</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>ALTER SYSTEM</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>ALTER SYSTEM</refname>
  19   <refpurpose>change a server configuration parameter</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 ALTER SYSTEM SET <replaceable class="parameter">configuration_parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> [, ...] | DEFAULT }
  25
  26 ALTER SYSTEM RESET <replaceable class="parameter">configuration_parameter</replaceable>
  27 ALTER SYSTEM RESET ALL
  28 </synopsis>
  29  </refsynopsisdiv>
  30
  31  <refsect1>
  32   <title>Description</title>
  33
  34   <para>
  35    <command>ALTER SYSTEM</command> is used for changing server configuration
  36    parameters across the entire database cluster.  It can be more convenient
  37    than the traditional method of manually editing
  38    the <filename>postgresql.conf</filename> file.
  39    <command>ALTER SYSTEM</command> writes the given parameter setting to
  40    the <filename>postgresql.auto.conf</filename> file, which is read in
  41    addition to <filename>postgresql.conf</filename>.
  42    Setting a parameter to <literal>DEFAULT</literal>, or using the
  43    <command>RESET</command> variant, removes that configuration entry from the
  44    <filename>postgresql.auto.conf</filename> file. Use <literal>RESET
  45    ALL</literal> to remove all such configuration entries.
  46   </para>
  47
  48   <para>
  49    Values set with <command>ALTER SYSTEM</command> will be effective after
  50    the next server configuration reload, or after the next server restart
  51    in the case of parameters that can only be changed at server start.
  52    A server configuration reload can be commanded by calling the SQL
  53    function <function>pg_reload_conf()</function>, running <literal>pg_ctl reload</literal>,
  54    or sending a <systemitem>SIGHUP</systemitem> signal to the main server process.
  55   </para>
  56
  57   <para>
  58    Only superusers and users granted <literal>ALTER SYSTEM</literal> privilege
  59    on a parameter can change it using <command>ALTER SYSTEM</command>.  Also, since
  60    this command acts directly on the file system and cannot be rolled back,
  61    it is not allowed inside a transaction block or function.
  62   </para>
  63  </refsect1>
  64
  65  <refsect1>
  66   <title>Parameters</title>
  67
  68   <variablelist>
  69    <varlistentry>
  70     <term><replaceable class="parameter">configuration_parameter</replaceable></term>
  71     <listitem>
  72      <para>
  73       Name of a settable configuration parameter.  Available parameters are
  74       documented in <xref linkend="runtime-config"/>.
  75      </para>
  76     </listitem>
  77    </varlistentry>
  78
  79    <varlistentry>
  80     <term><replaceable class="parameter">value</replaceable></term>
  81     <listitem>
  82      <para>
  83       New value of the parameter.  Values can be specified as string
  84       constants, identifiers, numbers, or comma-separated lists of
  85       these, as appropriate for the particular parameter.
  86       Values that are neither numbers nor valid identifiers must be quoted.
  87       <literal>DEFAULT</literal> can be written to specify removing the
  88       parameter and its value from <filename>postgresql.auto.conf</filename>.
  89      </para>
  90
  91      <para>
  92       For some list-accepting parameters, quoted values will produce
  93       double-quoted output to preserve whitespace and commas; for others,
  94       double-quotes must be used inside single-quoted strings to get
  95       this effect.
  96      </para>
  97     </listitem>
  98    </varlistentry>
  99   </variablelist>
 100  </refsect1>
 101
 102  <refsect1>
 103   <title>Notes</title>
 104
 105   <para>
 106    This command can't be used to set <xref linkend="guc-data-directory"/>,
 107    <xref linkend="guc-allow-alter-system"/>,
 108    nor parameters that are not allowed in <filename>postgresql.conf</filename>
 109    (e.g., <link linkend="runtime-config-preset">preset options</link>).
 110   </para>
 111
 112   <para>
 113    See <xref linkend="config-setting"/> for other ways to set the parameters.
 114   </para>
 115
 116   <para>
 117    <literal>ALTER SYSTEM</literal> can be disabled by setting
 118    <xref linkend="guc-allow-alter-system"/> to <literal>off</literal>, but this
 119    is not a security mechanism (as explained in detail in the documentation for
 120    this parameter).
 121   </para>
 122  </refsect1>
 123
 124  <refsect1>
 125   <title>Examples</title>
 126
 127   <para>
 128    Set the <literal>wal_level</literal>:
 129 <programlisting>
 130 ALTER SYSTEM SET wal_level = replica;
 131 </programlisting>
 132   </para>
 133
 134   <para>
 135    Undo that, restoring whatever setting was effective
 136    in <filename>postgresql.conf</filename>:
 137 <programlisting>
 138 ALTER SYSTEM RESET wal_level;
 139 </programlisting></para>
 140  </refsect1>
 141
 142  <refsect1>
 143   <title>Compatibility</title>
 144
 145   <para>
 146    The <command>ALTER SYSTEM</command> statement is a
 147    <productname>PostgreSQL</productname> extension.
 148   </para>
 149  </refsect1>
 150
 151  <refsect1>
 152   <title>See Also</title>
 153
 154   <simplelist type="inline">
 155    <member><xref linkend="sql-set"/></member>
 156    <member><xref linkend="sql-show"/></member>
 157   </simplelist>
 158  </refsect1>
 159
 160 </refentry>