How We Diagnosed and Resolved PostgreSQL Connection Exhaustion in Kubernetes

In this incident, the database pods appeared to be running, but applications were unable to establish new database connections. The investigation showed that the PostgreSQL connection limit had been exhausted by application connection pools.

All names, addresses, usernames, and cluster details in this article have been anonymized.

Initial Pod Status

We started by checking the database pods:

kubectl get pods -n data-platform -o wide | grep milad-db

Example output:

NAME         READY   STATUS    RESTARTS   AGE    IP           NODE
milad-db-1   1/1     Running   1          7d     10.42.3.21   worker-02
milad-db-2   1/1     Running   0          9d     10.42.7.18   worker-03

Both pods were running and ready. According to their labels, milad-db-2 was the primary and milad-db-1 was the replica.

Checking Services and Endpoints

kubectl get services,endpoints -n data-platform | \
grep -E 'milad-db|postgres'

Example output:

service/milad-db-external   LoadBalancer   10.96.14.20   192.0.2.24   5432:31254/TCP
service/milad-db-r          ClusterIP      10.96.14.21   <none>       5432/TCP
service/milad-db-ro         ClusterIP      10.96.14.22   <none>       5432/TCP
service/milad-db-rw         ClusterIP      10.96.14.23   <none>       5432/TCP

endpoints/milad-db-external   10.42.7.18:5432
endpoints/milad-db-r          10.42.3.21:5432,10.42.7.18:5432
endpoints/milad-db-ro         10.42.3.21:5432
endpoints/milad-db-rw         10.42.7.18:5432

The read-write service correctly pointed to the primary pod, while the read-only service pointed to the replica.

Inspecting the Database Pods

kubectl describe pod -n data-platform milad-db-1
kubectl describe pod -n data-platform milad-db-2

The relevant output showed that both pods were ready:

Status:           Running

Conditions:
  Type                        Status
  PodReadyToStartContainers   True
  Initialized                 True
  Ready                       True
  ContainersReady             True
  PodScheduled                True

Events:                      <none>

This confirmed that Kubernetes considered the pods healthy. The problem was therefore more likely to be inside PostgreSQL or in the application connection path.

Finding the Critical Error

We reviewed the logs from both the primary and replica:

kubectl logs -n data-platform milad-db-1 --since=2h
kubectl logs -n data-platform milad-db-2 --since=2h

The primary repeatedly logged the following error:

FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
SQLSTATE: 53300

Representative anonymized log entries:

{
  "level": "info",
  "logger": "postgres",
  "logging_pod": "milad-db-2",
  "record": {
    "user_name": "app_alpha",
    "database_name": "application_db",
    "connection_from": "10.42.3.17:15671",
    "error_severity": "FATAL",
    "sql_state_code": "53300",
    "message": "remaining connection slots are reserved for roles with the SUPERUSER attribute",
    "application_name": "PostgreSQL JDBC Driver",
    "backend_type": "client backend"
  }
}

This error means PostgreSQL has reached its configured connection limit. Only reserved administrative connection slots remain available.

Replication Was Also Affected

The replica logs showed repeated WAL streaming interruptions:

could not receive data from WAL stream:
SSL connection has been closed unexpectedly

The primary also rejected replication connection attempts:

{
  "user_name": "streaming_replica",
  "connection_from": "10.42.3.21:53502",
  "error_severity": "FATAL",
  "sql_state_code": "53300",
  "message": "remaining connection slots are reserved for roles with the SUPERUSER attribute"
}

This demonstrated that connection exhaustion was not only affecting applications. It was also preventing the replica from reconnecting to the primary.

Checking the Configured Limit

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c "SHOW max_connections;"

Output:

 max_connections
-----------------
 100
(1 row)

The database was configured to accept a maximum of 100 PostgreSQL connections.

Counting Connections by Application

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c \
"SELECT usename,
        application_name,
        client_addr,
        state,
        count(*)
 FROM pg_stat_activity
 GROUP BY 1,2,3,4
 ORDER BY 5 DESC;"

Anonymized output from the first inspection:

   usename   |    application_name    | client_addr |        state        | count
-------------+------------------------+-------------+---------------------+------
 app_alpha   | PostgreSQL JDBC Driver | 10.42.3.17  | idle                |  40
 app_beta    | PostgreSQL JDBC Driver | 10.42.3.17  | idle                |  20
 app_gamma   | PostgreSQL JDBC Driver | 10.42.3.17  | idle                |  20
 app_alpha   | PostgreSQL JDBC Driver | 10.42.3.17  | idle in transaction |   1
 app_alpha   | PostgreSQL JDBC Driver | 10.42.3.17  | active              |   1
 postgres    | psql                   |             | active              |   1
 replica     | milad-db-1             | 10.42.3.21  | active              |   1

