---
id: 05ee14c3-27f4-4eff-a945-4be778ec00b1
---

# Postgresql not enough connections incident
---

The Postgresql not enough connections incident type refers to an issue where the Postgresql database instance has reached its maximum allowed connections limit, causing new connection requests to fail. This can result in application errors and downtime until the issue is resolved. The incident typically requires immediate attention from a database administrator or software engineer to increase the maximum number of allowed connections or optimize existing connections.

### Parameters
```shell
# Environment Variables

export MAX_CONNECTIONS="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

```

## Debug

### Check the current number of connections to the Postgresql instance
```shell
sudo su - postgres -c "psql -c 'SELECT COUNT(*) FROM pg_stat_activity;'"
```

### Check the maximum allowed number of connections in the Postgresql configuration
```shell
sudo cat /etc/postgresql/main/postgresql.conf | grep max_connections
```

### Check the current memory usage of the Postgresql instance
```shell
ps aux | grep postgres
```

### Check the current disk usage of the Postgresql instance
```shell
df -h /var/lib/postgresql
```

### Check the system logs for any errors related to Postgresql
```shell
sudo tail -n 100 /var/log/syslog | grep postgres
```

### Check the Postgresql logs for any errors or connection-related messages
```shell
sudo tail -n 100 /var/log/postgresql/postgresql-main.log | grep -i error
```

### Check the network connections to the Postgresql instance
```shell
sudo netstat -nlp | grep ${DATABASE_PORT}
```

### Check the resource usage of the Postgresql process
```shell
sudo ps -eo pid,pcpu,pmem,vsz,rss,args | grep postgres | grep -v grep
```

### A long-running query or transaction has locked the database connection, preventing new connections from being established.
```shell


#!/bin/bash



# Set database connection parameters

DB_HOST=${DATABASE_HOST}

DB_PORT=${DATABASE_PORT}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Check if the database is running

if ! nc -z $DB_HOST $DB_PORT; then

  echo "ERROR: Database is not running on $DB_HOST:$DB_PORT"

  exit 1

fi



# Check database connection count

CURRENT_CONNECTIONS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';" -d $DB_NAME -t)

MAX_CONNECTIONS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "SELECT setting FROM pg_settings WHERE name = 'max_connections';" -d $DB_NAME -t)



echo "Current number of active connections: $CURRENT_CONNECTIONS"

echo "Maximum number of allowed connections: $MAX_CONNECTIONS"



# Check for long-running queries

LONG_RUNNING_QUERIES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "SELECT * FROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 minutes';" -d $DB_NAME)



if [ -z "$LONG_RUNNING_QUERIES" ]; then

  echo "No long-running queries found."

else

  echo "The following queries are running for more than 5 minutes:"

  echo "$LONG_RUNNING_QUERIES"

fi


```

## Repair

### Replace <MAX_CONNECTIONS> with the desired maximum number of allowed connections
```shell
MAX_CONNECTIONS=${MAX_CONNECTIONS}
```

### Modify the postgresql.conf file to set the max_connections parameter
```shell
sudo sed -i "s/^#*max_connections *= *[0-9]\+$/max_connections = $MAX_CONNECTIONS/" /etc/postgresql/main/postgresql.conf
```

### Restart the Postgresql service to apply the configuration changes
```shell
sudo service postgresql restart
```

### Use psql to verify the new maximum number of allowed connections
```shell
psql -U $DATABASE_USER -d $DATABASE_NAME -c "SHOW max_connections;"
```

### Identify and terminate idle or inactive connections to free up resources for new connection requests. This can be done using the pg_stat_activity view or a third-party monitoring tool.
```shell
bash

#!/bin/bash


# Query the pg_stat_activity view to identify idle connections

IDLE_CONNECTIONS=$(psql -h $DATABASE -p $DATABASE_PORT -U $DATABASE_USER -d $DATABASE_NAME -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';")



# Output the number of idle connections terminated

echo "$IDLE_CONNECTIONS idle connections terminated."


```

The Postgresql not enough connections incident type refers to an issue where the Postgresql database instance has reached its maximum allowed connections limit, causing new connection requests to fail. This can result in application errors and downtime until the issue is resolved. The incident typically requires immediate attention from a database administrator or software engineer to increase the maximum number of allowed connections or optimize existing connections.


This incident type refers to a situation where the Tomcat server's JDBC connection pool usage surpasses its configured limits. This can lead to a shortage of available connections for incoming requests, resulting in slow response times or even server crashes. It is important to monitor and manage the JDBC connection pool usage to prevent such incidents from occurring.


Tomcat High JDBC Connection Pool Usage Incident.

Tomcat Connection Pool Exhaustion Incident occurs when the maximum number of connections that a Tomcat server can handle has been reached. This can happen due to an increase in traffic or a sudden surge in user activity. When the connection pool is exhausted, new requests cannot be handled, and the application may become unavailable. This type of incident requires immediate attention to ensure that the application is restored to its normal functioning state.


Tomcat Connection Pool Exhaustion Incident

This incident type refers to a situation where Redis, a popular in-memory data structure store, has rejected connections. This could happen due to various reasons such as server overload, network issues, or misconfiguration. When this occurs, it can cause disruptions in the performance of applications that rely on Redis for data storage or caching. Thus, it requires immediate attention and resolution to minimize the impact on the applications and users.


Redis Rejected Connections Incident

This incident type occurs when there are too many locks acquired on a Postgresql database instance. This can cause issues with database performance and functionality. It may be necessary to adjust the max\_locks\_per\_transaction setting in Postgresql to prevent this issue from occurring.


