PostgreSQL中使用ALTER SYSTEM修改系统参数

在PostgreSQL9.5之前的版本中,修改系统参数一般的步骤是:
1、连接到PostgreSQL数据库服务器;
2、打开postgresql.conf文件,找到需要修改的参数,修改参数的值为所需要的值;
3、执行restart或reload使修改生效;
还有一点比较不方便的是,不能很直观的看到需要重启才生效的参数到底生效了没有,只能把参数show出来观察是否满足预期。
在PostgreSQL9.5中,增加了ALTER SYSTEM命令,并且在pg_settings视图中增加了pending_restart(boolean)列,该列标识了是否在重启之后才生效,使得修改参数和查看参数是否生效都方便了很多。步骤可以简化为:
1、执行ALTER SYSTEM命令
2、执行restart或reload使修改生效;
对于脚本的支持也好了很多,而且通过ALTER SYSTEM配置的参数都会保存在postgresql.auto.conf文件中,需要清除这些配置也只需要一条命令就可以很方便的处理完成,下面有详细的解释。

ALTER SYSTEM语法

vincent=# \h ALTER SYSTEM
Command:     ALTER SYSTEM
Description: change a server configuration parameter
Syntax:
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

示例

举两个例子,一个是shared_buffers参数,该参数需要重启生效;另一个是work_mem参数,该参数只需要pg_ctl reloadSELECT pg_reload_conf();就可以生效。
通过show命令查看,当前的shared_buffers的值为128MB

vincent=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

也可以直接从pg_settings表中去查

vincent=# \x
Expanded display is on.
vincent=# select * from pg_settings where name = 'shared_buffers';
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | shared_buffers
setting         | 16384
unit            | 8kB
category        | Resource Usage / Memory
short_desc      | Sets the number of shared memory buffers used by the server.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | configuration file
min_val         | 16
max_val         | 1073741823
enumvals        | 
boot_val        | 1024
reset_val       | 16384
sourcefile      | /opt/pgdev/data/postgresql.conf
sourceline      | 115
pending_restart | f

执行ALTER SYSTEM命令修改shared_buffers为1GB

vincent=# ALTER SYSTEM SET shared_buffers = '1GB';
ALTER SYSTEM

查看pg_settings表,有哪些参数在等待生效:

vincent=# select name,setting,category,source,sourcefile,pending_restart from pg_settings where pending_restart = true;
-[ RECORD 1 ]---+--------------------------------
name            | shared_buffers
setting         | 16384
category        | Resource Usage / Memory
source          | configuration file
sourcefile      | /opt/pgdev/data/postgresql.conf
pending_restart | t

重新启动DB

vincent@dellpad:~$ pg_ctl -D /opt/pgdev/data/ -m fast restart

通过查询pg_settings表看到shared_buffers已经生效

vincent=# select name,setting,category,source,sourcefile,pending_restart from pg_settings where name='shared_buffers';
      name      | setting |        category         |       source       |              sourcefile              | pending_restart 
----------------+---------+-------------------------+--------------------+--------------------------------------+-----------------
 shared_buffers | 131072  | Resource Usage / Memory | configuration file | /opt/pgdev/data/postgresql.auto.conf | f
(1 row)

如果想清除所有通过ALTER SYSTEM命令配置的参数值,执行

vincent=# ALTER SYSTEM RESET ALL;
ALTER SYSTEM

下面是对work_mem的修改,就不逐步解释了

vincent=# ALTER SYSTEM SET work_mem = '1MB';
ALTER SYSTEM
vincent=# 
vincent=# select name,setting,unit,category,source,sourcefile,pending_restart from pg_settings where name='work_mem';
   name   | setting | unit |        category         | source  | sourcefile | pending_restart 
----------+---------+------+-------------------------+---------+------------+-----------------
 work_mem | 4096    | kB   | Resource Usage / Memory | default |            | f
(1 row)
vincent=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
vincent=# select name,setting,unit,category,source,sourcefile,pending_restart from pg_settings where name='work_mem';
   name   | setting | unit |        category         |       source       |              sourcefile              | pending_restart 
----------+---------+------+-------------------------+--------------------+--------------------------------------+-----------------
 work_mem | 1024    | kB   | Resource Usage / Memory | configuration file | /opt/pgdev/data/postgresql.auto.conf | f
(1 row)
vincent=# \! cat /opt/pgdev/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
work_mem = '1MB'
vincent=#

还没有评论,快来抢沙发!

发表评论

  • 😉
  • 😐
  • 😡
  • 😈
  • 🙂
  • 😯
  • 🙁
  • 🙄
  • 😛
  • 😳
  • 😮
  • emoji-mrgree
  • 😆
  • 💡
  • 😀
  • 👿
  • 😥
  • 😎
  • ➡
  • 😕
  • ❓
  • ❗
  • 71 queries in 0.369 seconds