[Bucardo-general] Replication problems? data overwritten?

[Joshua Tolley]

On Fri, Aug 23, 2013 at 02:30:55PM +0000, eric machine wrote:
>    ------------------------------
>    ID | UserID | Project
>    ---------------------------
>    1   | John     | A
>    1   | Susan   | X
>    2   | John     | B
>    2   | Susan   | Y
>    3   | John     | C

The above can't happen, because ID is a primary key, so all values in that
column must be unique, and not null. So you can't have two rows with the same
ID value.

>    or at least this
>    HQ - project table
>    ------------------------------
>    ID | UserID | Project
>    ---------------------------
>    1   | John     | A
>    2   | John     | B
>    3   | John     | C
>    4   | Susan   | X
>    5   | Susan   | Y

This won't work either, because Bucardo would have to change the ID values for
you. It won't do that; a replication system that changes your data wouldn't be
very useful.

>    Note: RubyReps doesn't have this problem. Why? When you enable
>    streaming replication, for dbleft the primary key will be
>    auto-increment based on odd number. For dbright, it will auto increment
>    on even number. Just I can't figure out how-to solve this on bucardo 5.

Bucardo won't set that up for you, but that's exactly what you need to do
here. When you're creating the sequences at first, you'd do something like
this:

    CREATE SEQUENCE some_id_seq INCREMENT BY 2;
...and on the second database...
    CREATE SEQUENCE some_id_seq INCREMENT BY 2 START WITH 2;

Since your sequences are already created, you'll need to change both of them
like this:

    ALTER SEQUENCE whatever_id_seq INCREMENT BY 2;

You'll also need to check the current values of the sequence in both
databases, make sure one is even, one is odd, and both are larger than the
maximum ID value already in the database. You can check the current value in
each database with SELECT * FROM some_id_seq, which gives you something like
this:

josh=# select last_value from b_id_seq ;
 last_value 
------------
         13
(1 row)

Do that on both databases, and take the bigger one. Let's say it's 103. Then
on HQ you'll do this:

    SELECT setval('some_id_seq'::regclass, 104);

... and on Branch, you'll do this:

    SELECT setval('some_id_seq'::regclass, 105);

That way one sequence will generate odd values, and the other will generate
odd values.

-- 
Josh Tolley   josh at endpoint.com   801-987-0252
End Point Corporation  http://www.endpoint.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: Digital signature
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130823/577de5c2/attachment.sig>