replication_timeout not effective

[Dang Minh Huong]

Hi,

I'm wondering if this is a bug of PostgreSQL.

PostgreSQL's show that replication_timeout parameter can "Terminate replication connections that are inactive longer than the specified number of milliseconds". But in my environment the sender process is hang up (in several tens of minunites) if i turn off (by power off) Standby PC while pg_basebackup is excuting.

Is this correct?

As my debug, sender process is terminated when recieve SIGPIPE process but it come too slow (about 30minutes after standby PC was down).

I'm not certain but It might hang up in send () from /lib64/libc.so.6

I'm using version 9.1.9 of PostgreSQL.

Thanks,

[Dang Minh Huong]

Hi,
Thank you for your soon reply.
I'm trying to set the network timeout related parameters to terminate it.
# i've tried to set postgresql.conf's tcp_keepalives_* but not success.
Regards,

Post by Dang Minh Huong
Hi,
I'm wondering if this is a bug of PostgreSQL.
PostgreSQL's show that replication_timeout parameter can "Terminate replication connections that are inactive longer than the specified number of milliseconds". But in my environment the sender process > is hang up (in several tens of minunites) if i turn off (by power off) Standby PC while pg_basebackup is excuting.
Is this correct?
As my debug, sender process is terminated when recieve SIGPIPE process but it come too slow (about 30minutes after standby PC was down).

http://www.postgresql.org/docs/devel/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER
I am not sure how to get rid of this problem in 9.1.9
With Regards,
Amit Kapila.

[Dang Minh Huong]

Hi Amit,

Thank you for your consideration.

My project not allows to use 9.2 or 9.3.

In 9.3, it sounds replication_timeout is replaced by wal_sender_timeout.
So if it is solved in 9.3 i think there is a way to terminate it.
I hope it is fixed in 9.1 soon

Regards,

To: Amit Kapila
Subject: Re: [BUGS] replication_timeout not effective

On Wednesday, April 10, 2013 1:49 PM

Hi,
Thank you for your soon reply.
I'm trying to set the network timeout related parameters to terminate
it.
# i've tried to set postgresql.conf's tcp_keepalives_* but not success.

I have also tried those, but they didn't work that's why I have proposed
this feature in 9.3
Please send mail on community list, others can also help you if they have
any idea for avoiding such problems.

Post by Dang Minh Huong
Hi,
I'm wondering if this is a bug of PostgreSQL.
PostgreSQL's show that replication_timeout parameter can "Terminate

replication connections that are inactive longer than the specified
number of milliseconds". But in my environment the sender process > is
hang up (in several tens of minunites) if i turn off (by power off)
Standby PC while pg_basebackup is excuting.

Post by Dang Minh Huong
Is this correct?
As my debug, sender process is terminated when recieve SIGPIPE

process but it come too slow (about 30minutes after standby PC was
down).

For such scenario's, new parameter wal_sender_timeout has been
http://www.postgresql.org/docs/devel/static/runtime-config-

replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

I am not sure how to get rid of this problem in 9.1.9
With Regards,
Amit Kapila.

[Kyotaro HORIGUCHI]

Hello,

Post by Dang Minh Huong
In 9.3, it sounds replication_timeout is replaced by wal_sender_timeout.
So if it is solved in 9.3 i think there is a way to terminate it.
I hope it is fixed in 9.1 soon

Hmm. He said that,

Post by Dang Minh Huong
But in my environment the sender process is hang up (in several tens of minunites) if i turn off (by power off) Standby PC while *pg_basebackup* is excuting.

Does basebackup run only on 'replication connection' ?
As far as I saw base backup uses 'base backup' connection in addition
to 'streaming' connection. The former seems not under the control of
wal_sender_timeout or replication_timeout and easily blocked at
send(2) after sudden cut out of the network connection underneath.
Although the latter indeed is terminated by them.

Blocking in send(2) might could occur for async-rep connection but not
likely for sync-rep since it does not fill the buffers of libpq and
socket easilly.

I suppose he says about this.

This seems to occur as of the latest 9.3dev.

regards,
--
Kyotaro Horiguchi

[Andres Freund]

Post by Kyotaro HORIGUCHI
Hello,

Post by Dang Minh Huong
In 9.3, it sounds replication_timeout is replaced by wal_sender_timeout.
So if it is solved in 9.3 i think there is a way to terminate it.
I hope it is fixed in 9.1 soon

Hmm. He said that,

Post by Dang Minh Huong
But in my environment the sender process is hang up (in several tens of minunites) if i turn off (by power off) Standby PC while *pg_basebackup* is excuting.

Does basebackup run only on 'replication connection' ?
As far as I saw base backup uses 'base backup' connection in addition
to 'streaming' connection. The former seems not under the control of
wal_sender_timeout or replication_timeout and easily blocked at
send(2) after sudden cut out of the network connection underneath.
Although the latter indeed is terminated by them.

Yes, it's run via a walsender connection. The only "problem" is that it
doesn't check for those timeouts. I am not sure it would be a good thing
to do so to be honest. At least not using the same timeout as actual WAL
sending, thats just has different characteristics.
On the other hand, hanging around that long isn't nice either...

Post by Kyotaro HORIGUCHI
Blocking in send(2) might could occur for async-rep connection but not
likely for sync-rep since it does not fill the buffers of libpq and
socket easilly.

You just need larger transactions for it. A COPY or so ought to do it.

Greetings,

Andres Freund

[Dang Minh Huong]

Thanks all,

Post by Andres Freund

Post by Kyotaro HORIGUCHI
Hello,

Post by Dang Minh Huong
In 9.3, it sounds replication_timeout is replaced by wal_sender_timeout.
So if it is solved in 9.3 i think there is a way to terminate it.
I hope it is fixed in 9.1 soon

Hmm. He said that,

Post by Dang Minh Huong
But in my environment the sender process is hang up (in several tens of minunites) if i turn off (by power off) Standby PC while *pg_basebackup* is excuting.

Does basebackup run only on 'replication connection' ?
As far as I saw base backup uses 'base backup' connection in addition
to 'streaming' connection. The former seems not under the control of
wal_sender_timeout or replication_timeout and easily blocked at
send(2) after sudden cut out of the network connection underneath.
Although the latter indeed is terminated by them.

Yes, it's run via a walsender connection. The only "problem" is that it
doesn't check for those timeouts. I am not sure it would be a good thing
to do so to be honest. At least not using the same timeout as actual WAL
sending, thats just has different characteristics.
On the other hand, hanging around that long isn't nice either...

I tried max_wal_sender with 1, so when the walsender is hanging.
I can not run again pg_basebackup (or start the standby DB).
I'm increasing it to 2, so the seconds successfully. But i'm afraid
that when the third occures the hanging walsender in the first
is not yet terminated...

I think not, but is there a way to terminate hanging up but not
restart PostgreSQL server or kill walsender process?
(kill walsender process can caused a crash to DB server,
so i don't want to do it).

# i've also tried with pg_cancel_backend() but it did not work too.

Post by Andres Freund

Post by Kyotaro HORIGUCHI
Blocking in send(2) might could occur for async-rep connection but not
likely for sync-rep since it does not fill the buffers of libpq and
socket easilly.

You just need larger transactions for it. A COPY or so ought to do it.
Greetings,
Andres Freund

Regard,
Huong DM

[Andres Freund]

Post by Dang Minh Huong
Thanks all,

Post by Andres Freund

Post by Kyotaro HORIGUCHI
Hello,

Post by Dang Minh Huong
In 9.3, it sounds replication_timeout is replaced by wal_sender_timeout.
So if it is solved in 9.3 i think there is a way to terminate it.
I hope it is fixed in 9.1 soon

Hmm. He said that,

Post by Dang Minh Huong
But in my environment the sender process is hang up (in several tens of minunites) if i turn off (by power off) Standby PC while *pg_basebackup* is excuting.

Does basebackup run only on 'replication connection' ?
As far as I saw base backup uses 'base backup' connection in addition
to 'streaming' connection. The former seems not under the control of
wal_sender_timeout or replication_timeout and easily blocked at
send(2) after sudden cut out of the network connection underneath.
Although the latter indeed is terminated by them.

Yes, it's run via a walsender connection. The only "problem" is that it
doesn't check for those timeouts. I am not sure it would be a good thing
to do so to be honest. At least not using the same timeout as actual WAL
sending, thats just has different characteristics.
On the other hand, hanging around that long isn't nice either...

I tried max_wal_sender with 1, so when the walsender is hanging.
I can not run again pg_basebackup (or start the standby DB).
I'm increasing it to 2, so the seconds successfully. But i'm afraid
that when the third occures the hanging walsender in the first
is not yet terminated...
I think not, but is there a way to terminate hanging up but not
restart PostgreSQL server or kill walsender process?
(kill walsender process can caused a crash to DB server,
so i don't want to do it).

Depending on where its hanging a normal SELECT
pg_terminate_backend(pid); might do it.

Otherwise you will have to wait for the operating system's tcp timeout.

Greetings,

Andres Freund

[Dang Minh Huong]

Post by Andres Freund

Post by Dang Minh Huong
Thanks all,

Post by Andres Freund

Post by Kyotaro HORIGUCHI
Hello,

Post by Dang Minh Huong
In 9.3, it sounds replication_timeout is replaced by wal_sender_timeout.
So if it is solved in 9.3 i think there is a way to terminate it.
I hope it is fixed in 9.1 soon

Hmm. He said that,

Post by Dang Minh Huong
But in my environment the sender process is hang up (in several tens of minunites) if i turn off (by power off) Standby PC while *pg_basebackup* is excuting.

Does basebackup run only on 'replication connection' ?
As far as I saw base backup uses 'base backup' connection in addition
to 'streaming' connection. The former seems not under the control of
wal_sender_timeout or replication_timeout and easily blocked at
send(2) after sudden cut out of the network connection underneath.
Although the latter indeed is terminated by them.

Yes, it's run via a walsender connection. The only "problem" is that it
doesn't check for those timeouts. I am not sure it would be a good thing
to do so to be honest. At least not using the same timeout as actual WAL
sending, thats just has different characteristics.
On the other hand, hanging around that long isn't nice either...

I tried max_wal_sender with 1, so when the walsender is hanging.
I can not run again pg_basebackup (or start the standby DB).
I'm increasing it to 2, so the seconds successfully. But i'm afraid
that when the third occures the hanging walsender in the first
is not yet terminated...
I think not, but is there a way to terminate hanging up but not
restart PostgreSQL server or kill walsender process?
(kill walsender process can caused a crash to DB server,
so i don't want to do it).

Depending on where its hanging a normal SELECT
pg_terminate_backend(pid); might do it.

Greate! it worked. Thank you very much.

Post by Andres Freund
Otherwise you will have to wait for the operating system's tcp timeout.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Regards,
Huong DM