[Bucardo-general] Replication isn't working and status all gives a persistent error that doesn't match the state of the replicated databases

David Christensen david at endpoint.com
Thu Feb 8 14:42:32 UTC 2018


Hi Jeff,

The Pg error is presumably correct; is it possible there are multiple tables named such (different search_path, say)?  I’d expect that if so it’s just trying to copy rows which have already been populated (via onetimecopy or similar, though it looks like that’s not set, per the sync status you report.)

It’s also possible that if the indexes on that table were corrupt that it would report 0 rows, or it would return multiple row versions (even with PK); you might try to COPY data from the table in question just to an external file to examine instead of SELECT *, or use some of the `enable_*` flags to turn off using indexes in your transaction when using `SELECT *` to verify.

Best,

David

> On Feb 7, 2018, at 4:01 PM, Jeff Silverman <jsilverman at blispay.com> wrote:
> 
> Hi. We have a master/slave replication set up. It was working well until this morning, when I added some new tables. The error I'm getting makes no sense to us though.
> 
> 
> # bucardo status all
> ======================================================================
> Last good                : Feb 07, 2018 16:05:04 (time to run: 1s)
> Rows deleted/inserted    : 1 / 2
> Last bad                 : May 08, 2017 16:57:40 (time until fail: 10s)
> Sync name                : oltpdb_to_olapdw_sync
> Current state            : Good
> Source relgroup/database : oltpdb_relgroup / oltpdb
> Tables in sync           : 143
> Status                   : Active
> Check time               : None
> Overdue time             : 00:00:00
> Expired time             : 00:00:00
> Stayalive/Kidsalive      : Yes / Yes
> Rebuild index            : No
> Autokick                 : Yes
> Onetimecopy              : No
> Post-copy analyze        : Yes
> Last error:              : Failed : DBD::Pg::db pg_putcopyend failed: ERROR:  duplicate key value violates unique constraint "merchant_fraud_data_merchant_id_key"
> DETAIL:  Key (merchant_id)=(d649d732-cd1e-47db-b327-b5f190259866) already exists.
> CONTEXT:  COPY merchant_fraud_data, line 1 at /usr/local/share/perl/5.18.2/Bucardo.pm line 9879. Line: 5041 Main DB state: ? Error: none DB olapdb state: 23505 Error: 7 DB oltpdb state: ? Error: none  (KID 14206)
> ======================================================================
> 
> 
> The error "Key (merchant_id)=(d649d732-cd1e-47db-b327-b5f190259866) already exists" doesn't make sense for several reasons:
> 
> 1) The tables I added this morning do not have this column, or this constraint
> 2) The table that is actually mentioned, 'merchant_fraud_data', has zero rows in both master and slave
> 3) When I removed the constraint from the slave, i.e. ALTER TABLE...DROP CONSTRAINT..., and tried to resume bucardo sync, the error persists
> 4) The value of this key isn't anywhere in the master database. (i.e. this key is a FK for the 'merchants' table but that table does not have a row with that PK)
> 
> How can I clear this error? Or otherwise convince bucardo that everything is ok?
> 
> Thank you,
> Jeff
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/bucardo-general

--
David Christensen
End Point Corporation
david at endpoint.com
785-727-1171



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20180208/78673842/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20180208/78673842/attachment.sig>


More information about the Bucardo-general mailing list