How to increase the max connections in postgres

Question

I am using Postgres DB for my product  While doing the batch insert using slick 3  I am getting an error message       org postgresql util PSQLException  FATAL  sorry  too many clients already     My batch insert operation will be more than thousands of records  Max connection for my postgres is 100   How to increase the max connections

User · Answer

change max connections  variable in postgresql conf file located in   var lib pgsql data or  usr local pgsql data

User · Answer

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   SHOW config file    For me changing the max connections alone made the trick

User · Answer

Just increasing  max connections is bad idea  You need to increase shared buffers and kernel shmmax as well   Considerations max connections determines the maximum number of concurrent connections to the database server  The default is typically 100 connections  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  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 bouncer which can pool connections for you  As each connection consumes RAM  you should be looking to minimize their use   How to increase max connections 1  Increase max connection and shared buffers in  var lib pgsql  version number  data postgresql conf change max connections   100 shared buffers   24MB  to max connections   300 shared buffers   80MB  The shared buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data   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   2  Change kernel shmmax You would need to increase kernel max segment size to be slightly larger than the shared buffers  In file  etc sysctl conf set the parameter as shown below  It will take effect when postgresql reboots  The following line makes the kernel max to 96Mb  kernel shmmax 100663296   References Postgres Max Connections And Shared Buffers Tuning Your PostgreSQL Server

[postgresql] How to increase the max connections in postgres?

Examples related to postgresql