1

I have a spring boot, hibernate using java app. I deploy it on a jetty webserver with multiple instances. if I have too (greater than 10) many instances I get 

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: remaining connection slots are reserved for non-replication superuser connections

Many of the connections (10x instance) show up idle

ps:

postgres  9104  0.0  0.0 248968  4392 ?        Ss   08:07   0:00 postgres: user my_db 127.0.0.1(60095) idle

Hikari trace log for an instance:

2017-02-21 10:59:47.578  DEBUG 7401 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)

setting

 hikari.leakDetectionThreshold: 30000

Doesnt log anything interesing. I think this looks interesting HikariCP - connection is not available

Any ideas how i could debug this? Also I am on java 7, so hikari 2.4.7

Community
  • 1
  • 1

1 Answers1

1

I think you must increase the max_connections parameter in your PostgreSQL configuration.

See https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

You cannot allow the pool to keep more connections than your PostgreSQL installation allows.

Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
  • 2
    Or alternatively lower the amount of max connections in your pool. – Kayaman Feb 21 '17 at 10:31
  • 1
    If you have a connection leak, your connection pool should refuse to create more connections. The error message in your question indicates that it's the database that refuses to do that, so apparently your connection pool allows more connections than your database. – Erich Kitzmueller Feb 21 '17 at 10:58
  • @ammoQ Apparently he has several instances of the application, so `10 x instances > postgres_max_connections`. I'd probably look into lowering the pool size (or at least keeping less than 10 instances idle at all times) before increasing the max connections to postgres, unless he has a very powerful server for the database to run on. – Kayaman Feb 21 '17 at 12:18
  • @Kayaman: Both ways are equally possible. It depends on how many connections the application really needs to run smoothly. If there is a connection leak, no number of possible connections will be sufficient anyway. – Erich Kitzmueller Feb 21 '17 at 12:37
  • He doesn't seem to have a connection leak though, since he gets the error when deploying over 10 instances (over 100 connections being pooled). I'd set the minimum pool size to a lower value (such as 5), and the max pool size to something possibly larger, depending on usage patterns. – Kayaman Feb 21 '17 at 12:55