AWS RDS PostgreSQL error "remaining connection slots are reserved for non-replication superuser connections"

Question

In the dashboard I see there are currently 22 open connections to the DB instance, blocking new connections with the error:

remaining connection slots are reserved for non-replication superuser connections.

I'm accessing the DB from web service API running on EC2 instance and always keep the best practise of:

Connection connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD); Class.forName(DB_CLASS); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(SQL_Query_String); ... resultSet.close(); statement.close(); connection.close(); 

1. Can I do something else in the code?

2. Should I do something else in the DB management?

3. Is there a way to periodically close connections?

Answer 1

Amazon has to set the number of connections based on each model's right to demand a certain amount of memory and connections

MODEL      max_connections innodb_buffer_pool_size ---------  --------------- ----------------------- t1.micro   34                326107136 (  311M) m1-small   125              1179648000 ( 1125M,  1.097G) m1-large   623              5882511360 ( 5610M,  5.479G) m1-xlarge  1263            11922309120 (11370M, 11.103G) m2-xlarge  1441            13605273600 (12975M, 12.671G) m2-2xlarge 2900            27367833600 (26100M, 25.488G) m2-4xlarge 5816            54892953600 (52350M, 51.123G) 

But if you want you can change the max connection size to custom value by

From RDS Console > Parameter Groups > Edit Parameters,

You can change the value of the max_connections parameter to a custom value.

For closing the connections periodically you can setup a cron job some thing like this.

select pg_terminate_backend(procpid) from pg_stat_activity where usename = 'yourusername'  and current_query = '<IDLE>'  and query_start < current_timestamp - interval '5 minutes'; 

Answer 2

I'm using Amazon RDS, SCALA, Postgresql & Slick. First of all - number of available connections in RDS depends on the amount of available RAM - i.e. size of the RDS instance. It's best not to change the default conn number.

You can check the max connection number by executing the following SQL statement on your RDS DB instance:

show max_connections;  

Check your SPRING configuration to see how many threads you're spawning:

database {   dataSourceClass = org.postgresql.ds.PGSimpleDataSource   properties = {     url = "jdbc:postgresql://test.cb1111.us-east-2.rds.amazonaws.com:6666/dbtest"     user = "youruser"     password = "yourpass"   }   numThreads = 90 } 

All of the connections ARE made upon SRING BOOT initialization so beware not to cross the RDS limit. That includes other services that connect to the DB. In this case the number of connections will be 90+.

The current limit for db.t2.small is 198 (4GB of RAM)