How to fix Too many connections in PostgreSQL

Connect to PostgreSQL as a superuser and check your current settings:

SHOW max_connections;
SHOW superuser_reserved_connections;
SELECT count(*) FROM pg_stat_activity;

Note the current value and how many connections are active. The reserved slots (default 3) are reserved for superusers only.

Query the activity log to see if idle connections are consuming slots:

SELECT pid, usename, application_name, state, query_start, state_change
FROM pg_stat_activity
WHERE state = 'idle' OR (NOW() - query_start) > interval '30 minutes'
ORDER BY query_start;

If you find many idle connections, they may be leaked connections from your application. Check that your application is properly closing connections.

Instead of increasing max_connections indefinitely, use a connection pooler like PgBouncer to multiplex many client connections onto fewer database connections:

1. Install PgBouncer (on Debian/Ubuntu: sudo apt-get install pgbouncer)
2. Configure /etc/pgbouncer/pgbouncer.ini:

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

3. Point your application to PgBouncer (localhost:6432) instead of PostgreSQL (localhost:5432)

Pool mode "transaction" releases the database connection after each transaction, allowing many clients to share fewer connections.

If you cannot use connection pooling, increase max_connections:

1. Connect as superuser and modify the setting:

ALTER SYSTEM SET max_connections = 200;

2. Restart PostgreSQL for the change to take effect:

sudo systemctl restart postgresql

3. Verify the change:

SHOW max_connections;

Note: Increasing max_connections requires more shared memory. Monitor system resources after the change. Generally, 200-500 connections is reasonable; higher values require careful resource planning.

Check if limits are set at the database or role level:

SELECT datname, datconnlimit FROM pg_database WHERE datconnlimit > 0;
SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit > 0;

If limits are too low, adjust them:

ALTER DATABASE mydb CONNECTION LIMIT 100;
ALTER ROLE myuser CONNECTION LIMIT -1;  -- -1 means unlimited

If you need to free up connections immediately, terminate idle sessions:

-- Terminate idle connections (be careful not to kill active work)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND pid != pg_backend_pid();

This is a temporary measure. The real fix is to prevent idle connections from forming in the first place through connection pooling or application fixes.