一 查看配置文件位置
需求：需要用户有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)| 类型 | 描述 |
| internal | 这些参数不能直接修改，他们反应了内部确定的值，其中一些可以通过重新initdb来修改，例如block_size，另一些则不能修改，例如server_version(数据库版本) |
| postmaster | 这些参数只能在数据库服务启动时应用，因此修改这类参数需要重新启动数据库 |
| sighup | 应用这些配置需要向postmaster发送sighup信号，使其重新读取配置文件，所以使其生效需要执行pg_ctl reload或者pg_reload_conf()，并且postmaster还将sighup发送到子进程，让所有子进程都获取参数的新值。 |
| superuser-backend | 1.必须是superuser或适当权限才能修改该参数 2.在配置文件中修改后，需要发送SIGHUP使其生效 3.当前会话无法使新参数生效，无法通过set在当前会话中配置新值，只能影响后续启动的会话， 典型参数是log_connections和log_disconnections |
| backend | 1. 在配置文件中修改后，需要发送SIGHUP使其生效 2. 当前会话无法使新参数生效，无法通过set在当前会话中配置新值，只能影响后续启动的会话 |
| superuser | 1. 必须是superuser或适当权限才能修改该参数 |
| user | 1. 普通用户可执行 2. 可按照用户/库进行单独配置 |
按层级排列如下：
| 等级和生效方式 | internal | postmaster | sighup | superuser-backend | backend | superuser | user |
| 无需重新initdb | 否 | 是 | 是 | 是 | 是 | 是 | 是 |
| 无需重启 | 否 | 否 | 是 | 是 | 是 | 是 | 是 |
| 非全局，可按用户/库修改 | 否 | 否 | 否 | 否 | 否 | 是 | 是 |
| 当前会话即可生效 | 否 | 否 | 是 | 否 | 否 | 是 | 是 |
| 普通用户可修改 | 否 | 否 | 否 | 否 | 否 | 否 | 是 |
四 配置文件级、数据库级、用户级、会话级、事务级
优先顺序：
配置文件级<数据库级<用户级<会话级<事务级
1. 配置文件级
直接修改配置文件或alter system,然后reload或pg_reload_conf()
vim /opt/highgo/hgdb-see-4.5.8/data/postgresql.confhighgo=# alter system set work_mem = '8MB';
ALTER SYSTEM2. 数据库级
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)
