How to Fix Too Many Connections Error in PostgreSQL

The too many connections error in PostgreSQL happens when the number of client connections exceeds the configured limit. To fix it, increase the max_connections setting in postgresql.conf and restart the server, or close idle connections to free up slots.

Why This Happens

This error occurs because PostgreSQL limits how many clients can connect at the same time using the max_connections setting. If your application or users open more connections than this limit, new connection attempts fail with this error.

Common causes include not closing database connections properly or a sudden spike in traffic.

bash

psql -c "SELECT pg_sleep(1000);" &
psql -c "SELECT pg_sleep(1000);" &
psql -c "SELECT pg_sleep(1000);" &
-- Repeat until connections exceed max_connections

Output

FATAL:  sorry, too many clients already

The Fix

To fix this, increase the max_connections value in your postgresql.conf file to allow more simultaneous connections. Then restart PostgreSQL to apply the change. Alternatively, identify and close idle or unnecessary connections to free slots.

bash

# Edit postgresql.conf
max_connections = 200

# Restart PostgreSQL service
sudo systemctl restart postgresql

Output

PostgreSQL restarted successfully with increased max_connections

Prevention

To avoid this error in the future, use connection pooling tools like PgBouncer to manage connections efficiently. Also, ensure your application closes connections properly after use. Monitor active connections regularly with queries like SELECT * FROM pg_stat_activity;.

sql

SELECT pid, usename, application_name, client_addr, state, query_start FROM pg_stat_activity;

Output

 pid | usename | application_name | client_addr | state  |     query_start     
-----+---------+------------------+-------------+--------+---------------------
 123 | user1   | app1             | 192.168.1.5 | active | 2024-06-01 10:00:00
 124 | user2   | app2             | 192.168.1.6 | idle   | 2024-06-01 09:55:00

Related Errors

Other connection-related errors include:

• FATAL: password authentication failed - caused by wrong credentials.
• FATAL: remaining connection slots are reserved for non-replication superuser connections - happens when all slots except reserved ones are used.

Fixes usually involve checking credentials or increasing max_connections.

Key Takeaways

Increase max_connections in postgresql.conf and restart to allow more connections.

Close idle or unused connections to free up connection slots.

Use connection pooling tools like PgBouncer to manage connections efficiently.

Regularly monitor active connections with pg_stat_activity.

Fix related errors by checking credentials and reserved connection slots.