Re: DB connection issue suggestions
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 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 >
Re: DB connection issue suggestions
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
Re: DB connection issue suggestions
Em qua., 11 de mai. de 2022 às 04:18, Sudhir Guna 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
Re: DB connection issue suggestions
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 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 wrote: > >> >> >> Em ter., 10 de mai. de 2022 às 14:49, Sudhir Guna < >> sudhir.guna...@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 >> >
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 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 >
Re: DB connection issue suggestions
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 wrote: > Please show output of "show max_connections" to validate your assumptions. > > On 05/10/2022 12:59 PM 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. > > Regards, > Guna > >
Re: DB connection issue suggestions
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 wrote: > > > Em ter., 10 de mai. de 2022 às 14:49, Sudhir Guna < > sudhir.guna...@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 >
