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