Re: postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 06/27/2017 01:10 PM, DrakoRod wrote:
>
>> Hi folks.
>>
>> Today I had a problem with production's database PostgreSQL version
>> 9.4.4.9.
>> The server have max_connections set to 200, but today I reviewed
>> pg_stat_activity and saw 199 active connections, obviously the server
>> rejected any new connection and the production stopped.
>>
>> I saw another posts with a similar problems, but this was because the
>> pg_xlog was full or disk does'nt write, but the directory and disk had no
>> problems.
>>
>> I just canceled some SELECTs querys and the server returned to normality.
>> Now a monitoring activity of server and I can see some backends like this:
>>
>> postgres 9737 23340 2 14:55 ? 00:00:15 postgres: dbname user
>> 8.8.8.8[37082] idle in transaction
>> postgres 9741 23340 9 14:55 ? 00:00:47 postgres: dbname user
>> 8.8.8.8[54286] idle in transaction
>>
>> Any suggestions?
>>
>
> https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> html#PG-STAT-ACTIVITY-VIEW
>
> SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
>
> To get more detail on what is holding these transactions open.
>
>
>>
>>
>> -----
>> Dame un poco de fe, eso me bastará.
>> Rozvo Ware Solutions
>> --
>> View this message in context: http://www.postgresql-archive.
>> org/postgres-dbname-dbuser-9-9-9-9-2222-PARSE-waiting-tp5968923.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

*His problem is NOT 'idle in transaction' per se. It is all connections are
used up.*
*Hence the need for pg_bouncer for connection pooling.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.