diff options
Diffstat (limited to 'doc/src/sgml/html/config-setting.html')
| -rw-r--r-- | doc/src/sgml/html/config-setting.html | 336 |
1 files changed, 336 insertions, 0 deletions
diff --git a/doc/src/sgml/html/config-setting.html b/doc/src/sgml/html/config-setting.html new file mode 100644 index 0000000..a5d68b7 --- /dev/null +++ b/doc/src/sgml/html/config-setting.html @@ -0,0 +1,336 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>20.1. Setting Parameters</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config.html" title="Chapter 20. Server Configuration" /><link rel="next" href="runtime-config-file-locations.html" title="20.2. File Locations" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">20.1. Setting Parameters</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config.html" title="Chapter 20. Server Configuration">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html" title="20.2. File Locations">Next</a></td></tr></table><hr /></div><div class="sect1" id="CONFIG-SETTING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">20.1. Setting Parameters</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-NAMES-VALUES">20.1.1. Parameter Names and Values</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE">20.1.2. Parameter Interaction via the Configuration File</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION">20.1.3. Parameter Interaction via SQL</a></span></dt><dt><span class="sect2"><a href="config-setting.html#id-1.6.7.4.5">20.1.4. Parameter Interaction via the Shell</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-INCLUDES">20.1.5. Managing Configuration File Contents</a></span></dt></dl></div><div class="sect2" id="CONFIG-SETTING-NAMES-VALUES"><div class="titlepage"><div><div><h3 class="title">20.1.1. Parameter Names and Values</h3></div></div></div><p> + All parameter names are case-insensitive. Every parameter takes a + value of one of five types: boolean, string, integer, floating point, + or enumerated (enum). The type determines the syntax for setting the + parameter: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + <span class="emphasis"><em>Boolean:</em></span> + Values can be written as + <code class="literal">on</code>, + <code class="literal">off</code>, + <code class="literal">true</code>, + <code class="literal">false</code>, + <code class="literal">yes</code>, + <code class="literal">no</code>, + <code class="literal">1</code>, + <code class="literal">0</code> + (all case-insensitive) or any unambiguous prefix of one of these. + </p></li><li class="listitem"><p> + <span class="emphasis"><em>String:</em></span> + In general, enclose the value in single quotes, doubling any single + quotes within the value. Quotes can usually be omitted if the value + is a simple number or identifier, however. + (Values that match an SQL keyword require quoting in some contexts.) + </p></li><li class="listitem"><p> + <span class="emphasis"><em>Numeric (integer and floating point):</em></span> + Numeric parameters can be specified in the customary integer and + floating-point formats; fractional values are rounded to the nearest + integer if the parameter is of integer type. Integer parameters + additionally accept hexadecimal input (beginning + with <code class="literal">0x</code>) and octal input (beginning + with <code class="literal">0</code>), but these formats cannot have a fraction. + Do not use thousands separators. + Quotes are not required, except for hexadecimal input. + </p></li><li class="listitem"><p> + <span class="emphasis"><em>Numeric with Unit:</em></span> + Some numeric parameters have an implicit unit, because they describe + quantities of memory or time. The unit might be bytes, kilobytes, blocks + (typically eight kilobytes), milliseconds, seconds, or minutes. + An unadorned numeric value for one of these settings will use the + setting's default unit, which can be learned from + <code class="structname">pg_settings</code>.<code class="structfield">unit</code>. + For convenience, settings can be given with a unit specified explicitly, + for example <code class="literal">'120 ms'</code> for a time value, and they will be + converted to whatever the parameter's actual unit is. Note that the + value must be written as a string (with quotes) to use this feature. + The unit name is case-sensitive, and there can be whitespace between + the numeric value and the unit. + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p> + Valid memory units are <code class="literal">B</code> (bytes), + <code class="literal">kB</code> (kilobytes), + <code class="literal">MB</code> (megabytes), <code class="literal">GB</code> + (gigabytes), and <code class="literal">TB</code> (terabytes). + The multiplier for memory units is 1024, not 1000. + </p></li><li class="listitem"><p> + Valid time units are + <code class="literal">us</code> (microseconds), + <code class="literal">ms</code> (milliseconds), + <code class="literal">s</code> (seconds), <code class="literal">min</code> (minutes), + <code class="literal">h</code> (hours), and <code class="literal">d</code> (days). + </p></li></ul></div><p> + + If a fractional value is specified with a unit, it will be rounded + to a multiple of the next smaller unit if there is one. + For example, <code class="literal">30.1 GB</code> will be converted + to <code class="literal">30822 MB</code> not <code class="literal">32319628902 B</code>. + If the parameter is of integer type, a final rounding to integer + occurs after any unit conversion. + </p></li><li class="listitem"><p> + <span class="emphasis"><em>Enumerated:</em></span> + Enumerated-type parameters are written in the same way as string + parameters, but are restricted to have one of a limited set of + values. The values allowable for such a parameter can be found from + <code class="structname">pg_settings</code>.<code class="structfield">enumvals</code>. + Enum parameter values are case-insensitive. + </p></li></ul></div></div><div class="sect2" id="CONFIG-SETTING-CONFIGURATION-FILE"><div class="titlepage"><div><div><h3 class="title">20.1.2. Parameter Interaction via the Configuration File</h3></div></div></div><p> + The most fundamental way to set these parameters is to edit the file + <code class="filename">postgresql.conf</code><a id="id-1.6.7.4.3.2.2" class="indexterm"></a>, + which is normally kept in the data directory. A default copy is + installed when the database cluster directory is initialized. + 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 (except within a quoted + parameter value) and blank lines are + ignored. Hash marks (<code class="literal">#</code>) designate the remainder + of the line as a comment. 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. + If the file contains multiple entries for the same parameter, + all but the last one are ignored. + </p><p> + Parameters set in this way provide default values for the cluster. + The settings seen by active sessions will be these values unless they + are overridden. The following sections describe ways in which the + administrator or user can override these defaults. + </p><p> + <a id="id-1.6.7.4.3.4.1" class="indexterm"></a> + The configuration file is reread whenever the main server process + receives a <span class="systemitem">SIGHUP</span> signal; this signal is most easily + sent by running <code class="literal">pg_ctl reload</code> from the command line or by + calling the SQL function <code class="function">pg_reload_conf()</code>. The main + server process also propagates this signal to all currently running + server processes, so that existing sessions also adopt the new values + (this will happen after they complete any currently-executing client + command). 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. + Invalid parameter settings in the configuration file are likewise + ignored (but logged) during <span class="systemitem">SIGHUP</span> processing. + </p><p> + In addition to <code class="filename">postgresql.conf</code>, + a <span class="productname">PostgreSQL</span> data directory contains a file + <code class="filename">postgresql.auto.conf</code><a id="id-1.6.7.4.3.5.4" class="indexterm"></a>, + which has the same format as <code class="filename">postgresql.conf</code> but + is intended to be edited automatically, not manually. This file holds + settings provided through the <a class="link" href="sql-altersystem.html" title="ALTER SYSTEM"><code class="command">ALTER SYSTEM</code></a> command. + This file is read whenever <code class="filename">postgresql.conf</code> is, + and its settings take effect in the same way. Settings + in <code class="filename">postgresql.auto.conf</code> override those + in <code class="filename">postgresql.conf</code>. + </p><p> + External tools may also + modify <code class="filename">postgresql.auto.conf</code>. It is not + recommended to do this while the server is running, since a + concurrent <code class="command">ALTER SYSTEM</code> command could overwrite + such changes. Such tools might simply append new settings to the end, + or they might choose to remove duplicate settings and/or comments + (as <code class="command">ALTER SYSTEM</code> will). + </p><p> + The system view + <a class="link" href="view-pg-file-settings.html" title="54.7. pg_file_settings"><code class="structname">pg_file_settings</code></a> + can be helpful for pre-testing changes to the configuration files, or for + diagnosing problems if a <span class="systemitem">SIGHUP</span> signal did not have the + desired effects. + </p></div><div class="sect2" id="CONFIG-SETTING-SQL-COMMAND-INTERACTION"><div class="titlepage"><div><div><h3 class="title">20.1.3. Parameter Interaction via SQL</h3></div></div></div><p> + <span class="productname">PostgreSQL</span> provides three SQL + commands to establish configuration defaults. + The already-mentioned <code class="command">ALTER SYSTEM</code> command + provides an SQL-accessible means of changing global defaults; it is + functionally equivalent to editing <code class="filename">postgresql.conf</code>. + In addition, there are two commands that allow setting of defaults + on a per-database or per-role basis: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + The <a class="link" href="sql-alterdatabase.html" title="ALTER DATABASE"><code class="command">ALTER DATABASE</code></a> command allows global + settings to be overridden on a per-database basis. + </p></li><li class="listitem"><p> + The <a class="link" href="sql-alterrole.html" title="ALTER ROLE"><code class="command">ALTER ROLE</code></a> command allows both global and + per-database settings to be overridden with user-specific values. + </p></li></ul></div><p> + Values set with <code class="command">ALTER DATABASE</code> and <code class="command">ALTER ROLE</code> + are applied only when starting a fresh database session. They + override values obtained from the configuration files or server + command line, and constitute defaults for the rest of the session. + Note that some settings cannot be changed after server start, and + so cannot be set with these commands (or the ones listed below). + </p><p> + Once a client is connected to the database, <span class="productname">PostgreSQL</span> + provides two additional SQL commands (and equivalent functions) to + interact with session-local configuration settings: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + The <a class="link" href="sql-show.html" title="SHOW"><code class="command">SHOW</code></a> command allows inspection of the + current value of any parameter. The corresponding SQL function is + <code class="function">current_setting(setting_name text)</code> + (see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET" title="9.27.1. Configuration Settings Functions">Section 9.27.1</a>). + </p></li><li class="listitem"><p> + The <a class="link" href="sql-set.html" title="SET"><code class="command">SET</code></a> command allows modification of the + current value of those parameters that can be set locally to a + session; it has no effect on other sessions. + Many parameters can be set this way by any user, but some can + only be set by superusers and users who have been + granted <code class="literal">SET</code> privilege on that parameter. + The corresponding SQL function is + <code class="function">set_config(setting_name, new_value, is_local)</code> + (see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET" title="9.27.1. Configuration Settings Functions">Section 9.27.1</a>). + </p></li></ul></div><p> + In addition, the system view <a class="link" href="view-pg-settings.html" title="54.24. pg_settings"><code class="structname">pg_settings</code></a> can be + used to view and change session-local values: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + Querying this view is similar to using <code class="command">SHOW ALL</code> but + provides more detail. It is also more flexible, since it's possible + to specify filter conditions or join against other relations. + </p></li><li class="listitem"><p> + Using <code class="command">UPDATE</code> on this view, specifically + updating the <code class="structname">setting</code> column, is the equivalent + of issuing <code class="command">SET</code> commands. For example, the equivalent of +</p><pre class="programlisting"> +SET configuration_parameter TO DEFAULT; +</pre><p> + is: +</p><pre class="programlisting"> +UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter'; +</pre><p> + </p></li></ul></div></div><div class="sect2" id="id-1.6.7.4.5"><div class="titlepage"><div><div><h3 class="title">20.1.4. Parameter Interaction via the Shell</h3></div></div></div><p> + In addition to setting global defaults or attaching + overrides at the database or role level, you can pass settings to + <span class="productname">PostgreSQL</span> via shell facilities. + Both the server and <span class="application">libpq</span> client library + accept parameter values via the shell. + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + During server startup, parameter settings can be + passed to the <code class="command">postgres</code> command via the + <code class="option">-c</code> command-line parameter. For example, +</p><pre class="programlisting"> +postgres -c log_connections=yes -c log_destination='syslog' +</pre><p> + Settings provided in this way override those set via + <code class="filename">postgresql.conf</code> or <code class="command">ALTER SYSTEM</code>, + so they cannot be changed globally without restarting the server. + </p></li><li class="listitem"><p> + When starting a client session via <span class="application">libpq</span>, + parameter settings can be + specified using the <code class="envar">PGOPTIONS</code> environment variable. + Settings established in this way constitute defaults for the life + of the session, but do not affect other sessions. + For historical reasons, the format of <code class="envar">PGOPTIONS</code> is + similar to that used when launching the <code class="command">postgres</code> + command; specifically, the <code class="option">-c</code> flag must be specified. + For example, +</p><pre class="programlisting"> +env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql +</pre><p> + </p><p> + Other clients and libraries might provide their own mechanisms, + via the shell or otherwise, that allow the user to alter session + settings without direct use of SQL commands. + </p></li></ul></div></div><div class="sect2" id="CONFIG-INCLUDES"><div class="titlepage"><div><div><h3 class="title">20.1.5. Managing Configuration File Contents</h3></div></div></div><p> + <span class="productname">PostgreSQL</span> provides several features for breaking + down complex <code class="filename">postgresql.conf</code> files into sub-files. + These features are especially useful when managing multiple servers + with related, but not identical, configurations. + </p><p> + <a id="id-1.6.7.4.6.3.1" class="indexterm"></a> + In addition to individual parameter settings, + the <code class="filename">postgresql.conf</code> file can contain <em class="firstterm">include + directives</em>, which specify another file to read and process as if + it were inserted into the configuration file at this point. This + feature allows a configuration file to be divided into physically + separate parts. 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 id="id-1.6.7.4.6.4.1" class="indexterm"></a> + There is also an <code class="literal">include_if_exists</code> directive, which acts + the same as the <code class="literal">include</code> directive, except + when the referenced file does not exist or cannot be read. A regular + <code class="literal">include</code> will consider this an error condition, but + <code class="literal">include_if_exists</code> merely logs a message and continues + processing the referencing configuration file. + </p><p> + <a id="id-1.6.7.4.6.5.1" class="indexterm"></a> + The <code class="filename">postgresql.conf</code> file can also contain + <code class="literal">include_dir</code> directives, which specify an entire + directory of configuration files to include. These look like +</p><pre class="programlisting"> +include_dir 'directory' +</pre><p> + Non-absolute directory names are taken as relative to the directory + containing the referencing configuration file. Within the specified + directory, only non-directory files whose names end with the + suffix <code class="literal">.conf</code> will be included. File names that + start with the <code class="literal">.</code> character are also ignored, to + prevent mistakes since such files are hidden on some platforms. Multiple + files within an include directory are processed in file name order + (according to C locale rules, i.e., numbers before letters, and + uppercase letters before lowercase ones). + </p><p> + Include files or directories can be used to logically separate portions + of the database configuration, rather than having a single large + <code class="filename">postgresql.conf</code> file. Consider a company that has two + database servers, each with a different amount of memory. There are + likely elements of the configuration both will share, for things such + as logging. But memory-related parameters on the server will vary + between the two. And there might be server specific customizations, + too. One way to manage this situation is to break the custom + configuration changes for your site into three files. You could add + this to the end of your <code class="filename">postgresql.conf</code> file to include + them: +</p><pre class="programlisting"> +include 'shared.conf' +include 'memory.conf' +include 'server.conf' +</pre><p> + All systems would have the same <code class="filename">shared.conf</code>. Each + server with a particular amount of memory could share the + same <code class="filename">memory.conf</code>; you might have one for all servers + with 8GB of RAM, another for those having 16GB. And + finally <code class="filename">server.conf</code> could have truly server-specific + configuration information in it. + </p><p> + Another possibility is to create a configuration file directory and + put this information into files there. For example, a <code class="filename">conf.d</code> + directory could be referenced at the end of <code class="filename">postgresql.conf</code>: +</p><pre class="programlisting"> +include_dir 'conf.d' +</pre><p> + Then you could name the files in the <code class="filename">conf.d</code> directory + like this: +</p><pre class="programlisting"> +00shared.conf +01memory.conf +02server.conf +</pre><p> + This naming convention establishes a clear order in which these + files will be loaded. This is important because only the last + setting encountered for a particular parameter while the server is + reading configuration files will be used. In this example, + something set in <code class="filename">conf.d/02server.conf</code> would override a + value set in <code class="filename">conf.d/01memory.conf</code>. + </p><p> + You might instead use this approach to naming the files + descriptively: +</p><pre class="programlisting"> +00shared.conf +01memory-8GB.conf +02server-foo.conf +</pre><p> + This sort of arrangement gives a unique name for each configuration file + variation. This can help eliminate ambiguity when several servers have + their configurations all stored in one place, such as in a version + control repository. (Storing database configuration files under version + control is another good practice to consider.) + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config.html" title="Chapter 20. Server Configuration">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html" title="20.2. File Locations">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 20. Server Configuration </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.2. File Locations</td></tr></table></div></body></html>
\ No newline at end of file |