Re: [Postgres-xc-general] Move from a single-cluster machine to a multi cluster machine.

[Joseph M. <ma...@wi...>]

What they are looking for in the end is high-availability and load balancing. They want either system to be able to receive read and write requests, and for either one to be able to take over all the work if the other is down. My understanding is that postgres-xc is capable of this, it’s just a matter of getting there from where they are. All of this would be easier if their current database server weren’t running on one of the machines that needs to be in the two-machine cluster when we’re done.

And yes, minimizing downtime is a priority, too.

From: Mason Sharp 
Sent: Thursday, August 21, 2014 4:02 PM
To: Joseph Mays 
Cc: mailto:pos...@li... 
Subject: Re: [Postgres-xc-general] Move from a single-cluster machine to a multi cluster machine.





On Thu, Aug 21, 2014 at 3:04 PM, Joseph Mays <ma...@wi...> wrote:

  Okay, I have a postgres-xc “cluster” operating with only one machine in the cluster. What I want to do is load the database from the standard postgres cluster into it, then switch over to make it operational as the postgres server. Then I want to rebuild the original standard postgres machine to be another postgres-xc cluster machine and add it as a second machine to the cluster.
  There are still several things about this I have to figure out, but one question I don’t know the answer to is how I bring the two machines into “sync” at the database. Is there some way to add the second machine into the cluster and have it synchronize its database to the first machine in the cluster, or do I have to dump the database, get them running in a two machine cluster with a blank database, and the restore the dump into the two machine cluster?
  If the latter, if one machine goes down or something, so that the other is carrying all the load for a bit, how I do bring the two machines back into sync when the failed machine comes back up?

It sounds like you are trying to minimize downtime while you switchover.

If you were running on PostgreSQL 9.4, this may have been easier with the help of logical streaming replication. In the meantime, you may want to use a trigger-based scheme or Slony, where you enable triggers to track the changes, dump the data, load into XC and then play back those changes that were captured.

Regarding adding the other server back, if the load can handle it, you could already define it as a datanode and have data be loaded there. If you have a spare system and recent version of PostgreSQL, you could use streaming replication to it, promote it to being the master, then configure the other two systems.

Regards,


-- 

Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions