Setting Configuration parameter in Greenplum

Many of the configuration parameters have limitations on 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 only be set at the system-level in the postgresql.conf file or require a restart of the system for the changes to take effect.

Many configuration parameters are considered session parameters. A session parameter can be set at the system-level, the database-level, the role-level or the session-level. Most session parameters can be changed by any database user within their session, but a few may require superuser permissions.

Setting a Local Configuration Parameter

If you want to change a configuration parameter that is classified as local, you must change it in every postgresql.conf file where you want that change to take effect. In most cases, this means making the change at the master and at every segment (primary and mirror). To set a parameter in all postgresql.conf files of a Greenplum system, you can use the gpconfig utility. For example:

$ gpconfig -c gp_vmem_protect_limit -v 4096MB

Then restart Greenplum Database to make the configuration changes effective:

$ gpstop -r

Setting a Master Configuration Parameter

If a parameter is classified as master, you only need to set it at the Greenplum master instance. If it is also a session parameter, you have the additional flexibility of setting the parameter for a particular database, role or session. If a parameter is set at multiple levels, then the more granular level takes precedence. For example, session overrides role, role overrides database, and database overrides system.

Setting Parameters at the System Level

Setting master parameters in the master postgresql.conf file makes that setting the new system-wide default.

1. Edit the $MASTER_DATA_DIRECTORY/postgresql.conf file.

2. Find the parameter you want to change, uncomment it (remove the preceding # character), and set it to the desired value.

3. Save and close the file.

4. For session parameters that do not require a server restart, you can upload the postgresql.conf changes as follows:

$ gpstop -u

5. For parameter changes that require a server restart, restart Greenplum Database as follows:

$ gpstop -r

Setting Parameters at the Database Level

When a session parameter is set at the database level, every session that connects to that database will pick up that parameter setting. Settings at the database level override those at the system level. Use the ALTER DATABASE command to set a parameter at the database level. For example:

=# ALTER DATABASE mydatabase SET search_path TO myschema;

Setting Parameters at the Role Level

When a session parameter is set at the role level, every session initiated by that role will pick up that parameter setting. Settings at the role level override those at the

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

=# ALTER ROLE bob SET search_path TO sachischema;

Setting Parameters in a Session

Any session parameter can also be set in an active database session using the SET command. That parameter setting is then valid for the rest of that session (or until a RESET command is issued). Settings at the session level override those at the role level. For example:

=# SET work_mem TO '200MB';

=# RESET work_mem;