Re: Justifying a PG over MySQL approach to a project

On Thursday 17 December 2009 09:51:19 Richard Broersma wrote:
> On Thu, Dec 17, 2009 at 8:23 AM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com>
wrote:
> > How difficult is it to switch the master's hat from one DB instance to
> > another? Let's say the master in a master-slave scenario goes down but
> > the slave is fine. Can I designate the slave as being the new master,
> > use it for read/write, and then just call the broken master the new slave
> > once it comes back to life (something like that)?

This is very easy with SLONY. If the master is truly 'dead' you can run a
SLONY 'failover' command like this (note these are slonik commands where node
1 is the 'dead' master and node 2 is the current slave):

echo 'Preparing to failover (set id = 1, backup node = 2)';
failover (id = 1, backup node = 2);
echo 'Done';
echo 'Preparing to drop node (set id = 1, event node = 2)';
drop node (id = 1, event node = 2);
echo 'Done';
echo 'Failover complete';

at this point the dead master node is no longer part of the replication
cluster and the slave is the new master

Once the dead node is rebuilt then you simply add it to the replication
cluster as a new slave node

Also, if you just wanted to "move" the master, changing the existing master
into a slave in the process you can do this:

lock set (id = 1, origin = 1);
wait for event (origin = 1, confirmed = 2);
echo 'set locked';
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2);
echo 'switchover complete';

>
> I know someone that uses a revolving Sony Master-Slave setup between
> China and the US. During the US working hour the US server is the
> master, during the working hours of China it becomes the Master.
>
> Of course the person how constructed this system mentioned it was
> woefully challenging. In his case, not only was the slony
> configuration difficult but also finding and keeping stable
> communication path-ways between China and the US.
>