Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Sledovat 1
Oblíbit 0
Wiki 捐赠
gooderp18绿色标准版
Nelze vybrat více než 25 témat Téma musí začínat písmenem nebo číslem, může obsahovat pomlčky („-“) a může být dlouhé až 35 znaků.
Větev: master
Větve Značky
${ item.name }
Vytvořit větev ${ searchTerm }
z „master“
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/config-setting.html

331 lines
24KB
Surový Trvalý odkaz Blame Historie 

  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>19.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 V1.79.1" /><link rel="prev" href="runtime-config.html" title="Chapter 19. Server Configuration" /><link rel="next" href="runtime-config-file-locations.html" title="19.2. File Locations" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">19.1. Setting Parameters</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config.html" title="Chapter 19. Server Configuration">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html" title="19.2. File Locations">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="CONFIG-SETTING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.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">19.1.1. Parameter Names and Values</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE">19.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">19.1.3. Parameter Interaction via SQL</a></span></dt><dt><span class="sect2"><a href="config-setting.html#id-1.6.6.4.5">19.1.4. Parameter Interaction via the Shell</a></span></dt><dt><span class="sect2"><a href="config-setting.html#CONFIG-INCLUDES">19.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">19.1.1. Parameter Names and Values</h3></div></div></div><p>

  3.      All parameter names are case-insensitive. Every parameter takes a

  4.      value of one of five types: boolean, string, integer, floating point,

  5.      or enumerated (enum).  The type determines the syntax for setting the

  6.      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 as

  10.        <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 single

  22.        quotes within the value.  Quotes can usually be omitted if the value

  23.        is a simple number or identifier, however.

  24.        (Values that match a 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 and

  28.        floating-point formats; fractional values are rounded to the nearest

  29.        integer if the parameter is of integer type.  Integer parameters

  30.        additionally accept hexadecimal input (beginning

  31.        with <code class="literal">0x</code>) and octal input (beginning

  32.        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 describe

  38.        quantities of memory or time. The unit might be bytes, kilobytes, blocks

  39.        (typically eight kilobytes), milliseconds, seconds, or minutes.

  40.        An unadorned numeric value for one of these settings will use the

  41.        setting's default unit, which can be learned from

  42.        <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 be

  45.        converted to whatever the parameter's actual unit is.  Note that the

  46.        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 between

  48.        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 are

  58.           <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 rounded

  65.        to a multiple of the next smaller unit if there is one.

  66.        For example, <code class="literal">30.1 GB</code> will be converted

  67.        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 integer

  69.        occurs after any units 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 string

  73.        parameters, but are restricted to have one of a limited set of

  74.        values.  The values allowable for such a parameter can be found from

  75.        <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">19.1.2. Parameter Interaction via the Configuration File</h3></div></div></div><p>

  78.      The most fundamental way to set these parameters is to edit the file

  79.      <code class="filename">postgresql.conf</code><a id="id-1.6.6.4.3.2.2" class="indexterm"></a>,

  80.      which is normally kept in the data directory.  A default copy is

  81.      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 comment

  85. log_connections = yes

  86. log_destination = 'syslog'

  87. search_path = '"$user", public'

  88. shared_buffers = 128MB

  89. </pre><p>

  90.      One parameter is specified per line. The equal sign between name and

  91.      value is optional. Whitespace is insignificant (except within a quoted

  92.      parameter value) and blank lines are

  93.      ignored. Hash marks (<code class="literal">#</code>) designate the remainder

  94.      of the line as a comment.  Parameter values that are not simple

  95.      identifiers or numbers must be single-quoted.  To embed a single

  96.      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 they

  103.      are overridden.  The following sections describe ways in which the

  104.      administrator or user can override these defaults.

  105.     </p><p>

  106.      <a id="id-1.6.6.4.3.4.1" class="indexterm"></a>

  107.      The configuration file is reread whenever the main server process

  108.      receives a <span class="systemitem">SIGHUP</span> signal; this signal is most easily

  109.      sent by running <code class="literal">pg_ctl reload</code> from the command line or by

  110.      calling the SQL function <code class="function">pg_reload_conf()</code>. The main

  111.      server process also propagates this signal to all currently running

  112.      server processes, so that existing sessions also adopt the new values

  113.      (this will happen after they complete any currently-executing client

  114.      command).  Alternatively, you can

  115.      send the signal to a single server process directly.  Some parameters

  116.      can only be set at server start; any changes to their entries in the

  117.      configuration file will be ignored until the server is restarted.

  118.      Invalid parameter settings in the configuration file are likewise

  119.      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 file

  123.      <code class="filename">postgresql.auto.conf</code><a id="id-1.6.6.4.3.5.4" class="indexterm"></a>,

  124.      which has the same format as <code class="filename">postgresql.conf</code> but

  125.      is intended to be edited automatically, not manually.  This file holds

  126.      settings provided through the <a class="xref" href="sql-altersystem.html" title="ALTER SYSTEM"><span class="refentrytitle">ALTER SYSTEM</span></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.  Settings

  129.      in <code class="filename">postgresql.auto.conf</code> override those

  130.      in <code class="filename">postgresql.conf</code>.

  131.     </p><p>

  132.      External tools may also

  133.      modify <code class="filename">postgresql.auto.conf</code>.  It is not

  134.      recommended to do this while the server is running, since a

  135.      concurrent <code class="command">ALTER SYSTEM</code> command could overwrite

  136.      such changes.  Such tools might simply append new settings to the end,

  137.      or they might choose to remove duplicate settings and/or comments

  138.      (as <code class="command">ALTER SYSTEM</code> will).

  139.     </p><p>

  140.      The system view

  141.      <a class="link" href="view-pg-file-settings.html" title="51.70. pg_file_settings"><code class="structname">pg_file_settings</code></a>

  142.      can be helpful for pre-testing changes to the configuration files, or for

  143.      diagnosing problems if a <span class="systemitem">SIGHUP</span> signal did not have the

  144.      desired effects.

  145.     </p></div><div class="sect2" id="CONFIG-SETTING-SQL-COMMAND-INTERACTION"><div class="titlepage"><div><div><h3 class="title">19.1.3. Parameter Interaction via SQL</h3></div></div></div><p>

  146.       <span class="productname">PostgreSQL</span> provides three SQL

  147.       commands to establish configuration defaults.

  148.       The already-mentioned <a class="xref" href="sql-altersystem.html" title="ALTER SYSTEM"><span class="refentrytitle">ALTER SYSTEM</span></a> command

  149.       provides a SQL-accessible means of changing global defaults; it is

  150.       functionally equivalent to editing <code class="filename">postgresql.conf</code>.

  151.       In addition, there are two commands that allow setting of defaults

  152.       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="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a> command allows global

  155.        settings to be overridden on a per-database basis.

  156.       </p></li><li class="listitem"><p>

  157.        The <a class="xref" href="sql-alterrole.html" title="ALTER ROLE"><span class="refentrytitle">ALTER ROLE</span></a> command allows both global and

  158.        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.  They

  162.       override values obtained from the configuration files or server

  163.       command line, and constitute defaults for the rest of the session.

  164.       Note that some settings cannot be changed after server start, and

  165.       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) to

  169.       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="xref" href="sql-show.html" title="SHOW"><span class="refentrytitle">SHOW</span></a> command allows inspection of the

  172.       current value of all parameters.  The corresponding function is

  173.       <code class="function">current_setting(setting_name text)</code>.

  174.      </p></li><li class="listitem"><p>

  175.        The <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> command allows modification of the

  176.        current value of those parameters that can be set locally to a

  177.        session; it has no effect on other sessions.

  178.        The corresponding function is

  179.        <code class="function">set_config(setting_name, new_value, is_local)</code>.

  180.       </p></li></ul></div><p>

  181.      In addition, the system view <a class="link" href="view-pg-settings.html" title="51.86. pg_settings"><code class="structname">pg_settings</code></a> can be

  182.      used to view and change session-local values:

  183.     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  184.        Querying this view is similar to using <code class="command">SHOW ALL</code> but

  185.        provides more detail.  It is also more flexible, since it's possible

  186.        to specify filter conditions or join against other relations.

  187.       </p></li><li class="listitem"><p>

  188.        Using <a class="xref" href="sql-update.html" title="UPDATE"><span class="refentrytitle">UPDATE</span></a> on this view, specifically

  189.        updating the <code class="structname">setting</code> column, is the equivalent

  190.        of issuing <code class="command">SET</code> commands.  For example, the equivalent of

  191. </p><pre class="programlisting">

  192. SET configuration_parameter TO DEFAULT;

  193. </pre><p>

  194.        is:

  195. </p><pre class="programlisting">

  196. UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

  197. </pre><p>

  198.       </p></li></ul></div></div><div class="sect2" id="id-1.6.6.4.5"><div class="titlepage"><div><div><h3 class="title">19.1.4. Parameter Interaction via the Shell</h3></div></div></div><p>

  199.       In addition to setting global defaults or attaching

  200.       overrides at the database or role level, you can pass settings to

  201.       <span class="productname">PostgreSQL</span> via shell facilities.

  202.       Both the server and <span class="application">libpq</span> client library

  203.       accept parameter values via the shell.

  204.      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  205.        During server startup, parameter settings can be

  206.        passed to the <code class="command">postgres</code> command via the

  207.        <code class="option">-c</code> command-line parameter.  For example,

  208. </p><pre class="programlisting">

  209. postgres -c log_connections=yes -c log_destination='syslog'

  210. </pre><p>

  211.        Settings provided in this way override those set via

  212.        <code class="filename">postgresql.conf</code> or <code class="command">ALTER SYSTEM</code>,

  213.        so they cannot be changed globally without restarting the server.

  214.      </p></li><li class="listitem"><p>

  215.       When starting a client session via <span class="application">libpq</span>,

  216.       parameter settings can be

  217.       specified using the <code class="envar">PGOPTIONS</code> environment variable.

  218.       Settings established in this way constitute defaults for the life

  219.       of the session, but do not affect other sessions.

  220.       For historical reasons, the format of <code class="envar">PGOPTIONS</code> is

  221.       similar to that used when launching the <code class="command">postgres</code>

  222.       command; specifically, the <code class="option">-c</code> flag must be specified.

  223.       For example,

  224. </p><pre class="programlisting">

  225. env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql

  226. </pre><p>

  227.      </p><p>

  228.       Other clients and libraries might provide their own mechanisms,

  229.       via the shell or otherwise, that allow the user to alter session

  230.       settings without direct use of SQL commands.

  231.      </p></li></ul></div></div><div class="sect2" id="CONFIG-INCLUDES"><div class="titlepage"><div><div><h3 class="title">19.1.5. Managing Configuration File Contents</h3></div></div></div><p>

  232.       <span class="productname">PostgreSQL</span> provides several features for breaking

  233.       down complex <code class="filename">postgresql.conf</code> files into sub-files.

  234.       These features are especially useful when managing multiple servers

  235.       with related, but not identical, configurations.

  236.      </p><p>

  237.       <a id="id-1.6.6.4.6.3.1" class="indexterm"></a>

  238.       In addition to individual parameter settings,

  239.       the <code class="filename">postgresql.conf</code> file can contain <em class="firstterm">include

  240.       directives</em>, which specify another file to read and process as if

  241.       it were inserted into the configuration file at this point.  This

  242.       feature allows a configuration file to be divided into physically

  243.       separate parts.  Include directives simply look like:

  244. </p><pre class="programlisting">

  245. include 'filename'

  246. </pre><p>

  247.       If the file name is not an absolute path, it is taken as relative to

  248.       the directory containing the referencing configuration file.

  249.       Inclusions can be nested.

  250.      </p><p>

  251.       <a id="id-1.6.6.4.6.4.1" class="indexterm"></a>

  252.       There is also an <code class="literal">include_if_exists</code> directive, which acts

  253.       the same as the <code class="literal">include</code> directive, except

  254.       when the referenced file does not exist or cannot be read.  A regular

  255.       <code class="literal">include</code> will consider this an error condition, but

  256.       <code class="literal">include_if_exists</code> merely logs a message and continues

  257.       processing the referencing configuration file.

  258.      </p><p>

  259.       <a id="id-1.6.6.4.6.5.1" class="indexterm"></a>

  260.       The <code class="filename">postgresql.conf</code> file can also contain

  261.       <code class="literal">include_dir</code> directives, which specify an entire

  262.       directory of configuration files to include.  These look like

  263. </p><pre class="programlisting">

  264. include_dir 'directory'

  265. </pre><p>

  266.       Non-absolute directory names are taken as relative to the directory

  267.       containing the referencing configuration file.  Within the specified

  268.       directory, only non-directory files whose names end with the

  269.       suffix <code class="literal">.conf</code> will be included.  File names that

  270.       start with the <code class="literal">.</code> character are also ignored, to

  271.       prevent mistakes since such files are hidden on some platforms.  Multiple

  272.       files within an include directory are processed in file name order

  273.       (according to C locale rules, i.e. numbers before letters, and

  274.       uppercase letters before lowercase ones).

  275.      </p><p>

  276.       Include files or directories can be used to logically separate portions

  277.       of the database configuration, rather than having a single large

  278.       <code class="filename">postgresql.conf</code> file.  Consider a company that has two

  279.       database servers, each with a different amount of memory.  There are

  280.       likely elements of the configuration both will share, for things such

  281.       as logging.  But memory-related parameters on the server will vary

  282.       between the two.  And there might be server specific customizations,

  283.       too.  One way to manage this situation is to break the custom

  284.       configuration changes for your site into three files.  You could add

  285.       this to the end of your <code class="filename">postgresql.conf</code> file to include

  286.       them:

  287. </p><pre class="programlisting">

  288. include 'shared.conf'

  289. include 'memory.conf'

  290. include 'server.conf'

  291. </pre><p>

  292.       All systems would have the same <code class="filename">shared.conf</code>.  Each

  293.       server with a particular amount of memory could share the

  294.       same <code class="filename">memory.conf</code>; you might have one for all servers

  295.       with 8GB of RAM, another for those having 16GB.  And

  296.       finally <code class="filename">server.conf</code> could have truly server-specific

  297.       configuration information in it.

  298.      </p><p>

  299.       Another possibility is to create a configuration file directory and

  300.       put this information into files there. For example, a <code class="filename">conf.d</code>

  301.       directory could be referenced at the end of <code class="filename">postgresql.conf</code>:

  302. </p><pre class="programlisting">

  303. include_dir 'conf.d'

  304. </pre><p>

  305.       Then you could name the files in the <code class="filename">conf.d</code> directory

  306.       like this:

  307. </p><pre class="programlisting">

  308. 00shared.conf

  309. 01memory.conf

  310. 02server.conf

  311. </pre><p>

  312.        This naming convention establishes a clear order in which these

  313.        files will be loaded.  This is important because only the last

  314.        setting encountered for a particular parameter while the server is

  315.        reading configuration files will be used.  In this example,

  316.        something set in <code class="filename">conf.d/02server.conf</code> would override a

  317.        value set in <code class="filename">conf.d/01memory.conf</code>.

  318.      </p><p>

  319.       You might instead use this approach to naming the files

  320.       descriptively:

  321. </p><pre class="programlisting">

  322. 00shared.conf

  323. 01memory-8GB.conf

  324. 02server-foo.conf

  325. </pre><p>

  326.       This sort of arrangement gives a unique name for each configuration file

  327.       variation.  This can help eliminate ambiguity when several servers have

  328.       their configurations all stored in one place, such as in a version

  329.       control repository.  (Storing database configuration files under version

  330.       control is another good practice to consider.)

  331.      </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-file-locations.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 19. Server Configuration </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 19.2. File Locations</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1