Re: Best replication solution?

Andrew Sullivan wrote:
> On Sun, Apr 05, 2009 at 11:36:33AM -0700, Lists wrote:
>
>
>> *Slony-I* - I've used this in the past, but it's a huge pain to work
>> with, caused serious performance issues under heavy load due to long
>> running transactions (may not be the case anymore, it's been a while
>> since I used it on a large database with many writes), and doesn't seem
>> very reliable (I've had replication break on me multiple times).
>>
>
> It is indeed a pain to work with, but I find it hard to believe that
> it is the actual source of performance issues. What's more likely
> true is that it wasn't tuned to your write load -- that _will_ cause
> performance issues.
Can you point me in the direction of the documentation for tuning it? I
don't see anything in the documentation for tuning for write load.

> Of course, tuning it is a major pain, as
> mentioned. I'm also somewhat puzzled by the claim of unreliability:
> most of the actual replication failures I've ever seen under Slony are
> due to operator error (these are trivial to induce, alas --
> aforementioned pain to work with again).
Recently I had a problem with "duplicate key" errors on the slave, which
shouldn't be possible since they keys are the same.
I've just noticed in the documentation that

The Duplicate Key Violation
<http://www.slony.info/documentation/faq.html#DUPKEY> bug has helped
track down a number of rather obscure PostgreSQL race conditions, so
that in modern versions of Slony-I and PostgreSQL, there should be
little to worry about.

so that may no longer be an issue. However I experienced with this the
latest Slony (as of late last year) and Postgresql 8.3.

Also the dupe key error linked appears to be duplicate key of slony
meta-data were as this was a duplicate key of one of my table's primary
key.
> Slony is baroque and
> confusing, but it's specifically designed to fail in safe ways (which
> is not true of some of the other systems: several of them have modes
> in which it's possible to have systems out of sync with each other,
> but with no way to detect as much. IMO, that's much worse, so we
> designed Slony to fail noisily if it was going to fail at all).
>
An error is better than silently failing, but of course neither is optimal.

The slony project could really benefit from a simpler user interface and
simpler documentation. It's integration into pgadminIII is a good step,
but even with that it is still a bit of a pain so I hope it continues to
improve in ease of use.

Being powerful and flexable is good, but ease of use with sensible
defaults for complex items that can be easily overridden is even better.

>
>> *Mammoth Replicator* - This is open source now, is it any good? It
>> sounds like it's trigger based like Slony. Is it based on Slony, or
>> simply use a similar solution?
>>
>
> It's completely unrelated, and it doesn't use triggers. I think the
> people programming it are first-rate. Last I looked at it, I felt a
> little uncomfortable with certain design choices, which seemed to me
> to be a little hacky. They were all on the TODO list, though.
>
>
>> *SkyTools/Londiste* - Don't know anything special about it.
>>
>
> I've been quite impressed by the usability. It's not quite as
> flexible as Slony, but it has the same theory of operation. The
> documentation is not as voluminous, although it's also much handier as
> reference material than Slony's (which is, in my experience, a little
> hard to navigate if you don't already know the system pretty well).
>
> A
>
>
Thanks, I'll look into both of those as well.