Skip to content

Instantly share code, notes, and snippets.

@tdantas
Last active Mar 10, 2017
Embed
What would you like to do?
Too Many Connections Postgresql ( HomeBrew Installation)

HOWTO avoid 'too many connections'


Ask postgresql where is the configuration file

$ psql postgres
 psql (9.2.2)
   Type "help" for help.

   postgres=# show config_file;

                              config_file
   -------------------------------------------------------------------------
	/Users/tdantas/Library/Application Support/Postgres/var/postgresql.conf
   (1 row)
 

Open the postgresql configuration file:

 vi "/Users/tdantas/Library/Application Support/Postgres/var/postgresql.conf"
 

Find the max_connection option

      #port = 5432                        # (change requires restart)
->>>  max_connections = 20                # (change requires restart)
      # Note:  Increasing max_connections costs ~400 bytes of shared memory per
      # connection slot, plus lock space (see max_locks_per_transaction).
      #superuser_reserved_connections = 3 # (change requires restart)
      #unix_socket_directory = ''         # (change requires restart)
      #unix_socket_group = ''             # (change requires restart)
      #unix_socket_permissions = 0777     # begin with 0 to use octal notation

Increase the value

      #port = 5432                        # (change requires restart)
->>>  max_connections = 100                # (change requires restart)
      # Note:  Increasing max_connections costs ~400 bytes of shared memory per
      # connection slot, plus lock space (see max_locks_per_transaction).
      #superuser_reserved_connections = 3 # (change requires restart)
      #unix_socket_directory = ''         # (change requires restart)
      #unix_socket_group = ''             # (change requires restart)
      #unix_socket_permissions = 0777     # begin with 0 to use octal notation

After that, find the shared_buffer option

	#------------------------------------------------------------------------------
 	# RESOURCE USAGE (except WAL)
 	#------------------------------------------------------------------------------

 	# - Memory -

>>> shared_buffers = 1600kB         # min 128kB

Increase that value too

	#------------------------------------------------------------------------------
 	# RESOURCE USAGE (except WAL)
 	#------------------------------------------------------------------------------

 	# - Memory -

>>> shared_buffers = 16MB         # min 128kB

Postgresql will use shared memory and the default kernel value (macosx) is too low

	$ sysctl kern.sysv.shmmax
	kern.sysv.shmmax: 4194304

we need to increase this parameter

sudo sysctl -w kern.sysv.shmmax=1073741824
sudo sysctl -w kern.sysv.shmall=1073741824

To persist this configuration edit/create the /etc/sysctl.conf
Append this lines

kern.sysv.shmmax=1073741824
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.sysv.shmall=1073741824

Restart your postgresql

Homebrew

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Postgresql.APP

Just close and open the app

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment