How to Fix PostgreSQL Error: Remaining Connection Slots Are Reserved

The "Remaining connection slots are reserved for non-replication superuser connections" error occurs when regular users attempt to connect but only reserved superuser connection slots remain available. PostgreSQL reserves a small number of connections for superusers to allow administrative access even when the database is at connection capacity.

Impact

This error prevents normal user connections while the database approaches maximum connection capacity. It's similar to "Too many connections" but specifically indicates that only superuser-reserved slots remain.

Common Causes

1. Total connections approaching max_connections limit
2. superuser_reserved_connections taking up last available slots
3. Too many regular user connections
4. Missing connection pooling
5. Connection leaks in applications
6. Traffic spike exhausting connection pool

Troubleshooting and Resolution Steps

1. Check connection configuration:

   -- Check limits
   SHOW max_connections;
   SHOW superuser_reserved_connections;
   
   -- Calculate available for regular users
   SELECT
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') -
       (SELECT setting::int FROM pg_settings WHERE name = 'superuser_reserved_connections')
       AS available_for_regular_users;
   
   -- Check current usage
   SELECT COUNT(*), rolsuper
   FROM pg_stat_activity a
   JOIN pg_roles r ON a.usename = r.rolname
   GROUP BY rolsuper;
   

2. Connect as superuser to investigate:

   # Superusers can always connect using reserved slots
   psql -U postgres -d mydb
   
   # Check connection details
   SELECT
       pid,
       usename,
       application_name,
       client_addr,
       state,
       query_start
   FROM pg_stat_activity
   ORDER BY query_start;
   

3. Terminate idle or unnecessary connections:

   -- As superuser, terminate idle connections
   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE state = 'idle'
   AND usename != 'postgres'  -- Don't kill superuser connections
   AND now() - state_change > interval '5 minutes';
   
   -- Terminate specific application connections
   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE application_name = 'problematic_app';
   

4. Adjust connection limits:

   -- Increase max_connections (requires restart)
   ALTER SYSTEM SET max_connections = 200;
   
   -- Adjust superuser reserved connections if needed
   -- Default is 3, increase if multiple admin users
   ALTER SYSTEM SET superuser_reserved_connections = 5;
   
   -- Requires restart
   -- sudo systemctl restart postgresql
   

5. Implement connection pooling:

   # PgBouncer configuration
   # /etc/pgbouncer/pgbouncer.ini
   
   [databases]
   mydb = host=localhost port=5432 dbname=mydb
   
   [pgbouncer]
   listen_port = 6432
   listen_addr = *
   pool_mode = transaction
   max_client_conn = 1000
   default_pool_size = 20       # Actual DB connections
   reserve_pool_size = 5
   max_db_connections = 50      # Limit per database
   
   # This allows 1000 clients with only 20-50 DB connections
   

6. Set per-user connection limits:

   -- Limit connections for specific user
   ALTER ROLE app_user CONNECTION LIMIT 10;
   
   -- View current limits
   SELECT
       rolname,
       rolconnlimit
   FROM pg_roles
   WHERE rolconnlimit >= 0;
   
   -- Remove limit
   ALTER ROLE app_user CONNECTION LIMIT -1;
   

7. Set per-database connection limits:

   -- Limit total connections to database
   ALTER DATABASE mydb CONNECTION LIMIT 80;
   
   -- View database connection limits
   SELECT
       datname,
       datconnlimit
   FROM pg_database;
   
   -- Remove limit
   ALTER DATABASE mydb CONNECTION LIMIT -1;
   

8. Monitor connection usage by user:

   -- Create monitoring view
   CREATE VIEW user_connections AS
   SELECT
       usename,
       COUNT(*) as active_connections,
       r.rolconnlimit,
       COUNT(*) * 100.0 / NULLIF(r.rolconnlimit, -1) as percent_used
   FROM pg_stat_activity a
   JOIN pg_roles r ON a.usename = r.rolname
   GROUP BY usename, r.rolconnlimit
   ORDER BY active_connections DESC;
   
   SELECT * FROM user_connections;
   

9. Application-level connection management:

   # Python with connection pooling
   from psycopg2 import pool
   from contextlib import contextmanager
   
   # Create pool with limits
   connection_pool = pool.ThreadedConnectionPool(
       minconn=5,
       maxconn=20,  # Never exceed this
       host='localhost',
       database='mydb',
       user='app_user',  # Non-superuser
       password='password'
   )
   
   @contextmanager
   def get_db_connection():
       conn = connection_pool.getconn()
       try:
           yield conn
       finally:
           connection_pool.putconn(conn)
   
   # Usage
   with get_db_connection() as conn:
       cursor = conn.cursor()
       cursor.execute("SELECT * FROM users")
       result = cursor.fetchall()
   

10. Set up proactive monitoring and alerting:

    -- Query for monitoring system
    SELECT
        max_conn,
        used,
        res_for_super,
        used * 100.0 / (max_conn - res_for_super) AS percent_used
    FROM
        (SELECT COUNT(*) AS used FROM pg_stat_activity WHERE usename != 'postgres') t1,
        (SELECT setting::int AS res_for_super FROM pg_settings WHERE name='superuser_reserved_connections') t2,
        (SELECT setting::int AS max_conn FROM pg_settings WHERE name='max_connections') t3;
    
    -- Alert when percent_used > 80%
    

Additional Information

• Default superuser_reserved_connections is 3
• Superusers can always connect using reserved slots
• Reserved connections are for emergency administration
• Regular users cannot use reserved slots
• Connection pooling is essential for high-traffic applications
• Monitor connection usage proactively
• Set per-user and per-database limits to prevent runaway consumption

Frequently Asked Questions

Q: Why can't regular users use the reserved connections?
A: Reserved connections ensure administrators can always access the database for emergency management, even when regular connection slots are exhausted.

Q: How many superuser_reserved_connections should I set?
A: Default 3 is usually sufficient. Increase only if you have multiple admin users who might need simultaneous access during emergencies.

Q: Can I make a regular user temporarily a superuser?
A: Yes, but it's not recommended for security reasons. Better to increase max_connections or implement connection pooling.

Q: What's the total connections formula?
A: Total available for regular users = max_connections - superuser_reserved_connections. Default: 100 - 3 = 97 for regular users.

Q: Why not just increase max_connections to avoid this?
A: Each connection uses ~5-10MB RAM. Better to use connection pooling to multiplex many client connections over fewer database connections.

Q: Can I disable superuser_reserved_connections?
A: You can set it to 0, but this is dangerous as it prevents emergency admin access when connections are exhausted.

Q: How do connection poolers handle superuser connections?
A: Most poolers (PgBouncer) use regular user accounts. Superuser connections are typically direct for administrative tasks only.

Q: What if I see this error frequently?
A: Implement connection pooling, fix connection leaks in applications, set per-user connection limits, and monitor connection usage patterns.