Postgresql 中文操作指南

20.1. Setting Parameters #

20.1.1. Parameter Names and Values #

所有参数名称都不区分大小写。每个参数接受五种类型之一的值：布尔值、字符串、整数、浮点或枚举（enum）。类型决定了设置参数的语法：

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:

20.1.2. Parameter Interaction via the Configuration File #

设置这些参数的最基本方法是编辑文件 postgresql.conf，该文件通常保留在数据目录中。初始化数据库集群目录时，会安装一个默认副本。此文件的外观示例如下：

The most fundamental way to set these parameters is to edit the file postgresql.conf, 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:

# This is a comment
log_connections = yes
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

每行指定一个参数。名称和值之间的等号是可选的。空格无关紧要（引用的参数值内除外），并且会忽略空行。哈希标记（#）将该行的其余部分指定为注释。不是简单标识符或数字的参数值必须用单引号引起来。要在参数值中嵌入单引号，请写两个引号（推荐）或反斜杠引号。如果文件中为同一参数包含多个条目，则除最后一个条目之外的所有条目都将被忽略。

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 (#) 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.

通过这种方式设置的参数为集群提供了默认值。除非覆盖了这些值，否则活动会话看到的设置将是这些值。以下部分描述了管理员或用户如何覆盖这些默认值。

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.

每当主服务器进程接收到 SIGHUP 信号时，就会重新读取配置文件；从命令行运行 pg_ctl reload 或调用 SQL 函数 pg_reload_conf() 可以最容易地发送此信号。主服务器进程还将此信号传播到所有当前运行的服务器进程，以便现有会话也采用新值（这将在它们完成任何当前执行的客户端命令后发生）。或者，您可以直接向单个服务器进程发送信号。一些参数只能在服务器启动时设置；在服务器重新启动之前，将忽略对其在配置文件中的条目的任何更改。在 SIGHUP 处理期间，配置文件中的无效参数设置也会被忽略（但会记录）。

The configuration file is reread whenever the main server process receives a SIGHUP signal; this signal is most easily sent by running pg_ctl reload from the command line or by calling the SQL function pg_reload_conf(). 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 SIGHUP processing.

除了 postgresql.conf ，PostgreSQL 数据目录还包含一个文件 postgresql.auto.conf ，其格式与 postgresql.conf 相同，但旨在自动编辑，而不是手动编辑。此文件保存通过 ALTER SYSTEM 命令提供的设置。每当 postgresql.conf 时都会读取此文件，并且其设置以相同的方式生效。 postgresql.auto.conf 中的设置会覆盖 postgresql.conf 中的设置。

In addition to postgresql.conf, a PostgreSQL data directory contains a file postgresql.auto.conf, which has the same format as postgresql.conf but is intended to be edited automatically, not manually. This file holds settings provided through the ALTER SYSTEM command. This file is read whenever postgresql.conf is, and its settings take effect in the same way. Settings in postgresql.auto.conf override those in postgresql.conf.

外部工具也可以修改 postgresql.auto.conf。建议在服务器运行时不要这样做，因为并发的 ALTER SYSTEM 命令可能会覆盖这些更改。此类工具可能会简单地将新设置追加到末尾，或者可能会选择删除重复的设置和/或注释（如 ALTER SYSTEM 将要执行的操作）。

External tools may also modify postgresql.auto.conf. It is not recommended to do this while the server is running, since a concurrent ALTER SYSTEM 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 ALTER SYSTEM will).

系统视图 pg_file_settings 有助于对配置文件的更改进行预测试，或者在 SIGHUP 信号未产生预期效果时诊断问题。

The system view pg_file_settings can be helpful for pre-testing changes to the configuration files, or for diagnosing problems if a SIGHUP signal did not have the desired effects.

20.1.3. Parameter Interaction via SQL #

PostgreSQL 提供三个 SQL 命令来建立配置默认值。上述 ALTER SYSTEM 命令提供了一种通过 SQL 访问的方法来更改全局默认值；它在功能上等效于编辑 postgresql.conf。此外，还有两个命令允许在每个数据库或每个角色的基础上设置默认值：

PostgreSQL provides three SQL commands to establish configuration defaults. The already-mentioned ALTER SYSTEM command provides an SQL-accessible means of changing global defaults; it is functionally equivalent to editing postgresql.conf. In addition, there are two commands that allow setting of defaults on a per-database or per-role basis:

使用 ALTER DATABASE 和 ALTER ROLE 设置的值仅在启动新的数据库会话时应用。它们会覆盖从配置文件或服务器命令行获取的值，并构成会话其余部分的默认值。请注意，有些设置在服务器启动后无法更改，因此无法使用这些命令（或下面列出的命令）进行设置。

Values set with ALTER DATABASE and ALTER ROLE 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).

一旦客户端连接到数据库，PostgreSQL 就提供了两个额外的 SQL 命令（和等效的功能）来与会话局部配置设置进行交互：

Once a client is connected to the database, PostgreSQL provides two additional SQL commands (and equivalent functions) to interact with session-local configuration settings:

