【转自运维技术 - HackRoad.com】PostgreSQL中使用ALTER SYSTEM修改系统参数

本文转自 https://blog.hackroad.com/operations-engineer/linux_server/13212.html 

在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

vincent=# \h ALTER SYSTEMCommand: ALTER SYSTEMDescription: change a server configuration parameterSyntax:ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }ALTER SYSTEM RESET configuration_parameterALTER SYSTEM RESET ALL

示例

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

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

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

vincent=# \xExpanded display is on.vincent=# select * from pg_settings where name = 'shared_buffers';-[ RECORD 1 ]---+-------------------------------------------------------------name | shared_bufferssetting | 16384unit | 8kBcategory | Resource Usage / Memoryshort_desc | Sets the number of shared memory buffers used by the server.extra_desc | context | postmastervartype | integersource | configuration filemin_val | 16max_val | 1073741823enumvals | boot_val | 1024reset_val | 16384sourcefile | /opt/pgdev/data/postgresql.confsourceline | 115pending_restart | f

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

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

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

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

重新启动DB

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

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)

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

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=#