Managing Databases for EnterpriseDB Connections

Databases for EnterpriseDB Connection Limits

At provision, Databases for EnterpriseDB sets the maximum number of connections to your Databases for EnterpriseDB database to 115. 15 connections are reserved for the superuser to maintain the state and integrity of your database, and 100 connections are available for you and your applications. If the number of connections to the database exceeds the 100 connection limit, new connections fail and return an error.

FATAL: remaining connection slots are reserved for
non-replication superuser connections

Exceeding the connection limit for your deployment can cause your database to be unreachable by your applications.

You can check the number of connections to your deployment with the admin user, psql, and pg_stat_database.

SELECT count(distinct(numbackends)) FROM pg_stat_database;

If you need to figure out where the connections are going, you can break down the connections by database.

SELECT datname, numbackends FROM pg_stat_database;

To further investigate connections to a specific database, query pg_stat_activity.

SELECT * FROM pg_stat_activity WHERE datname='ibmclouddb';

Terminating Connections

Your admin user has the pg_signal_backend role. If you find connections that need to be reset or closed, the admin user can use both pg_cancel_backend and pg_terminate_backend. The pid of a process is found from the pg_stat_activity table.

• pg_cancel_backend Cancels a connection's current query without terminating the connection, and without stopping any other queries that it might be running.

  SELECT pg_cancel_backend(pid);
  

• pg_terminate_backend Stops the entire process and closes the connection.

  SELECT pg_terminate_backend(pid);
  

The admin user does have the power to reset or close the connections for any user on the deployment except superusers. Be careful not to terminate replication connections from the ibm-replication user, as it interferes with the high-availability of your deployment.

End Connections

If your deployment reaches the connection limit or you are having trouble connecting to your deployment and suspect that a high number of connections is a problem, you can disconnect (or end) all of the connections to your deployment.

In the UI, on the Settings tab, there is a button to End Connections to your deployment. Use caution, as it disrupts anything that is connected to your deployment.

The CLI command to end connections to the deployment is

ibmcloud cdb deployment-kill-connections <deployment name or CRN>

You can also use the Cloud Databases API to perform the end all connections operation.

Connection Pooling

One way to prevent exceeding the connection limit and ensure that connections from your applications are being handled efficiently is through connection pooling.

Many PostgreSQL driver libraries have connection pooling classes and functions. You need to consult your driver's documentation to implement connection pooling that is optimal for your use case. For example, the Python driver Psycopg2 has classes to handle connection pooling in your application. The Java PostgreSQL JDBC driver has methods for connection pooling at both the application and application server level.

Alternatively, you can use a third-party tool such as PgBouncer to manage your application's connections.

Raising the Connection Limit

Databases for EnterpriseDB allocates some amount of memory on a per connection basis. It is important to consider the total amount of memory that is available to your deployment before increasing the connection limit. To raise the connection limit, first you might want to scale your deployment to ensure that you have enough memory to accommodate more connections.

Next, change the value of max_connections on your deployment. To make permanent changes to the Databases for EnterpriseDB configuration, you want to use the Cloud Databases cli-plugin or API to write the changes to the configuration file for your deployment.

For example, to raise max_connections to 215, it might be a good idea to scale your deployment to at least 2 GB of RAM per data member, for a total of 6 GB of RAM for your deployment. Once the scaling operation has finished, then set the connection limit. In the CLI,

ibmcloud cdb deployment-groups-set example-deployment member --memory 6144

ibmcloud cdb deployment-configuration example-deployment '{"configuration":{"max_connections":215}}'

To make the changes by using the API,

curl -X PATCH `https://api.{region}.databases.cloud.ibm.com/v4/ibm/deployments/{id}/groups/member' \
-H "Authorization: Bearer $APIKEY" \
-H "Content-Type: application/json" \
-d '{"memory": {
        "allocation_mb": 6144
      }
    }'

curl -X PATCH 'https://api.{region}.databases.cloud.ibm.com/v4/ibm/deployments/{id}/configuration' \
-H "Authorization: Bearer $APIKEY" \
-H "Content-Type: application/json" \
-d '{"configuration":{
        "max_connections":215
      }
    }'