Re: [Postgres-xc-general] Multi-master cluster, high-availability and recovery

[Michael P. <mic...@gm...>]

Hi,

On Wed, Aug 22, 2012 at 10:45 PM, Dominick Rivard <dr...@da...>wrote:

> I am looking for a cluster solution with postgresql DBMS. I've implemented
> a MySQL clustering solution using the replication****
>
> engine from Codership.com called Galera in the past few months. This
> engine allows us to work with 3 MySQL nodes in master-master setup. The 3
> nodes are behind an Haproxy load-balancer which is also redundant using
> freebsd and pfsync.
>
OK, so I can imagine that all the MySQL nodes are synchronously or
asynchronously replicated. Based on your description it doesn't seem that
you are doing any data sharding (distribution of data among nodes).
Do you do write operations on all your nodes simultaneously?
If not, you might also be able to use a normal PostgreSQL server with 1
master and 2 slaves, and concentrate all the writes to the master.
Well, if your application is not able to concentrate all the writes on a
single master, XC might be a good candicate.


> ****
>
>  So unless our two haproxy and our three mysql servers fails at the same
> time we should be
>
> able to remain operational at all times.
>
Yes that should make it indeed. I would even imagine that even 2 nodes
could be enough.

What we want to accomplish here is to have a solution with the least
>
> downtime even if a breakage of one of the nodes happen whitin the cluster.
> In addition, I can write and read from any****
>
> of my MySQL servers.
>
Please forget what I wrote above... A single master is not suited for your
configuration.


> I use a DNS solution on this cluster to insert more than 20 million
> request per day and until now
>
> the production servers are doing great. If ever a server fails, the load
> balancer directs****
>
> read / write on the other two servers, and when it comes back online the
> other two servers copy****
>
> their data on the newcomer and after the copy is completed, it is made
> available back to the pool of the load-balancer****
>
> and all this automatically. This means that only the repair is manual.
>
****
>
> ** **
>
> I would like to reuse my haproxy load-balancers and accomplish the same
> goals that I filled with MySQL Cluster****
>
> using a postgresql solution. Why do I implement a cluster solution again
> with postgresql? Because we use the****
>
> OpenNMS solution which is a solution for monitoring network devices that
> monitor more than 25000 nodes and****
>
> which will be available for our customers at all times. SLAs will be very
> short. High availability is the goal that I want to****
>
> achieve. Plus OpenNMS supports postgresql only. I think after my first
> reading of postgres-xc project it could ****
>
> give me this ability.
>
In the case of XC, replication or distribution is table-based, meaning that
in your case what you would like to achieve is not
scalability but HA only. If you use XC for your application, well you will
have to replication all the tables.


> ** **
>
> I do not have many inserts to do comparatively to the mysql cluster. I
> think two servers each running a coordinator****
>
> and a data storage would be sufficient for the moment.
>
Yes a Coordinator and a Datanode on each server would be enough.
But you are going to need Datanode and Coordinator slaves also in case of
failure of one of your nodes to be able to recover your cluster quickly in
case of a node failure.
You could do that with 2 servers.
- Server 1: Coordinator 1, Datanode 1, Coordinator 2 slave, Datanode 2 slave
- Server 2: Coordinator 2, Datanode 2, Coordinator 1 slave, Datanode 1 slave
With such a configuration even if one of your servers is completely out you
will be able to save your cluster data.
It is possible to monitor the nodes with the same tools as PG-pool 2 or
Postgreslike Pacemaker.
Such as in case of failure if one of your node fails you can detect the
failure quickly and promote a slave node to become a slave.

Could you explain or point me to the right documentation
>
> so I can understand how the cluster is in case of failure of one server to
> know how to handle the recovery****
>
> data. I also wonder if you test the installation on debian squeeze,
> because all our production is on debian****
>
> squeeze and so I would avoid having to explain a new distributions to our
> sysadmins team.
>
There are some debian packages based on 1.0.0 which are available:
http://packages.debian.org/sid/database/postgres-xc


> ****
>
> Do you think postgres-xc could help me meet my needs?
>
Yes it might. XC can be a powerful solution for your tests, if put in good
hands.
I would advise you to fully understand XC structure first with some general
documents like the ones here:
https://sourceforge.net/projects/postgres-xc/files/Presentation/
https://sourceforge.net/projects/postgres-xc/files/Publication/

Regards,
-- 
Michael Paquier
http://michael.otacoo.com