Fix PostgreSQL Too Many Connections Error

PostgreSQL's "too many connections" error (FATAL: sorry, too many clients already) occurs when the connection count reaches max_connections. This prevents new connections including admin access for fixing the issue. This guide covers resolving and preventing this problem.

Immediate Recovery

# If you can't connect normally, use the superuser reserved connection
psql -U postgres -h localhost -p 5432 -d postgres
# This works because PostgreSQL reserves superuser_reserved_connections (default: 3)

# Check current connections
SELECT count(*) FROM pg_stat_activity;
SELECT max_connections FROM pg_settings WHERE name = 'max_connections';

# See what's using connections
SELECT pid, usename, application_name, client_addr, state, query_start,
       now() - query_start AS duration, query
FROM pg_stat_activity
ORDER BY query_start;

# Find idle connections (candidates for termination)
SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY query_start;

# Kill idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND query_start < now() - interval '10 minutes'
AND pid != pg_backend_pid();

Diagnose the Root Cause

# Connection breakdown by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count DESC;

# Connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name
ORDER BY count DESC;

# Connections by user
SELECT usename, count(*)
FROM pg_stat_activity
GROUP BY usename
ORDER BY count DESC;

# Connections by client IP
SELECT client_addr, count(*)
FROM pg_stat_activity
WHERE client_addr IS NOT NULL
GROUP BY client_addr
ORDER BY count DESC;

# Long-running queries (potential connection holders)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND query_start < now() - interval '1 minute'
ORDER BY duration DESC;

Increase max_connections

# Check current setting
SHOW max_connections;  -- Default: 100

# Edit postgresql.conf
sudo vim /etc/postgresql/16/main/postgresql.conf
# max_connections = 200

# Each connection uses ~5-10MB RAM
# Formula: max_connections * 10MB should not exceed available RAM
# 200 connections * 10MB = 2GB overhead

# Also adjust shared_buffers and work_mem
# shared_buffers = 25% of RAM (e.g., 2GB for 8GB server)
# work_mem = (RAM - shared_buffers) / (max_connections * 3)
# For 8GB RAM: work_mem = (8192 - 2048) / (200 * 3) = ~10MB

# Restart PostgreSQL (requires downtime)
sudo systemctl restart postgresql

Use Connection Pooling (Recommended)

# PgBouncer is the standard PostgreSQL connection pooler
sudo apt install pgbouncer

# Configure PgBouncer
sudo cat > /etc/pgbouncer/pgbouncer.ini  /etc/pgbouncer/userlist.txt
# Get md5 hash: SELECT 'md5' || md5('password' || 'username');

sudo systemctl restart pgbouncer

# Applications connect to PgBouncer (port 6432) instead of PostgreSQL (5432)
# PgBouncer maintains a small pool of real connections to PostgreSQL

Application-Side Fixes

# Node.js — set pool limits
const { Pool } = require('pg');
const pool = new Pool({
    max: 20,          // Maximum pool size
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 5000,
});

# Python (SQLAlchemy) — configure pool
engine = create_engine('postgresql://...', pool_size=10, max_overflow=20, pool_timeout=30, pool_recycle=1800)

# PHP (PDO) — use persistent connections carefully
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_PERSISTENT => true]);

# Django
DATABASES = {
    'default': {
        'CONN_MAX_AGE': 600,  # Reuse connections for 10 minutes
        'CONN_HEALTH_CHECKS': True,
    }
}

Monitoring

# Monitor connection count
SELECT count(*) as total,
    sum(case when state = 'active' then 1 else 0 end) as active,
    sum(case when state = 'idle' then 1 else 0 end) as idle,
    sum(case when state = 'idle in transaction' then 1 else 0 end) as idle_in_tx
FROM pg_stat_activity;

# Alert when approaching max
#!/bin/bash
current=$(psql -tAc "SELECT count(*) FROM pg_stat_activity")
max=$(psql -tAc "SHOW max_connections" | tr -d ' ')
pct=$((current * 100 / max))
if [ "$pct" -gt 80 ]; then
    echo "PostgreSQL connections at ${pct}% ($current/$max)" | mail -s "DB Alert" admin@example.com
fi

Best Practices

• Use PgBouncer: Connection pooling is the #1 solution for connection management
• Set pool limits in your application — don't let apps open unlimited connections
• Monitor connection count and alert at 80% of max_connections
• Kill idle-in-transaction connections — they hold locks and waste connections
• Don't just increase max_connections — each connection costs ~10MB RAM
• Use transaction pooling mode in PgBouncer for the best connection efficiency