1查看参数
方法一
select name,context,unit,setting,boot_val,reset_val from pg_settings where name in('listen_addresses','max_connectsions','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem') order by context,name;
方法二
show work_mem
2修改参数
修改参数可以修改配置文件
select * from pg_settings where category like 'File%';
修改上面查看到的配置文件
# - Memory -
shared_buffers = 128MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
work_mem=2MB
重新加载配置文件
使用super用户执行
select pg_reload_conf();
show work_mem
已经改成了2MB。
修改配置文件对于所有用户生效，可以针对一个回话或一个单独的事务设置参数
set work_mem='16MB'; 针对的是会话中的所有的事务生效
set local work_mem = '16MB' 针对的是当前事务生效
3恢复参数的默认设置
reset all;
4查看那些参数不是默认设置
select name,source,setting from pg_settings where source!='default' and source !='override' order by 2,1;
5对特定的用户或组设置参数
alter database saas set configuration_parameter = value; 对saas数据库的所有用户
alter role test set configuration_parameter= value; 对saas角色设置参数
alter role test in database saas set configuration_parameter= value;对连接到saas数据库的test角色设置参数
