<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >ALTER SYSTEM</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 9.6.19 Documentation" HREF="index.html"><LINK REL="UP" TITLE="SQL Commands" HREF="sql-commands.html"><LINK REL="PREVIOUS" TITLE="ALTER SERVER" HREF="sql-alterserver.html"><LINK REL="NEXT" TITLE="ALTER TABLE" HREF="sql-altertable.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2020-09-02T07:27:19"></HEAD ><BODY CLASS="REFENTRY" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="4" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 9.6.19 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="ALTER SERVER" HREF="sql-alterserver.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="sql-commands.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="ALTER TABLE" HREF="sql-altertable.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><H1 ><A NAME="SQL-ALTERSYSTEM" ></A >ALTER SYSTEM</H1 ><DIV CLASS="REFNAMEDIV" ><A NAME="AEN75707" ></A ><H2 >Name</H2 >ALTER SYSTEM -- change a server configuration parameter</DIV ><DIV CLASS="REFSYNOPSISDIV" ><A NAME="AEN75710" ></A ><H2 >Synopsis</H2 ><PRE CLASS="SYNOPSIS" >ALTER SYSTEM SET <TT CLASS="REPLACEABLE" ><I >configuration_parameter</I ></TT > { TO | = } { <TT CLASS="REPLACEABLE" ><I >value</I ></TT > | '<TT CLASS="REPLACEABLE" ><I >value</I ></TT >' | DEFAULT } ALTER SYSTEM RESET <TT CLASS="REPLACEABLE" ><I >configuration_parameter</I ></TT > ALTER SYSTEM RESET ALL</PRE ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN75716" ></A ><H2 >Description</H2 ><P > <TT CLASS="COMMAND" >ALTER SYSTEM</TT > 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 <TT CLASS="FILENAME" >postgresql.conf</TT > file. <TT CLASS="COMMAND" >ALTER SYSTEM</TT > writes the given parameter setting to the <TT CLASS="FILENAME" >postgresql.auto.conf</TT > file, which is read in addition to <TT CLASS="FILENAME" >postgresql.conf</TT >. Setting a parameter to <TT CLASS="LITERAL" >DEFAULT</TT >, or using the <TT CLASS="COMMAND" >RESET</TT > variant, removes that configuration entry from the <TT CLASS="FILENAME" >postgresql.auto.conf</TT > file. Use <TT CLASS="LITERAL" >RESET ALL</TT > to remove all such configuration entries. </P ><P > Values set with <TT CLASS="COMMAND" >ALTER SYSTEM</TT > 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 <CODE CLASS="FUNCTION" >pg_reload_conf()</CODE >, running <TT CLASS="LITERAL" >pg_ctl reload</TT >, or sending a <SPAN CLASS="SYSTEMITEM" >SIGHUP</SPAN > signal to the main server process. </P ><P > Only superusers can use <TT CLASS="COMMAND" >ALTER SYSTEM</TT >. 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. </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN75735" ></A ><H2 >Parameters</H2 ><P ></P ><DIV CLASS="VARIABLELIST" ><DL ><DT ><TT CLASS="REPLACEABLE" ><I >configuration_parameter</I ></TT ></DT ><DD ><P > Name of a settable configuration parameter. Available parameters are documented in <A HREF="runtime-config.html" >Chapter 19</A >. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >value</I ></TT ></DT ><DD ><P > 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. <TT CLASS="LITERAL" >DEFAULT</TT > can be written to specify removing the parameter and its value from <TT CLASS="FILENAME" >postgresql.auto.conf</TT >. </P ></DD ></DL ></DIV ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN75751" ></A ><H2 >Notes</H2 ><P > This command can't be used to set <A HREF="runtime-config-file-locations.html#GUC-DATA-DIRECTORY" >data_directory</A >, nor parameters that are not allowed in <TT CLASS="FILENAME" >postgresql.conf</TT > (e.g., <A HREF="runtime-config-preset.html" >preset options</A >). </P ><P > See <A HREF="config-setting.html" >Section 19.1</A > for other ways to set the parameters. </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN75759" ></A ><H2 >Examples</H2 ><P > Set the <TT CLASS="LITERAL" >wal_level</TT >: </P><PRE CLASS="PROGRAMLISTING" >ALTER SYSTEM SET wal_level = replica;</PRE ><P> </P ><P > Undo that, restoring whatever setting was effective in <TT CLASS="FILENAME" >postgresql.conf</TT >: </P><PRE CLASS="PROGRAMLISTING" >ALTER SYSTEM RESET wal_level;</PRE ><P> </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN75767" ></A ><H2 >Compatibility</H2 ><P > The <TT CLASS="COMMAND" >ALTER SYSTEM</TT > statement is a <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > extension. </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN75772" ></A ><H2 >See Also</H2 ><A HREF="sql-set.html" >SET</A >, <A HREF="sql-show.html" >SHOW</A ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="sql-alterserver.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="sql-altertable.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >ALTER SERVER</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="sql-commands.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >ALTER TABLE</TD ></TR ></TABLE ></DIV ></BODY ></HTML >