Postgresql参数配置文件及参数生效方法

PostgreSQL数据库的配置文件的位置在实例目录下，文件名为 postgresql.conf 。

修改配置参数的时候，需要手工修改 postgresql.conf 文件里边的配置参数。
大部分配置参数是被注释掉的，文件内初始值就是默认值。

使用 show all 命令可以看到所有参数值，例如：

test=# show all;
                name                 |                         setting                          |                                                          description 
                                                         
-------------------------------------+----------------------------------------------------------+----------------------------------------------------------------------
---------------------------------------------------------
 allow_system_table_mods             | off                                                      | Allows modifications of the structure of system tables.
 application_name                    | psql                                                     | Sets the application name to be reported in statistics and logs.
 archive_command                     | (disabled)                                               | Sets the shell command that will be called to archive a WAL file.
 archive_mode                        | off                                                      | Allows archiving of WAL files using archive_command.
 archive_timeout                     | 0                                                        | Forces a switch to the next xlog file if a new file has not been star
ted within N seconds.
 array_nulls                         | on                                                       | Enable input of NULL elements in arrays.
 authentication_timeout              | 1min                                                     | Sets the maximum allowed time to complete client authentication.
 autovacuum                          | on                                                       | Starts the autovacuum subprocess.
 autovacuum_analyze_scale_factor     | 0.1                                                      | Number of tuple inserts, updates, or deletes prior to analyze as a fr
action of reltuples.

下面语句也可以查看所有有效参数值和当前值，也可以看到生效时机：

select * from pg_settings;

关于 pg_settings 的官方文档：
https://www.postgresql.org/docs/9.3/view-pg-settings.html

全部生效时机如下所示：

test=# select distinct context from pg_settings;
  context   
------------
 backend
 user
 internal
 postmaster
 superuser
 sighup
(6 rows)

上述6种生效时机的具体含义如下：

***internal***
    These settings cannot be changed directly; they reflect internally determined values. Some of them may be adjustable by rebuilding the server with different configuration options, or by changing options supplied to initdb.
***postmaster***
    These settings can only be applied when the server starts, so any change requires restarting the server. Values for these settings are typically stored in the postgresql.conf file, or passed on the command line when starting the server. Of course, settings with any of the lower context types can also be set at server start time.
***sighup***
    Changes to these settings can be made in postgresql.conf without restarting the server. Send a SIGHUP signal to the postmaster to cause it to re-read postgresql.conf and apply the changes. The postmaster will also forward the SIGHUP signal to its child processes so that they all pick up the new value.
***backend***
    Changes to these settings can be made in postgresql.conf without restarting the server; they can also be set for a particular session in the connection request packet (for example, via libpq's PGOPTIONS environment variable). However, these settings never change in a session after it is started. If you change them in postgresql.conf, send a SIGHUP signal to the postmaster to cause it to re-read postgresql.conf. The new values will only affect subsequently-launched sessions.
***superuser***
    These settings can be set from postgresql.conf, or within a session via the SET command; but only superusers can change them via SET. Changes in postgresql.conf will affect existing sessions only if no session-local value has been established with SET.
***user***
    These settings can be set from postgresql.conf, or within a session via the SET command. Any user is allowed to change his session-local value. Changes in postgresql.conf will affect existing sessions only if no session-local value has been established with SET.

参数值修改之后，需要让参数生效。让参数生效的方法的官方文档链接：
https://www.postgresql.org/docs/9.3/config-setting.html
下面这段话：

The configuration file is reread whenever ***the main server process receives a SIGHUP signal***; this is most easily done 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 get the new value. 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.

说明有三种方式可以让参数生效：

1. 发送 SIGHUP 信号给主服务进程；
2. 运行 pg_ctl reload 命令；
3. 调用函数 select pg_reload_conf()；