postgresql 如何增加postgres中的最大连接数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30778015/
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
How to increase the max connections in postgres?
提问by Jet
I am using Postgres DB for my product. While doing the batch insert using slick 3, I am getting an error message:
我正在为我的产品使用 Postgres DB。在使用 slick 3 进行批量插入时，我收到一条错误消息：
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already.
org.postgresql.util.PSQLException：致命：抱歉，已经有太多客户端了。
My batch insert operation will be more than thousands of records. Max connection for my postgres is 100.
我的批量插入操作将超过数千条记录。我的 postgres 的最大连接数是 100。
How to increase the max connections?
如何增加最大连接数？
回答by Ankit
Just increasing max_connectionsis bad idea. You need to increase shared_buffersand kernel.shmmaxas well.
只是增加 max_connections是坏主意。你需要增加shared_buffers，kernel.shmmax以及。
Considerations
注意事项
max_connectionsdetermines the maximum number of concurrent connections to the database server. The default is typically 100 connections.
max_connections确定与数据库服务器的最大并发连接数。默认值通常为 100 个连接。
Before increasing your connection count you might need to scale up your deployment. But before that, you should consider whether you really need an increased connection limit.
在增加连接数之前，您可能需要扩大部署。但在此之前，您应该考虑是否真的需要增加连接限制。
Each PostgreSQL connection consumes RAM for managing the connection or the client using it. The more connections you have, the more RAM you will be using that could instead be used to run the database.
每个 PostgreSQL 连接都消耗 RAM 来管理连接或使用它的客户端。您拥有的连接越多，您将使用的 RAM 就越多，可用于运行数据库。
A well-written app typically doesn't need a large number of connections. If you have an app that does need a large number of connections then consider using a tool such as pg_bouncerwhich can pool connections for you. As each connection consumes RAM, you should be looking to minimize their use.
编写良好的应用程序通常不需要大量连接。如果您的应用确实需要大量连接，请考虑使用诸如pg_bouncer 之类的工具，它可以为您汇集连接。由于每个连接都消耗 RAM，您应该尽量减少它们的使用。
How to increase max connections
如何增加最大连接数
1. Increase max_connectionand shared_buffers
1.增加max_connection和shared_buffers
in /var/lib/pgsql/{version_number}/data/postgresql.conf
在 /var/lib/pgsql/{version_number}/data/postgresql.conf
change
改变
max_connections = 100
shared_buffers = 24MB
to
到
max_connections = 300
shared_buffers = 80MB
The shared_buffersconfiguration parameter determines how much memoryis dedicatedto PostgreSQL to use for caching data.
所述shared_buffers配置参数确定多少存储器是专用到PostgreSQL到使用用于高速缓存数据。
- If you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system.
- it's unlikely you'll find using more than 40% of RAM to work better than a smaller amount (like 25%)
- Be aware that if your system or PostgreSQL build is 32-bit, it might not be practical to set shared_buffers above 2 ~ 2.5GB.
- Note that on Windows, large values for shared_buffers aren't as effective, and you may find better results keeping it relatively low and using the OS cache more instead. On Windows the useful range is 64MB to 512MB.
- 如果您的系统具有 1GB 或更多 RAM，则 shared_buffers 的合理起始值是系统内存的 1/4。
- 您不太可能发现使用超过 40% 的 RAM 比使用较少的 RAM（例如 25%）效果更好
- 请注意，如果您的系统或 PostgreSQL 版本是 32 位，则将 shared_buffers 设置为 2 ~ 2.5GB 以上可能不切实际。
- 请注意，在 Windows 上，shared_buffers 的大值不是那么有效，您可能会发现更好的结果，保持它相对较低并更多地使用操作系统缓存。在 Windows 上，有用的范围是 64MB 到 512MB。
2. Change kernel.shmmax
2.更改kernel.shmmax
You would need to increase kernel max segment size to be slightly largerthan the shared_buffers.
您将需要增加内核的最大片段大小要稍大比shared_buffers。
In file /etc/sysctl.confset the parameter as shown below. It will take effect when postgresqlreboots (The following line makes the kernel max to 96Mb)
在文件中/etc/sysctl.conf设置如下所示的参数。postgresql重新启动时生效（以下行使内核最大为96Mb）
kernel.shmmax=100663296
References
参考
回答by Jinxer Albatross
Adding to Winnie's great answer,
加上温妮的精彩回答，
If anyone is not able to find the postgresql.conf file location in your setup, you can always ask the postgres itself.
如果有人无法在您的设置中找到 postgresql.conf 文件位置，您可以随时询问 postgres 本身。
SHOW config_file;
For me changing the max_connections alone made the trick.
对我来说，单独改变 max_connections 就成功了。
回答by Archana
change max_connections variable in postgresql.conf file located in /var/lib/pgsql/data or /usr/local/pgsql/data/
更改位于 /var/lib/pgsql/data 或 /usr/local/pgsql/data/ 的 postgresql.conf 文件中的 max_connections 变量
