Updated on 2025-05-29 GMT+08:00

Setting Parameters

Context

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

  • Parameter names are case-insensitive.
  • The parameter values can be integers, floating points, strings, Boolean values, or enumerated values.
    • 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 system catalog pg_settings.
  • 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, see GUC Parameters.

Setting GUC Parameters

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

Table 1 GUC parameter types

Parameter Type

Description

Setting Method

INTERNAL

Fixed parameters. They are set during database creation and cannot be modified. Users can only view the parameters by running the SHOW command or in the pg_settings view.

None.

POSTMASTER

Database server parameters. They can be set when the database is started or in the configuration file.

Method 1 in Table 2.

SIGHUP

Global database parameters. They can be set when the database is started or be modified later.

Method 1 or 2 in Table 2.

BACKEND

Session connection parameters. They are specified during session connection creation and cannot be modified after that. The parameter setting becomes invalid when the session is disconnected. The parameters of this type are internal parameters 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 parameters. They can be set by common users during database startup or after the database is started. They 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 parameters. They 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 four methods listed in Table 2.
Table 2 Methods for setting GUC parameters

No.

Setting Method

Method 1

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

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

  4. Reboot the DB instance to make the modifications take effect.
    NOTE:

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

Method 2

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

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

Method 3

Set parameters at the database, user, or session level.

  • Set a database-level parameter.
    1
    gaussdb=# ALTER DATABASE dbname SET paraname TO value;
    

    The setting takes effect in the next session.

  • Set a user-level parameter.
    1
    gaussdb=# ALTER USER username SET paraname TO value;
    

    The setting takes effect in the next session.

  • Set a session-level parameter.
    1
    gaussdb=# SET paraname TO value;
    

    The 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, prior to those set by ALTER. Parameter values set by ALTER DATABASE have a higher priority than those set using ALTER USER. Priorities of the three methods are all higher than using gs_guc.

    The parameters set by ALTER DATABASE and ALTER USER do not take effect in the current session. You need to restart the session for the parameters to take effect.

  • If you use method 1 or method 2 to set a parameter that does not belong to the current node, the database displays a message indicating that the parameter is not supported.
  • You cannot set a parameter in the unit of pages by using method 1 or method 2.
  • 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.