Re: connection dropped from the backend server - Mailing list pgsql-general
From | armand pirvu |
---|---|
Subject | Re: connection dropped from the backend server |
Date | |
Msg-id | 70F4952C-B0EA-4DDC-AACD-3CFBDA8425DD@gmail.com Whole thread Raw |
In response to | Re: connection dropped from the backend server (armand pirvu <armand.pirvu@gmail.com>) |
List | pgsql-general |
Sent from my iPhone
On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Adrian Klaver <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 happensSo 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 laneWell there is no flow pattern, The flow can be inexistent for days , even weeks and then it can get super busyThe 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 | activebirstdb | 10082 | 192.168.1.187 | 55859 | 2018-03-27 20:43:55.301216-05 | 2018-03-27 20:46:07.190064-05 | idleps -fu armandpUID PID PPID C STIME TTY TIME CMDarmandp 3264 3263 0 20:39:13 pts/2 0:00 tail -f /u1/sys_admin/dba/ingres2birst.fifoarmandp 3265 3263 0 20:39:13 pts/2 0:00 psql -U csidba -d birstdb -h 172.16.10.93Give it about 10 min at most and bam out it goesbirstdb=# 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 | activeAlthough the above two processes are still outI 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 linesAny 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 definedThank you both— Armand
Sorry for the double post but as a possible solution. Why not move the named pipe to the postgres host and simply whatever i was dumping into said pipe instead of doing locally just doing over ssh
What do you think ?
Thank you
-- Armand
pgsql-general by date: