Re: connection dropped from the backend server - Mailing list pgsql-general

On 03/27/2018 07:21 PM, armand pirvu wrote:
> 
>> On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl@sss.pgh.pa.us 
>> <mailto:tgl@sss.pgh.pa.us>> wrote:
>>
>> Adrian Klaver <adrian.klaver@aklaver.com 
>> <mailto:adrian.klaver@aklaver.com>> writes:
>>> On 03/27/2018 04:07 PM, armand pirvu wrote:
>>>> As long as the connection stays up yes data gets fine across
>>>> In pg_stat_activity I see the node ip address where tail -f piped 
>>>> into psql happens
>>
>>> So what does the rest of that record show? In particular for:
>>
>> I wonder how often data gets put into the pipe.  If it's "not very often",
>> maybe the connection from psql to the server is timing out due to
>> inactivity?  This would be the fault of a firewall or something in
>> between.  You could probably fix it by enabling (more aggressive) TCP
>> keepalive settings.
>>
>> regards, tom lane
> 
> Well there is no flow pattern, The flow can be inexistent for days , 
> even weeks and then it can get super busy
> 
> The data flows as expected well untill the connection gets dropped. 
> Bolded from pg_stat_activity (a test I just did)
> 
> birstdb=# select datname, pid, client_addr, client_port, backend_start, 
> query_start, state from pg_stat_Activity;
>   datname |  pid  |  client_addr  | client_port |         backend_start 
>          |          query_start          | state
>
---------+-------+---------------+-------------+-------------------------------+-------------------------------+--------
>   birstdb | 10046 |               |          -1 | 2018-03-27 
> 20:40:11.721804-05 | 2018-03-27 20:47:27.118434-05 | active
> * birstdb | 10082 | 192.168.1.187 |       55859 | 2018-03-27 
> 20:43:55.301216-05 | 2018-03-27 20:46:07.190064-05 | idle*
> 
> 
> ps -fu armandp
>       UID   PID  PPID   C    STIME TTY         TIME CMD
>   armandp  3264  3263   0 20:39:13 pts/2       0:00 tail -f 
> /u1/sys_admin/dba/ingres2birst.fifo
>   armandp  3265  3263   0 20:39:13 pts/2       0:00 psql -U csidba -d 
> birstdb -h 172.16.10.93
> 
> 
> 
> Give it about 10 min at most and  bam out it goes
> 
> birstdb=# select datname, pid, client_addr, client_port, backend_start, 
> query_start, state from pg_stat_Activity;
>   datname |  pid  | client_addr | client_port |         backend_start   
>        |          query_start          | state
>
---------+-------+-------------+-------------+-------------------------------+-------------------------------+--------
>   birstdb | 10208 |             |          -1 | 2018-03-27 
> 20:51:25.835382-05 | 2018-03-27 21:08:47.164249-05 | active
> 
> Although the above two processes are still out
> 
> I think the tcp keep alives might help but I am also thinking like a 
> each min check maybe and if things got in the pipe well dump ‘em to 
> Postgres. Something along these lines
> 
> Any ideas/suggestions you might have to improve this ? I am not saying 

Yeah, dump the named pipe idea and just create the connection for the 
duration of the DML event.

> it is perfect far from it, but I kinda took the model/idea from the 
> Nagios named pipe only that one too runs at x seconds/minutes interval 
> defined
> 
> 
> 
> Thank you both
> — Armand


-- 
Adrian Klaver
adrian.klaver@aklaver.com