Almost all application sessions were idle. Only a very small number were actively processing queries.

Inspecting Connection Age

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c \
"SELECT pid,
        usename,
        client_addr,
        state,
        now() - state_change AS state_age,
        now() - query_start AS query_age,
        left(query,120) AS query
 FROM pg_stat_activity
 WHERE backend_type = 'client backend'
 ORDER BY state_change;"

Representative output:

  pid   |  usename  | client_addr | state | state_age | query_age | query
--------+-----------+-------------+-------+-----------+-----------+------------------------------
 535057 | app_beta  | 192.0.2.23  | idle  | 01:52:37  | 01:52:37  | SET application_name = ...
 535090 | app_gamma | 192.0.2.23  | idle  | 01:51:23  | 01:51:23  | COMMIT
 535263 | app_alpha | 192.0.2.23  | idle  | 01:33:42  | 01:33:42  | SELECT ...
 540399 | app_alpha | 10.42.3.17  | idle  | 00:13:24  | 00:13:24  | SET application_name = ...
 540607 | app_gamma | 10.42.3.17  | idle  | 00:03:55  | 00:03:55  | COMMIT

This confirmed that many connections had remained open and unused for extended periods.

Identifying the Client Workload

kubectl get pods -A -o wide | grep '10.42.3.17'

If no pod is returned, the address may belong to an external host, another cluster, a host-networked process, a NAT address, or a previously allocated workload address.

Emergency Recovery

To recover capacity without restarting the database, we terminated only client sessions that had been idle for more than 30 minutes:

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c \
"SELECT pg_terminate_backend(pid)
 FROM pg_stat_activity
 WHERE backend_type = 'client backend'
   AND state = 'idle'
   AND state_change < now() - interval '30 minutes'
   AND pid <> pg_backend_pid();"

Output:

 pg_terminate_backend
----------------------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(28 rows)

Twenty-eight old idle sessions were successfully terminated.

Checking the Result

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c \
"SELECT state, count(*)
 FROM pg_stat_activity
 GROUP BY state
 ORDER BY 2 DESC;"

Output after cleanup:

 state  | count
--------+------
 idle   | 40
        |  9
 active |  2
(3 rows)

We also confirmed that no new connection-limit errors appeared:

kubectl logs -n data-platform milad-db-2 --since=10m | \
grep -E '53300|remaining connection slots'

Output:

No matching log entries

Verifying Replication Recovery

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c \
"SELECT application_name,
        client_addr,
        state,
        sync_state,
        pg_size_pretty(
          pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)
        ) AS lag
 FROM pg_stat_replication;"

Output:

 application_name | client_addr |   state   | sync_state |   lag
------------------+-------------+-----------+------------+--------
 milad-db-1       | 10.42.3.21  | streaming | async      | 0 bytes
(1 row)

Replication had returned to the streaming state with zero reported WAL lag.

The Connections Returned

After the first cleanup, the application recreated a large number of connections. A second inspection showed:

 backend_type  |  usename   | client_addr |        state        | count
---------------+-------------+-------------+---------------------+------
 client backend| app_alpha   | 10.42.3.17  | idle                | 76
 client backend| app_gamma   | 10.42.3.17  | idle                | 10
 client backend| app_beta    | 10.42.3.17  | idle                | 10
 client backend| app_alpha   | 10.42.3.17  | idle in transaction |  1
 walsender     | replica     | 10.42.3.21  | active              |  1

This was the strongest evidence that the root cause was application-side connection-pool behavior. One application account was consuming 77 connections, of which 76 were idle.

Targeted Cleanup

We then terminated only old idle connections belonging to the affected application and client address:

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c \
"SELECT pg_terminate_backend(pid)
 FROM pg_stat_activity
 WHERE backend_type = 'client backend'
   AND usename = 'app_alpha'
   AND client_addr = '10.42.3.17'
   AND state = 'idle'
   AND state_change < now() - interval '5 minutes'
   AND pid <> pg_backend_pid();"

Output:

 pg_terminate_backend
----------------------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(10 rows)

Final Connection Distribution

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c \
"SELECT usename,
        client_addr,
        state,
        count(*)
 FROM pg_stat_activity
 WHERE backend_type = 'client backend'
 GROUP BY 1,2,3
 ORDER BY 4 DESC;"

Final output:

  usename   | client_addr | state  | count
------------+-------------+--------+------
 app_alpha  | 10.42.3.17  | idle   | 20
 app_gamma  | 10.42.3.17  | idle   | 10
 app_beta   | 10.42.3.17  | idle   | 10
 postgres   |             | active |  1
(4 rows)

The database returned to a stable operating range with approximately 40 application sessions.

Monitoring Client Connections

To monitor only application connections every 10 seconds:

