Ruby-on-rails Heroku “psql: FATAL：剩余的连接槽是为非复制超级用户连接保留的”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13640871/
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
Heroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”
提问by Derek Hill
I got the above error message running Heroku Postgres Basic (as per this question) and have been trying to diagnose the problem.
我在运行 Heroku Postgres Basic 时收到了上述错误消息（根据这个问题），并且一直在尝试诊断问题。
One of the suggestions is to use connection pooling but it seems Rails has this built in. Another suggestion is that the app is configured improperly and opens too many connections.
建议之一是使用连接池，但似乎Rails 已将其内置于. 另一个建议是该应用程序配置不当并打开了太多连接。
My app manages all it's connections through Active Record, and I had one direct connection to the database from Navicat (or at least I thought I had).
我的应用程序通过 Active Record 管理它的所有连接，并且我有一个从 Navicat 到数据库的直接连接（或者至少我认为我有）。
How would I debug this?
我将如何调试这个？
RESOLUTION
解析度
Turns out it was an Heroku issue. From Heroku support:
原来这是一个 Heroku 问题。来自 Heroku 的支持：
We've detected an issue on the server running your Basic database. While we pinpoint this and address it, we would recommend you provision a new Basic database and migrate over with PGBackups as detailed here: https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups. That should put your database on a new server. I apologize for this disruption – we're working to fix this issue and prevent it from occurring in the future.
我们在运行您的基本数据库的服务器上检测到一个问题。虽然我们查明并解决了这个问题，但我们建议您配置一个新的 Basic 数据库并使用 PGBackups 迁移，详情如下： https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups。这应该将您的数据库放在新服务器上。对于这次中断，我深表歉意——我们正在努力解决这个问题并防止它在未来发生。
采纳答案by mvp
You might be able to find why you have so many connections by inspecting view pg_stat_activity:
通过检查视图pg_stat_activity ，您可能能够找到为什么有这么多连接：
SELECT * FROM pg_stat_activity
Most likely, you have some stray loop that opens new connection(s) without closing it.
最有可能的是，您有一些杂散循环可以打开新连接而不关闭它。
回答by jpadvo
This has happened a few times on my app -- somehow there is a connection leak, then all of a sudden the database is getting 10 times as many connections as it should. If it is the case that you are getting swamped by an error like this, not traffic, try running this:
这在我的应用程序上发生过几次——不知何故存在连接泄漏，然后突然间数据库获得了应有数量的 10 倍连接。如果您被这样的错误而不是交通淹没，请尝试运行以下命令：
heroku pg:killall
That will terminate all connections to the database. If it is dangerous for your situation to possibly cut off queries be careful. I just have a rails app, and if it goes down, losing a couple queries is not a big deal, because the browser requests will have looooooong since timed out anyway.
这将终止与数据库的所有连接。如果您的情况可能中断查询是危险的，请小心。我只有一个 rails 应用程序，如果它出现故障，丢失几个查询并不是什么大问题，因为无论如何浏览器请求都会有 looooooong 超时。
回答by Aur Saraf
To save you the support call, here's the response I got from Heroku Support for a similar issue:
为了节省您的支持电话，以下是我从 Heroku 支持部门收到的针对类似问题的回复：
Hello,
One of the limitations of the hobby tier databases is unannounced maintenance. Many hobby databases run on a single shared server, and we will occasionally need to restart that server for hardware maintenance purposes, or migrate databases to another server for load balancing. When that happens, you'll see an error in your logs or have problems connecting. If the server is restarting, it might take 15 minutes or more for the database to come back online.
Most apps that maintain a connection pool (like ActiveRecord in Rails) can just open a new connection to the database. However, in some cases an app won't be able to reconnect. If that happens, you can heroku restart your app to bring it back online.
This is one of the reasons we recommend against running hobby databases for critical production applications. Standard and Premium databases include notifications for downtime events, and are much more performant and stable in general. You can use pg:copy to migrate to a standard or premium plan.
If this continues, you can try provisioning a new database (on a different server) with heroku addons:add, then use pg:copy to move the data. Keep in mind that hobby tier rules apply to the $9 basic plan as well as the free database.
Thanks, Bradley
你好，
爱好层数据库的限制之一是不通知的维护。许多爱好数据库运行在单个共享服务器上，我们偶尔需要重新启动该服务器以进行硬件维护，或将数据库迁移到另一台服务器以进行负载平衡。发生这种情况时，您会在日志中看到错误或连接出现问题。如果服务器正在重新启动，则数据库可能需要 15 分钟或更长时间才能重新联机。
大多数维护连接池的应用程序（如 Rails 中的 ActiveRecord）只能打开一个到数据库的新连接。但是，在某些情况下，应用程序将无法重新连接。如果发生这种情况，您可以 heroku 重新启动您的应用程序以使其重新上线。
这是我们建议不要为关键生产应用程序运行爱好数据库的原因之一。标准和高级数据库包括停机事件通知，并且通常性能更高且更稳定。您可以使用 pg:copy 迁移到标准或高级计划。
如果这种情况继续存在，您可以尝试使用 heroku addons:add 配置一个新数据库（在不同的服务器上），然后使用 pg:copy 移动数据。请记住，爱好等级规则适用于 9 美元的基本计划以及免费数据库。
谢谢，布拉德利
