Setting Configuration Parameters

Many configuration parameters limit who can change them and where or when they can be set. For example, to change certain parameters, you must be a Greenplum Database superuser. Other parameters can be set only at the system level in the postgresql.conf file or require a system restart to take effect.

Many configuration parameters are session parameters. You can set session parameters at the system level, the database level, the role level or the session level. Database users can change most session parameters within their session, but some require superuser permissions.

See the Greenplum Database Reference Guide for information about setting server configuration parameters.

Setting a Local Configuration Parameter

To change a local configuration parameter across multiple segments, update the parameter in the postgresql.conf file of each targeted segment, both primary and mirror. Use the gpconfig utility to set a parameter in all Greenplum postgresql.conf files. For example:

$ gpconfig -c gp_vmem_protect_limit -v 4096MB

Restart Greenplum Database to make the configuration changes effective:

$ gpstop -r

Setting a Master Configuration Parameter

To set a master configuration parameter, set it at the Greenplum master instance. If it is also a session parameter, you can set the parameter for a particular database, role or session. If a parameter is set at multiple levels, the most granular level takes precedence. For example, session overrides role, role overrides database, and database overrides system.

Setting Parameters at the System Level

Master parameter settings in the master postgresql.conf file are the system-wide default. To set a master parameter:

Edit the $MASTER_DATA_DIRECTORY/postgresql.conf file.
Find the parameter to set, uncomment it (remove the preceding # character), and type the desired value.
Save and close the file.
For session parameters that do not require a server restart, upload the postgresql.conf changes as follows:
```
$ gpstop -u
```
For parameter changes that require a server restart, restart Greenplum Database as follows:
```
$ gpstop -r
```

For details about the server configuration parameters, see the Greenplum Database Reference Guide.

Setting Parameters at the Database Level

Use ALTER DATABASE to set parameters at the database level. For example:

=# ALTER DATABASE mydatabase SET search_path TO myschema;

When you set a session parameter at the database level, every session that connects to that database uses that parameter setting. Settings at the database level override settings at the system level.

Setting Parameters at the Role Level

Use ALTER ROLE to set a parameter at the role level. For example:

=# ALTER ROLE bob SET search_path TO bobschema;

When you set a session parameter at the role level, every session initiated by that role uses that parameter setting. Settings at the role level override settings at the database level.

Setting Parameters in a Session

Any session parameter can be set in an active database session using the SET command. For example:

=# SET statement_mem TO '200MB';

The parameter setting is valid for the rest of that session or until you issue a RESET command. For example:

=# RESET statement_mem;

Settings at the session level override those at the role level.