gp_toolkit schema objects (function and view) to check parameter settings across all primary postgresql.conf files in the system

Post date: Jan 20, 2014 10:12:46 PM

Following 2 gp_toolkit schema objects (function and view) can be used to check parameter settings across all primary postgresql.conf files in the system.

1. gp_param_setting('parameter_name')

2. gp_param_settings_seg_value_diffs

Each component of a Greenplum Database system (master, standby master, primary segments, and mirror segments) has its own server configuration file (postgresql.conf).

1. gp_param_setting('parameter_name')

This function takes the name of a server configuration parameter and returns the postgresql.conf value for the master and each active segment. This function is accessible to all users.

paramsegment=>The segment content id (only active segments are shown). The master content id is always -1.

paramname=>The name of the parameter.

paramvalue=>The value of the parameter.

gpadmin=# SELECT * FROM gp_toolkit.gp_param_setting('max_connections');

 paramsegment |    paramname    | paramvalue 


            0 | max_connections | 750

            1 | max_connections | 750

           -1 | max_connections | 250

(3 rows)

2. gp_param_settings_seg_value_diffs

gpadmin=# \d gp_toolkit.gp_param_settings_seg_value_diffs

View "gp_toolkit.gp_param_settings_seg_value_diffs"

Column | Type | Modifiers


psdname | text |

psdvalue | text |

psdcount | bigint |

View definition:

SELECT gp_param_settings.paramname AS psdname, gp_param_settings.paramvalue AS psdvalue, count(*) AS psdcount

FROM gp_toolkit.gp_param_settings() gp_param_settings(paramsegment, paramname, paramvalue)

WHERE gp_param_settings.paramname <> ALL (ARRAY['config_file'::text, 'data_directory'::text, 'gp_contentid'::text, 'gp_dbid'::text, 'hba_file'::text, 'ident_file'::text, 'port'::text])

GROUP BY gp_param_settings.paramname, gp_param_settings.paramvalue

HAVING count(*) < (( SELECT __gp_number_of_segments.numsegments

FROM gp_toolkit.__gp_number_of_segments))

ORDER BY gp_param_settings.paramname, gp_param_settings.paramvalue, count(*);

Server configuration parameters that are classified as local parameters (meaning each segment gets the parameter value from its own postgresql.conf file), should be set identically on all segments. This view shows local parameter settings that are inconsistent. Parameters that are supposed to have different values (such as port) are not included. This view is accessible to all users.

psdname=>The name of the parameter.

psdvalue=>The value of the parameter.

psdcount=>The number of segments that have this value.

gpadmin=# select * from  gp_toolkit.gp_param_settings_seg_value_diffs;

 psdname | psdvalue | psdcount 


(0 rows)