MogDB
Ecological Tools
Doc Menu

Resetting Parameters

Background

MogDB 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

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

Table 1 GUC parameters

Parameter type. Remarks 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 MogDB using the methods listed in Table 2.

Table 2 Methods for setting GUC parameters

No. Description
Method 1 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"
2. Restart the database to make the setting take effect.
NOTE:
Restarting MogDB 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 3: Set parameters at database, user, or session levels.
- Set a database-level parameter.
mogdb=# ALTER DATABASE dbname SET paraname TO value;
The setting takes effect in the next session.
- Set a user-level parameter.
mogdb=# ALTER USER username SET paraname TO value;
The setting takes effect in the next session.
- Set a session-level parameter.
mogdb=# SET paraname TO value;
Parameter value in the current session is changed. After you exit the session, the setting becomes invalid.

Procedure

The following example shows how to set archive_mode 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 archive_mode.

    cat /mogdb/data/dbnode/postgresql.conf | grep archive_mode
    archive_mode = on

    on indicates logs are archived.

  3. Set archive_mode to off to disable log archiving.

    gs_guc set -D /mogdb/data/dbnode -c "archive_mode=off"

    img 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"  
  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 mogdb -p 8000

    mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
        
    mogdb=#
  6. Check whether the parameter is correctly set.

    mogdb=# SHOW archive_mode;
     archive_mode
    --------------
     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.

    cat /mogdb/data/dbnode/mogdbql.conf | grep authentication_timeout
    authentication_timeout = 1min
  3. 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!

    img 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"  
  4. Run the following command to connect to the database:

    gsql -d mogdb -p 8000

    mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
        
    mogdb=# 
  5. Check whether the parameter is correctly set.

    mogdb=# 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 mogdb -p 8000

    mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
        
    mogdb=#
  3. View the value of explain_perf_mode.

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

      ALTER DATABASE mogdb 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.

      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.

      SET explain_perf_mode TO pretty;

      If the following information is displayed, the setting succeeds:

      SET
  5. Check whether the parameter is correctly set.

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

Examples

  • Example 1: modifying the maximum connections for the primary database node in MogDB 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 mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    3. View the allowed maximum connections.

      mogdb=# SHOW max_connections;
       max_connections 
      -----------------
       200
      (1 row)
    4. Run the following command to exit the database:

      \q
    5. Change the maximum connections for the primary database node in MogDB.

      gs_guc set -N all -I all -c "max_connections = 800"
    6. Restart MogDB.

      gs_om -t stop && gs_om -t start
    7. Run the following command to connect to the database:

      gsql -d mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    8. View the allowed maximum connections.

      mogdb=# 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 mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    3. View the value of authentication_timeout.

      mogdb=# SHOW authentication_timeout;
       authentication_timeout 
      ------------------------
       1min
      (1 row)
    4. Run the following command to exit the database:

      \q
    5. Change the allowed longest client authentication duration of the primary database node in MogDB.

      gs_guc reload -N all -I all -c "authentication_timeout = 59s"
    6. Run the following command to connect to the database:

      gsql -d mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    7. View the value of authentication_timeout.

      mogdb=# SHOW authentication_timeout;
       authentication_timeout 
      ------------------------
       59s
      (1 row)
  • Example 3: Change the maximum number of connections between MogDB 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 mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    3. View the allowed maximum connections.

      mogdb=# SHOW max_connections;
       max_connections 
      -----------------
       200
      (1 row)
    4. Run the following command to exit the database:

      \q
    5. Change the maximum number of connections between MogDB database nodes.

      gs_guc set -N all -I all -c "max_connections = 500"
    6. Restart MogDB.

      gs_om -t stop
      gs_om -t start
    7. Run the following command to connect to the database:

      gsql -d mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    8. View the allowed maximum connections.

      mogdb=# 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 mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    3. View the value of authentication_timeout.

      mogdb=# SHOW authentication_timeout;
       authentication_timeout 
      ------------------------
       1min
      (1 row)
    4. Run the following command to exit the database:

      \q
    5. Change the allowed longest client authentication duration of MogDB 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 mogdb -p 8000

      mogdb 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 ((MogDB x.x.x build 5be05d82) compiled at 2020-12-08 02:59:43 commit 2143 last mr 131
      Non-SSL connection (SSL connection is recommended when requiring high-security)
      Type "help" for help.
              
      mogdb=#
    7. View the value of authentication_timeout.

      mogdb=# SHOW authentication_timeout;
       authentication_timeout 
      ------------------------
       30s
      (1 row)