I received the error "connection limit 500 exceeded for non-bootstrap users." How can I manage my connections in Amazon Redshift?

Amazon Redshift limits the number of connections per cluster and per database user. For more information, see Limits in Amazon Redshift. When the limit is reached, subsequent connection attempts fail with the error "connection limit 500 exceeded for non-bootstrap users." Connection limits can't be increased. To prevent connection limit errors, restrict the number of connections per user and per database. To reduce resource usage and simplify troubleshooting user sessions, keep as few idle connections as possible.

You can use connection pool software to limit the number of connections to your Amazon Redshift cluster and validate your connections. We recommend using a lightweight health check query command, such as SELECT 1, to reduce resource consumption.

Review and manage Amazon Redshift connections

Use CREATE DATABASE and ALTER DATABASE statements to manage connection limits for specific databases. Use CREATE USER and ALTER USER to manage connection limits for specific users.

Each session corresponds to a connection. Use the STV_SESSIONS table to view information about the active user sessions for Amazon Redshift, or check the total number of the connections. Run the following query to find out if other users are logged in to Amazon Redshift:

select count(*) from stv_sessions

Run the following query and check the starttime column to identify long-running sessions on clusters that do not use enhanced VPC routing:

select * from stv_sessions;

Join the STV_SESSIONS table with the STL_CONNECTION_LOG table to see the IP addresses associated with the connection. For example, the following query checks the connection count from different remote IP addresses:

select c.remotehost,count(*) from stv_sessions s left join stl_connection_log c on s.process=c.pid where event='authenticated' and s.user_name<>'rdsdb' group by 1 order by 2 desc;

Note: If the same process ID (PID) is used for multiple sessions, this query might also produce records for the previous sessions that used the PID.

Clean up Amazon Redshift connections

Use PG_TERMINATE_BACKEND to terminate a process and the corresponding session. Use STL_CONNECTION_LOG to log authentication attempts, connections, and disconnections.

Note: To manage disk space, all STL log tables, including STL_CONNECTION_LOG, retain only two to five days of log history. To keep your connection logs longer, use Database Audit Logging.

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2017-12-21

Updated: 2019-03-24