1

I am using postgresql version 9.2 which does not support the configuration parameter idle_in_transaction_session_timeout. I have a perl script which is giving below error and I can see there are open connection processes which are in ideal state for more then few days. I want to close this open connections.

failed: FATAL: sorry, too many clients already at GA/MiddleTier/Pg.pm line 72

I have set max_connections = 200 in my postgresql.conf file. Please help how this can be achieved.

Abhee
  • 340
  • 3
  • 16
  • 2
    You should be using some form of connection pooling. If not in your Perl code, then you could use [PgBouncer](https://pgbouncer.github.io/) or [Pgpool-II](http://www.pgpool.net/) – Colin 't Hart Aug 24 '18 at 14:46
  • Possible duplicate of [Is there a timeout for idle PostgreSQL connections?](https://stackoverflow.com/questions/13236160/is-there-a-timeout-for-idle-postgresql-connections) – Carlos Lima Aug 31 '18 at 19:54
  • If you are looking for help on how to handle the connection on the perl side, I would suggest that you provide a sample of your current code and how you expect it to behave. – Carlos Lima Aug 31 '18 at 20:03
  • This is more like a environment issue as I have upgraded to RHEL 7.5 and PostgreSQL 9.2. – Abhee Sep 11 '18 at 13:29

1 Answers1

0

This SQL will kill active connections:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'database_name'
AND pid <> pg_backend_pid();
Matt S
  • 14,976
  • 6
  • 57
  • 76