Thread: Fully-automatic streaming replication failover when master dies?
On 1/22/2014 1:35 PM, Dmitry Koterov wrote: > > So does something similar and more-or-less stable exist for PostgrSQL > too? > you'd need to implement that yourself using a cluster management package, and something like repmgr to handle the transitions. database failover is extremely tricky stuff. you have to make very very sure you don't get into a stoned cluster where both nodes THINK they are master. most well implemented failover clusters make use of hardware 'fencing' to block the presumed-dead former master from coming back online without manual intervention. -- john r pierce 37N 122W somewhere on the middle of the left coast
On 1/22/2014 1:35 PM, Dmitry Koterov wrote:you'd need to implement that yourself using a cluster management package, and something like repmgr to handle the transitions.
So does something similar and more-or-less stable exist for PostgrSQL too?
database failover is extremely tricky stuff. you have to make very very sure you don't get into a stoned cluster where both nodes THINK they are master. most well implemented failover clusters make use of hardware 'fencing' to block the presumed-dead former master from coming back online without manual intervention.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 23, 2014 at 03:12:07AM +0400, Dmitry Koterov wrote: > for all the machines). At least MongoDB does the work well, and with almost > zero configuration. Mongo's data guarantees are, um, somewhat less robust than PostgreSQL's. Failover is easy if you don't have to be exactly right. A -- Andrew Sullivan ajs@crankycanuck.ca
Hello.I googled 1 hour approximately, but have not found a ready solution for this. So maybe this feature is in PostgreSQL todo-list, or something similar exists somewhere...Before the actual question, I'd like to give a small analogy. What I mostly love in MongoDB is that it supports a fully transparent scheme of replication failover. If you have >= 3 MongoDB notes (e.g. 1 master and 2 replicas), and the master dies, in a couple of seconds a replica is AUTOMATICALLY elected as a new master, and all other replicas are AUTOMATICALLY begin to follow it. If the dead master is back again suddenly, it first appears as a replica, but in a couple of seconds it becomes a new master back (because it initially had a highest weight assigned), and all replicas become to follow it. All these steps are done automatically and transparently. It just works.So does something similar and more-or-less stable exist for PostgrSQL too?P.S.The links I've found already:- http://www.databasesoup.com - that's it, but too young and, possibly, not yet stable- repmgr: always referred at StackOverflow (thousands of times), but it does not provide an automatic failover :-)- pgpool-2: it contains a couple of config options for "failover", but in practice they are too poor to be used in production and with large databases, I believe after reading the docs- pacemaker's plugin: extremely complex (IMHO) overweighted for this purpose- EnterpriseDB's solutions: they are not free
pgpool-II may do what you want. Lots of people use it.
> for all the machines). At least MongoDB does the work well, and with almost
> zero configuration.
Mongo's data guarantees are, um, somewhat less robust than
PostgreSQL's.
Failover is easy if you don't have to be exactly right.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
Currnently there is no automatic failover for postgresql. If I were to build something to do this I'd start with repmgr: http://www.repmgr.org/ On Wed, Jan 22, 2014 at 2:35 PM, Dmitry Koterov <dmitry.koterov@gmail.com> wrote: > Hello. > > I googled 1 hour approximately, but have not found a ready solution for > this. So maybe this feature is in PostgreSQL todo-list, or something similar > exists somewhere... > > Before the actual question, I'd like to give a small analogy. What I mostly > love in MongoDB is that it supports a fully transparent scheme of > replication failover. If you have >= 3 MongoDB notes (e.g. 1 master and 2 > replicas), and the master dies, in a couple of seconds a replica is > AUTOMATICALLY elected as a new master, and all other replicas are > AUTOMATICALLY begin to follow it. If the dead master is back again suddenly, > it first appears as a replica, but in a couple of seconds it becomes a new > master back (because it initially had a highest weight assigned), and all > replicas become to follow it. All these steps are done automatically and > transparently. It just works. > > So does something similar and more-or-less stable exist for PostgrSQL too? > > P.S. > The links I've found already: > - http://www.databasesoup.com - that's it, but too young and, possibly, not > yet stable > - repmgr: always referred at StackOverflow (thousands of times), but it does > not provide an automatic failover :-) > - pgpool-2: it contains a couple of config options for "failover", but in > practice they are too poor to be used in production and with large > databases, I believe after reading the docs > - pacemaker's plugin: extremely complex (IMHO) overweighted for this purpose > - EnterpriseDB's solutions: they are not free -- To understand recursion, one must first understand recursion.
On Thu, Jan 23, 2014 at 7:16 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote: > > > On Fri, Jan 24, 2014 at 1:38 AM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote: >> >> pgpool-II may do what you want. Lots of people use it. > > > I don't think pgpool adds the lost node on its own (once the node is live or available again). Plus if you have a 3 nodereplication you need to have your own failover_command (as a shell script) which changes the master node for 2nd secondarywhen one of the secondary servers decides to be promoted to primary). I hope things will get easy with version 9.4(I guess in 9.4 one won't have to rebuild a master node from backup. if the wal files are available it will just rollforward). > >> > for all the machines). At least MongoDB does the work well, and with almost >> > zero configuration. >> Mongo's data guarantees are, um, somewhat less robust than >> PostgreSQL's. > > > I don't think this has anything to do with data reliability or ACID property (if that is what you are referring to). > >> Failover is easy if you don't have to be exactly right. > > > IMHO That's not a fair point. PostgreSQL supports sync replication (as well as async) and does that complicate the failoverprocess or an async replication? I guess what he is asking for is automation of whatever feature PostgreSQL alreadysupports. No it's a fair point. When you go from "we promise to try and not lose your data" to "we promise to not lose any of your data" the situation is much different. There are many things to consider in the postgresql situation. Is it more important to keep your application up and running, even if only in read only mode? Is performance more important than data integrity? How many nodes do you have? How man can auto-fail over before you auto-fail over to the very last one? How do you rejoin failed nodes, one at a time, all at once, by hand, automagically? And so on. There are a LOT of questions to ask that mongo already decided for you, and the decision was that if you lose some data that's OK as long as the cluster stays up. With PostgreSQL the decision making process probably has a big impact on how you answer these types of questions and how you fail over. Add to that that most postgresql database servers are VERY robust, with multi-lane RAID array controllers and / or sturdy SANs underneath them, and their failure rates are very low, you run the risk of your auto-failover causing much of an outage as the server failing, since most failovers are going to cause some short interruption in service. It's not a simple push a button take a banana, one size fits all problem and solution.
> I don't think pgpool adds the lost node on its own (once the node is live > or available again). That's one of my TODOs. If we limit it to the standby nodes, it will be safe. I hope this is added to next major version of pgpool-II. > Plus if you have a 3 node replication you need to have > your own failover_command (as a shell script) which changes the master node > for 2nd secondary when one of the secondary servers decides to be promoted > to primary). I hope things will get easy with version 9.4 (I guess in 9.4 > one won't have to rebuild a master node from backup. if the wal files are > available it will just roll forward). Yes, if you have multiple candicate standbys to be promoted, you should make a discion on this. Pgpool-II cannot guess your will. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
> I don't think pgpool adds the lost node on its own (once the node is liveThat's one of my TODOs. If we limit it to the standby nodes, it will
> or available again).
be safe. I hope this is added to next major version of pgpool-II.
> Plus if you have a 3 node replication you need to haveYes, if you have multiple candicate standbys to be promoted, you
> your own failover_command (as a shell script) which changes the master node
> for 2nd secondary when one of the secondary servers decides to be promoted
> to primary). I hope things will get easy with version 9.4 (I guess in 9.4
> one won't have to rebuild a master node from backup. if the wal files are
> available it will just roll forward).
should make a discion on this. Pgpool-II cannot guess your will.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
Dmitry Koterov, 22.01.2014 22:35: > I googled 1 hour approximately, but have not found a ready solution > for this. So maybe this feature is in PostgreSQL todo-list, or > something similar exists somewhere... > > Before the actual question, I'd like to give a small analogy. What I > mostly love in MongoDB is that it supports a fully transparent scheme > of replication failover. If you have >= 3 MongoDB notes (e.g. 1 > master and 2 replicas), and the master dies, in a couple of seconds a > replica is AUTOMATICALLY elected as a new master, and all other > replicas are AUTOMATICALLY begin to follow it. If the dead master is > back again suddenly, it first appears as a replica, but in a couple > of seconds it becomes a new master back (because it initially had a > highest weight assigned), and all replicas become to follow it. All > these steps are done automatically and transparently. It just works. > > So does something similar and more-or-less stable exist for PostgrSQL > too? Someone just blogged about this: http://evol-monkey.blogspot.de/2014/01/setting-up-postgres-automated-failover.html
On Friday, January 24, 2014, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Jan 23, 2014 at 7:16 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
>
>
> On Fri, Jan 24, 2014 at 1:38 AM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
>>
>> pgpool-II may do what you want. Lots of people use it.
>
>
> I don't think pgpool adds the lost node on its own (once the node is live or available again). Plus if you have a 3 node replication you need to have your own failover_command (as a shell script) which changes the master node for 2nd secondary when one of the secondary servers decides to be promoted to primary). I hope things will get easy with version 9.4 (I guess in 9.4 one won't have to rebuild a master node from backup. if the wal files are available it will just roll forward).
>
>> > for all the machines). At least MongoDB does the work well, and with almost
>> > zero configuration.
>> Mongo's data guarantees are, um, somewhat less robust than
>> PostgreSQL's.
>
>
> I don't think this has anything to do with data reliability or ACID property (if that is what you are referring to).
>
>> Failover is easy if you don't have to be exactly right.
>
>
> IMHO That's not a fair point. PostgreSQL supports sync replication (as well as async) and does that complicate the failover process or an async replication? I guess what he is asking for is automation of whatever feature PostgreSQL already supports.
No it's a fair point. When you go from "we promise to try and not lose
your data" to "we promise to not lose any of your data" the situation
is much different.
There are many things to consider in the postgresql situation. Is it
more important to keep your application up and running, even if only
in read only mode? Is performance more important than data integrity?
How many nodes do you have? How man can auto-fail over before you
auto-fail over to the very last one? How do you rejoin failed nodes,
one at a time, all at once, by hand, automagically? And so on. There
are a LOT of questions to ask that mongo already decided for you, and
the decision was that if you lose some data that's OK as long as the
cluster stays up. With PostgreSQL the decision making process probably
has a big impact on how you answer these types of questions and how
you fail over.
Add to that that most postgresql database servers are VERY robust,
with multi-lane RAID array controllers and / or sturdy SANs underneath
them, and their failure rates are very low, you run the risk of your
auto-failover causing much of an outage as the server failing, since
most failovers are going to cause some short interruption in service.
It's not a simple push a button take a banana, one size fits all
problem and solution.
Please don't top post in technical discussions. On Sat, Jan 25, 2014 at 11:29 AM, Dmitry Koterov <dmitry.koterov@gmail.com> wrote: > > On Friday, January 24, 2014, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> >> On Thu, Jan 23, 2014 at 7:16 PM, Sameer Kumar <sameer.kumar@ashnik.com> >> wrote: >> > >> > >> > On Fri, Jan 24, 2014 at 1:38 AM, Susan Cassidy >> > <susan.cassidy@decisionsciencescorp.com> wrote: >> >> >> >> pgpool-II may do what you want. Lots of people use it. >> > >> > >> > I don't think pgpool adds the lost node on its own (once the node is >> > live or available again). Plus if you have a 3 node replication you need to >> > have your own failover_command (as a shell script) which changes the master >> > node for 2nd secondary when one of the secondary servers decides to be >> > promoted to primary). I hope things will get easy with version 9.4 (I guess >> > in 9.4 one won't have to rebuild a master node from backup. if the wal files >> > are available it will just roll forward). >> > >> >> > for all the machines). At least MongoDB does the work well, and with >> >> > almost >> >> > zero configuration. >> >> Mongo's data guarantees are, um, somewhat less robust than >> >> PostgreSQL's. >> > >> > >> > I don't think this has anything to do with data reliability or ACID >> > property (if that is what you are referring to). >> > >> >> Failover is easy if you don't have to be exactly right. >> > >> > >> > IMHO That's not a fair point. PostgreSQL supports sync replication (as >> > well as async) and does that complicate the failover process or an async >> > replication? I guess what he is asking for is automation of whatever feature >> > PostgreSQL already supports. >> >> No it's a fair point. When you go from "we promise to try and not lose >> your data" to "we promise to not lose any of your data" the situation >> is much different. >> >> There are many things to consider in the postgresql situation. Is it >> more important to keep your application up and running, even if only >> in read only mode? Is performance more important than data integrity? >> How many nodes do you have? How man can auto-fail over before you >> auto-fail over to the very last one? How do you rejoin failed nodes, >> one at a time, all at once, by hand, automagically? And so on. There >> are a LOT of questions to ask that mongo already decided for you, and >> the decision was that if you lose some data that's OK as long as the >> cluster stays up. With PostgreSQL the decision making process probably >> has a big impact on how you answer these types of questions and how >> you fail over. >> >> Add to that that most postgresql database servers are VERY robust, >> with multi-lane RAID array controllers and / or sturdy SANs underneath >> them, and their failure rates are very low, you run the risk of your >> auto-failover causing much of an outage as the server failing, since >> most failovers are going to cause some short interruption in service. >> It's not a simple push a button take a banana, one size fits all >> problem and solution. > Failover is NOT about the RAID or SAN robusness mostly. It's about > datacenters connectivity and network issues. If you lose one datacenter (it > happens, and there is no aid for it), you should redirect all traffic to > another DC ASAP and failover the master DB to it. When the disconnected DC > is up again, it should recover from this situation. > > So +1 for the previous man, PostgreSQL ACID and MongoDB non-ACID have > absolute no relevance to the failover problem. If you'll bother reading what I wrote AGAIN, you'll notice my mention on ACID etc was more of an afterthought here. There are real questions about data loss and recovery that matter when you are failing over. Are you running your cluster in synchronous mode across geographically diverse data centers? If not how long do you wait for the master to come back before you fail over? A millisecond? A second? A minute? The answer will likely be different for me than for you. While ACID isn't the main or only reason for things being different, it IS a valid reason because different people use PostgreSQL for different things. If I'm running it as a session server, I treat it one way, as a key-value store another, as a transactional database handling monetary funds yet another. You're refusal to accept that this is a complex issue with complex answers isn't helping you find the right answer to your problem. -- To understand recursion, one must first understand recursion.
Are you running your cluster in synchronous mode across geographically
diverse data centers? If not how long do you wait for the master to
come back before you fail over? A millisecond? A second? A minute? The
answer will likely be different for me than for you.
While ACID isn't the main or only reason for things being different,
it IS a valid reason because different people use PostgreSQL for
different things. If I'm running it as a session server, I treat it
one way, as a key-value store another, as a transactional database
handling monetary funds yet another.
You're refusal to accept that
this is a complex issue with complex answers isn't helping you find
the right answer to your problem.
PostgreSQL supports synchronous multi-master, MongoDB supports write concern, but this causes a performance penalty).
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
Dmitry Koterov wrote:PostgreSQL supports synchronous multi-master, MongoDB supports write concern, but this causes a performance penalty).Anyways I doubt that "PostgreSQL supports synchronous multi-master"
Michael
PostgreSQL supports synchronous multi-master, MongoDB supports write concern, but this causes a performance penalty).Anyways I doubt that "PostgreSQL supports synchronous multi-master"Postgres core taken as such does not support multi-master. The fork of PostgreSQL called Postgres-XC somewhat does, for OLTP applications.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
> I know about PostgresXC, but I thought it is distributed database (similar > to shards of mongoDB). [Though if I am correct there could be tables which > are shared across different nodes, but that is not the best way of > utilizing features of PostgresXC] I think it is not apt to call it > "synchronous" (since there is no replication happening). I thought non "eventual consistency" type DB can be called "synchronous", no? If I am correct, Posgres-XC is definitely synchronous. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Mon, Jan 27, 2014 at 11:24 AM, Michael Paquier <michael.paquier@gmail.com> wrote:PostgreSQL supports synchronous multi-master, MongoDB supports write concern, but this causes a performance penalty).Anyways I doubt that "PostgreSQL supports synchronous multi-master"Postgres core taken as such does not support multi-master. The fork of PostgreSQL called Postgres-XC somewhat does, for OLTP applications.I know about PostgresXC, but I thought it is distributed database (similar to shards of mongoDB). [Though if I am correct there could be tables which are shared across different nodes, but that is not the best way of utilizing features of PostgresXC] I think it is not apt to call it "synchronous" (since there is no replication happening).
Michael
Please don't top post in technical discussions.If you'll bother reading what I wrote AGAIN, you'll notice my mention
On Sat, Jan 25, 2014 at 11:29 AM, Dmitry Koterov
<dmitry.koterov@gmail.com> wrote:
>
> On Friday, January 24, 2014, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>> On Thu, Jan 23, 2014 at 7:16 PM, Sameer Kumar <sameer.kumar@ashnik.com>
>> wrote:
>> >
>> >
>> > On Fri, Jan 24, 2014 at 1:38 AM, Susan Cassidy
>> > <susan.cassidy@decisionsciencescorp.com> wrote:
>> >>
>> >> pgpool-II may do what you want. Lots of people use it.
>> >
>> >
>> > I don't think pgpool adds the lost node on its own (once the node is
>> > live or available again). Plus if you have a 3 node replication you need to
>> > have your own failover_command (as a shell script) which changes the master
>> > node for 2nd secondary when one of the secondary servers decides to be
>> > promoted to primary). I hope things will get easy with version 9.4 (I guess
>> > in 9.4 one won't have to rebuild a master node from backup. if the wal files
>> > are available it will just roll forward).
>> >
>> >> > for all the machines). At least MongoDB does the work well, and with
>> >> > almost
>> >> > zero configuration.
>> >> Mongo's data guarantees are, um, somewhat less robust than
>> >> PostgreSQL's.
>> >
>> >
>> > I don't think this has anything to do with data reliability or ACID
>> > property (if that is what you are referring to).
>> >
>> >> Failover is easy if you don't have to be exactly right.
>> >
>> >
>> > IMHO That's not a fair point. PostgreSQL supports sync replication (as
>> > well as async) and does that complicate the failover process or an async
>> > replication? I guess what he is asking for is automation of whatever feature
>> > PostgreSQL already supports.
>>
>> No it's a fair point. When you go from "we promise to try and not lose
>> your data" to "we promise to not lose any of your data" the situation
>> is much different.
>>
>> There are many things to consider in the postgresql situation. Is it
>> more important to keep your application up and running, even if only
>> in read only mode? Is performance more important than data integrity?
>> How many nodes do you have? How man can auto-fail over before you
>> auto-fail over to the very last one? How do you rejoin failed nodes,
>> one at a time, all at once, by hand, automagically? And so on. There
>> are a LOT of questions to ask that mongo already decided for you, and
>> the decision was that if you lose some data that's OK as long as the
>> cluster stays up. With PostgreSQL the decision making process probably
>> has a big impact on how you answer these types of questions and how
>> you fail over.
>>
>> Add to that that most postgresql database servers are VERY robust,
>> with multi-lane RAID array controllers and / or sturdy SANs underneath
>> them, and their failure rates are very low, you run the risk of your
>> auto-failover causing much of an outage as the server failing, since
>> most failovers are going to cause some short interruption in service.
>> It's not a simple push a button take a banana, one size fits all
>> problem and solution.
> Failover is NOT about the RAID or SAN robusness mostly. It's about
> datacenters connectivity and network issues. If you lose one datacenter (it
> happens, and there is no aid for it), you should redirect all traffic to
> another DC ASAP and failover the master DB to it. When the disconnected DC
> is up again, it should recover from this situation.
>
> So +1 for the previous man, PostgreSQL ACID and MongoDB non-ACID have
> absolute no relevance to the failover problem.
on ACID etc was more of an afterthought here. There are real questions
about data loss and recovery that matter when you are failing over.
Are you running your cluster in synchronous mode across geographically
diverse data centers? If not how long do you wait for the master to
come back before you fail over? A millisecond? A second? A minute? The
answer will likely be different for me than for you.
While ACID isn't the main or only reason for things being different,
it IS a valid reason because different people use PostgreSQL for
different things. If I'm running it as a session server, I treat it
one way, as a key-value store another, as a transactional database
handling monetary funds yet another. You're refusal to accept that
this is a complex issue with complex answers isn't helping you find
the right answer to your problem.
--
To understand recursion, one must first understand recursion.
PostgreSQL supports synchronous multi-master
no, PostgreSQL does not support multi-master, not without some sort of third party replication system, all of which have serious compromises.
-- john r pierce 37N 122W somewhere on the middle of the left coast