On 03/27/2018 07:21 PM, armand pirvu wrote:
On Mar 27, 2018, at 6:49 PM, Tom Lane <tgl@xxxxxxxxxxxxx
<mailto:tgl@xxxxxxxxxxxxx>> wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto: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
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@xxxxxxxxxxx
