---
id: 83f265c2-7998-48a7-9ffa-81c6738d6937
---
# Postgres Connection Saturation
---

This incident type occurs when the number of connections to a Postgres server is approaching its limit and may cause a saturation of the connection pool. This can lead to slow queries, timeouts, and other performance issues. It is important to monitor and manage the number of connections to ensure the smooth functioning of the database.

### Parameters
```shell
# Environment Variables

export DATABASE_NAME="PLACEHOLDER"

export NEW_CONNECTION_LIMIT="PLACEHOLDER"

export PATH_TO_POSTGRESQL_CONF="PLACEHOLDER"


```

## Debug

### Check the current number of connections to the Postgres instance
```shell
sudo -u postgres psql -c "select count(*) from pg_stat_activity;" ${DATABASE_NAME}
```

### Check the maximum number of connections allowed by the Postgres instance
```shell
sudo -u postgres psql -c "show max_connections;" ${DATABASE_NAME}
```

### Check the current value of the connection limit for each user
```shell
sudo -u postgres psql -c "select usename, current_connections from pg_stat_activity join pg_user on pg_stat_activity.usename = pg_user.usename where pg_stat_activity.datname = '${DATABASE_NAME}';" ${DATABASE_NAME}
```

### Check if there are any idle connections that can be closed
```shell
sudo -u postgres psql -c "select * from pg_stat_activity where state = 'idle';" ${DATABASE_NAME}
```

### Check the size of the connection pool
```shell
sudo -u postgres psql -c "select * from pg_settings where name = 'max_connections';"
```

### Check the log files for any errors related to connections or connection pool
```shell
sudo tail -f /var/log/postgresql/postgresql-main.log
```

## Repair

### Define variables
```shell
PG_CONFIG_FILE=${PATH_TO_POSTGRESQL_CONF}

NEW_CONNECTION_LIMIT=${NEW_CONNECTION_LIMIT}
```

### Check if PG_CONFIG_FILE exists and is writable
```shell
if [ ! -w "${PG_CONFIG_FILE}" ]; then

  echo "ERROR: ${PG_CONFIG_FILE} does not exist or is not writable."

  exit 1

fi
```

### Backup the PostgreSQL configuration file
```shell
cp "${PG_CONFIG_FILE}" "${PG_CONFIG_FILE}.bak"
```

### Update the connection limit in the configuration file
```shell
sed -i "s/^max_connections = .*/max_connections = ${NEW_CONNECTION_LIMIT}/" "${PG_CONFIG_FILE}"
```

### Restart PostgreSQL server to apply changes
```shell
systemctl restart postgresql
```

### Next Step
```shell
echo "Connection limit updated to ${NEW_CONNECTION_LIMIT}."
```

### Identify and terminate idle connections to free up resources for other connections.
```shell


#!/bin/bash



# Set variables

PG_USER=${YOUR_POSTGRES_USER}

PG_PASSWORD=${YOUR_POSTGRES_PASSWORD}

PG_DATABASE=${YOUR_POSTGRES_DATABASE}

PG_HOST=${YOUR_POSTGRES_HOST}

PG_PORT=${YOUR_POSTGRES_PORT}



# Get idle connections

IDLE_CONNECTIONS=$(psql -U $PG_USER -h $PG_HOST -p $PG_PORT $PG_DATABASE -c "SELECT pid, (now() - query_start) as duration, query FROM pg_stat_activity WHERE state = 'idle' AND now() - query_start > interval '5 minutes';")



# Loop through idle connections and kill them

while read -r row; do

    PID=$(echo "$row" | awk '{print $1}')

    psql -U $PG_USER -h $PG_HOST -p $PG_PORT $PG_DATABASE -c "SELECT pg_terminate_backend($PID);"

done <<< "$IDLE_CONNECTIONS"


```


This incident type occurs when the number of connections to a Postgres server is approaching its limit and may cause a saturation of the connection pool. This can lead to slow queries, timeouts, and other performance issues. It is important to monitor and manage the number of connections to ensure the smooth functioning of the database.


This incident type is related to the Tomcat server, which is a popular open-source Java application server. It occurs when the available threads in the connection pool are running low, which means that there are not enough resources to serve incoming requests. This can cause delays or even failures in the application's performance, impacting the end-user experience. It is important to monitor and address this issue promptly to ensure that the application runs smoothly and efficiently.


Tomcat Low Available Threads in Connection Pool

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 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 DATABASE_NAME="PLACEHOLDER"

export NEW_CONNECTION_LIMIT="PLACEHOLDER"

export PATH_TO_POSTGRESQL_CONF="PLACEHOLDER"


```


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

```shell
sudo -u postgres psql -c "select count(*) from pg_stat_activity;" ${DATABASE_NAME}
```

### Check the maximum number of connections allowed by the Postgres instance

```shell
sudo -u postgres psql -c "show max_connections;" ${DATABASE_NAME}
```

### Check the current value of the connection limit for each user

```shell
sudo -u postgres psql -c "select usename, current_connections from pg_stat_activity join pg_user on pg_stat_activity.usename = pg_user.usename where pg_stat_activity.datname = '${DATABASE_NAME}';" ${DATABASE_NAME}
```

### Check if there are any idle connections that can be closed

```shell
sudo -u postgres psql -c "select * from pg_stat_activity where state = 'idle';" ${DATABASE_NAME}
```

### Check the size of the connection pool

```shell
sudo -u postgres psql -c "select * from pg_settings where name = 'max_connections';"
```

### Check the log files for any errors related to connections or connection pool

```shell
sudo tail -f /var/log/postgresql/postgresql-main.log
```


### Define variables

```shell
PG_CONFIG_FILE=${PATH_TO_POSTGRESQL_CONF}

NEW_CONNECTION_LIMIT=${NEW_CONNECTION_LIMIT}
```

### Check if PG\_CONFIG\_FILE exists and is writable

```shell
if [ ! -w "${PG_CONFIG_FILE}" ]; then

  echo "ERROR: ${PG_CONFIG_FILE} does not exist or is not writable."

  exit 1

fi
```

### Backup the PostgreSQL configuration file

```shell
cp "${PG_CONFIG_FILE}" "${PG_CONFIG_FILE}.bak"
```

### Update the connection limit in the configuration file

```shell
sed -i "s/^max_connections = .*/max_connections = ${NEW_CONNECTION_LIMIT}/" "${PG_CONFIG_FILE}"
```

### Restart PostgreSQL server to apply changes

```shell
systemctl restart postgresql
```

### Next Step

```shell
echo "Connection limit updated to ${NEW_CONNECTION_LIMIT}."
```

### Identify and terminate idle connections to free up resources for other connections.

```shell


#!/bin/bash



# Set variables

PG_USER=${YOUR_POSTGRES_USER}

PG_PASSWORD=${YOUR_POSTGRES_PASSWORD}

PG_DATABASE=${YOUR_POSTGRES_DATABASE}

PG_HOST=${YOUR_POSTGRES_HOST}

PG_PORT=${YOUR_POSTGRES_PORT}



# Get idle connections

IDLE_CONNECTIONS=$(psql -U $PG_USER -h $PG_HOST -p $PG_PORT $PG_DATABASE -c "SELECT pid, (now() - query_start) as duration, query FROM pg_stat_activity WHERE state = 'idle' AND now() - query_start > interval '5 minutes';")



# Loop through idle connections and kill them

while read -r row; do

    PID=$(echo "$row" | awk '{print $1}')

    psql -U $PG_USER -h $PG_HOST -p $PG_PORT $PG_DATABASE -c "SELECT pg_terminate_backend($PID);"

done <<< "$IDLE_CONNECTIONS"


```


Postgres Connection Saturation

Overview

Parameters

Debug

Check the current number of connections to the Postgres instance

Check the maximum number of connections allowed by the Postgres instance

Check the current value of the connection limit for each user

Check if there are any idle connections that can be closed

Check the size of the connection pool

Repair

Define variables

Check if PG_CONFIG_FILE exists and is writable

Backup the PostgreSQL configuration file

Update the connection limit in the configuration file

Restart PostgreSQL server to apply changes

Next Step

Identify and terminate idle connections to free up resources for other connections.

Learn more

Related Runbooks

Tomcat Low Available Threads in Connection Pool

Tomcat High JDBC Connection Pool Usage Incident.

Tomcat Connection Pool Exhaustion Incident

Postgresql too many locks acquired

Support