[Bucardo-general] ERROR: duplicate key value violates with master master replication

[Chris Keane]

We have a very complex source-source-source-source... setup that uses
sequences for indexes.
The solution we use is to use bigserial (so the sequence is created with
bigint) then on each master we set the initial value of the sequence to
have a unique prefix for that server.
For example, on server 15 we might set all the sequences to have a start
value of 1500000000000001 and a max value of 1599999999999999. So it
allocates all IDs for that table from that range, and server 16 does the
same thing but with sequences starting with 16.... That means that we never
get an overlapping primary key AND we can also tell at a glance which
server a specific record originated from.

We don't replicate the sequences at all. They are completely unique for
each server. Based on the size of a bigint, using this scheme we can have
64k servers before we run out of server prefixes, a problem that would be
nice to have but somewhat unlikely ;)

Chris.



On Tue, May 13, 2014 at 11:48 PM, Juned Khan <jkhan6722 at gmail.com> wrote:

> I think this problem is because i am not replicating sequences so
> sequences are not updating and as result when i am trying to add rows from
> another master its trying to start from first. in that case its giving
> duplicate error.
>
> So i think i should check with adding sequence too. it doesn't work and i
> can go with odd even sequence solution.
>
>
> On Wed, May 14, 2014 at 12:11 PM, Rosser Schwarz <rosser.schwarz at gmail.com
> > wrote:
>
>> On Tue, May 13, 2014 at 11:13 PM, Juned Khan <jkhan6722 at gmail.com> wrote:
>>
>>> Thanks for the answer, if i use this command to add all tables then
>>> sequence will be added automatically for replication ?
>>>
>>>> bucardo add all tables db=master1 herd=mherd
>>>
>>>
>> That command will only add *tables* to the things Bucardo knows to
>> replicate. The bucardo command also has an "add all sequences" operation,
>> which you should not perform for your scenario.
>>
>> with live database whenever first master will go down then second master
>>> db will be used for write process, how do i manage that with sequence ?
>>>
>>
>> Both databases should have all of your tables and sequences, but Bucardo
>> should be configured to replicate only the tables.
>>
>> On db1, you then set the sequences to issue only *odd* numbers. ("ALTER
>> SEQUENCE foo START 1 INCREMENT 2;" — produces 1, 3, 5...)
>>
>> On db2, you set them to issue only *even* numbers. ("ALTER SEQUENCE foo
>> START 2 INCREMENT 2;" — produces 2, 4, 6...)
>>
>>
>>
>> --
>> :wq
>>
>
>
>
> --
> Thanks,
> Juned Khan
> iNextrix Technologies Pvt Ltd.
> www.inextrix.com
>
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>
>


-- 
*Chris Keane* * Track Intelligence Inc *  +1 (650) 703 5523 (cell)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20140514/bc47a74c/attachment.html>