[Bucardo-general] DBGroup fail - insert or update on table "sync" violates foreign key constraint "sync_targetdb_fk"

Jonathan Brinkman jb at blackskytech.com
Thu Aug 29 02:10:13 UTC 2013


Hi there
I am still having a problem using DBGroup to clone 2 push-delta syncs.
I set it up like instructed, but am getting this error on creating the sync:
Failed to add sync: DBD::Pg::st execute failed: ERROR:  insert or update on
table "sync" violates foreign key constraint "sync_targetdb_fk"
DETAIL:  Key (targetdb)=(fgcc_gates) is not present in table "db". at
/usr/local/bin/bucardo_ctl line 3362.

But "fgcc_gates" IS present in the DBgroup table, and there are 2 rows in
the dbmap table whose db fields match the db names in the DB table
precisely.

Here is my exact build-process up to the "add sync" where it breaks:
bucardo_ctl add all tables herd=cmsfgcc_main_to_gate db=cmsfgcc_main
bucardo_ctl add all sequences herd=cmsfgcc_main_to_gate db=cmsfgcc_main
psql -U bucardo bucardo -c "delete from herdmap where goat not in ( select
id from bucardo.goat where tablename IN 
(
'admin_XYP',
TABLES & SEQUENCES HERE
)) ;"

psql -U bucardo bucardo -c "INSERT INTO dbgroup (name, cdate) VALUES
('fgcc_gates', NOW()); "

psql -U bucardo bucardo -c "INSERT INTO dbmap (db, dbgroup, priority, cdate)
VALUES ('cmsfgcc_gate1', 'fgcc_gates', 1, NOW()),('cmsfgcc_gate2',
'fgcc_gates', 1, NOW()); "

bucardo_ctl add sync cmsfgcc_pushdelta_main_to_gates
source=cmsfgcc_main_to_gate targetdb=fgcc_gates type=pushdelta


I must be missing something here.
THANK YOU!!!
J


-----Original Message-----
From: Michelle Sullivan [mailto:michelle at sorbs.net] 
Sent: Saturday, August 10, 2013 8:24 AM
To: Jonathan Brinkman
Cc: bucardo-general at bucardo.org
Subject: Re: [Bucardo-general] duplicate key value violates unique
constraint "bucardo_sequences_tablename"

Jonathan Brinkman wrote:
> That's clever, thank you.
> I am using 4.5.0
> How did you create the DBGroup with bucardo_ctl?
> There are 4 syncs total, 2 are push from Master to the slaves as 
> discussed, and 1 push-delta sync comes from each slave to Master. 
> Those other 2 (from slave to master) work fine.
>   

I didn't use bucardo_ctl (as I said) I did it in the DB...

psql -U bucardo bucardo will get you in there...

Then something like this shoudl work:

BEGIN;
INSERT INTO dbgroup (name, cdate) VALUES ('mydbseqsyncgrp', NOW()); INSERT
INTO dbmap (db, dbgroup, priority, cdate) VALUES ('cmsfgcc_gate1',
'mydbseqsyncgrp', 1, NOW()),('cmsfgcc_gate2', 'mydbseqsyncgrp', 1, NOW());
COMMIT;

Then add your sync with:

bucardo_ctl add sync cmsfgcc_pushdelta_gates_to_main source=cmsfgcc_main
targetdb=mydbseqsyncgrp type=pushdelta



... However, just re-reading your setup (I only quickly skimmed it at
first)... have you checked it's right...?

bucardo_ctl add sync cmsfgcc_pushdelta_gate2_to_main
source=cmsfgcc_gate2_to_main targetdb=cmsfgcc_main type=pushdelta
bucardo_ctl add sync cmsfgcc_pushdelta_gate1_to_main
source=cmsfgcc_gate1_to_main targetdb=cmsfgcc_main type=pushdelta


Both your targetdbs are the same... that doesn't look right ... perhaps you
meant to add them the other way around where the source is cmsfgcc_main and
the targets are to be gate1 and gate2...?

Regards,

Michelle

> -----Original Message-----
> From: Michelle Sullivan [mailto:michelle at sorbs.net]
> Sent: Saturday, August 10, 2013 7:48 AM
> To: Jonathan Brinkman
> Cc: bucardo-general at bucardo.org
> Subject: Re: [Bucardo-general] duplicate key value violates unique 
> constraint "bucardo_sequences_tablename"
>
> I ran into this very problem a couple of days ago.
>
> The work around I found is to delete the syncs, then create a DBGroup 
> with the 2 slaves in it, and add a single sync with the DB group as the
target.
>
> If you have other seperate syncs to the 2 slaves it's a good idea to 
> do the same with them, but if you can't you can create this setup 
> manually by manipulating the bucardo config tables (I don't know if 
> you can do it with Bucardo_ctl as I didn't try.)
>
> NB: I'm working with 4.5.0, I have no idea whether this will work with
> 4.99.7 (but it *should*)
>
>   


--
Michelle Sullivan
http://www.mhix.org/




More information about the Bucardo-general mailing list