pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDB connection issue suggestions
14+ messages / 6 participants
[nested] [flat]
* DB connection issue suggestions
@ 2022-05-10 16:59 Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:13 ` Re: DB connection issue suggestions MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-05-10 18:15 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
2022-05-10 18:54 ` Re: DB connection issue suggestions Ranier Vilela <ranier.vf@gmail.com>
2022-05-11 06:13 ` Re: DB connection issue suggestions Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 4 replies; 14+ messages in thread
From: Sudhir Guna @ 2022-05-10 16:59 UTC (permalink / raw)
To: pgsql-performance
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.
Regards,
Guna
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-10 18:13 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-05-11 01:46 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: MichaelDBA Vitale @ 2022-05-10 18:13 UTC (permalink / raw)
To: Sudhir Guna <sudhir.guna.sg@gmail.com>; pgsql-performance
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
Please show output of "show max_connections" to validate your assumptions.
</div>
<blockquote type="cite">
<div>
On 05/10/2022 12:59 PM Sudhir Guna <sudhir.guna.sg@gmail.com> wrote:
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div dir="ltr">
<div>
Dear All,
</div>
<div>
<br>
</div>
<div>
We have recently upgraded Postgresql 9.4 standalone server to Postgresql 11.2 with High Availability (2 servers : Master and Standby).
</div>
<div>
<br>
</div>
<div>
While trying to test using ETL applications and reports, we observe that the ETL jobs fails with below error,
</div>
<div>
<br>
</div>
<div>
2022/05/06 16:27:36 - Error occurred while trying to connect to the database
<br>2022/05/06 16:27:36 - Error connecting to database: (using class org.postgresql.Driver)
<br>2022/05/06 16:27:36 - FATAL: Sorry, too many clients already
</div>
<div>
<br>
</div>
<div>
We have increased the<code> max_connections = 1000 in postgresql.conf file.</code>
</div>
<div>
<code><br></code>
</div>
<div>
<code>It worked ok for a day and later we get the same error message.</code>
</div>
<div>
<code><br></code>
</div>
<div>
<code>Please help to advise on any additional settings required. The prior Postgresql 9.4 had the default <code>max_connections = 100</code> and the applications worked fine.</code>
</div>
<div>
<code><br></code>
</div>
<div>
<code>Regards,</code>
</div>
<div>
<code>Guna</code>
</div>
</div>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:13 ` Re: DB connection issue suggestions MichaelDBA Vitale <michaeldba@sqlexec.com>
@ 2022-05-11 01:46 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 11:45 ` Re: DB connection issue suggestions Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Sudhir Guna @ 2022-05-11 01:46 UTC (permalink / raw)
To: MichaelDBA Vitale <michaeldba@sqlexec.com>; +Cc: pgsql-performance
Hi MichaelDBA,
Thank you for reviewing.
I had validated the show max_connections and its 1000.
[image: image.png]
Regards,
Guna
On Wed, May 11, 2022 at 2:13 AM MichaelDBA Vitale <michaeldba@sqlexec.com>
wrote:
> Please show output of "show max_connections" to validate your assumptions.
>
> On 05/10/2022 12:59 PM Sudhir Guna <sudhir.guna.sg@gmail.com> 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.
>
> Regards,
> Guna
>
>
Attachments:
[image/png] image.png (9.4K, ../../CAGedWvNS9zo85UMweQC3ZE3LP=iRUHxY7BJKQFuCuFitAbmsbg@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:13 ` Re: DB connection issue suggestions MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-05-11 01:46 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-11 11:45 ` Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Ranier Vilela @ 2022-05-11 11:45 UTC (permalink / raw)
To: Sudhir Guna <sudhir.guna.sg@gmail.com>; +Cc: MichaelDBA Vitale <michaeldba@sqlexec.com>; pgsql-performance
Em qua., 11 de mai. de 2022 às 04:18, Sudhir Guna <sudhir.guna.sg@gmail.com>
escreveu:
> Hi MichaelDBA,
>
> Thank you for reviewing.
>
> I had validated the show max_connections and its 1000.
>
I think that you are wasting resources with this configuration.
Try enabling Connection Pool at Pentaho configuration.
And set the *Pool Size* (Maximum) to 100 for Pentaho and 100 for Postgres
(max_connections).
Under Advanced Options (DataSource Windows) enable Connection Pool.
Probably Pentaho is trying to use more connections than Postgres allows.
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-10 18:15 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-11 01:52 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Justin Pryzby @ 2022-05-10 18:15 UTC (permalink / raw)
To: Sudhir Guna <sudhir.guna.sg@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:15 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
@ 2022-05-11 01:52 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 19:09 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 14+ messages in thread
From: Sudhir Guna @ 2022-05-11 01:52 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
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@telsasoft.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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:15 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
2022-05-11 01:52 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-11 19:09 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-12 04:53 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Justin Pryzby @ 2022-05-11 19:09 UTC (permalink / raw)
To: Sudhir Guna <sudhir.guna.sg@gmail.com>; +Cc: pgsql-performance
On Wed, May 11, 2022 at 09:52:10AM +0800, Sudhir Guna wrote:
> 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.
That doesn't sound right. Are you sure you're connecting to the correct
instance ? Are there really only 5 postgres processes on the server, and fewer
than 5 connections to its network port or socket ?
You didn't provide any other info like what OS this is.
--
Justin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:15 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
2022-05-11 01:52 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 19:09 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
@ 2022-05-12 04:53 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-13 04:08 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 14+ messages in thread
From: Sudhir Guna @ 2022-05-12 04:53 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
Hi Justin,
Yes , I have checked pg_stat_activity from both the master node and the
standby node server and the total rows of the connection doesn't even
exceed 10.
Sorry the OS is Red Hat Enterprise Linux Server 7.5 (Maipo).
Does the streaming replication between the master and standby node have any
impact to this ?
[image: image.png]
Regards,
Guna
On Thu, May 12, 2022 at 3:09 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Wed, May 11, 2022 at 09:52:10AM +0800, Sudhir Guna wrote:
> > 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.
>
> That doesn't sound right. Are you sure you're connecting to the correct
> instance ? Are there really only 5 postgres processes on the server, and
> fewer
> than 5 connections to its network port or socket ?
>
> You didn't provide any other info like what OS this is.
>
> --
> Justin
>
Attachments:
[image/png] image.png (10.1K, ../../CAGedWvNyeEi+-5gkxnvv3DvrnAtZ5ScS9buc1=rY=7bQ6dDbnQ@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:15 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
2022-05-11 01:52 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 19:09 ` Re: DB connection issue suggestions Justin Pryzby <pryzby@telsasoft.com>
2022-05-12 04:53 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-13 04:08 ` Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 14+ messages in thread
From: Justin Pryzby @ 2022-05-13 04:08 UTC (permalink / raw)
To: Sudhir Guna <sudhir.guna.sg@gmail.com>; +Cc: pgsql-performance
If the problem occurs gradually (like leaking 20 connections per hour during
ETL), you can check pg_stat_activity every hour or so to try to observe the
problem before all the connection slots are used up, to collect diagnostic
information.
Alternately, leave a connection opened to the DB and wait until all connection
slots *are* used up, and then check pg_stat_activity. That will take longer,
and you'll have more information to weed through.
What messages are in the server's log ?
v11.2 is years old and hundreds of bugfixes behind. Since you ran into this
problem anyway, why not run 11.16, which was released today ?
How did you install postgres 11 ? From source or from packages ? Which
packages ? The semi-official PGDG RPM packages are available here:
https://yum.postgresql.org/
--
Justin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-10 18:54 ` Ranier Vilela <ranier.vf@gmail.com>
2022-05-11 01:44 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Ranier Vilela @ 2022-05-10 18:54 UTC (permalink / raw)
To: Sudhir Guna <sudhir.guna.sg@gmail.com>; +Cc: pgsql-performance
Em ter., 10 de mai. de 2022 às 14:49, Sudhir Guna <sudhir.guna.sg@gmail.com>
escreveu:
> 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.
>
I guess that ETL is pentaho?
You can try to use the latest JDBC driver (42.3.5) .
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:54 ` Re: DB connection issue suggestions Ranier Vilela <ranier.vf@gmail.com>
@ 2022-05-11 01:44 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 07:11 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Sudhir Guna @ 2022-05-11 01:44 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance
Hi Ranier,
Thank you for reviewing this.
Yes this is Pentaho and SSRS application.
We are currently using postgresql-42.2.4.jar currently.
Regards,
Guna
On Wed, May 11, 2022 at 2:55 AM Ranier Vilela <ranier.vf@gmail.com> wrote:
>
>
> Em ter., 10 de mai. de 2022 às 14:49, Sudhir Guna <
> sudhir.guna.sg@gmail.com> escreveu:
>
>> 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.
>>
> I guess that ETL is pentaho?
> You can try to use the latest JDBC driver (42.3.5) .
>
> regards,
> Ranier Vilela
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:54 ` Re: DB connection issue suggestions Ranier Vilela <ranier.vf@gmail.com>
2022-05-11 01:44 ` Re: DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-11 07:11 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Sudhir Guna @ 2022-05-11 07:11 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance
Hi Ranier,
We have tried to upgrade the postgresql- 42.3.5 .jarand unfortunately the
issue still persists.
Regards,
Guna
On Wed, May 11, 2022 at 9:44 AM Sudhir Guna <sudhir.guna.sg@gmail.com>
wrote:
> Hi Ranier,
>
> Thank you for reviewing this.
>
> Yes this is Pentaho and SSRS application.
>
> We are currently using postgresql-42.2.4.jar currently.
>
> Regards,
> Guna
>
> On Wed, May 11, 2022 at 2:55 AM Ranier Vilela <ranier.vf@gmail.com> wrote:
>
>>
>>
>> Em ter., 10 de mai. de 2022 às 14:49, Sudhir Guna <
>> sudhir.guna.sg@gmail.com> escreveu:
>>
>>> 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.
>>>
>> I guess that ETL is pentaho?
>> You can try to use the latest JDBC driver (42.3.5) .
>>
>> regards,
>> Ranier Vilela
>>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
@ 2022-05-11 06:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-05-11 06:30 ` Re: DB connection issue suggestions Anbazhagan M <anbazhagan.m@instaclustr.com>
3 siblings, 1 reply; 14+ messages in thread
From: Laurenz Albe @ 2022-05-11 06:13 UTC (permalink / raw)
To: Sudhir Guna <sudhir.guna.sg@gmail.com>; pgsql-performance
On Wed, 2022-05-11 at 00:59 +0800, Sudhir Guna wrote:
> 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 = 100and the applications worked fine.
Some application that uses the database has a connection leak: it opens new connections
without closing old ones. Examine "pg_stat_activity" to find out which application is
at fault, and then go and fix that application.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: DB connection issue suggestions
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 06:13 ` Re: DB connection issue suggestions Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-05-11 06:30 ` Anbazhagan M <anbazhagan.m@instaclustr.com>
0 siblings, 0 replies; 14+ messages in thread
From: Anbazhagan M @ 2022-05-11 06:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Sudhir Guna <sudhir.guna.sg@gmail.com>; pgsql-performance
Dear team,
Can you confirm whether, post upgrade activity, all the post-upgrade steps including stats update on all the relations is complete. Upgrade doesn’t carry over the stats to the new upgraded cluster.
Regards,
Anbazhagan M
> On 11-May-2022, at 11:43 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Wed, 2022-05-11 at 00:59 +0800, Sudhir Guna wrote:
>> 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 = 100and the applications worked fine.
>
> Some application that uses the database has a connection leak: it opens new connections
> without closing old ones. Examine "pg_stat_activity" to find out which application is
> at fault, and then go and fix that application.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2022-05-13 04:08 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-10 16:59 DB connection issue suggestions Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-10 18:13 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-05-11 01:46 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 11:45 ` Ranier Vilela <ranier.vf@gmail.com>
2022-05-10 18:15 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-11 01:52 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 19:09 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-12 04:53 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-13 04:08 ` Justin Pryzby <pryzby@telsasoft.com>
2022-05-10 18:54 ` Ranier Vilela <ranier.vf@gmail.com>
2022-05-11 01:44 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 07:11 ` Sudhir Guna <sudhir.guna.sg@gmail.com>
2022-05-11 06:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-05-11 06:30 ` Anbazhagan M <anbazhagan.m@instaclustr.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox