Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-2.x)/ Configuring GUC Parameters/ Resetting Parameters
Updated on 2025-02-27 GMT+08:00
View PDF
Share

Resetting Parameters

You are advised to modify some parameters on the GaussDB console. If the parameters cannot be modified on the console, evaluate the risks and contact customer service.

Background

GaussDB provides multiple methods to set the GUC parameters of databases, users, or sessions.

  • Parameter names are case-insensitive.
  • A parameter value can be an integer, floating point value, 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. Otherwise, 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.

For details about parameters in the hosts configuration template, see GUC Parameters.

Setting GUC Parameters

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

Table 1 GUC parameters

Category

Description

How to Set

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 when the database is started or in the configuration file.

Method 1 in Table 2.

SIGHUP

Global database parameter. It can be set when the database is started or be modified later.

Method 1 or 2 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.

Method 1 or 2 in Table 2.

NOTE:

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 set by any user at any time.

Method 1, 2, or 3 in Table 2.
You can set GUC parameters in GaussDB using the three methods listed in Table 2.
Table 2 Methods for setting GUC parameters

No.

How to Set

Method 1
  1. Log in to the console.
  2. On the Instances page, click the instance name to go to the Basic Information page.
  3. In the navigation pane on the left, click Parameters. On the displayed page, modify parameters.

    If the parameters cannot be modified on the console, evaluate the risks in advance and contact customer service for modification.

  4. Restart the database to make the setting take effect.
    NOTE:

    Rebooting instances will interrupt user operations. Plan a proper execution window before the reboot.

Method 2
  1. Log in to the console.
  2. On the Instances page, click the instance name to go to the Basic Information page.
  3. In the navigation pane on the left, click Parameters. On the displayed page, modify parameters.

    If the parameters cannot be modified on the console, evaluate the risks in advance and contact customer service for modification.

Method 3:
  1. Log in to the console.
  2. On the Instances page, click the instance name to go to the Basic Information page.
  3. In the navigation pane on the left, click Parameters. On the displayed page, modify parameters.

    If the parameters cannot be modified on the console, evaluate the risks in advance and contact customer service for modification.
  • 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.
  • When you use method 3 to set a parameter, if the parameter value is an integer, leading zeros will be filtered out. For example, SET paraname TO 008192 and SET paraname TO 8192 have the same effect.

Procedure

The following example shows how to set hot_standby on the primary node of the database using method 1.

  1. Log in as the OS user omm to the primary node of the database.
  2. View the value of hot_standby. 

    1
    		 
    cat /gaussdb/data/dbnode/postgresql.conf | grep "hot_standby"

    hot_standby = on

    on indicates logs are archived.

  3. Set hot_standby to off to disable log archiving. 

    gs_guc set -Z datanode -D /gaussdb/data/dbnode -c "hot_standby=off"

    You can set hot_standby to off for the database nodes.

    gs_guc set -Z datanode -N all -I all -c "hot_standby=off"

  4. Restart the database to make the setting take effect. 

    gs_om -t stop && gs_om -t start

  5. Run the following command to connect to the database: 

    gsql -d postgres -p 8000

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

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

    gsql((GaussDB Kernel VxxxRxxxCxx 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=#

  6. Check whether the parameter is correctly set. 

    1
2
3
4
5
    		 
    openGauss=# SHOW hot_standby;
 hot_standby
--------------
 off
(1 row)

The following example shows how to set authentication_timeout on the primary node of the database using method 2.

  1. Log in as the OS user omm to the primary node of the database.
  2. View the value of authentication_timeout. 

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

    authentication_timeout = 1min

  3. Set authentication_timeout to 59s. 

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

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

    You can run the following command to set authentication_timeout to 59s for the database nodes:

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

  4. Run the following command to connect to the database: 

    gsql -d postgres -p 8000

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

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

    gsql((GaussDB Kernel VxxxRxxxCxx 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=#

  5. Check whether the parameter is correctly set. 

    1
2
3
4
5
    		 
    openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 59s
(1 row)

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

  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, and 8000 is the port number of the primary node of the database.

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

    gsql((GaussDB Kernel VxxxRxxxCxx 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 explain_perf_mode. 

    1
2
3
4
5
    		 
    openGauss=# SHOW explain_perf_mode;
 explain_perf_mode 
-------------------
 normal
(1 row)

  4. Set explain_perf_mode.

    Perform one of the following operations:

    • Set a database-level parameter. 
      1
      		 
      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. 
      1
      		 
      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. 
      1
      		 
      openGauss=# SET explain_perf_mode TO pretty;

      If the following information is displayed, the setting succeeds:

      SET

  5. Check whether the parameter is correctly set. 

    1
2
3
4
5
    		 
    openGauss=# SHOW explain_perf_mode;
 explain_perf_mode
--------------
 pretty
(1 row)

Examples

  • Example 1: modifying the maximum connections for the primary database node in GaussDB 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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      openGauss=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)
    4. Run the following command to exit the database: 
      1
      		 
      openGauss=# \q
    5. Change the maximum connections for the primary database node in GaussDB. 
      gs_guc set -Z datanode -N all -I all -c "max_connections = 800"
    6. Restart the database. 
      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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)
    4. Run the following command to exit the database: 
      1
      		 
      openGauss=# \q
    5. Change the allowed longest client authentication duration of the primary database node. 
      gs_guc reload -Z datanode -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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 59s
(1 row)
  • Example 3: Change the maximum number of connections between GaussDB 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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      openGauss=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)
    4. Run the following command to exit the database: 
      1
      		 
      openGauss=# \q
    5. Change the maximum number of connections between GaussDB database nodes. 
      gs_guc set -Z datanode -N all -I all -c "max_connections = 500"
    6. Restart the database. 
      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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)
    4. Run the following command to exit the database: 
      1
      		 
      openGauss=# \q
    5. Change the allowed longest client authentication duration of GaussDB database nodes. 
      gs_guc reload -Z datanode -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, and 8000 is the port number of the primary node of the database.

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

      gsql((GaussDB Kernel VxxxRxxxCxx 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. 
      1
2
3
4
5
      		 
      openGauss=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 30s
(1 row)