Re: connection dropped from the backend server

[armand pirvu <armand.pirvu@xxxxxxxxx>]

On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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 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