Setting a Master Configuration Parameter

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:

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

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

3. Save and close the file.

4. For session parameters that do not require a server restart, 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

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

=# ALTER DATABASE sachi 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 sachi SET search_path TO tomschema;

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 work_mem TO '200MB';

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

=# RESET work_mem;

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