Skip to main content
Version 3.1.1

Connection Pool(s) Configuration

A connection pool is a set of ad-hoc connections to access databases, maintained so that the connections can be reused when future requests to the databases are made.

To enable this operation, PgSmart integrates with [PgBouncer](* PgBouncer).

Command Syntax
Terminal input
pgsmart pool config [Flags]

Flags:​

  • -a, --agent=<agent> => Alias, host name or IP of the server where the PgSmart Agent is installed.
  • -h, --help => Help.
  • -q, --quiet => Non-interactive execution. You can view the operation results in PgSmart Client log. Used together with --agent.
Non-Interactive Configuration

Non-interactive execution is disabled for this operation.

Interactive Configuration
Terminal input
pgsmart pool config

  1. Select PgSmart Agent (or Register new Agent).

  2. Select the Connection Pools section (selection is made with the [spaces] key).

    Upon selecting the Connection Pools section, the following operations are made available:

    • List the already configured Pools of Connection: Allows viewing the existing connection Pools.
    • Create a Connections Pool: Allows creating a new connection Pool.
    • Edit a Connections Pool: Allows editing a connection Pool.
    • Remove a Connections Pool: Allows removing a connection Pool.

List the already configured Pools of Connection​

  1. Select the List operation.

    Figure 1 - Connection Pool Configuration- list
    Figure 1 - Connection Pool Configuration- list
note

The list of configured Connection Pools will be presented.

Create a Connections Pool​

  1. Select the Create option.

  2. Provide the Name of the Pool that applications/clients will connect to. The asterisk (*) indicates that if there is no pool available at the time of connection to the database with the specified name, a standard pool already existing in PgBouncer will be used.

  3. Provide the Type of Connection.

  4. Provide the IP connection of the PostgreSYS instance [Default:localhost].

  5. Provide the Connection Port of the PostgreSQL instance.

  6. Select the Major Version of PostgreSQL or the Data Directory (whichever is requested).

  7. Provide the Name of the Database.

  8. Confirm if you want to add advanced parameters for this Pool (the default parameters will be overwritten).

    If the need to add advanced parameters is confirmed:

    8.1. Provide the Database Connection User(user). [optional]

    8.2. Provide the User used to query in the PostgreSQL instance (auth_user). [optional]

    8.3. Provide the Maximum Pool Size (pool_size). [optional]

    8.4. Provide the Number of additional reserved connections (reserved_pool) [optional].

    8.5. Provide the Query (within single quotes) to be executed after the connection to the Database is stabilized (connect_query) [optional]

    8.6. Select the Connection Reuse Mode[optional].

    8.7. Provide the Maximum Number of Connections in the Database (max_db_connections). [optional]

    8.8. Provide the Character Encoding to be used in the connection (client_encoding). [optional]

    8.9. Provide the Date Style to be used in the connection (datestyle). [optional]

    8.10. Provide the Time Zone to be used in the connection (timezone). [optional]

  9. Confirm the creation of the Connections Pool.

    note

    Parameters not informed have their values assumed based on the Global Parameters, whose values are adopted as "default".

    note

    • Upon completion of the creation, PgSmart offers an example command to test the connection and a series of recommendations.

    • To test the connection, execute the command:

    Terminal input
    psql -U <User> -h <IP-address> -p 6432 apppool
Figure 2 - Connection Pool Configuration - Add
Figure 2 - Connection Pool Configuration - Add

Edit a Connections Pool​

  1. Select the Edit option.

    note

    When selecting this action, PgSmart will provide a list with the Pools already configured.

  2. Select the configured Pool you wish to change and adjust.

  3. Provide the Name of the Pool which the applications/Clients will connect to (Default: pool1)

  4. Select the Type of Connection.

  5. Provide the IP/hostname of the PostgreSQL instance [Default: localhost]

  6. Provide the Port of the PostgreSQL instance[Default:5432]

  7. Select the Major version of the PostgreSQL instance.

  8. Provide the Name of the Database (optional for Unix Socket or AutoDB)

  9. Confirm the Edition of advanced parameters for this Pool(the default parameters will be overwritten).

    If the need to add advanced parameters is confirmed:

    9.1. Provide the Database connection user (user). [optional]. If informed, it will require that the User be the one informed. If not informed, any User will have permission.

    9.2. Provide the User used to query in the PostgreSQL instance (auth_user). [optional]. User who will be used for authentication in the instance. If not informed and there is no User in the authentication file, PgSmart will reject the connection.

    9.3. Provide the Maximum Pool Size (pool_size). [optional]. Maximum number of pool connections.

    9.4. Provide the Number of additional reserved connections (reserved_pool) [optional]. Indicates that, of the total number of connections, a specified volume should be reserved for a superuser or a Maintenance User.

    9.5. Provide the Query (within single quotes) to be executed after the connection to the Database is stabilized (connect_query) [optional]. Indicates the query to be used to validate the connection. Empty indicates the use of a standard query.

    9.6. Select the Connection Reuse Mode[optional]. If not informed, the Default will be adopted, which is "session" (or the one defined in the Global Parameter, in case it has been modified).

    9.7. Provide the Maximum Number of connections in the Database (max_db_connections). [optional].

    9.8. Provide the Character Encoding to be used in the connection (client_encoding). [optional]

    9.9. Provide the Date Style to be used in the connection (datestyle). [optional]

    9.10. Provide the Time Zone to be used in the connection (timezone). [optional]

    note

    Parameters not informed have their values assumed based on the Global Parameters, whose values are adopted as "default".

  10. Confirm the Edition of the Connections Pool.

Figure 3 - Connection Pool Configuration - Edit
Figure 3 - Connection Pool Configuration - Edit

Remove a Connections Pool​

  1. Select the Remove operation.

    note

    When selecting this action, PgSmart will provide a list with the Pools already configured.

  2. Select the Pool configured that you wish to remove.

  3. Confirm the Deletion of the Connections Pool.

Figure 4 - Connection Pool Configuration - Remove
Figure 4 - Connection Pool Configuration - Remove