Fix PostgreSQL FATAL: remaining connection slots are reserved for non-replication superuser connections

The error

FATAL: remaining connection slots are reserved for non-replication superuser connections

PostgreSQL hit its max_connections ceiling and stopped accepting new connections from regular users. It always holds back a few slots — default 3 — specifically for superuser logins. That way, admins can still connect and fix things. Everyone else hits this wall.

Root cause

Every PostgreSQL instance has a hard limit on concurrent connections, controlled by max_connections in postgresql.conf. Out of the box, that's 100. Once your app consumes those slots (minus the 3 reserved for superusers), the next connection attempt gets rejected immediately — no queuing, no waiting.

Common triggers:

• No connection pooler — each app thread or process opens its own dedicated connection
• Connection leaks — code that opens connections but never closes them
• Traffic spike — a sudden load increase exhausts the pool faster than connections can be recycled
• Multiple app instances sharing one DB with no pooling (e.g., 10 Node.js pods × 10 connections each = 100 connections instantly)
• max_connections set too low for the actual workload

Step 1: Check current connection state

Since the reserved superuser slots are still available, connect as postgres:

psql -U postgres -d mydb

Get a breakdown of what's consuming those connections:

SELECT count(*), state, usename, application_name
FROM pg_stat_activity
GROUP BY state, usename, application_name
ORDER BY count DESC;

Then check how close you are to the limit:

SELECT max_conn, used, res_for_super, max_conn - used - res_for_super AS available
FROM
  (SELECT count(*) used FROM pg_stat_activity) t1,
  (SELECT setting::int res_for_super FROM pg_settings WHERE name = 'superuser_reserved_connections') t2,
  (SELECT setting::int max_conn FROM pg_settings WHERE name = 'max_connections') t3;

If available is 0 or negative — that's your culprit confirmed.

Fix 1: Kill idle connections (immediate relief)

Spot a pile of idle or idle in transaction connections? Terminate them now:

-- Kill idle connections sitting for 10+ minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes'
  AND usename != 'postgres';

Handle idle in transaction connections separately — they hold locks and are more disruptive:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '5 minutes';

This buys you breathing room. It won't stay fixed without addressing the underlying cause.

Fix 2: Increase max_connections

Find your config file location first:

SHOW config_file;

Then edit postgresql.conf:

# postgresql.conf
max_connections = 200          # was 100
reserved_connections = 3       # keep some for superuser

Restart to apply:

sudo systemctl restart postgresql

Before you raise it to 500: each PostgreSQL connection consumes roughly 5–10 MB of RAM. At 200 connections, that's 1–2 GB just for connection overhead — before your queries touch a single byte of data. If you're regularly hitting 100 connections, the problem is almost certainly missing pooling, not a low limit.

When changing max_connections, also review shared_buffers and work_mem — more connections mean more memory pressure across the board.

Fix 3: Deploy PgBouncer (the durable fix)

PgBouncer acts as a proxy between your app and PostgreSQL. Your app thinks it's talking directly to the database — but PgBouncer is recycling a small set of real DB connections across thousands of app requests.

sudo apt install pgbouncer

Configure /etc/pgbouncer/pgbouncer.ini:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          ; or session
max_client_conn = 1000           ; app-facing connections
default_pool_size = 20           ; actual PostgreSQL connections
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
log_connections = 1
log_disconnections = 1

Create /etc/pgbouncer/userlist.txt with the MD5 hash of each user's credentials:

"myuser" "md5passwordhash"

Generate the hash:

echo -n "passwordusername" | md5sum

Enable and start PgBouncer:

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

Point your application to port 6432 instead of 5432. With this in place, PostgreSQL's max_connections can drop to 25–50 while PgBouncer handles 1000+ app connections on top. Teams commonly run 500 concurrent app connections against a PostgreSQL pool of just 20.

Fix 4: Application-level connection pooling

Before reaching for PgBouncer, check whether your framework already provides pooling — and whether you're actually using it.

Node.js (pg library):

const { Pool } = require('pg');
const pool = new Pool({
  max: 10,              // max connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});
// Use pool.query() instead of client.connect() per request

Python (SQLAlchemy):

from sqlalchemy import create_engine
engine = create_engine(
    'postgresql://user:pass@localhost/mydb',
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_recycle=1800,
)

Django: set CONN_MAX_AGE to reuse connections across requests rather than reopening them on every hit:

DATABASES = {
    'default': {
        ...
        'CONN_MAX_AGE': 60,  # seconds; 0 = close after every request
    }
}

Verify the fix

Watch connection counts live after applying changes:

-- Run repeatedly or wire into your monitoring
SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity
WHERE datname = 'mydb';

Running PgBouncer? Check pool stats directly:

psql -p 6432 -U pgbouncer pgbouncer -c 'SHOW POOLS;'

Look at cl_active vs sv_active. If cl_active is 200 and sv_active is 15, PgBouncer is doing exactly what it should.

Prevention

• Alert when pg_stat_activity count exceeds 80% of max_connections — don't wait for it to hit 100%
• Add connection pooling from day one. Retrofitting it into a live production app is genuinely painful
• Set idle_in_transaction_session_timeout = '5min' in postgresql.conf to auto-kill stuck transactions
• Set statement_timeout to cut off runaway queries before they hold connections indefinitely
• In Kubernetes: each pod gets its own pool, so total connections = pods × pool_size. With 20 pods at 10 connections each, you've already burned 200 slots. PgBouncer isn't optional in this setup