[Bucardo-general] "duplicate key value violates unique constraint" in sync to RDS

[Olov Pettersson]

Hi!


I'm trying to use bucardo for a db migration from stand-alone postgres to
aws rds, with about 250gb of data.
Since I don't want to overload the current database when setting up the
sync, I take the following approach:

1. Set up the RDS instance
2. Make a sql dump with pg_dump and restore it to rds
3. Set up a sync between the current master postgres and rds using bucardo
with pushdelta to only copy the diffing rows between source and destination

The problem I run into is that bucardo seems to expect the destinaton to be
empty and aborts the sync. Since I set conflict_strategy=bucardo_source
when creating the sync, I would expect bucardo to overwrite the row in the
destination if there is a conflict. The error message is:

(1317) [Thu May  4 08:50:48 2017] KID (the_sync) Warning! Aborting due to
> exception for public.users:? Error was DBD::Pg::db pg_putcopyend failed:
> ERROR:  duplicate key value violates unique constraint
> "index_users_on_email"\nDETAIL:  Key (email)=(xxx at yyy.com) already
> exists.\nCONTEXT:  COPY users, line 1747 at /usr/local/share/perl/5.18.2/B
> ucardo.pm line 9800.
> (1317) [Thu May  4 08:50:48 2017] KID (the_sync) Kid has died, error is:
> DBD::Pg::db pg_putcopyend failed: ERROR:  duplicate key value violates
> unique constraint "index_users_on_email"\nDETAIL:  Key (email)=(
> xxx at yyy.com) already exists.\nCONTEXT:  COPY users, line 1747 at
> /usr/local/share/perl/5.18.2/Bucardo.pm line 9800. Line: 5453 Main DB
> state: ? Error: none DB dest_db state: 23505 Error: 7 DB source_db state: ?
> Error: none
> DBI::db=HASH(0x3db7248)->disconnect invalidates 13 active statement
> handles (either destroy statement handles or call finish on them before
> disconnecting) at /usr/local/share/perl/5.18.2/Bucardo.pm line 2729.
> (1317) [Thu May  4 08:50:48 2017] KID (the_sync) Kid 1317 exiting at
> cleanup_kid. Sync "the_sync" public.users Reason: DBD::Pg::db pg_putcopyend
> failed: ERROR:  duplicate key value violates unique constraint
> "index_users_on_email"\nDETAIL:  Key (email)=(xxx at yyy.com) already
> exists.\nCONTEXT:  COPY users, line 1747 at /usr/local/share/perl/5.18.2/B
> ucardo.pm line 9800. Line: 5453 Main DB state: ? Error: none DB dest_db
> state: 23505 Error: 7 DB source_db state: ? Error: none



Any idea what is going on? Since the wiki states that,

To populate the slave initially, you have two options:
>
>    1. Get the sync running to that delta rows are being saved, then copy
>    the data to the slave via another method, such as an LVM snapshot, or
>    simply a pg_dump.
>    2. Get the master and slaves running, then set onetimecopy
>    <https://bucardo.org/wiki/Onetimecopy>=2 for the sync. This will do a
>    complete COPY of the table from the master to the slaves. Note that this
>    can be set at any time, so if your slaves ever get corrupted, out of sync,
>    etc. you can easily restore them to the master's version. The onetimecopy
>    can be set by running:
>
>
>

I am inclined to try to first set up the sync (after cleaning bucardo delta
tables in source and dest), but not start running it, then restore the dump
and start the sync afterwards in the hopes that bucardo will recognize the
existing rows.
Any help is appreciated!

Best regards
Olov Pettersson
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170508/68911665/attachment.html>