+关注继续查看

# Postgresql服务配置-设置参数

## 1、Parameter Names and Values

• Boolean：值可以是off, true, false, yes, no, 1, 0 (区分大小写)或其中一个值的任何明确前缀。
• String：通常用单引号括起来，数字和标识符可以忽略单引号
• Numeric：允许整数和浮点型
• Numeric with Unit：带单位的数字，某些数字参数具有隐式单位,为方便起见, 可以使用显式指定的单位给出设置, 例如时间值的 "120 ms", 它们将转换为参数的实际单位。请注意, 该值必须以字符串 (带引号) 的形式写入才能使用此功能。单位名称区分大小写, 数值和单位之间可以有空白。
• Enumerated：枚举不区分大小写。

## 2、Parameter Interaction via the Configuration File

• 通过更改文件postgresql.conf配置参数
• 通过这种方式设置的参数，提供的是默认值
• pg_file_settings查看参数文件配置的参数
查看参数在参数文件中的配置
postgres=# select * from pg_FILE_settings where name='max_connections';
sourcefile | sourceline | seqno | name | setting | applied | error
-----------------------------------------+------------+-------+-----------------+---------+---------+-------
/opt/postgres/data/postgresql.conf | 66 | 3 | max_connections | 100 | f |
/opt/postgres/data/postgresql.auto.conf | 9 | 30 | max_connections | 200 | t |
(2 rows)

## 3、Parameter Interaction via SQL

pg提供三种SQL命令用于配置参数

• alter system命令更改全局（global/cluster）配置
• alter database命令针对每个数据库更改设置，覆盖全局设置
• alter role命令允许配置特定用户的参数值，覆盖全局和数据库设置

注意：alter database和alter role仅对新的会话生效，且有些参数无法修改，使之永久生效,只能修改动态参数
postgres=# alter database postgres set max_connections=300;
ERROR: parameter "max_connections" cannot be changed without restarting the server
• pg_settings查看当前参数配置

查看当前参数值
postgres=# select name,setting,unit,context,sourcefile from pg_settings where name='max_connections';
name | setting | unit | context | sourcefile
-----------------+---------+------+------------+-----------------------------------------
max_connections | 200 | | postmaster | /opt/postgres/data/postgresql.auto.conf
(1 row)

postgres=# select name,context from pg_settings where name in ('max_connections','log_connections','log_temp_files');
name | context
-----------------+-------------------
log_connections | superuser-backend
log_temp_files | superuser
max_connections | postmaster
(3 rows)
context内容参考：
https://www.postgresql.org/docs/10/view-pg-settings.html
There are several possible values of context. In order of decreasing difficulty of changing the setting, they are:
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.
superuser-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), but only if the connecting user is a superuser. 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.
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); any user can make such a change for their session. 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 their session-local value. Changes in postgresql.conf will affect existing sessions only if no session-local value has been established with SET.
• pg_settings视图可以查看和修改（只能修改会话级别的值）
In addition, the system view pg_settings can be used to view and change session-local values
• 客户端连接后可以使用show和set命令设置当前会话的参数值，不影响其他会话。show调用内部函数current_setting，set调用内部函数set_config(setting_name, new_value, is_local)
查看参数大小
postgres=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
or
postgres=# SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='shared_buffers';
name | setting | unit | current_setting
----------------+---------+------+-----------------
shared_buffers | 16384 | 8kB | 128MB
(1 row)

## 4、Parameter Interaction via the Shell

• 在服务器启动过程中, 可以通过-c 命令行参数将参数设置传递给 postgres
postgres -c log_connections=yes -c log_destination='syslog'
• 通过 libpq 启动客户端会话时, 可以使用 PGOPOPS 环境变量指定参数设置。以这种方式建立的设置构成会话生存期的默认值, 但不会影响其他会话。由于历史原因, POSTGRES 的格式类似于启动 postgres 命令时使用的格式;具体而言, 必须指定-c 标志。例如,
env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql

## 5、Managing Configuration File Contents

PostgreSQL 提供了几个功能, 用于将复杂的 poostgresql. conf 文件分解为子文件。在管理具有相关但不相同的配置的多台服务器时, 这些功能特别有用。

include 'filename'

include_dir 'directory'

PostgreSQL服务器管理：管理数据库

1970 0

1055 0
+关注
13

0