connection dropped from the backend server

On 03/27/2018 03:36 PM, armand pirvu wrote:

Hi all

I have a process of some data manipulation and ultimate transfer to a postgres database
A DML statement gest concoted with the transformed data and pusshed into a named pipe
The named pipe is tailed -f in the background like this

nohup $SHELL <<EOF &
tail -f /u1/sys_admin/dba/mypipe.fifo | psql -U csidba -d repdb -h rephost
EOF

All good BUT I do notice every say 10 min although I see the tail and psql processes in the ps output, looking in pg_stat_activity there is really nothing the host I run the nohuped tail

Could it be that pg_stat_activity shows nothing because the DML has
completed when you look?

Does the data find its way into the database?

Any suggestions how to approach this/make it better/monitor ?

Thanks
-- Armand

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Sent from my iPhone

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:

state
query
backend_start

and any others you might think are important from here:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Sent from my iPhone

On Mar 27, 2018, at 6:03 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 03/27/2018 03:36 PM, armand pirvu wrote:
Hi all
I have a process of some data manipulation and ultimate transfer to a postgres database
A DML statement gest concoted with the transformed data and pusshed into a named pipe
The named pipe is tailed -f in the background like this
nohup $SHELL <<EOF &
tail -f /u1/sys_admin/dba/mypipe.fifo | psql -U csidba -d repdb -h rephost
EOF
All good BUT I do notice every say 10 min although I see the tail and psql processes in the ps output, looking in pg_stat_activity there is really nothing the host I run the nohuped tail

Could it be that pg_stat_activity shows nothing because the DML has completed when you look?

Does the data find its way into the database?

Any suggestions how to approach this/make it better/monitor ?
Thanks
-- Armand

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

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 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

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 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

Sent from my iPhone

On Mar 27, 2018, at 9:21 PM, armand pirvu <armand.pirvu@gmail.com> wrote:

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 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

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

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