Configure DBMS via GUCs

Pavel Semyonov

Contents

Configuration overview
Parameter types and values
Parameter classification
- Context
- master and local
Parameter interaction

This topic explains how to configure a Greengage DB (based on Greenplum) using its configuration parameters.

Greengage DBMS provides a flexible mechanism that allows you to control the cluster through configuration parameters. Each parameter determines a specific aspect of the cluster behavior, such as a resource limit, a query execution setting, or a logging parameter. All available parameters are described in the Server configuration parameters (GUCs) reference.

Many configuration parameters in Greengage DB are inherited from PostgreSQL, while other ones are specific to its own distributed architecture. In PostgreSQL, this configuration mechanism is referred to as Grand Unified Configuration (GUC). You can refer to the PostgreSQL configuration documentation for general information about GUC.

NOTE

The GUC acronym is also sometimes interpreted as Global User Configuration.

GUC parameters must be consistent across all segment instances of a Greengage DB cluster. Differences in segment configurations can lead to unpredictable behavior or errors. Although, the master instance can have a different configuration. It is automatically kept in sync with the standby master.

The configuration is stored in the postgresql.conf files in data directories of cluster instances. Inside the database, the configuration is available in the pg_catalog.pg_settings system catalog view. Greengage DB also provides the gpconfig utility for managing configuration from the command line.

Greengage DB configuration parameters have the following types:

boolean
integer
floating point
string
enumeration

Parameter types can be found in the vartype column of the pg_settings system view. Their corresponding values are bool, integer, real, string, and enum.

This section provides details on each of these types.

Boolean parameters accept values representing true or false:

true: TRUE, ON, YES, 1
false: FALSE, OFF, NO, 0

Values are case-insensitive. Unambiguous prefixes of these values are also accepted. For example, y and n are valid for true and false, respectively.

Examples:

optimizer = on
ssl = TRUE
track_activities = y

Numeric parameters can be either integers or floating-point numbers.

Integers are written as is: 0, 1, -10.
Floating-point numbers use a period (.) as the decimal separator: 0.05.
No thousand separators are used: 10000, not 10.000 or 10,000.
Numeric parameters can have a limited range of allowed values. These limits can be found in the min_val and max_val columns of the pg_settings system view.

Examples:

max_connections = 250
extra_float_digits = -5
max_appendonly_tables = 10000
cpu_tuple_cost = 0.02
memory_spill_ratio = 10 # allowed range 0-100

Check allowed values

Allowed values can be checked as follows:

In the pg_settings view:

SELECT name, min_val, max_val FROM pg_settings WHERE name = 'memory_spill_ratio';

Result:

        name        | min_val | max_val
--------------------+---------+---------
 memory_spill_ratio | 0       | 100
(1 row)

Using the gpconfig utility:
```
$ gpconfig -l | grep memory_spill_ratio
```
Result:

[name: memory_spill_ratio] [unit: ] [context: user] [vartype: integer] [min_val: 0] [max_val: 100]

Some numeric parameters represent quantities of memory or time. For such parameters, you can explicitly specify a unit in which the value is measured. To specify a unit, enclose the value in single quotes without whitespace between the number and the unit, for example, '100MB'.

If no unit is provided, the parameter’s default unit is used. Default units vary by parameter. Typically, these are kilobytes, blocks (eight kilobytes), or megabytes for memory size, and milliseconds, seconds, or minutes for time. You can view a parameter’s default unit using the unit column in the pg_settings system view.

The following units are supported:

Memory size: kB (kilobytes), MB (megabytes), GB (gigabytes), and TB (terabytes).

NOTE

Memory units use a multiplier of 1024, not 1000.
Time: ms (milliseconds), s (seconds), min (minutes), h (hours), d (days).

Unit names are case-sensitive.

Examples:

statement_mem = '256MB'
authentication_timeout = '2min'

Check default units

A parameter’s default unit can be checked as follows:

In the pg_settings view:

SELECT name, unit FROM pg_settings WHERE name = 'statement_mem';

Result:

     name      | unit
---------------+------
 statement_mem | kB
(1 row)

Using the gpconfig utility:
```
$ gpconfig -l | grep statement_mem
```
Result:

[name: statement_mem] [unit: kB] [context: user] [vartype: integer] [min_val: 1000] [max_val: 2147483647]

String parameters accept arbitrary string values. They are typically enclosed in single quotes, although quotes can be omitted for one-word values:

