一 查看配置文件位置
需求:需要用户有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.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)