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

Post date: Oct 05, 2012 10:52:2 PM

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

Note:

Global User Configuration (GUC) parameters can be either global or local in relation to the segment instances. Global GUCs need only be set on the master instance, but the change will affect all segment instances as well. Local GUCs can be set per segment, but it is a requirement that all segments share the same configuration. In this case you must set a local GUC by editing the postgresql.conf file for every segment instance.

In many cases you will want to validate the current setting of a GUC for all segments. For more information about GUCs and the list of parameters that can be set, see Appendix E: Server Configuration Parameters of the Greenplum Admin Guide.

The Greenplum Database "jetpack" schema includes a function to validate GUC settings. See gp_jetpack.gp_jetpack_guc_setting(varchar) in the file $GPHOME/lib/jetpack/jetpack.sql.