Postgresql too many locks acquired

This incident type refers to a situation where the Postgresql database has a high rollback rate, which means that a high percentage of transactions are being aborted compared to the committed ones. This can cause issues with data consistency and performance, and may require investigation and resolution by the responsible team. The incident details may include information about the affected database instance, the service or system impacted, the urgency level, and any related alerts or escalations.


Postgresql high rollback rate incident

```shell
# Environment Variables

export MAX_CONNECTIONS="PLACEHOLDER"

export DATABASE_HOST="PLACEHOLDER"

export DATABASE_NAME="PLACEHOLDER"

export DATABASE_USER="PLACEHOLDER"

export DATABASE_PASSWORD="PLACEHOLDER"

export DATABASE_PORT="PLACEHOLDER"

```


### Check the current number of connections to the Postgresql instance

```shell
sudo su - postgres -c "psql -c 'SELECT COUNT(*) FROM pg_stat_activity;'"
```

### Check the maximum allowed number of connections in the Postgresql configuration

```shell
sudo cat /etc/postgresql/main/postgresql.conf | grep max_connections
```

### Check the current memory usage of the Postgresql instance

```shell
ps aux | grep postgres
```

### Check the current disk usage of the Postgresql instance

```shell
df -h /var/lib/postgresql
```

### Check the system logs for any errors related to Postgresql

```shell
sudo tail -n 100 /var/log/syslog | grep postgres
```

### Check the Postgresql logs for any errors or connection-related messages

```shell
sudo tail -n 100 /var/log/postgresql/postgresql-main.log | grep -i error
```

### Check the network connections to the Postgresql instance

```shell
sudo netstat -nlp | grep ${DATABASE_PORT}
```

### Check the resource usage of the Postgresql process

```shell
sudo ps -eo pid,pcpu,pmem,vsz,rss,args | grep postgres | grep -v grep
```

### A long-running query or transaction has locked the database connection, preventing new connections from being established.

```shell


#!/bin/bash



# Set database connection parameters

DB_HOST=${DATABASE_HOST}

DB_PORT=${DATABASE_PORT}

DB_NAME=${DATABASE_NAME}

DB_USER=${DATABASE_USER}

DB_PASSWORD=${DATABASE_PASSWORD}



# Check if the database is running

if ! nc -z $DB_HOST $DB_PORT; then

  echo "ERROR: Database is not running on $DB_HOST:$DB_PORT"

  exit 1

fi



# Check database connection count

CURRENT_CONNECTIONS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';" -d $DB_NAME -t)

MAX_CONNECTIONS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "SELECT setting FROM pg_settings WHERE name = 'max_connections';" -d $DB_NAME -t)



echo "Current number of active connections: $CURRENT_CONNECTIONS"

echo "Maximum number of allowed connections: $MAX_CONNECTIONS"



# Check for long-running queries

LONG_RUNNING_QUERIES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -c "SELECT * FROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 minutes';" -d $DB_NAME)



if [ -z "$LONG_RUNNING_QUERIES" ]; then

  echo "No long-running queries found."

else

  echo "The following queries are running for more than 5 minutes:"

  echo "$LONG_RUNNING_QUERIES"

fi


```


### Replace \<MAX\_CONNECTIONS> with the desired maximum number of allowed connections

```shell
MAX_CONNECTIONS=${MAX_CONNECTIONS}
```

### Modify the postgresql.conf file to set the max\_connections parameter

```shell
sudo sed -i "s/^#*max_connections *= *[0-9]\+$/max_connections = $MAX_CONNECTIONS/" /etc/postgresql/main/postgresql.conf
```

### Restart the Postgresql service to apply the configuration changes

```shell
sudo service postgresql restart
```

### Use psql to verify the new maximum number of allowed connections

```shell
psql -U $DATABASE_USER -d $DATABASE_NAME -c "SHOW max_connections;"
```

### Identify and terminate idle or inactive connections to free up resources for new connection requests. This can be done using the pg\_stat\_activity view or a third-party monitoring tool.

```shell
bash

#!/bin/bash


# Query the pg_stat_activity view to identify idle connections

IDLE_CONNECTIONS=$(psql -h $DATABASE -p $DATABASE_PORT -U $DATABASE_USER -d $DATABASE_NAME -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';")



# Output the number of idle connections terminated

echo "$IDLE_CONNECTIONS idle connections terminated."


```


Postgresql not enough connections incident

Overview

Parameters

Debug

Check the current number of connections to the Postgresql instance

Check the maximum allowed number of connections in the Postgresql configuration

Check the current memory usage of the Postgresql instance

Check the current disk usage of the Postgresql instance

Check the network connections to the Postgresql instance

Check the resource usage of the Postgresql process

A long-running query or transaction has locked the database connection, preventing new connections from being established.

Repair

Replace <MAX_CONNECTIONS> with the desired maximum number of allowed connections

Modify the postgresql.conf file to set the max_connections parameter

Restart the Postgresql service to apply the configuration changes

Use psql to verify the new maximum number of allowed connections

Identify and terminate idle or inactive connections to free up resources for new connection requests. This can be done using the pg_stat_activity view or a third-party monitoring tool.

Learn more

Related Runbooks

Tomcat High JDBC Connection Pool Usage Incident.

Tomcat Connection Pool Exhaustion Incident

Redis Rejected Connections Incident

Postgresql too many locks acquired

Support