Consul and master-slave database

[Theron Luhn]

I have a master-slave (streaming replication) Postgres configuration managed by PgPool, but I'm becoming increasing dissatisfied with it and am thinking of putting something together with Consul.  It'll need to do the following:

• Health check the master — I'm thinking a simple "select nextval('serial');" ever couple of seconds.  (Is there a more comprehensive health check?)
• Health check the slaves — Make sure the replication delay is within acceptable limits
• Upon the master failing, electing a slave as the new master (https://www.consul.io/docs/guides/leader-election.html)
• STONITH—The old master can't come back online.  I'm thinking that the new master should deregister the old one via the catalog API, but I'm not 100% how well that will work.
• The other slaves will have to deregister themselves, perform a backup from the new master and resume streaming, then register themselves again.

I'm wondering if anybody has any experience with this or has some insight to offer.

[Alvaro Miranda]

Hello 

I am an oracle dba by trade and what you describe is a common setup to survive in case of disaster. ( no pun intended )

can you describe your setup? physical, vm, cloud?

this will help to give tier or protection 2 servers with a shared disc, o 2 servers replicating in the same datacenter, etc

in a distributed way,  that seems what you are describing i can share the oracle rdbms way without going to deep what basically works is like this

all servers startup without opening the database

using something called dataguard manager they learn about other servers

they agree one will become a master

the others become a destination of the master

the way you handle a server in split brain is with a self reboot if a server became isolated ( cant see the rest )

if 2 o 3 servers think they are together but the other is isolated, they kick it out of the cluster

here you mix a fence mechanism that can reboot the server, tell the switch to disable the ethernet port, tell the ups to powercycle the box, etc

to add an extra layer of confidence a couple of external machines are setup like clients watching are setup, these are called observers

on non oracle databases, mysql uses drdb and/or they setup multimaster replication

back to postgresql, i would be interested on how this develops, in the same datacenter shouldnt be too hard setup something

additional measure can be setup a virtual ip between the nodes with keepalived . i did a test with consul last weekend and worked like a charm..

will send a follow up email once i get home

--
You received this message because you are subscribed to the Google Groups "Consul" group.
To unsubscribe from this group and stop receiving emails from it, send an email to consul-tool...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.