Resetting Parameters

Background

openGauss provides multiple methods to set GUC parameters for databases, users, or sessions.

Parameter names are case-insensitive.
A parameter value can be an integer, floating point number, string, Boolean value, or enumerated value.
- The Boolean values can be on/off, true/false, yes/no, or 1/0, and are case-insensitive.
- The enumerated value range is specified in the enumvals column of the pg_settings system catalog.
For parameters using units, specify their units during the setting, or default units are used.
- The default units are specified in the unit column of pg_settings.
- The unit of memory can be kB, MB, or GB.
- The unit of time can be ms, s, min, h, or d.

Setting GUC Parameters

openGauss provides six types of GUC parameters. For details about parameter types and their setting methods, see Table 1.

Table 1 GUC parameters

Parameter Type	Description	Setting Method
INTERNAL	Fixed parameter. It is set during database creation and cannot be modified. Users can only view the parameter by running the SHOW command or in the pg_settings view.	None
POSTMASTER	Database server parameter. It can be set in the configuration file when the database is started.	Methods 1 and 4 in Table 2
SIGHUP	Global database parameter. It can be set when the database is started or be modified later.	Methods 1, 2, and 4 in Table 2
BACKEND	Session connection parameter. It is specified during session connection creation and cannot be modified after that. The parameter setting becomes invalid when the session is disconnected. This is an internal parameter and not recommended for users to set it.	Methods 1, 2, and 4 in Table 2 Note: After this parameter is set on the The parameter setting takes effect when the next session is created.
SUSET	Database administrator parameter. It can be set by common users when or after the database is started. It can also be set by database administrators using SQL statements.	Method 1 or 2 by a common user, or method 3 by a database administrator in Table 2.
USERSET	Common user parameter. It can be configured by any user at any time.	Methods 1, 2, and 3 in Table 2.

openGauss provides four methods to modify GUC parameters. For details, see Table 2.

Table 2 Methods for setting GUC parameters

No.	Setting Method
Method 1	Run the following command to set a parameter: gs_guc set -D datadir -c "paraname=value" Note: If the parameter is a string variable, use -c parameter="'value'" or -c "parameter = 'value'". Run the following command to set a parameter for database nodes at the same time: *gs_guc set -N all* -I all -c "paraname=value" Run the following commands to set a cm_agent parameter for database nodes: gs_guc set -Z cmagent -c "paraname=value" gs_guc set -Z cmagent -N all -I all -c "paraname=value" Run the following commands to set a cm_server parameter for database nodes: gs_guc set -Z cmserver -c "paraname=value" gs_guc set -Z cmserver -N all -I all -c "paraname=value" Restart the database to make the setting take effect. Note: Restarting the openGauss results in operation interruption. Properly plan the restart to avoid affecting users. gs_om -t stop && gs_om -t start**
Method 2	gs_guc reload -D datadir -c "paraname=value" Note: Run the following command to set a parameter for database nodes at the same time: gs_guc reload -N** all -I all -c "paraname=value"**
Method 2	Set parameters at database, user, or session levels. Set a database-level parameter. openGauss=# ALTER DATABASE dbname SET paraname TO value; The setting takes effect in the next session. Set a user-level parameter. openGauss=# ALTER USER username SET paraname TO value; The setting takes effect in the next session. Set a session-level parameter. openGauss=# SET paraname TO value; Parameter value in the current session is changed. After you exit the session, the setting becomes invalid. Note: Session-level parameters set by SET have the highest priority, followed by parameters set by ALTER. Parameter values set by ALTER DATABASE have a higher priority than those set using ALTER USER. Priorities of the first three methods are all higher than those of gs_guc.
Method 4	Use ALTER SYSTEM SET to modify database parameters. Set a POSTMASERT-level parameter. openGauss=# ALTER SYSTEM SET paraname TO value; The setting takes effect after the system is restarted. Set a SIGHUP-level parameter. openGauss=# ALTER SYSTEM SET paraname TO value; The setting takes effect immediately. (Actually, there is a slight delay to wait for the thread reloading the parameter.) Set a BACKEND-level parameter. openGauss=# ALTER SYSTEM SET paraname TO value; The setting takes effect in the next session.

Caution: If you use method 1 or 2 to set a parameter that does not belong to the current environment, the database displays a message indicating that the parameter is not supported.

Procedure

The following example shows how to set archive_mode on a primary database node using method 1.

Check the archive_mode parameter.

cat /gaussdb/data/dbnode/postgresql.conf | grep archive_mode

archive_mode = on

on indicates logs are archived.

Set archive_mode to off to disable log archiving.
```
gs_guc set -D /gaussdb/data/dbnode -c "archive_mode=off"
```
NOTE: You can run the following command to set archive_mode to off for the database nodes:
```
gs_guc set -N all -I all -c "archive_mode=off"
```
Restart the database to make the setting take effect.
```
gs_om -t stop && gs_om -t start
```

Run the following command to connect to the database:

gsql -d postgres -p 8000

postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.

If information similar to the following is displayed, the connection is successful:

gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=#

Check whether the parameter is correctly configured.

openGauss=# SHOW archive_mode;
 archive_mode
--------------
 off
(1 row)

The following describes how to set authentication_timeout on a primary database node using method 2.

Check the authentication_timeout parameter.

cat /gaussdb/data/dbnode/postgresql.conf | grep authentication_timeout

authentication_timeout = 1min

Set authentication_timeout to 59s.

gs_guc reload -N all -I all -c "authentication_timeout = 59s"

Total instances: 2. Failed instances: 0.
Success to perform gs_guc!

NOTE: You can run the following command to set authentication_timeout to 59s for the database nodes:
gs_guc reload -N all -I all -c "authentication_timeout = 59s"

Run the following command to connect to the database:

gsql -d postgres -p 8000

postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.

If information similar to the following is displayed, the connection is successful:

gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=#

Check whether the parameter is correctly configured.

openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 59s
(1 row)

The following example shows how to set explain_perf_mode using method 3.

Run the following command to connect to the database:

gsql -d postgres -p 8000

postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.

If information similar to the following is displayed, the connection is successful:

gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=#

Check the explain_perf_mode parameter.

openGauss=# SHOW explain_perf_mode;
 explain_perf_mode 
-------------------
 normal
(1 row)

Set the explain_perf_mode parameter.
Perform one of the following operations:
- Set a database-level parameter.
```
openGauss=# ALTER DATABASE postgres SET explain_perf_mode TO pretty;
```
  If the following information is displayed, the setting succeeds:
```
ALTER DATABASE
```
  The setting takes effect in the next session.
- Set a user-level parameter.
```
openGauss=# ALTER USER omm SET explain_perf_mode TO pretty;
```
  If the following information is displayed, the setting succeeds:
```
ALTER ROLE
```
  The setting takes effect in the next session.
- Set a session-level parameter.
```
openGauss=# SET explain_perf_mode TO pretty;
```
  If the following information is displayed, the setting succeeds:
```
SET
```

Check whether the parameter is correctly configured.

openGauss=# SHOW explain_perf_mode;
 explain_perf_mode
--------------
 pretty
(1 row)

Examples

Example 1: modifying the maximum connections for the primary database node in openGauss using method 1
1. Log in as the OS user omm to the primary node of the database.
2. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
3. View the allowed maximum connections.
```
openGauss=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)
```
4. Run the following command to exit the database:
```
openGauss=# \q
```
5. Change the maximum number of connections for the primary database node in openGauss.
```
gs_guc set -N all -I all -c "max_connections = 800"
```
6. Restart the openGauss.
```
gs_om -t stop && gs_om -t start
```
7. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
8. View the allowed maximum connections.
```
openGauss=# SHOW max_connections;
 max_connections 
-----------------
 800
(1 row)
```
Example 2: setting authentication_timeout (the allowed longest client authentication duration) for the primary database node using method 2
1. Log in as the OS user omm to the primary node of the database.
2. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
3. View the value of authentication_timeout.
```
openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)
```
4. Run the following command to exit the database:
```
openGauss=# \q
```
5. Change the allowed longest client authentication duration of the primary database node.
```
gs_guc reload -N all -I all -c "authentication_timeout = 59s"
```
6. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
7. View the value of authentication_timeout.
```
openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 59s
(1 row)
```
Example 3: Change the maximum number of connections of the openGauss database node.
1. Log in as the OS user omm to the primary node of the database.
2. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
3. View the allowed maximum connections.
```
openGauss=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)
```
4. Run the following command to exit the database:
```
openGauss=# \q
```
5. Change the maximum number of connections of the openGauss database node.
```
gs_guc set -N all -I all -c "max_connections = 500"
```
6. Restart the openGauss.
```
gs_om -t stop
gs_om -t start
```
7. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
8. View the allowed maximum connections.
```
openGauss=# SHOW max_connections;
 max_connections 
-----------------
 500
(1 row)
```
Example 4: setting authentication_timeout (the allowed longest client authentication duration) for database nodes
1. Log in as the OS user omm to the primary node of the database.
2. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
3. View the value of authentication_timeout.
```
openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)
```
4. Run the following command to exit the database:
```
openGauss=# \q
```
5. Change the allowed longest client authentication duration of openGauss database nodes.
```
gs_guc reload -N all -I all -c "authentication_timeout = 30s"
```
6. Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
  postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.
  If information similar to the following is displayed, the connection is successful:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
7. View the value of authentication_timeout.
```
openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 30s
(1 row)
```