PHP实战中知识总结 / PgSQL - pgsql系统配置参数说明

Postgresql运行的系统参数一般是保存在postgresql.conf配置文件中，该配置文件为数据库集簇提供了默认值。除非这些参数被覆盖，否则数据库当前活动会话使用的就是这些参数。

一、查看配置文件位置

1、可以通过show命令查看配置文件位置

postgres=# show config_file;
        config_file
-------------------------------------------
/www/server/data/postgres/postgresql.conf
(1 row)
postgres=# show hba_file;
        hba_file
---------------------------------------
/www/server/data/postgres/pg_hba.conf
(1 row)
postgres=# show ident_file;
        ident_file
-----------------------------------------
/www/server/data/postgres/pg_ident.conf
(1 row)


2、可以通过 pg_settings表查看配置文件位置

postgres=# select name,setting from pg_settings where category= 'File Locations';
    name    |         setting
-------------------+-------------------------------------------
config_file    | /www/server/data/postgres/postgresql.conf
data_directory  | /www/server/data/postgres
external_pid_file |
hba_file     | /www/server/data/postgres/pg_hba.conf
ident_file    | /www/server/data/postgres/pg_ident.conf
(5 rows)


二、查看运行时的参数值：show、pg_settings、current_setting

1、通过show命令查看

postgres=# show work_mem;
work_mem
----------
4MB
(1 row)


2、通过pg_setting视图查看

postgres=# select name,setting from pg_settings where name in ('work_mem');
  name  | setting
----------+---------
work_mem | 4096
(1 row)


3、通过current_setting内置函数查看

postgres=# select current_setting('work_mem');
current_setting
-----------------
4MB
(1 row)


三、通过配置文件修改参数

postgresql.conf中有些参数修改后，需要重启服务才能生效：pg_ctl restart，比如shared_buffers，定义了共享内存的大小。也有许多参数在pg服务运行的时候就能修改，再更改之后像服务器执行一个reload操作，强制pg重新读取postgresql.conf，因此只需要执行 pg_ctl reload即可。

除了直接编辑postgresql.conf文件来修改配置之外，还可以通过使用 alter system命令修改参数，使用alter system命令将修改postgresql.auto.conf文件，每当postgresql.conf被读取时这个文件会被自动读取，并且它的设置会以同样的方式生效。postgresql.auto.conf中的设置会覆盖postgresql.conf中的设置。

用alter system设置的值将在下一次重载服务器配置后生效，那些只能在服务器启动时更改的参数则会在下一次服务器重启后生效。

postgres=# \h alter system
Command:   ALTER SYSTEM
Description: change a server configuration parameter
Syntax:
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
URL: https://www.postgresql.org/docs/13/sql-altersystem.html
// 设置 wal_level
postgres=# ALTER SYSTEM SET wal_level = replica;
// 撤销设置
postgres=# ALTER SYSTEM RESET wal_level;


四、通过SQL修改参数：alter system、alter database、alter role

（1）alter system命令提供了一种改变全局默认值的从SQL可 访问的方法；它在功效上等效于编辑postgresql.conf

（2）为特定的数据库里的所有的用户设置参数：alter database

postgres=# \h alter database
Command:   ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
  ALLOW_CONNECTIONS allowconn
  CONNECTION LIMIT connlimit
  IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
URL: https://www.postgresql.org/docs/13/sql-alterdatabase.html
// 在数据库test中默认禁用索引扫描
postgres=# ALTER DATABASE test SET enable_indexscan TO off;
// 为test数据库所有的连接设置work_mem为16MB
test=# alter database test set work_mem='16MB';
ALTER DATABASE


（3）为数据库中的某个特定用户设置参数：alter role

postgres=# \h alter role
Command:   ALTER ROLE
Description: change a database role
Syntax:
ALTER ROLE role_specification [ WITH ] option [ ... ]
where option can be:
   SUPERUSER | NOSUPERUSER
  | CREATEDB | NOCREATEDB
  | CREATEROLE | NOCREATEROLE
  | INHERIT | NOINHERIT
  | LOGIN | NOLOGIN
  | REPLICATION | NOREPLICATION
  | BYPASSRLS | NOBYPASSRLS
  | CONNECTION LIMIT connlimit
  | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
  | VALID UNTIL 'timestamp'
ALTER ROLE name RENAME TO new_name
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
where role_specification can be:
  role_name
 | CURRENT_USER
 | SESSION_USER
URL: https://www.postgresql.org/docs/13/sql-alterrole.html
// 为brent用户,设置work_mem为2MB
postgres=# alter role brent set work_mem='2MB';
ALTER ROLE


只有当开始一个新的数据库会话时，用ALTER DATABASE和ALTER ROLE设置的值才会被应用。它们会覆盖从配置文件或服务器命令行获得的值，并且作为该会话后续的默认值。注意某些设置在服务器启动后不能被更改，并且因此不能被这些命令设置。

如果同时为数据库和用户设置了特定参数，那么以用户为准；如果同时为数据库和用户设置了特定参数，以及为某个特定用户连接到特定的数据库设置参数，那么以为某个特定用户连接到特定的数据库设置的参数为准、

// 为用户postgres在数据库test中设置work_mem为8MB
test=# alter role postgres in database test set work_mem='8MB';
ALTER ROLE


五、修改当前会话的参数：set

PostgreSQL会提供两个额外的SQL命令（ 以及等效的函数）用以影响会话本地的配置设置：

（1）show命令允许察看所有参数的当前值。对应的函数是current_setting(setting_name text)。

（2）set命令允许修改对于一个会话可以本地设置的参数的当前值， 它对其他会话没有影响。对应的函数是set_config(setting_name, new_value, is_local)

postgres=# show work_mem;
work_mem
----------
4MB
(1 row)
postgres=# set work_mem='7MB';
SET
postgres=# show work_mem;
work_mem
----------
7MB
(1 row)
// 新打开一个会话，查询work_mem，依然显示4MB


（3）set命令后添加 local关键字，只在当前事务中修改，只在当前事务内有效。

postgres=# begin;
BEGIN
postgres=*# show work_mem;
work_mem
----------
7MB
(1 row)
postgres=*# set local work_mem='4MB';
SET
postgres=*# show work_mem;
work_mem
----------
4MB
(1 row)
postgres=*# commit;
COMMIT
postgres=# show work_mem;
work_mem
----------
7MB
(1 row)


六、通过Shell修改参数

除了在数据库或者角色层面上设置全局默认值或者进行覆盖，你还可以通过 shell 工具把设置 传递给PostgreSQL。服务器和libpq客户端库都能通过 shell 接受参数值

（1）在服务器启动期间，可以通过-c命令行参数把参数设置传递给postgres命令，这种方式提供的设置会覆盖通过postgresql.conf或者ALTER SYSTEM提供的设置，因此除了重启服务器之外无法从全局上改变它们

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


（2）当通过libpq启动一个客户端会话时，可以使用PGOPTIONS环境变量指定参数设置。这种方式建立的设置构成了会话生存期间的默认值，但是不会影响 其他的会话。由于历史原因，PGOPTIONS的格式和启动postgres命令时用到的相似，特别是-c标志必须被指定

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