How to modify Global User Configuration (GUC) values in a Greenplum cluster

In 3.x versions, the following SQL scripts can be used to retrieve a single GUC.

Note: You must change “max_connections” in this example for the GUC that you want to check. It must be changed in two places.

set search_path to public;

drop table if exists foo;

create table foo(a int);

insert into foo select generate_series(1,1000);

drop type if exists __t_guc_tab_fmt cascade;

create type __t_guc_tab_fmt as (guc_name text, guc_value text);

create or replace

function gucs_on_segments(varchar)

returns setof __t_guc_tab_fmt

as

$

select $1, current_setting($1)::text;

$

language sql

immutable;

select guc_name, guc_value ,foo.gp_segment_id from foo,

gucs_on_segments('max_connections') where foo.a > 0

group by 1,2,3

union all

select 'Master value'::text, current_setting

('max_connections')::text, -1 order by 3;

In 4.x versions, use the gpconfig utility to view/change GUC settings.

The gpconfig utility allows you to set, unset, or view configuration parameters from the postgresql.conf files of all instances (master, segments, and mirrors) in your Greenplum Database system.

Example:

Set the work_mem parameter to 120 MB in the master host file only:

gpconfig -c work_mem -v 120MB --masteronly 

Set the max_connections setting to 100 on all segments and 10 on the master:

gpconfig -c max_connections -v 100 -m 10

Comment out all instances of the default_statistics_target configuration parameter, and restore the system default:

gpconfig -r default_statistics_target

List all configuration parameters supported by gpconfig:

gpconfig -l

Show the values of a particular configuration parameter across the system:

gpconfig -s max_connections