postgresql Heroku“psql：致命：剩余的连接槽是为非复制超级用户连接保留的”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11847144/
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 nathancahill
I'm developing an app on Heroku with a Postgresql backend. Periodically, I get this error message when trying to access the database, both from the CLI and from loading a page on the server:
我正在使用 Postgresql 后端在 Heroku 上开发应用程序。在尝试访问数据库时，我会定期从 CLI 和在服务器上加载页面时收到此错误消息：
psql: FATAL: remaining connection slots are reserved for non-replication superuser connections
Anyone seen this before or please help point me in the right direction?
任何人以前见过这个，或者请帮助我指出正确的方向？
回答by kgrittn
You either need to increase the max_connectionsconfiguration setting or (probably better) use connection poolingto route a large number of user requests through a smaller connection pool.
您要么需要增加max_connections配置设置，要么（可能更好）使用连接池通过较小的连接池路由大量用户请求。
https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
回答by Sanyifej?
This exception happened when I forgot to close the connections
当我忘记关闭连接时发生此异常
回答by Aur Saraf
请参阅Heroku “psql: FATAL: 剩余连接槽为非复制超级用户连接保留”：
Heroku sometimes has a problem with database load balancing.
Heroku 有时会遇到数据库负载平衡问题。
André Laszlo, markshizand me all reported dealing with that in comments on the question.
André Laszlo，markshiz我都在对该问题的评论中报告了处理这个问题。
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 美元的基本计划以及免费数据库。
谢谢，布拉德利
回答by markshiz
I actually tried to implement connection pooling on the django end using:
我实际上尝试使用以下方法在 django 端实现连接池：
https://github.com/gmcguire/django-db-pool
https://github.com/gmcguire/django-db-pool
but I still received this error, despite lowering the number of connections available to below the standard development DB quota of 20 open connections.
但我仍然收到此错误，尽管将可用连接数降低到低于 20 个打开连接的标准开发数据库配额。
There is an article here about how to move your postgresql database to the free/cheap tier of Amazon RDS. This would allow you to set max_connectionshigher. This will also allow you to pool connections at the database level using PGBouncer.
这里有一篇关于如何将您的 postgresql 数据库移动到 Amazon RDS 的免费/廉价层的文章。这将允许您设置max_connections更高。这也将允许您使用 PGBouncer 在数据库级别建立连接池。
https://www.lewagon.com/blog/how-to-migrate-heroku-postgres-database-to-amazon-rds
https://www.lewagon.com/blog/how-to-migrate-heroku-postgres-database-to-amazon-rds
UPDATE:
更新：
Heroku responded to my open ticket and stated that my database was improperly load balanced in their network. They said that improvements to their system should prevent similar problems in the future. Nonetheless, support manually relocated my database and performance is noticeably improved.
Heroku 回应了我的开放请求，并表示我的数据库在他们的网络中负载平衡不当。他们说对他们的系统的改进应该可以防止将来出现类似的问题。尽管如此，支持手动重新定位我的数据库并且性能显着提高。
回答by Naveen Agarwal
Restart your postgres database by following command :
通过以下命令重新启动您的 postgres 数据库：
postgres -D /usr/local/var/postgres