此外，可以将系统视图 pg_settings 用于查看并更改会话局部值：

In addition, the system view pg_settings can be used to view and change session-local values:

20.1.4. Parameter Interaction via the Shell #

除了设置全局默认值或在数据库或角色级别附加覆盖之外，您还可以通过 shell 设施向 PostgreSQL 传递设置。服务器和 libpq 客户端库都通过 shell 接受参数值。

In addition to setting global defaults or attaching overrides at the database or role level, you can pass settings to PostgreSQL via shell facilities. Both the server and libpq client library accept parameter values via the shell.

20.1.5. Managing Configuration File Contents #

PostgreSQL 提供了几种功能，用于将复杂 postgresql.conf 文件分解为子文件。在管理具有相关但并非完全相同的配置的多个服务器时，这些功能特别有用。

PostgreSQL provides several features for breaking down complex postgresql.conf files into sub-files. These features are especially useful when managing multiple servers with related, but not identical, configurations.

除了单个参数设置外， postgresql.conf 文件可以包含 include directives ，其指定另一文件，以便将其读取并处理，就像在此处插入到配置文件中。此特性允许配置文件划分为物理分离部分。包含指令只是如下所示：

In addition to individual parameter settings, the postgresql.conf file can contain include directives, 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:

include 'filename'

如果文件名不是绝对路径，则它将相对于包含引用配置文件的目录。可以嵌套包括。

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.

还有一个 include_if_exists 指令，其作用与 include 指令的作用相同，但当引用的文件不存在或无法读取时除外。规则 include 将此视为错误条件，但 include_if_exists 只是记录一条消息并继续处理引用配置文件。

There is also an include_if_exists directive, which acts the same as the include directive, except when the referenced file does not exist or cannot be read. A regular include will consider this an error condition, but include_if_exists merely logs a message and continues processing the referencing configuration file.

postgresql.conf 文件还包含 include_dir 指令，该指令指定整个配置文件目录以纳入其中。它们如下所示：

The postgresql.conf file can also contain include_dir directives, which specify an entire directory of configuration files to include. These look like

include_dir 'directory'

非绝对目录名被视为相对于包含引用配置文件的目录。在指定的目录中，只有名称以后缀 .conf 结尾的非目录文件才会被包含。以 . 字符开头的文件名也被忽略，以避免错误，因为此类文件在某些平台上是隐藏的。包含目录中的多个文件按文件名顺序（根据 C 区域规则，即数字在字母之前，大写字母在小写字母之前）进行处理。

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 .conf will be included. File names that start with the . 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).

包含文件或目录可用于逻辑地分隔数据库配置的部分，而不是使用单个大型 postgresql.conf 文件。考虑一家有两台数据库服务器的公司，每台服务器的内存量不同。配置可能有一些元素是两台服务器都将共享的，例如日志记录。但两台服务器的内存相关参数会有所不同。服务器也可能会有特定于服务器的自定义。处理这种情况的一种方法是将您网站的自定义配置更改分解为三个文件。您可以在 postgresql.conf 文件的末尾添加以下内容来将其包括在内：

Include files or directories can be used to logically separate portions of the database configuration, rather than having a single large postgresql.conf 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 postgresql.conf file to include them:

include 'shared.conf'
include 'memory.conf'
include 'server.conf'

所有系统都会有相同的 shared.conf。具有特定内存量的每台服务器都可以共享相同的 memory.conf；对于拥有 8GB RAM 的所有服务器，您可能有一个，对于拥有 16GB RAM 的服务器，您可能有一个。最后，server.conf 可以包含真正特定于服务器的配置信息。

All systems would have the same shared.conf. Each server with a particular amount of memory could share the same memory.conf; you might have one for all servers with 8GB of RAM, another for those having 16GB. And finally server.conf could have truly server-specific configuration information in it.

另一种可能性是创建一个配置文件目录，并将此信息放入该目录中的文件。例如，可以在 postgresql.conf 的末尾引用一个 conf.d 目录：

Another possibility is to create a configuration file directory and put this information into files there. For example, a conf.d directory could be referenced at the end of postgresql.conf:

include_dir 'conf.d'

然后，您可以像这样为 conf.d 目录中的文件命名：

Then you could name the files in the conf.d directory like this:

00shared.conf
01memory.conf
02server.conf

此命名约定建立了将加载这些文件的清晰顺序。这很重要，因为在服务器读取配置文件时，针对特定参数遇到的最后一个设置才会被使用。在此示例中，conf.d/02server.conf 中设置的内容将覆盖 conf.d/01memory.conf 中设置的值。

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 conf.d/02server.conf would override a value set in conf.d/01memory.conf.

相反，您可以使用此方法对文件进行描述性命名：

You might instead use this approach to naming the files descriptively:

00shared.conf
01memory-8GB.conf
02server-foo.conf

此类排列为每个配置文件变体提供一个唯一名称。当多台服务器将其配置全部存储在一个地方时，例如存储在版本控制库中，这有助于消除歧义。（将数据库配置文件存储在版本控制之下是另一个值得考虑的好做法。）

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