SQL AWS RDS PostgreSQL 错误“剩余连接槽为非复制超级用户连接保留”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38555679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
AWS RDS PostgreSQL error "remaining connection slots are reserved for non-replication superuser connections"
提问by michael
In the dashboard I see there are currently 22 open connections to the DB instance, blocking new connections with the error:
在仪表板中，我看到当前有 22 个与数据库实例的打开连接，阻止新连接并显示错误：
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:
我正在从 EC2 实例上运行的 Web 服务 API 访问数据库，并始终保持以下最佳实践：
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();
Can I do something else in the code?
Should I do something else in the DB management?
Is there a way to periodically close connections?
我可以在代码中做其他事情吗？
我应该在数据库管理中做其他事情吗？
有没有办法定期关闭连接？
采纳答案by error2007s
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,
从 RDS 控制台 > 参数组 > 编辑参数，
You can change the value of the max_connections parameter to a custom value.
您可以将 max_connections 参数的值更改为自定义值。
For closing the connections periodically you can setup a cron job some thing like this.
为了定期关闭连接，您可以设置一个像这样的 cron 作业。
select pg_terminate_backend(procpid)
from pg_stat_activity
where usename = 'yourusername'
and current_query = '<IDLE>'
and query_start < current_timestamp - interval '5 minutes';
回答by kosiara - Bartosz Kosarzycki
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.
我正在使用 Amazon RDS、SCALA、Postgresql 和 Slick。首先 - RDS 中的可用连接数取决于可用RAM的数量- 即 RDS 实例的大小。最好不要更改默认的 conn number。
You can check the max connection number by executing the following SQL statement on your RDS DB instance:
您可以通过在 RDS 数据库实例上执行以下 SQL 语句来检查最大连接数：
show max_connections;
Check your SPRING configuration to see how many threadsyou're spawning:
检查您的 SPRING 配置以查看您生成的线程数：
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 madeupon SRING BOOT initializationso 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+.
所有连接都是在 SRING BOOT初始化时建立的，因此请注意不要超过 RDS 限制。这包括连接到数据库的其他服务。在这种情况下，连接数将是 90+。
The current limit for db.t2.small is 198(4GB of RAM)
db.t2.small 的当前限制为198（4GB RAM）
回答by Cédric Nilly
you can change the max connections in the Parameters Groupfor your RDS instance. Try to increase it.
Or you can try to upgrade your instance, as the max connexions is set to {DBInstanceClassMemory/31457280}
您可以更改Parameters GroupRDS 实例中的最大连接数。尝试增加它。或者您可以尝试升级您的实例，因为最大连接数设置为{DBInstanceClassMemory/31457280}
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html
