Managing PostgreSQL

Configuring Connection Pool

PostgreSQL clusters use PgBouncer for managing connection pools. PgBouncer minimizes overhead when configuring new connections to PostgreSQL. You can configure the connection pool on the Settings page when creating a cluster or during its operation.

To configure it, specify the values for the following parameters:

Pooling mode

Strategy for connecting the application server to the database.

In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling:

  • Transaction — connection is assigned to a client for the duration of a transaction. This mode allows each client to use the pool for the duration of a single transaction at a time.

    If there are not enough available connections in the pool to handle the transactions submitted, the additional transactions are queued to run once connections are available.

    Transaction mode is useful when you have a large number of clients that maintain connections.

    This mode is enabled by default.

  • Session — connection is assigned when a client connects. This mode allows clients to continue issuing requests until they disconnect from the database, which maintains the client’s connection the entire time.

    If there are not enough available connections in the pool to handle all the connecting clients, the additional clients are queued up to connect once an existing client disconnects.

    Session mode is useful when your application uses prepared statements, advisory locks, listen/notify, or other features which operate on a session level rather than on a transaction level.

  • Statement — multi-statement transactions are not allowed.

    This mode is the most restrictive one and only allows a single statement at a time before moving on to the next in the queue. This means queries with multiple statements are not allowed and will fail.

Statement mode is useful when you force autocommit for each client, meaning each transaction is limited to a single statement.

Pool size

How many concurrent connections are allowed from the pool to PostgreSQL.

The pool size determines the minimum number of concurrent connections allowed from the pool to PostgreSQL.

Default pool size is 30. You can increase this value up to 50.

Creating a Database

After creating a cluster, the user can create databases and users within this cluster.

Follow these steps to create a database:

  1. Go to the database cluster page.
  2. Go to the Databases tab.
  3. Click Create database.
  4. Enter the database name.

Follow these steps to create a user:

  1. Go to the Users tab.
  2. Click New user.
  3. Enter the user’s name and set a password.

    Please note that you need to save your password, as it will not be stored in the Control panel.

After creating databases and users, you can provide the users with access to certain databases.

Cluster Operations History

The Operations tab displays the main actions that were performed on the cluster. This allows you to track all the operations and diagnose problems.

The operations history will display the following operations:

  • Creating a cluster;
  • Creating/deleting databases;
  • Creating/deleting users;
  • Assigning access rights/denying access;
  • Scaling the cluster;
  • Renaming the cluster.

Connecting Extensions to the Database

Follow these steps to connect extensions via the interface:

  1. Go to the Databases tab within a cluster.
  2. Expand the accordion with settings in the database card.
  3. Click Add next to Extensions.
  4. Select all required extensions from the list and save the settings.

Extensions are connected to each database separately.