<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Setting Parameters</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 8.3.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Server Configuration" HREF="runtime-config.html"><LINK REL="PREVIOUS" TITLE="Server Configuration" HREF="runtime-config.html"><LINK REL="NEXT" TITLE="File Locations" HREF="runtime-config-file-locations.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="2010-05-14T03:43:41"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.3.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="runtime-config.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="runtime-config.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 18. Server Configuration</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="runtime-config.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="runtime-config-file-locations.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="CONFIG-SETTING" >18.1. Setting Parameters</A ></H1 ><P > All parameter names are case-insensitive. Every parameter takes a value of one of four types: Boolean, integer, floating point, or string. Boolean values can be written as <TT CLASS="LITERAL" >ON</TT >, <TT CLASS="LITERAL" >OFF</TT >, <TT CLASS="LITERAL" >TRUE</TT >, <TT CLASS="LITERAL" >FALSE</TT >, <TT CLASS="LITERAL" >YES</TT >, <TT CLASS="LITERAL" >NO</TT >, <TT CLASS="LITERAL" >1</TT >, <TT CLASS="LITERAL" >0</TT > (all case-insensitive) or any unambiguous prefix of these. </P ><P > Some settings specify a memory or time value. Each of these has an implicit unit, which is either kilobytes, blocks (typically eight kilobytes), milliseconds, seconds, or minutes. Default units can be queried by referencing <TT CLASS="LITERAL" >pg_settings.unit</TT >. For convenience, a different unit can also be specified explicitly. Valid memory units are <TT CLASS="LITERAL" >kB</TT > (kilobytes), <TT CLASS="LITERAL" >MB</TT > (megabytes), and <TT CLASS="LITERAL" >GB</TT > (gigabytes); valid time units are <TT CLASS="LITERAL" >ms</TT > (milliseconds), <TT CLASS="LITERAL" >s</TT > (seconds), <TT CLASS="LITERAL" >min</TT > (minutes), <TT CLASS="LITERAL" >h</TT > (hours), and <TT CLASS="LITERAL" >d</TT > (days). Note that the multiplier for memory units is 1024, not 1000. </P ><P > One way to set these parameters is to edit the file <TT CLASS="FILENAME" >postgresql.conf</TT ><A NAME="AEN22528" ></A >, which is normally kept in the data directory. (<SPAN CLASS="APPLICATION" >initdb</SPAN > installs a default copy there.) An example of what this file might look like is: </P><PRE CLASS="PROGRAMLISTING" ># This is a comment log_connections = yes log_destination = 'syslog' search_path = '"$user", public' shared_buffers = 128MB</PRE ><P> One parameter is specified per line. The equal sign between name and value is optional. Whitespace is insignificant and blank lines are ignored. Hash marks (<TT CLASS="LITERAL" >#</TT >) introduce comments anywhere. Parameter values that are not simple identifiers or numbers must be single-quoted. To embed a single quote in a parameter value, write either two quotes (preferred) or backslash-quote. </P ><P > <A NAME="AEN22534" ></A > In addition to parameter settings, the <TT CLASS="FILENAME" >postgresql.conf</TT > file can contain <I CLASS="FIRSTTERM" >include directives</I >, which specify another file to read and process as if it were inserted into the configuration file at this point. Include directives simply look like: </P><PRE CLASS="PROGRAMLISTING" >include 'filename'</PRE ><P> If the file name is not an absolute path, it is taken as relative to the directory containing the referencing configuration file. Inclusions can be nested. </P ><P > <A NAME="AEN22542" ></A > The configuration file is reread whenever the main server process receives a <SPAN CLASS="SYSTEMITEM" >SIGHUP</SPAN > signal (which is most easily sent by means of <TT CLASS="LITERAL" >pg_ctl reload</TT >). The main server process also propagates this signal to all currently running server processes so that existing sessions also get the new value. Alternatively, you can send the signal to a single server process directly. Some parameters can only be set at server start; any changes to their entries in the configuration file will be ignored until the server is restarted. </P ><P > A second way to set these configuration parameters is to give them as a command-line option to the <TT CLASS="COMMAND" >postgres</TT > command, such as: </P><PRE CLASS="PROGRAMLISTING" >postgres -c log_connections=yes -c log_destination='syslog'</PRE ><P> Command-line options override any conflicting settings in <TT CLASS="FILENAME" >postgresql.conf</TT >. Note that this means you won't be able to change the value on-the-fly by editing <TT CLASS="FILENAME" >postgresql.conf</TT >, so while the command-line method might be convenient, it can cost you flexibility later. </P ><P > Occasionally it is useful to give a command line option to one particular session only. The environment variable <TT CLASS="ENVAR" >PGOPTIONS</TT > can be used for this purpose on the client side: </P><PRE CLASS="PROGRAMLISTING" >env PGOPTIONS='-c geqo=off' psql</PRE ><P> (This works for any <SPAN CLASS="APPLICATION" >libpq</SPAN >-based client application, not just <SPAN CLASS="APPLICATION" >psql</SPAN >.) Note that this won't work for parameters that are fixed when the server is started or that must be specified in <TT CLASS="FILENAME" >postgresql.conf</TT >. </P ><P > Furthermore, it is possible to assign a set of parameter settings to a user or a database. Whenever a session is started, the default settings for the user and database involved are loaded. The commands <A HREF="sql-alteruser.html" ><I >ALTER USER</I ></A > and <A HREF="sql-alterdatabase.html" ><I >ALTER DATABASE</I ></A >, respectively, are used to configure these settings. Per-database settings override anything received from the <TT CLASS="COMMAND" >postgres</TT > command-line or the configuration file, and in turn are overridden by per-user settings; both are overridden by per-session settings. </P ><P > Some parameters can be changed in individual <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > sessions with the <A HREF="sql-set.html" ><I >SET</I ></A > command, for example: </P><PRE CLASS="SCREEN" >SET ENABLE_SEQSCAN TO OFF;</PRE ><P> If <TT CLASS="COMMAND" >SET</TT > is allowed, it overrides all other sources of values for the parameter. Some parameters cannot be changed via <TT CLASS="COMMAND" >SET</TT >: for example, if they control behavior that cannot be changed without restarting the entire <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > server. Also, some parameters can be modified via <TT CLASS="COMMAND" >SET</TT > or <TT CLASS="COMMAND" >ALTER</TT > by superusers, but not by ordinary users. </P ><P > The <A HREF="sql-show.html" ><I >SHOW</I ></A > command allows inspection of the current values of all parameters. </P ><P > The virtual table <TT CLASS="STRUCTNAME" >pg_settings</TT > (described in <A HREF="view-pg-settings.html" >Section 44.51</A >) also allows displaying and updating session run-time parameters. It is equivalent to <TT CLASS="COMMAND" >SHOW</TT > and <TT CLASS="COMMAND" >SET</TT >, but can be more convenient to use because it can be joined with other tables, or selected from using any desired selection condition. </P ></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="runtime-config.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="runtime-config-file-locations.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Server Configuration</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="runtime-config.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >File Locations</TD ></TR ></TABLE ></DIV ></BODY ></HTML >