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

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