Skip to main content
Version 4.1.0

Connection Pool Configuration

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

To enable this operation, PgSmart integrates with PgBouncer.

Command Syntax
Terminal input
    pgsmart pool config [Flags]

Flags:​

  • -a, --agent=<agent> => Alias, hostname, or IP address of the server where the desired PgSmart Agent is installed.
  • -h, --help => Help.
  • -q, --quiet => Non-interactive execution for controlling the component service (status|start|stop|restart|reload). Results can be viewed in the PgSmart Client log. Used along with the --agent option.
Non-Interactive Configuration

Non-interactive execution is disabled for this operation.

Interactive Configuration
Terminal input
    pgsmart pool config

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

  2. Select the Connection Pools Section (selection made with [space] key).

When selecting the Connection Pools section, the following operations are available:

  • Listar — Allows listing existing connection pools
  • Criar — Allows creating a connection pool
  • Editar — Allows editing a connection pool
  • Remover — Allows removing a connection pool

List configured connection pools​

  1. Select the Listar operation.

    Figure 1 - Connection Pool Configuration - List
    Figure 1 - Connection Pool Configuration - List
note

A list of configured connection pools will be displayed.

Create a Connection Pool​

  1. Select the Criar operation.

  2. Enter the pool name for applications/clients to connect to. ("*" means if a pool does not exist at the moment of database connection, a default PgBouncer pool will be used).

  3. Enter the connection type.

  4. Enter the connection IP for the PostgreSQL instance [Default: localhost].

  5. Enter the connection port for the PostgreSQL instance.

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

  7. Enter the Database Name.

  8. Confirm if you want to add advanced parameters for this pool (default parameters will be overridden).

If advanced parameters are confirmed:

8.1. Enter the Database connection user (user). [optional].

8.2. Enter the Authentication user to query the PostgreSQL instance (auth_user). [optional].

8.3. Enter the Maximum pool size (pool_size). [optional].

8.4. Enter the Reserved connections (reserved_pool). [optional].

8.5. Enter the Connect query (connect_query) executed after the connection is established (single quotes). [optional].

8.6. Select the Connection reuse mode. [optional].

8.7. Enter the Maximum number of database connections (max_db_connections). [optional].

8.8. Enter the Character encoding (client_encoding). [optional].

8.9. Enter the Date style (datestyle). [optional].

8.10. Enter the Timezone. [optional].

  1. Confirm the creation of the connection pool.

    note

    Parameters not provided will adopt the Global Parameters as defaults.

    note

    • At the end of the creation, PgSmart provides an example command to test the connection along with recommendations.

    • To test the connection, execute:

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

Edit a Connection Pool​

  1. Select the Editar operation.

    note

    PgSmart will list the already configured pools.

  2. Select the Configured Pool you want to change.

  3. Enter the Pool Name for applications/clients [Default: pool1].

  4. Select the connection type.

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

  6. Enter the PostgreSQL instance port [Default: 5432].

  7. Select the Major Version of PostgreSQL.

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

  9. Confirm editing of advanced parameters for this pool.

    If advanced parameters are confirmed:

    9.1. Enter the Database connection user (user). [optional].

    9.2. Enter the Authentication user for the PostgreSQL instance (auth_user). [optional].

    9.3. Enter the Maximum pool size (pool_size). [optional].

    9.4. Enter the Reserved connections (reserved_pool). [optional].

    9.5. Enter the Connect query (connect_query) after connection stabilization. [optional].

    9.6. Select the Connection reuse mode. [optional].

    9.7. Enter the Maximum database connections (max_db_connections). [optional].

    9.8. Enter the Character encoding (client_encoding). [optional].

    9.9. Enter the Date style (datestyle). [optional].

    9.10. Enter the Timezone. [optional].

    note

    Parameters not informed will inherit defaults from Global Parameters.

  10. Confirm the edition of the connection pool.

    Figure 3 - Connection Pool Configuration - Editing
    Figure 3 - Connection Pool Configuration - Editing

Remove a Connection Pool​

  1. Select the Remover operation.

    note

    PgSmart will list the already configured pools.

  2. Select the Configured Pool you want to remove.

  3. Confirm the removal of the connection pool.

    Figure 4 - Connection Pool Configuration - Removal
    Figure 4 - Connection Pool Configuration - Removal