[Bucardo-general] Replication problems? data overwritten?

[eric machine]

Hi Josh,

Thanks for replying. Few questions.

a) If I change the sequence in postgres just like that, will that normally affect the overall database design from the open source system? I mean will it crash the system?

b) And when you do this?

CREATE SEQUENCE some_id_seq INCREMENT BY 2;

Will that sequence reflects to all tables automatically? Or just specific table?

I hope it's all tables. After all, the project table is dependent to many other tables too.

c) If I follow your approach on updating the sequences, do I still run this command?

bucardo add all sequences herd=therd

d) Lastly, for this command

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

I assume I run this only once right? 

However for new database from fresh, I can just ignore this. Correct?

e) Can bucardo do streaming replication? I mean always sync when there's data changes?

Looking forward to hear from you soon.

Thank you.

> Date: Fri, 23 Aug 2013 10:16:07 -0600
> From: josh at endpoint.com
> To: eric.machine at hotmail.com
> CC: bucardo-general at bucardo.org
> Subject: Re: [Bucardo-general] Replication problems? data overwritten?
> 
> 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 --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130823/b5caf42b/attachment.html>