Configure DBMS via GUCs

Pavel Semyonov

Contents

Configuration overview
Parameter types and values
Classification
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.

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.

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 ways to change the parameter value.

You can learn a parameter’s context from the context column in the pg_settings system view or 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 in a session, without a cluster restart. For the list of allowed context values and their descriptions, refer to the pg_settings reference in the PostgreSQL documentation.

The sections below describe how different contexts define various aspects of configuration parameters.

This classification defines whether a parameter can be changed on the fly within a database session and the level at which it takes effect:

session parameters can be changed in a running cluster using SQL commands. 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 on other levels. System-level values are set in the postgresql.conf file.
  
  IMPORTANT
  
  If a session parameter is configured only at the system level, then its update requires a system restart or a configuration reload, like for system parameters.
  
  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.
  
  Session parameters typically have the user and superuser context.
system parameters are defined in the postgresql.conf configuration file. To change a system parameter value, a cluster restart or a configuration reload is required depending on the exact parameter.

NOTE

This classification applies only to parameters defined in the postgresql.conf file (system parameters or session parameters set on the system level).

master parameters are set only in the postgresql.conf file of 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.
local parameters must be set in the postgresql.conf files of 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.

The master/local classification is not defined by the PostgreSQL context. It is an additional classification introduced due to the distributed architecture of Greengage DB.

NOTE

This classification applies only to parameters defined in the postgresql.conf file (system parameters or session parameters set on the system level).

restart parameters require a full cluster restart to take effect:
```
$ gpstop -r
```
reload parameters do not require a restart and can be applied via a configuration reload:
```
$ gpstop -u
```

The postmaster context value indicates that a cluster restart is required. Parameters with other context values except internal can be updated with a configuration reload (sighup context) or on the fly.

superuser parameters can only be set by a database superuser. These typically control sensitive or low-level system behavior. Examples of such parameters are most logging settings: log_error_verbosity, log_statement, and others.

Superuser parameters have the superuser context.

Values of read-only parameters cannot be changed by anyone — neither superusers nor regular users. These parameters reflect internal system properties and are available for inspection only.

Examples:

gp_contentid — the content ID of the current segment.
gp_dbid — the unique ID of the database instance.

Read-only parameters have the internal context.

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

Classification

session and system

master and local

restart and reload

superuser

read-only

Parameter interaction

postgresql.conf

gpconfig utility

SQL commands

pg_settings system view