PostgreSQL too_many_connections

A FATAL error raised when a new client attempts to connect but the total number of connections has reached the `max_connections` limit set in the PostgreSQL server configuration.

Common Causes

The `max_connections` parameter in `postgresql.conf` is set too low for the application's load.
Connection leaks in the application where connections are not properly closed after use.
A sudden traffic spike or many concurrent users without a connection pooling mechanism.

How to Fix

1 Increase max_connections (Temporary Fix)

Increase the connection limit in the configuration file and reload PostgreSQL. This is a temporary measure; also consider increasing `shared_buffers` and kernel settings.

BASH

 $ # Edit postgresql.conf
max_connections = 200

# Reload configuration (does not require restart)
sudo -u postgres psql -c "SELECT pg_reload_conf();"

2 Terminate Idle Connections

Free up slots by terminating idle or long-running connections. Use this to regain capacity immediately.

BASH

 $ -- View active connections
SELECT pid, usename, application_name, client_addr, state, query FROM pg_stat_activity;

-- Terminate a specific connection (replace PID)
SELECT pg_terminate_backend(PID);

-- Terminate all idle connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';

3 Implement a Connection Pooler

Use PgBouncer or pgpool-II to manage a small pool of database connections shared among many application clients, drastically reducing connection count.

BASH

 $ # Install PgBouncer on Ubuntu/Debian
sudo apt-get install pgbouncer

# Configure /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

# Start the service
sudo systemctl restart pgbouncer

4 Review and Fix Application Logic

Ensure your application uses connection pools correctly, closes connections promptly, and implements retry logic with exponential backoff for this error.

BASH

 $ # Example: Python with psycopg2 and connection context manager
import psycopg2
from psycopg2 import pool

# Create a connection pool
connection_pool = psycopg2.pool.SimpleConnectionPool(
    1,  # minconn
    10, # maxconn
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypass"
)

# Application gets a connection from the pool
def get_data():
    conn = connection_pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM mytable")
            return cur.fetchall()
    finally:
        # Always return connection to the pool
        connection_pool.putconn(conn)