watch -n 10 "kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -Atc \
\"SELECT count(*)
  FROM pg_stat_activity
  WHERE backend_type='client backend';\""

It is important to filter on backend_type='client backend'. Counting every row in pg_stat_activity also includes PostgreSQL background processes.

Root Cause

The database itself was operating according to its configuration. The immediate outage occurred because application connection pools consumed nearly all 100 available PostgreSQL connection slots.

The evidence indicated:

• The majority of sessions were idle.
• One application account created significantly more sessions than expected.
• Terminated sessions were quickly recreated.
• Only a small number of sessions were actively executing queries.
• Replication was temporarily unable to connect because no normal connection slots remained.

The likely application-side causes were:

• An oversized maximum pool size
• Multiple application instances, each maintaining a separate pool
• Multiple pools being created inside one application instance
• Connections not being returned correctly after exceptions
• A rapid retry loop following a temporary failure

Required Application Changes

The development team should implement the following changes:

1. Create only one connection pool per application instance.
2. Calculate maximum pool capacity across every running replica.
3. Keep the combined pool capacity safely below the database connection limit.
4. Use a low minimum-idle setting, such as two to five connections per instance.
5. Configure an idle timeout.
6. Configure a maximum connection lifetime.
7. Return connections to the pool in both success and exception paths.
8. Close statements and result sets correctly.
9. Use exponential backoff for reconnect attempts.
10. Enable connection-leak detection.
11. Set a unique application_name for each service and instance.
12. Monitor active, idle, and idle-in-transaction sessions separately.

A possible starting point for a small JDBC-based application instance is:

maximumPoolSize=10
minimumIdle=2
idleTimeout=300000
maxLifetime=1800000
connectionTimeout=30000

These values must be multiplied by the total number of running application instances. Six instances with a pool size of 10 may consume up to 60 database connections.

Increasing max_connections Safely

As an additional safety margin, the PostgreSQL connection limit may be increased from 100 to 200. This does not replace the application-side fix.

Important: Changing max_connections requires PostgreSQL to restart. In a high-availability cluster, the operator may perform a rolling restart and role transition. Applications may experience a brief interruption.

Before applying this change:

• Confirm that all database instances are ready.
• Confirm that replication is streaming with zero or minimal lag.
• Confirm that the database operator is healthy.
• Verify sufficient memory capacity.
• Export the current cluster configuration.
• Prepare a rollback command.
• Use a maintenance window if interruption is unacceptable.

Check Cluster Health

kubectl get clusters.postgresql.cnpg.io \
milad-db -n data-platform

kubectl get pods -n data-platform \
-l cnpg.io/cluster=milad-db

Example healthy output:

NAME       AGE    INSTANCES   READY   STATUS                     PRIMARY
milad-db   109d   2           2       Cluster in healthy state   milad-db-2

Export the Existing Configuration

kubectl get clusters.postgresql.cnpg.io \
milad-db -n data-platform -o yaml \
> milad-db-before-max-connections.yaml

Preview the Change

kubectl patch clusters.postgresql.cnpg.io \
milad-db -n data-platform \
--type=merge \
--dry-run=server -o yaml \
-p '{"spec":{"postgresql":{"parameters":{"max_connections":"200"}}}}'

Apply the Change

kubectl patch clusters.postgresql.cnpg.io \
milad-db -n data-platform \
--type=merge \
-p '{"spec":{"postgresql":{"parameters":{"max_connections":"200"}}}}'

Watch the Rolling Operation

kubectl get pods -n data-platform \
-l cnpg.io/cluster=milad-db -w

Wait until both pods return to the Running and Ready state.

Verify the New Value

kubectl exec -n data-platform milad-db-1 -- \
psql -U postgres -d postgres -c "SHOW max_connections;"

kubectl exec -n data-platform milad-db-2 -- \
psql -U postgres -d postgres -c "SHOW max_connections;"

Expected output on both instances:

 max_connections
-----------------
 200
(1 row)

Rollback Command

kubectl patch clusters.postgresql.cnpg.io \
milad-db -n data-platform \
--type=merge \
-p '{"spec":{"postgresql":{"parameters":{"max_connections":"100"}}}}'

Rolling back also requires a controlled restart.

Conclusion

The outage was caused by PostgreSQL connection exhaustion. Application connection pools consumed almost all available slots, even though most sessions were idle.

Terminating old idle sessions restored availability and allowed replication to return to a healthy streaming state with zero lag. However, the sessions began returning, confirming that the permanent solution had to be implemented in the application connection-pool configuration.

The long-term fix is to limit total pool capacity, prevent duplicate pool creation, correctly release connections, configure idle timeouts, and avoid aggressive retry loops.

For additional operational headroom, the database max_connections setting can be increased from 100 to 200 after completing the high-availability, restart, rollback, and memory-capacity checks described above.