Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
config-setting.html336 linesDownload Raw Back to html
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>
codekingpro/portable-devtools · Team Ai