codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 16.3 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 <a href="#CONFIG-SETTING" class="id_link">#</a></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">20.1.3. Parameter Interaction via SQL</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-SHELL">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 <a href="#CONFIG-SETTING-NAMES-VALUES" class="id_link">#</a></h3></div></div></div><p>3 All parameter names are case-insensitive. Every parameter takes a4 value of one of five types: boolean, string, integer, floating point,5 or enumerated (enum). The type determines the syntax for setting the6 parameter:7 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>8 <span class="emphasis"><em>Boolean:</em></span>9 Values can be written as10 <code class="literal">on</code>,11 <code class="literal">off</code>,12 <code class="literal">true</code>,13 <code class="literal">false</code>,14 <code class="literal">yes</code>,15 <code class="literal">no</code>,16 <code class="literal">1</code>,17 <code class="literal">0</code>18 (all case-insensitive) or any unambiguous prefix of one of these.19 </p></li><li class="listitem"><p>20 <span class="emphasis"><em>String:</em></span>21 In general, enclose the value in single quotes, doubling any single22 quotes within the value. Quotes can usually be omitted if the value23 is a simple number or identifier, however.24 (Values that match an SQL keyword require quoting in some contexts.)25 </p></li><li class="listitem"><p>26 <span class="emphasis"><em>Numeric (integer and floating point):</em></span>27 Numeric parameters can be specified in the customary integer and28 floating-point formats; fractional values are rounded to the nearest29 integer if the parameter is of integer type. Integer parameters30 additionally accept hexadecimal input (beginning31 with <code class="literal">0x</code>) and octal input (beginning32 with <code class="literal">0</code>), but these formats cannot have a fraction.33 Do not use thousands separators.34 Quotes are not required, except for hexadecimal input.35 </p></li><li class="listitem"><p>36 <span class="emphasis"><em>Numeric with Unit:</em></span>37 Some numeric parameters have an implicit unit, because they describe38 quantities of memory or time. The unit might be bytes, kilobytes, blocks39 (typically eight kilobytes), milliseconds, seconds, or minutes.40 An unadorned numeric value for one of these settings will use the41 setting's default unit, which can be learned from42 <code class="structname">pg_settings</code>.<code class="structfield">unit</code>.43 For convenience, settings can be given with a unit specified explicitly,44 for example <code class="literal">'120 ms'</code> for a time value, and they will be45 converted to whatever the parameter's actual unit is. Note that the46 value must be written as a string (with quotes) to use this feature.47 The unit name is case-sensitive, and there can be whitespace between48 the numeric value and the unit.49 50 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>51 Valid memory units are <code class="literal">B</code> (bytes),52 <code class="literal">kB</code> (kilobytes),53 <code class="literal">MB</code> (megabytes), <code class="literal">GB</code>54 (gigabytes), and <code class="literal">TB</code> (terabytes).55 The multiplier for memory units is 1024, not 1000.56 </p></li><li class="listitem"><p>57 Valid time units are58 <code class="literal">us</code> (microseconds),59 <code class="literal">ms</code> (milliseconds),60 <code class="literal">s</code> (seconds), <code class="literal">min</code> (minutes),61 <code class="literal">h</code> (hours), and <code class="literal">d</code> (days).62 </p></li></ul></div><p>63 64 If a fractional value is specified with a unit, it will be rounded65 to a multiple of the next smaller unit if there is one.66 For example, <code class="literal">30.1 GB</code> will be converted67 to <code class="literal">30822 MB</code> not <code class="literal">32319628902 B</code>.68 If the parameter is of integer type, a final rounding to integer69 occurs after any unit conversion.70 </p></li><li class="listitem"><p>71 <span class="emphasis"><em>Enumerated:</em></span>72 Enumerated-type parameters are written in the same way as string73 parameters, but are restricted to have one of a limited set of74 values. The values allowable for such a parameter can be found from75 <code class="structname">pg_settings</code>.<code class="structfield">enumvals</code>.76 Enum parameter values are case-insensitive.77 </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 <a href="#CONFIG-SETTING-CONFIGURATION-FILE" class="id_link">#</a></h3></div></div></div><p>78 The most fundamental way to set these parameters is to edit the file79 <code class="filename">postgresql.conf</code><a id="id-1.6.7.4.3.2.2" class="indexterm"></a>,80 which is normally kept in the data directory. A default copy is81 installed when the database cluster directory is initialized.82 An example of what this file might look like is:83</p><pre class="programlisting">84# This is a comment85log_connections = yes86log_destination = 'syslog'87search_path = '"$user", public'88shared_buffers = 128MB89</pre><p>90 One parameter is specified per line. The equal sign between name and91 value is optional. Whitespace is insignificant (except within a quoted92 parameter value) and blank lines are93 ignored. Hash marks (<code class="literal">#</code>) designate the remainder94 of the line as a comment. Parameter values that are not simple95 identifiers or numbers must be single-quoted. To embed a single96 quote in a parameter value, write either two quotes (preferred)97 or backslash-quote.98 If the file contains multiple entries for the same parameter,99 all but the last one are ignored.100 </p><p>101 Parameters set in this way provide default values for the cluster.102 The settings seen by active sessions will be these values unless they103 are overridden. The following sections describe ways in which the104 administrator or user can override these defaults.105 </p><p>106 <a id="id-1.6.7.4.3.4.1" class="indexterm"></a>107 The configuration file is reread whenever the main server process108 receives a <span class="systemitem">SIGHUP</span> signal; this signal is most easily109 sent by running <code class="literal">pg_ctl reload</code> from the command line or by110 calling the SQL function <code class="function">pg_reload_conf()</code>. The main111 server process also propagates this signal to all currently running112 server processes, so that existing sessions also adopt the new values113 (this will happen after they complete any currently-executing client114 command). Alternatively, you can115 send the signal to a single server process directly. Some parameters116 can only be set at server start; any changes to their entries in the117 configuration file will be ignored until the server is restarted.118 Invalid parameter settings in the configuration file are likewise119 ignored (but logged) during <span class="systemitem">SIGHUP</span> processing.120 </p><p>121 In addition to <code class="filename">postgresql.conf</code>,122 a <span class="productname">PostgreSQL</span> data directory contains a file123 <code class="filename">postgresql.auto.conf</code><a id="id-1.6.7.4.3.5.4" class="indexterm"></a>,124 which has the same format as <code class="filename">postgresql.conf</code> but125 is intended to be edited automatically, not manually. This file holds126 settings provided through the <a class="link" href="sql-altersystem.html" title="ALTER SYSTEM"><code class="command">ALTER SYSTEM</code></a> command.127 This file is read whenever <code class="filename">postgresql.conf</code> is,128 and its settings take effect in the same way. Settings129 in <code class="filename">postgresql.auto.conf</code> override those130 in <code class="filename">postgresql.conf</code>.131 </p><p>132 External tools may also133 modify <code class="filename">postgresql.auto.conf</code>. It is not134 recommended to do this while the server is running, since a135 concurrent <code class="command">ALTER SYSTEM</code> command could overwrite136 such changes. Such tools might simply append new settings to the end,137 or they might choose to remove duplicate settings and/or comments138 (as <code class="command">ALTER SYSTEM</code> will).139 </p><p>140 The system view141 <a class="link" href="view-pg-file-settings.html" title="54.7. pg_file_settings"><code class="structname">pg_file_settings</code></a>142 can be helpful for pre-testing changes to the configuration files, or for143 diagnosing problems if a <span class="systemitem">SIGHUP</span> signal did not have the144 desired effects.145 </p></div><div class="sect2" id="CONFIG-SETTING-SQL"><div class="titlepage"><div><div><h3 class="title">20.1.3. Parameter Interaction via SQL <a href="#CONFIG-SETTING-SQL" class="id_link">#</a></h3></div></div></div><p>146 <span class="productname">PostgreSQL</span> provides three SQL147 commands to establish configuration defaults.148 The already-mentioned <code class="command">ALTER SYSTEM</code> command149 provides an SQL-accessible means of changing global defaults; it is150 functionally equivalent to editing <code class="filename">postgresql.conf</code>.151 In addition, there are two commands that allow setting of defaults152 on a per-database or per-role basis:153 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>154 The <a class="link" href="sql-alterdatabase.html" title="ALTER DATABASE"><code class="command">ALTER DATABASE</code></a> command allows global155 settings to be overridden on a per-database basis.156 </p></li><li class="listitem"><p>157 The <a class="link" href="sql-alterrole.html" title="ALTER ROLE"><code class="command">ALTER ROLE</code></a> command allows both global and158 per-database settings to be overridden with user-specific values.159 </p></li></ul></div><p>160 Values set with <code class="command">ALTER DATABASE</code> and <code class="command">ALTER ROLE</code>161 are applied only when starting a fresh database session. They162 override values obtained from the configuration files or server163 command line, and constitute defaults for the rest of the session.164 Note that some settings cannot be changed after server start, and165 so cannot be set with these commands (or the ones listed below).166 </p><p>167 Once a client is connected to the database, <span class="productname">PostgreSQL</span>168 provides two additional SQL commands (and equivalent functions) to169 interact with session-local configuration settings:170 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>171 The <a class="link" href="sql-show.html" title="SHOW"><code class="command">SHOW</code></a> command allows inspection of the172 current value of any parameter. The corresponding SQL function is173 <code class="function">current_setting(setting_name text)</code>174 (see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET" title="9.27.1. Configuration Settings Functions">Section 9.27.1</a>).175 </p></li><li class="listitem"><p>176 The <a class="link" href="sql-set.html" title="SET"><code class="command">SET</code></a> command allows modification of the177 current value of those parameters that can be set locally to a178 session; it has no effect on other sessions.179 Many parameters can be set this way by any user, but some can180 only be set by superusers and users who have been181 granted <code class="literal">SET</code> privilege on that parameter.182 The corresponding SQL function is183 <code class="function">set_config(setting_name, new_value, is_local)</code>184 (see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET" title="9.27.1. Configuration Settings Functions">Section 9.27.1</a>).185 </p></li></ul></div><p>186 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 be187 used to view and change session-local values:188 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>189 Querying this view is similar to using <code class="command">SHOW ALL</code> but190 provides more detail. It is also more flexible, since it's possible191 to specify filter conditions or join against other relations.192 </p></li><li class="listitem"><p>193 Using <code class="command">UPDATE</code> on this view, specifically194 updating the <code class="structname">setting</code> column, is the equivalent195 of issuing <code class="command">SET</code> commands. For example, the equivalent of196</p><pre class="programlisting">197SET configuration_parameter TO DEFAULT;198</pre><p>199 is:200</p><pre class="programlisting">201UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';202</pre><p>203 </p></li></ul></div></div><div class="sect2" id="CONFIG-SETTING-SHELL"><div class="titlepage"><div><div><h3 class="title">20.1.4. Parameter Interaction via the Shell <a href="#CONFIG-SETTING-SHELL" class="id_link">#</a></h3></div></div></div><p>204 In addition to setting global defaults or attaching205 overrides at the database or role level, you can pass settings to206 <span class="productname">PostgreSQL</span> via shell facilities.207 Both the server and <span class="application">libpq</span> client library208 accept parameter values via the shell.209 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>210 During server startup, parameter settings can be211 passed to the <code class="command">postgres</code> command via the212 <code class="option">-c</code> command-line parameter. For example,213</p><pre class="programlisting">214postgres -c log_connections=yes -c log_destination='syslog'215</pre><p>216 Settings provided in this way override those set via217 <code class="filename">postgresql.conf</code> or <code class="command">ALTER SYSTEM</code>,218 so they cannot be changed globally without restarting the server.219 </p></li><li class="listitem"><p>220 When starting a client session via <span class="application">libpq</span>,221 parameter settings can be222 specified using the <code class="envar">PGOPTIONS</code> environment variable.223 Settings established in this way constitute defaults for the life224 of the session, but do not affect other sessions.225 For historical reasons, the format of <code class="envar">PGOPTIONS</code> is226 similar to that used when launching the <code class="command">postgres</code>227 command; specifically, the <code class="option">-c</code> flag must be specified.228 For example,229</p><pre class="programlisting">230env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql231</pre><p>232 </p><p>233 Other clients and libraries might provide their own mechanisms,234 via the shell or otherwise, that allow the user to alter session235 settings without direct use of SQL commands.236 </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 <a href="#CONFIG-INCLUDES" class="id_link">#</a></h3></div></div></div><p>237 <span class="productname">PostgreSQL</span> provides several features for breaking238 down complex <code class="filename">postgresql.conf</code> files into sub-files.239 These features are especially useful when managing multiple servers240 with related, but not identical, configurations.241 </p><p>242 <a id="id-1.6.7.4.6.3.1" class="indexterm"></a>243 In addition to individual parameter settings,244 the <code class="filename">postgresql.conf</code> file can contain <em class="firstterm">include245 directives</em>, which specify another file to read and process as if246 it were inserted into the configuration file at this point. This247 feature allows a configuration file to be divided into physically248 separate parts. Include directives simply look like:249</p><pre class="programlisting">250include 'filename'251</pre><p>252 If the file name is not an absolute path, it is taken as relative to253 the directory containing the referencing configuration file.254 Inclusions can be nested.255 </p><p>256 <a id="id-1.6.7.4.6.4.1" class="indexterm"></a>257 There is also an <code class="literal">include_if_exists</code> directive, which acts258 the same as the <code class="literal">include</code> directive, except259 when the referenced file does not exist or cannot be read. A regular260 <code class="literal">include</code> will consider this an error condition, but261 <code class="literal">include_if_exists</code> merely logs a message and continues262 processing the referencing configuration file.263 </p><p>264 <a id="id-1.6.7.4.6.5.1" class="indexterm"></a>265 The <code class="filename">postgresql.conf</code> file can also contain266 <code class="literal">include_dir</code> directives, which specify an entire267 directory of configuration files to include. These look like268</p><pre class="programlisting">269include_dir 'directory'270</pre><p>271 Non-absolute directory names are taken as relative to the directory272 containing the referencing configuration file. Within the specified273 directory, only non-directory files whose names end with the274 suffix <code class="literal">.conf</code> will be included. File names that275 start with the <code class="literal">.</code> character are also ignored, to276 prevent mistakes since such files are hidden on some platforms. Multiple277 files within an include directory are processed in file name order278 (according to C locale rules, i.e., numbers before letters, and279 uppercase letters before lowercase ones).280 </p><p>281 Include files or directories can be used to logically separate portions282 of the database configuration, rather than having a single large283 <code class="filename">postgresql.conf</code> file. Consider a company that has two284 database servers, each with a different amount of memory. There are285 likely elements of the configuration both will share, for things such286 as logging. But memory-related parameters on the server will vary287 between the two. And there might be server specific customizations,288 too. One way to manage this situation is to break the custom289 configuration changes for your site into three files. You could add290 this to the end of your <code class="filename">postgresql.conf</code> file to include291 them:292</p><pre class="programlisting">293include 'shared.conf'294include 'memory.conf'295include 'server.conf'296</pre><p>297 All systems would have the same <code class="filename">shared.conf</code>. Each298 server with a particular amount of memory could share the299 same <code class="filename">memory.conf</code>; you might have one for all servers300 with 8GB of RAM, another for those having 16GB. And301 finally <code class="filename">server.conf</code> could have truly server-specific302 configuration information in it.303 </p><p>304 Another possibility is to create a configuration file directory and305 put this information into files there. For example, a <code class="filename">conf.d</code>306 directory could be referenced at the end of <code class="filename">postgresql.conf</code>:307</p><pre class="programlisting">308include_dir 'conf.d'309</pre><p>310 Then you could name the files in the <code class="filename">conf.d</code> directory311 like this:312</p><pre class="programlisting">31300shared.conf31401memory.conf31502server.conf316</pre><p>317 This naming convention establishes a clear order in which these318 files will be loaded. This is important because only the last319 setting encountered for a particular parameter while the server is320 reading configuration files will be used. In this example,321 something set in <code class="filename">conf.d/02server.conf</code> would override a322 value set in <code class="filename">conf.d/01memory.conf</code>.323 </p><p>324 You might instead use this approach to naming the files325 descriptively:326</p><pre class="programlisting">32700shared.conf32801memory-8GB.conf32902server-foo.conf330</pre><p>331 This sort of arrangement gives a unique name for each configuration file332 variation. This can help eliminate ambiguity when several servers have333 their configurations all stored in one place, such as in a version334 control repository. (Storing database configuration files under version335 control is another good practice to consider.)336 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.2. File Locations</td></tr></table></div></body></html>