1.参数文件在哪
MogDB安装后，有一套默认的运行参数，保存在GaussDB 的data目录下面的postgresql.conf文件。我们切换到omm用户，执行命令gs_om查看数据目录/opt/mogdb/install/data/dn
[omm@mogdb01 ~]$ gs_om -t status --detail
[ Cluster State ]
cluster_state : Unavailable
redistributing : No
current_az : AZ_ALL
[ Datanode State ]
node node_ip port instance state
---------------------------------------------------------------------------------------------
1 mogdb01 192.168.56.203 15400 6001 /opt/mogdb/install/data/dn P Down Manually stopped
2 mogdb02 192.168.56.204 15400 6002 /opt/mogdb/install/data/dn S Down Manually stopped
进入数据目录查看配置文件postgresql.conf
[omm@mogdb01 ~]$ cd /opt/mogdb/install/data/dn/
[omm@mogdb01 dn]$ ls postgresql.conf
postgresql.conf
[omm@mogdb01 dn]$2.怎样查看参数
查看参数的方式有3种，分别是查看视图pg_settings、show和直接查看参数文件postgres.sql。
2.1 视图pg_settings
执行元命令\d查看视图pg_settings的列及详细描述。
MogDB=# \d pg_settings
View "pg_catalog.pg_settings"
Column | Type | Modifiers
------------+---------+-----------
name | text |
setting | text |
unit | text |
category | text |
short_desc | text |
extra_desc | text |
context | text |
vartype | text |
source | text |
min_val | text |
max_val | text |
enumvals | text[] |
boot_val | text |
reset_val | text |
sourcefile | text |
sourceline | integer || 名称 | 类型 | 描述 |
| name | text | 参数名称。 |
| setting | text | 参数当前值。 |
| unit | text | 参数的隐式结构。 |
| category | text | 参数的逻辑组。 |
| short_desc | text | 参数的简单描述。 |
| extra_desc | text | 参数的详细描述。 |
| context | text | 设置参数值的上下文，包括internal、postmaster、sighup、backend、superuser、user。 |
| vartype | text | 参数类型，包括bool、enum、integer、real、string。 |
| source | text | 参数的赋值方式。 |
| min_val | text | 参数最小值。如果参数类型不是数值型，那么该字段值为null。 |
| max_val | text | 参数最大值。如果参数类型不是数值型，那么该字段值为null。 |
| enumvals | text[] | enum类型参数合法值。如果参数类型不是enum型，那么该字段值为null。 |
| boot_val | text | 数据库启动时参数默认值。 |
| reset_val | text | 数据库重置时参数默认值。 |
| sourcefile | text | 设置参数值的配置文件。如果参数不是通过配置文件赋值，那么该字段值为null。 |
| sourceline | integer | 设置参数值的配置文件的行号。如果参数不是通过配置文件赋值，那么该字段值为null。 |
比如，查看参数max_connections，没有单位，类型为integer，默认值为200等等。
MogDB=# \x on
Expanded display is on.
MogDB=# select * from pg_settings where name like '%max_connec%';
-[ RECORD 1 ]--------------------------------------------------------------
name | max_connections
setting | 5000
unit |
category | Connections and Authentication / Connection Settings
short_desc | Sets the maximum number of concurrent connections for clients.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 10
max_val | 262143
enumvals |
boot_val | 200
reset_val | 5000
sourcefile | /opt/mogdb/install/data/dn/postgresql.conf
sourceline | 74
MogDB=# \x
Expanded display is off.
MogDB=#
2.2 命令show
命令show可以用于查看单个参数或者所有参数，使用帮助查看命令show的用法。
MogDB=# \h show
Command: SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW { configuration_parameter | CURRENT_SCHEMA | TIME ZONE | TRANSACTION ISOLATION LEVEL | SESSION AUTHORIZATION | ALL };
MogDB=# 比如，查看服务器版本，可以看到，show不支持模糊匹配，show all将显示所有参数：
MogDB=# show server_;
ERROR: unrecognized configuration parameter "server_"
MogDB=# show server_version;
server_version
----------------
9.2.4
(1 row)
MogDB=#
3.参数生效方式有哪些
查下视图pg_settings列context可以获取每个参数生效的方式，可以看到有6种类型，可以划分为4类生效方式，分别为：重新编译代码、重启服务、所有连接立即生效、新连接生效
MogDB=# select distinct context from pg_settings;
context
------------
internal
user
postmaster
backend
sighup
superuser
(6 rows)
MogDB=#
参数生效方式及设置方式
| 生效类型 | 生效方式 | 设置方式 | |||
| internal | 重新编译生效 | ||||
| postmaster | 重启服务生效 | gs_guc set、alter system | |||
| sighup | reload生效 | gs_guc set/reload、alter system | |||
| backend | reload后，新连接生效 | gs_guc set/reload、alter system | |||
| superuser | superuser才能更改，立即生效 | gs_guc set/reload、alter database/user | |||
| user | 任何时间做修改，只会影响该会话 | gs_guc set/reload、set/alter database/alter user | |||
每种生效方式的说明：
| 参数类型 | 说明 |
| INTERNAL | 固定参数，在创建数据库的时候确定，用户无法修改，只能通过show语法或者pg_settings视图进行查看。 |
| POSTMASTER | 数据库服务端参数，在数据库启动时确定，可以通过配置文件指定。 |
| SIGHUP | 数据库全局参数，可在数据库启动时设置或者在数据库启动后，发送指令重新加载。 |
| BACKEND | 会话连接参数。在创建会话连接时指定，连接建立后无法修改。连接断掉后参数失效。内部使用参数，不推荐用户设置。 |
| SUPERUSER | 数据库管理员参数。可在数据库启动时、数据库启动后或者数据库管理员通过SQL进行设置。 |
| USER | 普通用户参数。可被任何用户在任何时刻设置。 |
4.怎样修改参数
3.1 有哪些方式可以用来进行参数的修改
配置文件（“postgresql.conf”、“pg_hba.conf”）保存数据库所有参数。
有3种修改方式，分别是gs_guc、alter和set。
1) gs_guc
服务端工具gs_guc用于设置openGauss配置文件（“postgresql.conf”、“pg_hba.conf”）中的参数。路径$GAUSSLOG/bin/gs_guc下面文件记录gs_guc产生的日志。
[omm@mogdb01 ~]$ cd $GAUSSLOG/bin/gs_guc
[omm@mogdb01 gs_guc]$ ls
gs_guc-2023-03-27_100400-current.log
[omm@mogdb01 gs_guc]$ gs_guc的命令有3个，set, check和reload
•set 表示只修改配置文件中的参数。
•check 表示只检查配置文件中的参数。
•reload 表示修改配置文件中的参数，同时发送信号量给数据库进程，使其重新加载配置文件。
gs_guc常用的命令选项有，具体的命令使用说明可以使用gs_guc --help进行查看
-Z默认且仅仅为datanode
-N 表示节点,all为所有节点
-I 表示实例，all为所有实列
-c 表示为postgresql.conf
-h表示为pg_hba.conf
2) alter
修改指定数据库，用户，重新连接后生效。
3) set
设置会话级别的参数。
3.2 修改参数方式一：gs_guc set
命令set，表示只修改配置文件中的参数，重启生效。主要用于修改pg_settings.context值为postmaster的参数。如果是高可用架构，会同步修改参数的值到备节点。
[omm@mogdb01 gs_guc]$ gs_guc check -N all -I all -c "max_connections"
The gs_guc run with the following arguments: [gs_guc -N all -I all -c max_connections check ].
Total GUC values: 2. Failed GUC values: 0.
The value of parameter max_connections is same on all instances.
max_connections=5000
[omm@mogdb01 gs_guc]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# show max_connections;
max_connections
-----------------
5000
(1 row)
MogDB=# \q
[omm@mogdb01 gs_guc]$ gs_guc set -I all -c "max_connections=1000"
The gs_guc run with the following arguments: [gs_guc -I all -c max_connections=1000 set ].
expected instance path: [/opt/mogdb/install/data/dn/postgresql.conf]
gs_guc set: max_connections=1000: [/opt/mogdb/install/data/dn/postgresql.conf]
Total instances: 1. Failed instances: 0.
Success to perform gs_guc!
[omm@mogdb01 gs_guc]$ gs_guc check -N all -I all -c "max_connections"
The gs_guc run with the following arguments: [gs_guc -N all -I all -c max_connections check ].
Total GUC values: 2. Failed GUC values: 0.
The details for max_connections:
[mogdb01] max_connections=1000 [/opt/mogdb/install/data/dn/postgresql.conf]
[mogdb02] max_connections=5000 [/opt/mogdb/install/data/dn/postgresql.conf]
[omm@mogdb01 gs_guc]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# show max_connections;
max_connections
-----------------
5000
(1 row)
MogDB=# select name,setting,context,unit from pg_settings where name='max_connections';
name | setting | context | unit
-----------------+---------+------------+------
max_connections | 5000 | postmaster |
(1 row)
修改参数值后，重启数据库，使修改后的参数重新加载生效。
[omm@mogdb01 gs_guc]$ gs_om -t stop && gs_om -t start
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
Starting cluster.
=========================================
[SUCCESS] mogdb01
2023-04-10 11:18:48.024 64338017.1 [unknown] 140417748863232 [unknown] 0 dn_6001_6002 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2023-04-10 11:18:48.027 64338017.1 [unknown] 140417748863232 [unknown] 0 dn_6001_6002 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (2915 Mbytes) is larger.
[SUCCESS] mogdb02
2023-04-10 11:18:50.274 6433801a.1 [unknown] 140325525240064 [unknown] 0 dn_6001_6002 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2023-04-10 11:18:50.278 6433801a.1 [unknown] 140325525240064 [unknown] 0 dn_6001_6002 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3617 Mbytes) is larger.
Waiting for check cluster state...
=========================================
Successfully started.
[omm@mogdb01 gs_guc]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# select name,setting,context,unit from pg_settings where name='max_connections';
name | setting | context | unit
-----------------+---------+------------+------
max_connections | 1000 | postmaster |
(1 row)
MogDB=#
3.3 修改参数方式二：gs_guc reload
命令reload，表示修改配置文件中的参数，同时发送信号量给数据库进程，使其重新加载配置文件，也就是会话不用退出也会看到修改后的值。
使用reload修改context为postmaster的参数，看看具体执行哪些动作。可以看到，修改了配置文件postgresql.conf，也发送信号，由于参数max_connections需要重启才能生效，所有无法看到修改后的值。
[omm@mogdb01 gs_guc]$ gs_guc reload -I all -c "max_connections=2000"
The gs_guc run with the following arguments: [gs_guc -I all -c max_connections=2000 reload ].
expected instance path: [/opt/mogdb/install/data/dn/postgresql.conf]
gs_guc reload: max_connections=2000: [/opt/mogdb/install/data/dn/postgresql.conf]
server signaled
Total instances: 1. Failed instances: 0.
Success to perform gs_guc!
[omm@mogdb01 gs_guc]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# show max_connections;
max_connections
-----------------
1000
(1 row)
MogDB=# \q
[omm@mogdb01 gs_guc]$ gs_guc check -N all -I all -c "max_connections"
The gs_guc run with the following arguments: [gs_guc -N all -I all -c max_connections check ].
Total GUC values: 2. Failed GUC values: 0.
The value of parameter max_connections is same on all instances.
max_connections=2000
[omm@mogdb01 gs_guc]$ cat /opt/mogdb/install/data/dn/postgresql.conf|grep max_connections
max_connections = 2000 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
[omm@mogdb01 gs_guc]$
我们再来修改context为sighup的参数audit_enabled，可以看到参数修改后，当前会话不用退出立即生效。
[omm@mogdb01 gs_guc]$ gs_guc check -N all -I all -c "audit_enabled"
The gs_guc run with the following arguments: [gs_guc -N all -I all -c audit_enabled check ].
Total GUC values: 2. Failed GUC values: 0.
The value of parameter audit_enabled is same on all instances.
audit_enabled=on
[omm@mogdb01 gs_guc]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# show audit_enabled;
audit_enabled
---------------
on
(1 row)
MogDB=# \! gs_guc reload -I all -c "audit_enabled=off"
The gs_guc run with the following arguments: [gs_guc -I all -c audit_enabled=off reload ].
expected instance path: [/opt/mogdb/install/data/dn/postgresql.conf]
gs_guc reload: audit_enabled=off: [/opt/mogdb/install/data/dn/postgresql.conf]
server signaled
Total instances: 1. Failed instances: 0.
Success to perform gs_guc!
MogDB=# show audit_enabled;
audit_enabled
---------------
off
(1 row)
MogDB=# select name,setting,context,unit from pg_settings where name='audit_enabled';
name | setting | context | unit
---------------+---------+---------+------
audit_enabled | off | sighup |
(1 row)
MogDB=#
3.3 修改参数方式三：alter
1）alter database/user
SQL命令alter database/user，用于修改指定数据库和用户的context值为superuser和user的参数，会话重新连接后生效。
MogDB=# select name,setting,context,unit from pg_settings where name='deadlock_timeout';
name | setting | context | unit
------------------+---------+-----------+------
deadlock_timeout | 1000 | superuser | ms
(1 row)
MogDB=# \c
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
MogDB=# alter user omm set deadlock_timeout to 1500;
ALTER ROLE
MogDB=# show deadlock_timeout;
deadlock_timeout
------------------
1s
(1 row)
MogDB=# \q
[omm@mogdb01 gs_guc]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# show deadlock_timeout;
deadlock_timeout
------------------
1500ms
(1 row)
MogDB=#
2） alter system
ALTER SYSTEM，用于修改context值为postmaster、sighup和backend的数据库参数。
修改context值为superuser则为报错，提时如下：
MogDB=# alter system set deadlock_timeout to 1000;
ERROR: unsupport parameter: deadlock_timeout
ALTER SYSTEM SET only support POSTMASTER-level, SIGHUP-level and BACKEND-level guc variable,
and it must be allowed to set in postgresql.conf.
下面分别修改context值为postmaster、sighup和backend的参数：
MogDB=# select name,setting,context,unit from pg_settings where name='audit_enabled';
name | setting | context | unit
---------------+---------+---------+------
audit_enabled | off | sighup |
(1 row)
MogDB=# alter system set audit_enabled to on;
ALTER SYSTEM SET
MogDB=# show audit_enabled;
audit_enabled
---------------
on
(1 row)
MogDB=# select name,setting,context,unit from pg_settings where name='max_connections';
name | setting | context | unit
-----------------+---------+------------+------
max_connections | 2000 | postmaster |
(1 row)
MogDB=# alter system set max_connections to 500;
NOTICE: please restart the database for the POSTMASTER level parameter to take effect.
ALTER SYSTEM SET
MogDB=#
MogDB=# select name,setting,context,unit from pg_settings where name='log_connections';
name | setting | context | unit
-----------------+---------+---------+------
log_connections | off | backend |
(1 row)
MogDB=# alter system set log_connections to on;
NOTICE: please reconnect the database for the BACKEND level parameter to take effect.
ALTER SYSTEM SET
MogDB=#
3.4 修改参数方式三：set
set，用于设置会话级别的参数，修改本次会话中的取值。退出会话后，设置将失效
MogDB=# select name,setting,context,unit from pg_settings where name='client_encoding';
name | setting | context | unit
-----------------+---------+---------+------
client_encoding | UTF8 | user |
(1 row)
MogDB=# set client_encoding to GBK;
SET
MogDB=# show client_encoding;
client_encoding
-----------------
GBK
(1 row)
MogDB=# show deadlock_timeout;
deadlock_timeout
------------------
1500ms
(1 row)
MogDB=# set deadlock_timeout to 1000;
SET
MogDB=# show deadlock_timeout;
deadlock_timeout
------------------
1s
(1 row)
MogDB=# select name,setting,context,unit from pg_settings where name='deadlock_timeout';
name | setting | context | unit
------------------+---------+-----------+------
deadlock_timeout | 1000 | superuser | ms
(1 row)
MogDB=# show log_connections;
log_connections
-----------------
off
(1 row)
MogDB=# set log_connections to on;
ERROR: parameter "log_connections" cannot be set after connection start
MogDB=# \q
[omm@mogdb01 gs_guc]$ g
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 17:47:05 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# show client_encoding;
client_encoding
-----------------
UTF8
(1 row)
MogDB=#
3.4 修改参数方式四：编辑配置文件
直接编辑postgresql.conf/pg_hba.conf，这是不推荐的方式。比如，参数无法校验；主备架构，无法自动同步等风险。
最后修改时间：2023-04-10 14:25:44
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
