pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to Identify and Fail Transaction When Slave Node Down
6+ messages / 3 participants
[nested] [flat]
* How to Identify and Fail Transaction When Slave Node Down
@ 2017-03-15 23:51 Anushka Weerakkodyge <anushkaw@ceylonit.com>
0 siblings, 1 reply; 6+ messages in thread
From: Anushka Weerakkodyge @ 2017-03-15 23:51 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I have a synchronous streaming replication environment which is implemented
using inbuilt replication mechanism in postgresql. Replication is working
fine except I need to fail the data modification transactions when the
slave node is not accessible.
Is it possible?
Please let me know whether there is a way to identify slave node status and
fail the transaction using any inbuilt mechanism or some other technique.
Thanks,
Anushka
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to Identify and Fail Transaction When Slave Node Down
@ 2017-03-16 09:06 Anushka Weerakkodyge <anushkaw@ceylonit.com>
parent: Anushka Weerakkodyge <anushkaw@ceylonit.com>
0 siblings, 1 reply; 6+ messages in thread
From: Anushka Weerakkodyge @ 2017-03-16 09:06 UTC (permalink / raw)
To: pgsql-admin
Hi,
problem still persists.
Is there any configuration (parameter?) to set the timeout of the
connection ? Because if the slave node is down, transaction to master node
waits forever until slave node comes back online.
Any suggestion would be highly appreciated.
Thanks,
Anushka
On Thu, Mar 16, 2017 at 5:21 AM, Anushka Weerakkodyge <anushkaw@ceylonit.com
> wrote:
> Hi all,
>
> I have a synchronous streaming replication environment which is
> implemented using inbuilt replication mechanism in postgresql. Replication
> is working fine except I need to fail the data modification transactions
> when the slave node is not accessible.
>
> Is it possible?
>
> Please let me know whether there is a way to identify slave node status
> and fail the transaction using any inbuilt mechanism or some other
> technique.
>
> Thanks,
> Anushka
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to Identify and Fail Transaction When Slave Node Down
@ 2017-03-16 09:51 vinny <vinny@xs4all.nl>
parent: Anushka Weerakkodyge <anushkaw@ceylonit.com>
0 siblings, 1 reply; 6+ messages in thread
From: vinny @ 2017-03-16 09:51 UTC (permalink / raw)
To: Anushka Weerakkodyge <anushkaw@ceylonit.com>; +Cc: pgsql-admin; pgsql-admin-owner@postgresql.org
On 2017-03-16 10:06, Anushka Weerakkodyge wrote:
> Hi,
>
> problem still persists.
> Is there any configuration (parameter?) to set the timeout of the
> connection ? Because if the slave node is down, transaction to master
> node waits forever until slave node comes back online.
> Any suggestion would be highly appreciated.
>
> Thanks,
> Anushka
> On Thu, Mar 16, 2017 at 5:21 AM, Anushka Weerakkodyge
> <anushkaw@ceylonit.com> wrote:
>
>> Hi all,
>>
>> I have a synchronous streaming replication environment which is
>> implemented using inbuilt replication mechanism in postgresql.
>> Replication is working fine except I need to fail the data
>> modification transactions when the slave node is not accessible.
>>
>> Is it possible?
>>
>> Please let me know whether there is a way to identify slave node
>> status and fail the transaction using any inbuilt mechanism or some
>> other technique.
>>
>> Thanks,
>> Anushka
As far as I know, which is does not mean much, there is no timeout
setting.
And that's by design; a timeout can occur because the node is down,
but also because it's busy and cannot answer in time, or there is a
network hickup.
Adding a timeout would basically break the "syncronous" promise
because that guarantees that either all nodes are updated, or the
transaction fails.
And yes I googled this from:
https://www.postgresql.org/message-id/CADp-Sm7ENFJH4YQmJVYD2qB2OaKa6qb6TEHCJWfce%2BQoQXsdhA%40mail.g...
In the case of a node going down you should probably but some failover
mechanism in place so you can safely remove the dead node from the setup
and rebuild it before making it part of the cluster again.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to Identify and Fail Transaction When Slave Node Down
@ 2017-03-16 11:49 Anushka Weerakkodyge <anushkaw@ceylonit.com>
parent: vinny <vinny@xs4all.nl>
0 siblings, 1 reply; 6+ messages in thread
From: Anushka Weerakkodyge @ 2017-03-16 11:49 UTC (permalink / raw)
To: vinny <vinny@xs4all.nl>; +Cc: pgsql-admin; pgsql-admin-owner@postgresql.org
Thanks for your reply.
I was wondering whether there is a transaction level timeout in postgresql
at least to rollback the transaction if it waits for too long, or something
that I can set in connection string(client).
I tried statement_timeout, didn't work though.
Thanks,
Anushka
On Thu, Mar 16, 2017 at 3:21 PM, vinny <vinny@xs4all.nl> wrote:
> On 2017-03-16 10:06, Anushka Weerakkodyge wrote:
>
>> Hi,
>>
>> problem still persists.
>> Is there any configuration (parameter?) to set the timeout of the
>> connection ? Because if the slave node is down, transaction to master
>> node waits forever until slave node comes back online.
>> Any suggestion would be highly appreciated.
>>
>> Thanks,
>> Anushka
>> On Thu, Mar 16, 2017 at 5:21 AM, Anushka Weerakkodyge
>> <anushkaw@ceylonit.com> wrote:
>>
>> Hi all,
>>>
>>> I have a synchronous streaming replication environment which is
>>> implemented using inbuilt replication mechanism in postgresql.
>>> Replication is working fine except I need to fail the data
>>> modification transactions when the slave node is not accessible.
>>>
>>> Is it possible?
>>>
>>> Please let me know whether there is a way to identify slave node
>>> status and fail the transaction using any inbuilt mechanism or some
>>> other technique.
>>>
>>> Thanks,
>>> Anushka
>>>
>>
>
> As far as I know, which is does not mean much, there is no timeout setting.
> And that's by design; a timeout can occur because the node is down,
> but also because it's busy and cannot answer in time, or there is a
> network hickup.
>
> Adding a timeout would basically break the "syncronous" promise
> because that guarantees that either all nodes are updated, or the
> transaction fails.
>
> And yes I googled this from: https://www.postgresql.org/mes
> sage-id/CADp-Sm7ENFJH4YQmJVYD2qB2OaKa6qb6TEHCJWfce%
> 2BQoQXsdhA%40mail.gmail.com
>
> In the case of a node going down you should probably but some failover
> mechanism in place so you can safely remove the dead node from the setup
> and rebuild it before making it part of the cluster again.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to Identify and Fail Transaction When Slave Node Down
@ 2017-03-16 12:48 vinny <vinny@xs4all.nl>
parent: Anushka Weerakkodyge <anushkaw@ceylonit.com>
0 siblings, 1 reply; 6+ messages in thread
From: vinny @ 2017-03-16 12:48 UTC (permalink / raw)
To: Anushka Weerakkodyge <anushkaw@ceylonit.com>; +Cc: pgsql-admin; pgsql-admin-owner@postgresql.org
Although I see the logic of having a query timeout if it cannot be
replicated,
I don't think there is much point in having such a timeout because it
still makes the master reject updates
until you can solve the real problem: the slave has died and needs to be
reanimated.
Your monitoring should notice that the slave has gone down and act on
it, before your users have time to pick up the phone.
PgPool, for example, can monitor multiple servers and reconfigure them
if one of them has a problem,
allowing you to automatically take the slave away from the master, or to
promote the slave if the master fails.
That means that if the slave has a problem, the master will only wait
for the slave as long as it takes for PgPool to kick the slave out,
after which it will tell the master to reconfigure and that will
probably close the hanging transactions too (not sure about that but it
seems logical)
On 2017-03-16 12:49, Anushka Weerakkodyge wrote:
> Thanks for your reply.
> I was wondering whether there is a transaction level timeout in
> postgresql at least to rollback the transaction if it waits for too
> long, or something that I can set in connection string(client).
> I tried statement_timeout, didn't work though.
>
> Thanks,
> Anushka
> On Thu, Mar 16, 2017 at 3:21 PM, vinny <vinny@xs4all.nl> wrote:
>
>> On 2017-03-16 10:06, Anushka Weerakkodyge wrote:
>> Hi,
>>
>> problem still persists.
>> Is there any configuration (parameter?) to set the timeout of the
>> connection ? Because if the slave node is down, transaction to
>> master
>> node waits forever until slave node comes back online.
>> Any suggestion would be highly appreciated.
>>
>> Thanks,
>> Anushka
>> On Thu, Mar 16, 2017 at 5:21 AM, Anushka Weerakkodyge
>> <anushkaw@ceylonit.com> wrote:
>>
>> Hi all,
>>
>> I have a synchronous streaming replication environment which is
>> implemented using inbuilt replication mechanism in postgresql.
>> Replication is working fine except I need to fail the data
>> modification transactions when the slave node is not accessible.
>>
>> Is it possible?
>>
>> Please let me know whether there is a way to identify slave node
>> status and fail the transaction using any inbuilt mechanism or some
>> other technique.
>>
>> Thanks,
>> Anushka
>
> As far as I know, which is does not mean much, there is no timeout
> setting.
> And that's by design; a timeout can occur because the node is down,
> but also because it's busy and cannot answer in time, or there is a
> network hickup.
>
> Adding a timeout would basically break the "syncronous" promise
> because that guarantees that either all nodes are updated, or the
> transaction fails.
>
> And yes I googled this from:
> https://www.postgresql.org/message-id/CADp-Sm7ENFJH4YQmJVYD2qB2OaKa6qb6TEHCJWfce%2BQoQXsdhA%40mail.g...
> [1]
>
> In the case of a node going down you should probably but some failover
> mechanism in place so you can safely remove the dead node from the
> setup
> and rebuild it before making it part of the cluster again.
>
>
>
> Links:
> ------
> [1]
> https://www.postgresql.org/message-id/CADp-Sm7ENFJH4YQmJVYD2qB2OaKa6qb6TEHCJWfce%2BQoQXsdhA%40mail.g...
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to Identify and Fail Transaction When Slave Node Down
@ 2017-03-16 13:08 Lazaro Garcia <lazaro3487@gmail.com>
parent: vinny <vinny@xs4all.nl>
0 siblings, 0 replies; 6+ messages in thread
From: Lazaro Garcia @ 2017-03-16 13:08 UTC (permalink / raw)
To: 'vinny' <vinny@xs4all.nl>; 'Anushka Weerakkodyge' <anushkaw@ceylonit.com>; +Cc: pgsql-admin; pgsql-admin-owner@postgresql.org
When slave goes down, pgpool detects that and triggers a failover, then the
master remains active and the slave are marked as down state. On the
failover_command of pgpool, you could execute a command that drop the slave
from the master synchronous_standby_names and reload the configuration on
the master. By this way new transactions can be executed on the master
without checking the state of the slave because synchronous replication has
been disabled.
If you recover the slave you can attach this server to master again an
activate the synchronous replication.
Regards.
-----Mensaje original-----
De: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] En nombre de vinny
Enviado el: jueves, 16 de marzo de 2017 08:49 a. m.
Para: Anushka Weerakkodyge
CC: pgsql-admin@postgresql.org; pgsql-admin-owner@postgresql.org
Asunto: Re: [ADMIN] How to Identify and Fail Transaction When Slave Node
Down
Although I see the logic of having a query timeout if it cannot be
replicated, I don't think there is much point in having such a timeout
because it still makes the master reject updates until you can solve the
real problem: the slave has died and needs to be reanimated.
Your monitoring should notice that the slave has gone down and act on it,
before your users have time to pick up the phone.
PgPool, for example, can monitor multiple servers and reconfigure them if
one of them has a problem, allowing you to automatically take the slave away
from the master, or to promote the slave if the master fails.
That means that if the slave has a problem, the master will only wait for
the slave as long as it takes for PgPool to kick the slave out, after which
it will tell the master to reconfigure and that will probably close the
hanging transactions too (not sure about that but it seems logical)
On 2017-03-16 12:49, Anushka Weerakkodyge wrote:
> Thanks for your reply.
> I was wondering whether there is a transaction level timeout in
> postgresql at least to rollback the transaction if it waits for too
> long, or something that I can set in connection string(client).
> I tried statement_timeout, didn't work though.
>
> Thanks,
> Anushka
> On Thu, Mar 16, 2017 at 3:21 PM, vinny <vinny@xs4all.nl> wrote:
>
>> On 2017-03-16 10:06, Anushka Weerakkodyge wrote:
>> Hi,
>>
>> problem still persists.
>> Is there any configuration (parameter?) to set the timeout of the
>> connection ? Because if the slave node is down, transaction to master
>> node waits forever until slave node comes back online.
>> Any suggestion would be highly appreciated.
>>
>> Thanks,
>> Anushka
>> On Thu, Mar 16, 2017 at 5:21 AM, Anushka Weerakkodyge
>> <anushkaw@ceylonit.com> wrote:
>>
>> Hi all,
>>
>> I have a synchronous streaming replication environment which is
>> implemented using inbuilt replication mechanism in postgresql.
>> Replication is working fine except I need to fail the data
>> modification transactions when the slave node is not accessible.
>>
>> Is it possible?
>>
>> Please let me know whether there is a way to identify slave node
>> status and fail the transaction using any inbuilt mechanism or some
>> other technique.
>>
>> Thanks,
>> Anushka
>
> As far as I know, which is does not mean much, there is no timeout
> setting.
> And that's by design; a timeout can occur because the node is down,
> but also because it's busy and cannot answer in time, or there is a
> network hickup.
>
> Adding a timeout would basically break the "syncronous" promise
> because that guarantees that either all nodes are updated, or the
> transaction fails.
>
> And yes I googled this from:
> https://www.postgresql.org/message-id/CADp-Sm7ENFJH4YQmJVYD2qB2OaKa6qb
> 6TEHCJWfce%2BQoQXsdhA%40mail.gmail.com
> [1]
>
> In the case of a node going down you should probably but some failover
> mechanism in place so you can safely remove the dead node from the
> setup and rebuild it before making it part of the cluster again.
>
>
>
> Links:
> ------
> [1]
> https://www.postgresql.org/message-id/CADp-Sm7ENFJH4YQmJVYD2qB2OaKa6qb
> 6TEHCJWfce%2BQoQXsdhA%40mail.gmail.com
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-03-16 13:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-03-15 23:51 How to Identify and Fail Transaction When Slave Node Down Anushka Weerakkodyge <anushkaw@ceylonit.com>
2017-03-16 09:06 ` Anushka Weerakkodyge <anushkaw@ceylonit.com>
2017-03-16 09:51 ` vinny <vinny@xs4all.nl>
2017-03-16 11:49 ` Anushka Weerakkodyge <anushkaw@ceylonit.com>
2017-03-16 12:48 ` vinny <vinny@xs4all.nl>
2017-03-16 13:08 ` Lazaro Garcia <lazaro3487@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox