How to limit Concurrent Connections in Greenplum

posted Apr 28, 2017, 3:54 PM by Sachchida Ojha
1. You can configure the max_connections server configuration parameter to limit the number of active concurrent sessions to your Greenplum Database system.
2.  max_connections parameter is a local parameter.  This means you must set it in the postgresql.conf file of the master, the standby master, and each segment instance (primary and mirror). it is recommended that the value of max_connections on segments must be 5-10 times the value on the master.
3. When you set max_connections, you must also set the dependent parameter max_prepared_transactions. This value must be at least as large as the value of max_connections on the master, and segment instances should be set to the same value as the master.

In $MASTER_DATA_DIRECTORY/postgresql.conf (including standby master):

In SEGMENT_DATA_DIRECTORY/postgresql.conf for all segment instances:

Steps to change the number of allowed connections
1.Stop your Greenplum Database system:
$ gpstop

2.On your master host, edit $MASTER_DATA_DIRECTORY/postgresql.conf and change the following two parameters:
max_connections (the number of active user sessions you want to allow plus the number of superuser_reserved_connections) max_prepared_transactions (must be greater than or equal to max_connections)

3.On each segment instance, edit SEGMENT_DATA_DIRECTORY/postgresql.conf and and change the following two parameters: max_connections (must be 5-10 times the value on the master) max_prepared_transactions (must be equal to the value on the master)

4.Restart your Greenplum Database system:
$ gpstart