Re: HA and Replication - how to choose among all the available solutions

> On Fri, Nov 30, 2007 at 10:30:47PM +0100, Pascal Cohen wrote:
>
>> I am facing a probably very common problem. I made a search in the
>> recent archives and could find many posts related to my issue. But I did
>> not get exactly "the answer" to my question.
>>
>
> No, and I doubt you will.
>
I don't why I had such a feeling ;)
Thanks for your answer. It was an open question and your answer
concerning Log-Shipping/Slony is very helpful.
>
>> But I don't know how to chose a solution. I don't have much experience
>> and can't see precisely the advantages and the drawbacks of each solution.
>>
>
> That's because, quite frankly, it's hard to answer that question in general.
> You have to answer it under your circumstances. Since you're the one who
> knows those, you'll have to do the answering, I'm afraid. This probably
> means "do some tests".
>
That is what I am currently doing.
My purpose is to study (we are absolutely not in a hurry so I have time
to evaluate all the cases) how to handle:
High-availability and scalability in our environment where there are
many reads and few writes.

* For High-availability, I mainly studied PgPool and Log-shipping (and
in fact forgot Slony).
Until now I feel more comfortable with Log-shipping because it seems
safer (I am not sure I can't get some problems with sequences and
current_timestamp with PgPool - it is even mentioned in the doc).

* For Scalability, I had a look to sharding, and tried to look at
replication/load-balancing.
For load-balancing, I really appreciated PgPool, but again the statement
replication does not make me really comfortable so I studied two ways:
Synchronous replication and asynchronous replication.
I think that synchronous synchronization is a nice to have feature but
absolutely not mandatory. So if I am concerned by scalability, I think
we can avoid overload caused by synchronous solution.
For now I think using PgPool as a load-balancer while Slony handles
Master-Slave replication seems a good solution.
But I will anyway have a look to CyberCluster.

>> And why should I chose Slony instead of Log-shipping to update the slaves ?
>>
>
> This is a good example (and the only thing in your list I feel comfortable
> talking about): if you think that you might find it handy to be able to
> query the replicas, use Slony, because you can't do it with log shipping
> (yet).
>
Do you mean that Log-shipping is well for a Standby server not handling
request while Slony is good with "select" load balancing ?
Why and what limits Log-shipping for that case ?

Thanks for the answer!

Pascal