application_name = 'My cluster'
search_path = public

Enumeration parameters (enums) are string values restricted to a predefined set of options, for example:

log_error_verbosity = 'VERBOSE' # enum: one of allowed values

Allowed values for each enum parameter can be found in the enumvals column of the pg_settings system view:

SELECT name, enumvals FROM pg_settings WHERE name = 'log_error_verbosity';

Result:

        name         |        enumvals
---------------------+-------------------------
 log_error_verbosity | {terse,default,verbose}
(1 row)

This section describes the key characteristics of GUC parameters in Greengage DB, including when and where they can be set, and whether they require a cluster restart or superuser privileges to change.

Context

In the PostgreSQL configuration mechanism, which is used in Greengage DB, each parameter has its context value. It is a string value that defines the available methods for setting the parameter, from the most restrictive (requiring a full cluster restart) to the least restrictive (allowing changes by any user within a session).

The following table summarizes the available methods for setting parameter values based on their context. The rows represent contexts in decreasing order of restriction level.

Context	Cluster restart	Configuration reload	Session start	In session (superuser)	In session (user)
internal	no	no	no	no	no
postmaster	yes	no	no	no	no
sighup	yes	yes	no	no	no
backend	yes	yes	yes	no	no
superuser	yes	yes	yes	yes	no
user	yes	yes	yes	yes	yes

Context

Cluster restart

Configuration reload

Session start

In session (superuser)

In session (user)

internal

postmaster

yes

sighup

yes

backend

yes

superuser

yes

user

yes

For the original descriptions of context values, refer to the pg_settings reference in the PostgreSQL documentation.

In Greengage DB, context values apply as follows:

internal parameters are read-only and cannot be changed. These parameters reflect internal system properties and are available for inspection only. Examples are gp_contentid — the content ID of the current segment — and gp_dbid — the unique ID of the database instance.
postmaster parameters require a full cluster restart for a change to take effect:
```
$ gpstop -r
```
sighup parameters do not require a restart and can be applied via a configuration reload:
```
$ gpstop -u
```
NOTE

Parameters with internal, postmaster, and sighup contexts are also referred to as system parameters.
backend parameters can be provided in PG_OPTIONS at session start:
```
$ PGOPTIONS='-c gp_session_role=utility' psql postgres
```
superuser and user parameters can be changed in a running cluster using SQL commands by superusers and regular users, respectively. The superuser context is typically used for parameters that control sensitive or low-level system behavior.

NOTE

Parameters with superuser and user contexts are also referred to as session parameters.

They can be set at the following levels:
- Session level applies only to the current session.
- Database and role level applies to sessions of a specific role in a particular database.
- Role level applies to all sessions of a specific role.
- Database level applies to all sessions of a specific database.
- System level is used for all sessions, roles, and databases if a parameter is not defined at other levels. System-level values for superuser and user parameters are set in the postgresql.conf file.
  
  IMPORTANT
  
  If a parameter is configured only at the system level, then its update requires a system restart or a configuration reload.
When a parameter is defined at multiple levels, the most specific level takes precedence. For example, a role-level setting overrides a database-level default. Session-level settings override all other settings.

You can learn a parameter’s context from the context column in the pg_settings system view or from the gpconfig -l output:

SELECT name, setting, unit, context
FROM pg_settings WHERE name ='statement_mem';

Output:

     name      | setting | unit | context
---------------+---------+------+---------
 statement_mem | 524288  | kB   | user
(1 row)

The user context indicates that the parameter value can be changed by a regular user on the fly without additional actions such as a cluster restart or a configuration reload.

This classification is introduced in addition to the PostgreSQL context to support the distributed architecture of Greengage DB. Since a cluster consists of multiple PostgreSQL instances of different nature (master and segments), their configurations may differ by design. Therefore, configuration parameters fall into two categories depending on where their value applies:

master parameters are set only on the master instance. These parameters affect only the master or are passed to segments at runtime if applicable. If a parameter is not relevant to segments, it is ignored there.

An example is the log_min_messages parameter that controls the logging level of the cluster. When set on a master, its value applies to all cluster instances, changing the log level of segments as well.
local parameters must be set on all segment instances and the master. Each segment uses its local value, so it is critical that these parameters remain consistent across all segments to avoid cluster errors.

