PG 参数设置的正确方式

#1.查看参数文件位置
show config_file ;
show hba_file ;
show ident_file ;

也可以用pg_settings数据字典来查看
postgres=# select name,setting from pg_settings where category= 'File Locations';
       name        |               setting                
-------------------+--------------------------------------
 config_file       | /u01/postgresql/data/postgresql.conf
 data_directory    | /u01/postgresql/data
 external_pid_file | 
 hba_file          | /u01/postgresql/data/pg_hba.conf
 ident_file        | /u01/postgresql/data/pg_ident.conf
(5 rows)


#2.查看当前会话的参数值
1) 通过show 查看
show 参数名 ;  
show all;
show work_mem ;

2) 通过pg_settings查看
select * from pg_settings where name like  '%work_mem%' ;

3) 通过current_setting 内置函数查看
select current_setting('work_mem');

#3.修改pg的参数值
通过SQL修改参数：alter system、alter database 。  可以通过\h 命令  的方式查看帮助文档 
alter system 命令提供了一种改变全局默认值的从SQL可 访问的方法；它在功效上等效于编辑postgresql.conf
alter database 为特定的数据库里的所有的用户设置参数 
alter role 为数据库中的某个特定用户设置参数

##1.全局修改pg的参数.
有些参数只有当pg服务重启的时候才生效,典型的例子就是shared_buffers,定义了共享内存的大小.
许多参数在pg服务运行的时候就能修改.再更改之后像服务器执行一个reload操作,强制pg重新读取postgresql.conf,因此你只需要编辑postgresql.conf文件,再执行pg_ctl reload即可.
对于需要重启的,在修改完postgresql后需要执行pg_ctl restart

pg_file_settings查看你设置的参数是否生效.例如如果你设置了一个参数需要重启数据库才能生效或者设置错误,那么在此字典中会出现报错.

对于9.4以后的版本,你还可以使用alter system 命令修改参数.使用alter system命令将修改postgresql.auto.conf文件

alter system set work_mem='8MB';

pg_ctl reload

##2.直接使用set命令,在会话层修改,修改之后将被用于未来的每一个事务,只对当前会话有效:
set work_mem='16MB';

##3.set命令后添加local关键字,只在当前事务中修改,只在当前事务内有效:
test=# begin;
test=# set local work_mem='8MB';
test=# show work_mem;
test=# commit;

##4.使用reset恢复参数的默认值
再pg_settings字典reset_val字段表示了如果使用reset,则此参数恢复的默认值为多少
使用reset 参数名 来恢复某个参数的默认值,使用reset all来恢复所有的参数值.

test=# reset work_mem;

##5.为特定的用户组设置参数
1)为特定的数据库里的所有的用户设置参数,例如为testdb数据库所有的连接设置work_mem为16MB:
test=# alter database testdb set work_mem='16MB';
 
2)为数据库中的某个特定用户设置参数.例如为test用户,设置work_mem为2MB:
经过测试发现,如果你同时为数据库和用户设置了特定参数,那么以用户为准.例如上面的,如果我用test用户连接到testdb数据库,那么我的work_mem应该为2MB:

postgres=# alter role test set work_mem='2MB';
postgres=# \c testdb test
You are now connected to database “testdb” as user “test”.
test=> show work_mem;
work_mem
2MB

3)为某个特定用户连接到特定的数据库设置参数.例如为用户brent在数据库test中设置work_mem为8MB
上面说的三种设置,优先级递增,也就是说,如果设置了1,2,3那么就以第3个为准,如果设置了1,2那么就是以2为准,以此类推.
pg对此的实现方法和当用户连接数据库的时候,立刻手动执行set命令的效果完全相同

test=# alter role test in database testdb set work_mem='8MB';
ALTER ROLE

查看你当前的参数值是从何处指定,可以通过查询pg_setttings中的source字段获取,例如如果设置了database级别的参数.那么查询结果应该如下:
test=# select name,setting,source from pg_settings where name='work_mem';
name | setting | source
----------±--------±---------
work_mem | 16384 | database

其它的,例如设置了第三种: 
test=# \c testdb test
You are now connected to database “testdb” as user “test”.
test=> select name,setting,source from pg_settings where name='work_mem';
name | setting | source
----------±--------±--------------
work_mem | 8192 | database user

##6.通过shell 修改参数
服务器和libpq客户端库都能通过 shell 接受参数值
1在服务器启动期间，可以通过-c命令行参数把参数设置传递给postgres命令，这种方式提供的设置会覆盖通过postgresql.conf或者ALTER SYSTEM提供的设置，
因此除了重启服务器之外无法从全局上改变它们

来自为知笔记(Wiz)