[Bucardo-general] B4 best practices for unreliable DB connections

[Alexey Klyukin]

On Nov 26, 2012, at 7:43 PM, Greg Sabino Mullane <greg at endpoint.com> wrote:

>> I'm looking for some best practices when running Bucardo 4 swap syncs under bad
>> connectivity, specifically, how to deal with serialization conflicts caused by a
>> combination of large bucardo transactions that accumulate data changes over
>> periods of connectivity loss and high rate of changes on the target DB tables?
>> 
>> It seems that the most efficient way to deal with this is to make Bucardo
>> transactions smaller by using multiple herds/syncs for a single database, but
>> I'd like to learn about other options.
> 
> Well, the two options in B4 is to make smaller herds, or to lock the tables. 
> Bucardo can do this locking itself: you have to create files that Bucardo will 
> look for and do the heavy locking before the sync starts. The format is:
> /tmp/bucardo-force-lock-<syncname>. Hacky, but available if needed. You can 
> also stop other things from accessing the tables until things are caught up 
> by simply turning off/pausing your app, etc.

Aha, thank you, I saw the mentioning of the locking feature before, but thought
about it as an absolute last resort mean, cause it forces the application to
wait. Now that you've mentioned it, I'm thinking about an idea of creating the
lock file automatically in a per-sync exception handler (checking the
{attempts} to do this only if a serialized conflict exception happens more than
predefined number of times), getting a ROW EXCLUSIVE LOCK until the sync
finishes and continuing as normal afterwards. Does it make sense, or sounds
totally crazy?

> 
> However, the large size of the Bucardo deltas was a primary reason for the 
> development of Bucardo 5, which does things in a much more efficient way 
> and is thus much less likely to develop serialization errors, as the syncs 
> now run much, much faster than B4. So the best answer is to try out B5. :)

Good to hear, I'll definitely give it a try in the future!

--
Alexey Klyukin        http://www.commandprompt.com
The PostgreSQL Company – Command Prompt, Inc.