An example is the max_connections parameter — the maximum allowed number of active connections. Its value is typically greater on segments than on master. To preserve the consistency of local parameters across the cluster, it is recommended to use the gpconfig utility. For more information on its usage, see the gpconfig reference page and an example below.

This section explains ways to get and set Greengage DB configuration parameters. The order of methods reflects their precedence: each method overrides settings defined by the ones described before it.

postgresql.conf

Each cluster instance — master or segment — has its own postgresql.conf configuration file. It is located in the instance’s data directory and is read by the underlying PostgreSQL process during startup or configuration reload. postgresql.conf is the fundamental source of configuration values.

The file describes one parameter per line as follows:

parameter_name=value

The equals sign between the parameter name and its value is optional. It also can be surrounded by whitespaces. The # character comments out the remainder of the line.

IMPORTANT

Due to the distributed architecture of Greengage DB, manual editing is not recommended for local parameters. Instead, use the gpconfig utility, which ensures the consistency of parameter values across all segments.

To check a GUC parameter’s value in postgresql.conf, search for its name:

$ cat $MASTER_DATA_DIRECTORY/postgresql.conf | grep max_connections

If a parameter appears multiple times in the file, the last occurrence takes precedence. This usually happens when parameters are updated using gpconfig, which comments out the previous assignment and appends a new one.

To update a parameter value in the master’s postgresql.conf, add a line with its new value to the end of the file:

$ echo "max_connections=300" >> $MASTER_DATA_DIRECTORY/postgresql.conf

This line will override all previous settings for the same parameter.

Then restart the cluster of reload configuration as required by the parameter context:

$ gpstop -r

$ gpstop -u

For max_connections, a restart is required.

The gpconfig utility allows consistent management of configuration parameters in postgresql.conf files across Greengage DB cluster instances. Unlike manual editing, gpconfig ensures consistent updates to all relevant postgresql.conf files, making it suitable for configuring both master and local parameters. Note that gpconfig only edits configuration files; to apply new values, a configuration reload or a cluster restart is needed.

To check the current value of a parameter, specify its name in the -s (--show) option:

$ gpconfig -s max_connections

The output shows the parameter values configured on the master and on segment instances:

Values on all segments are consistent
GUC          : max_connections
Master  value: 250
Segment value: 750

To list all parameters that can be configured using the utility, use the -l (--list) option:

$ gpconfig -l

Each output line shows a parameter’s metadata:

[name: max_connections] [unit: ] [context: postmaster] [vartype: integer] [min_val: 10] [max_val: 8388607]

To change a parameter value across the entire cluster, use -c (--change) and -v (--value):

$ gpconfig -c statement_mem -v '256MB'

Result:

[INFO]:-completed successfully with parameters '-c statement_mem -v 256MB'

You can check the new value in the postgresql.conf file by printing its last line:

$ tail $MASTER_DATA_DIRECTORY/postgresql.conf -n 1

It is the following:

statement_mem=256MB

However, to apply the new value, you need to reload its configuration using gpstop:

$ gpstop -u

NOTE

statement_mem used in the example is a reload parameter. For restart parameters, a cluster restart is needed:

$ gpstop -r

To set different values for master and segments, add the -m (--mastervalue) option specifying the master value:

$ gpconfig -c statement_mem -v '256MB' -m '512MB'

To change a parameter value on master without affecting segment values, use the -v and --masteronly options:

$ gpconfig -c statement_mem -v '512MB' --masteronly

NOTE

The master value also applies to the standby master if it exists.

The --skipvalidation option changes parameter values without performing any checks. Use it when you need to change a parameter, but it’s impossible to validate the new value for some reason.

CAUTION

Use gpconfig with the --skipvalidation option with extreme caution. This can bring the cluster into an inconsistent state.

--skipvalidation also allows changing restricted and hidden parameters, which can’t be normally modified with gpconfig. For example, to change the gp_add_column_inherits_table_setting, run:

$ gpconfig -c gp_add_column_inherits_table_setting -v on --skipvalidation

Result:

20250606:08:06:29:003703 gpconfig:mdw:gpadmin-[INFO]:-completed successfully with parameters '-c gp_add_column_inherits_table_setting -v on --skipvalidation'

A similar call without --skipvalidation fails with an error.

Prerequisites

To execute commands described in this section, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql.

To view configuration parameters, use the SHOW command:

View the current value of a specified parameter:

SHOW max_connections;

Result:

 max_connections
-----------------
 250
(1 row)

List all parameters with current values and descriptions:
```
SHOW ALL;
```

You can set session parameter values from the SQL console at different configuration levels. Each level defines a default value, which can be overridden by a more specific level.

The levels and their corresponding SQL commands are:

Database level applies to all sessions connected to a specific database:
```
ALTER DATABASE books_store SET optimizer TO off;
```
Role level applies to all sessions initiated by a specific role:
```
ALTER ROLE alice SET optimizer TO off;
```
Database and role level applies to sessions of a specific role in a particular database:
```
ALTER ROLE alice IN DATABASE books_store SET optimizer TO off;
```
Session level applies only to the current session:
```
SET optimizer = off;
```
To revert a session-level parameter to the most specific default, use the RESET command:
```
RESET optimizer;
```

The pg_settings system catalog view allows getting detailed information about GUC parameters using SQL queries. Its columns include parameter name, type, description, current value, and other details.

To get a GUC parameter value from pg_settings:

SELECT setting FROM pg_settings WHERE name = 'max_connections';

setting
---------
 250
(1 row)

Query more columns to get more details:

SELECT name, setting, unit, context, vartype, min_val, max_val
FROM pg_settings
WHERE name like '%size%';

Result:

                 name                 | setting | unit |  context   | vartype | min_val |  max_val
--------------------------------------+---------+------+------------+---------+---------+------------
 block_size                           | 32768   |      | internal   | integer | 32768   | 32768
 effective_cache_size                 | 524288  | 32kB | user       | integer | 1       | 2147483647
 gp_enable_relsize_collection         | off     |      | user       | bool    |         |
 gp_instrument_shmem_size             | 5120    | kB   | postmaster | integer | 0       | 131072
 gp_interconnect_cursor_ic_table_size | 128     |      | user       | integer | 128     | 102400
 gp_max_packet_size                   | 8192    |      | backend    | integer | 512     | 65507
 gp_max_plan_size                     | 0       | kB   | superuser  | integer | 0       | 2147483647
 gp_safefswritesize                   | 0       |      | backend    | integer | 0       | 2147483647
 gp_udp_bufsize_k                     | 0       |      | backend    | integer | 0       | 32768
 log_rotation_size                    | 1048576 | kB   | sighup     | integer | 0       | 2097151
 max_slot_wal_keep_size               | -1      | MB   | sighup     | integer | -1      | 2147483647
 optimizer_mdcache_size               | 16384   | kB   | user       | integer | 0       | 2147483647
 pljava_statement_cache_size          | 0       |      | superuser  | integer | 0       | 512
 segment_size                         | 32768   | 32kB | internal   | integer | 32768   | 32768
 track_activity_query_size            | 1024    |      | postmaster | integer | 100     | 102400
 wal_block_size                       | 32768   |      | internal   | integer | 32768   | 32768
 wal_segment_size                     | 2048    | 32kB | internal   | integer | 2048    | 2048
 writable_external_table_bufsize      | 1024    | kB   | user       | integer | 32      | 131072
(18 rows)

To view a parameter’s description, use the short_desc column:

SELECT name, short_desc FROM pg_settings WHERE name = 'block_size';

Result:

    name    |           short_desc
------------+---------------------------------
 block_size | Shows the size of a disk block.
(1 row)

For some parameters, the extra_desc column stores additional information on the parameter usage.

The source column displays the level on which the currently used parameter value is defined. This is especially useful for session parameters, which can be set on different levels. For example:

SELECT name, setting, source
FROM pg_settings WHERE name like '%statement_mem';

Result:

       name        | setting |       source
-------------------+---------+--------------------
 max_statement_mem | 2048000 | default
 statement_mem     | 524288  | configuration file
(2 rows)

source shows that max_statement_mem uses the default value, and statement_mem if overriden in postgresql.conf.

Change statement_mem for the current session using SET:

SET statement_mem = '256MB';

Then run the same query again. The output shows the new value and the session level on which it is set:

       name        | setting | source
-------------------+---------+---------
 max_statement_mem | 2048000 | default
 statement_mem     | 262144  | session
(2 rows)

Recover a failed master

Use resource groups

Configure DBMS via GUCs

Configuration overview

Parameter types and values

Boolean

Numbers and units

String and enum

Parameter classification

Context

master and local

Parameter interaction

postgresql.conf

gpconfig utility

SQL commands

pg_settings system view