GaussDB_Huawei Cloud

Background

openGauss 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

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.	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 or 4 in Table 2.
SIGHUP	Global database parameter. It can be set when the database is started or be modified later.	Method 1, 2, or 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.	Method 1, 2, or 4 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 openGauss using the methods listed in Table 2.

Table 2 Methods for setting GUC parameters

No.

Description

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"
```
Restart the database to make the setting take effect.
NOTE:

Restarting 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 3:

Set parameters at database, user, or session levels.

Set a database-level parameter.

            
                 postgres=# ALTER DATABASE dbname SET paraname TO value;

The setting takes effect in the next session.

Set a user-level parameter.

            
                 postgres=# ALTER USER username SET paraname TO value;

The setting takes effect in the next session.

Set a session-level parameter.

            
                 postgres=# SET paraname TO value;

Parameter value in the current session is changed. After you exit the session, the setting becomes invalid.

Method 4

Modify database parameters using ALTER SYSTEM SET.

Set a POSTMASERT-level parameter.

            
                 postgres=# ALTER SYSTEM SET paraname TO value;

Restart the system for the setting to take effect.

Set a SIGHUP-level parameter.

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

            
                 postgres=# ALTER SYSTEM SET paraname TO value;

The setting takes effect in the next session.

Procedure

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

Log in as the OS user omm to the primary node of the database.

View the value of archive_mode.

      
           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"
```
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, 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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

Check whether the parameter is correctly set.

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

Log in as the OS user omm to the primary node of the database.

View the value of authentication_timeout.

      
           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!

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, 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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

Check whether the parameter is correctly set.

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

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

Log in as the OS user omm to the primary node of the database.

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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the value of explain_perf_mode.

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

Set explain_perf_mode.

Perform one of the following operations:

Set a database-level parameter.

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

        
             postgres=# 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

postgres=# SET explain_perf_mode TO pretty;
If the following information is displayed, the setting succeeds:
```
SET
```

Check whether the parameter is correctly set.

      
           postgres=# 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

Log in as the OS user omm to the primary node of the database.

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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the allowed maximum connections.

        
             postgres=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)

Run the following command to exit the database:
1

postgres=# \q
Change the maximum connections for the primary database node in openGauss.
```
gs_guc set -N all -I all -c "max_connections = 800"
```
Restart openGauss.
```
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, 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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the allowed maximum connections.

        
             postgres=# 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

Log in as the OS user omm to the primary node of the database.

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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the value of authentication_timeout.

        
             postgres=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)

Run the following command to exit the database:
1

postgres=# \q
Change the allowed longest client authentication duration of the primary database node in openGauss.
```
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, 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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the value of authentication_timeout.

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

Example 3: Change the maximum number of connections between openGauss database nodes.

Log in as the OS user omm to the primary node of the database.

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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the allowed maximum connections.

        
             postgres=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)

Run the following command to exit the database:
1

postgres=# \q
Change the maximum number of connections between openGauss database nodes.
```
gs_guc set -N all -I all -c "max_connections = 500"
```
Restart openGauss.
```
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, 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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the allowed maximum connections.

        
             postgres=# SHOW max_connections;
 max_connections 
-----------------
 500
(1 row)

Example 4: setting authentication_timeout (the allowed longest client authentication duration) for database nodes

Log in as the OS user omm to the primary node of the database.

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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the value of authentication_timeout.

        
             postgres=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)

Run the following command to exit the database:
1

postgres=# \q
Change the allowed longest client authentication duration of openGauss database nodes.
```
gs_guc reload -N all -I all -c "authentication_timeout = 30s"
```

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 ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the value of authentication_timeout.

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

Resetting Parameters

Background

Setting GUC Parameters

Procedure

Examples

Feedback

Was this page helpful?