Re: DB connection issue suggestions

Hi Justin,

Thank you for reviewing.

I have tried to run the below query and could see only less than 5
connections active when I get this error. The total rows I see is only 10
including idle and active sessions for this output.

select pid as process_id,
usename as username,
datname as database_name,
client_addr as client_address,
application_name,
backend_start,
state,
state_change
from pg_stat_activity;

Regards,
Guna

On Wed, May 11, 2022 at 2:15 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Wed, May 11, 2022 at 12:59:01AM +0800, Sudhir Guna wrote:
> > Dear All,
> >
> > We have recently upgraded Postgresql 9.4 standalone server to Postgresql
> > 11.2 with High Availability (2 servers : Master and Standby).
> >
> > While trying to test using ETL applications and reports, we observe that
> > the ETL jobs fails with below error,
> >
> > 2022/05/06 16:27:36 - Error occurred while trying to connect to the
> database
> > 2022/05/06 16:27:36 - Error connecting to database: (using class
> > org.postgresql.Driver)
> > 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already
> >
> > We have increased the max_connections = 1000 in postgresql.conf file.
> >
> > It worked ok for a day and later we get the same error message.
> >
> > Please help to advise on any additional settings required. The prior
> > Postgresql 9.4 had the default max_connections = 100 and the applications
> > worked fine.
>
> It sounds like at least one thing is still running, perhaps running very
> slowly.
>
> You should monitor the number of connections to figure out what.
>
> If you expect to be able to run with only 100 connections, then when
> connections>200, there's already over 100 connections which shouldn't
> still be
> there.
>
> You could query pg_stat_activity to determine what they're doing - trying
> to
> run a slow query ? Are all/most of them stuck doing the same thing ?
>
> You should try to provide the information here for the slow query, and for
> the
> rest of your environment.
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> --
> Justin
>