postgres配置参数生效方式和优先级

一 查看配置文件位置

需求：需要用户有pg_read_all_settings权限或者是superUser

1. 通过show命令查看配置文件位置

highgo=# show config_file;
                   config_file                   
-------------------------------------------------
 /opt/highgo/hgdb-see-4.5.8/data/postgresql.conf
(1 row)
highgo=# show hba_file ;
                  hba_file                   
---------------------------------------------
 /opt/highgo/hgdb-see-4.5.8/data/pg_hba.conf
(1 row)
highgo=# show ident_file ;
                  ident_file                   
-----------------------------------------------
 /opt/highgo/hgdb-see-4.5.8/data/pg_ident.conf
(1 row)

2. 通过pg_setting表查看配置文件位置

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

二 查看运行时的参数值

1. 通过show命令查看

highgo=# show work_mem ;
 work_mem 
----------
 8MB
(1 row)

2. 通过pg_setting视图查看

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

3. 通过current_setting内置函数查看

highgo=# select current_setting('work_mem');
 current_setting 
-----------------
 8MB
(1 row)

三 参数修改生效条件

1. 通过pg_settings视图检查参数生效条件

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

按层级排列如下：

四 配置文件级、数据库级、用户级、会话级、事务级

优先顺序：
配置文件级<数据库级<用户级<会话级<事务级

1. 配置文件级

直接修改配置文件或alter system,然后reload或pg_reload_conf()

vim /opt/highgo/hgdb-see-4.5.8/data/postgresql.conf

highgo=# alter system set  work_mem  = '8MB';
ALTER SYSTEM

2. 数据库级

highgo=# alter database db_test set work_mem  = '7MB';
ALTER DATABASE
highgo=# \c db_test
db_test=# show work_mem ;
 work_mem 
----------
 7MB
(1 row)

3. 用户级

db_test=# alter user user1 set work_mem  = '6MB';
ALTER ROLE
db_test=# \c db_test user1
db_test=> show work_mem ;
 work_mem 
----------
 6MB
(1 row)

查询用户级的配置

db_test=> select usename , useconfig from pg_user;
 usename |                                                 useconfig                                                 
---------+-----------------------------------------------------------------------------------------------------------
 user1   | {"search_path=\"$user\", public, dongsc",vacuum_cost_delay=1,commit_delay=1,wal_recycle=off,work_mem=6MB}
 dongsc4 | 
 zfwdba  | 
 sysdba  | 
 dongsc3 | 
 syssso  | 
 syssao  | 
 dongsc5 | 
 dongsc  | 
 dongsc2 | 
(10 rows)

4. 会话级

db_test=> set work_mem = '5MB';
SET
db_test=> show work_mem ;
 work_mem 
----------
 5MB
(1 row)

5. 事务级

db_test=> begin;
BEGIN
db_test=> show work_mem;
 work_mem 
----------
 5MB
(1 row)
db_test=> set local work_mem='4MB';      --修改事务内的workmem=4MB
SET
db_test=> show work_mem;
 work_mem 
----------
 4MB
(1 row)
db_test=> commit;
COMMIT
db_test=> show work_mem;           --事务结束后，work_mem恢复到会话级别的5MB
 work_mem 
----------
 5MB
(1 row)