bucardo
Failed to add sync, cannot delete from table "bucardo_delta_targets" because it does not have a replica identity and publishes deletes
bucardo add sync syncrds2aurora1 relgroup=alltable1 dbs=sourcerds1:source,targetaurora1:target onetimecopy=1 --verbose
WARNING: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Pg::db handle dbname=xxy;host=ayayai.us-west-2.rds.amazonaws.com;port=5432 at line 833.
Failed to add sync: DBD::Pg::st execute failed: ERROR: DBD::Pg::db do failed: ERROR: cannot delete from table "bucardo_delta_targets" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE. at line 833. at line 30.
CONTEXT: PL/Perl function "validate_sync" at /usr/local/bin/bucardo line 4670.
What's the possible cause of this?
Setting "wal_level" to "logical" @kokizzu It's used to enable PUBLICATIONS and SUBSCRIPTIONS, for primary -> secondary replication.
If you set up two local databases, and sync them using bucardo sync, it'll show the same error message if you had set wal_level=logical at your postgresql.conf.
But, since you're using this to sync between RDS and Aurora (based on your elements names), you probably can't control that file.
Perhaps patching bucardo to set REPLICA IDENTITY FULL on that table might fix it, not sure.
@kokizzu should I patch /usr/share/perl5/Bucardo.pm or /usr/bin/bucardo? I didn't find what is needed to patch.
only tried symetricsds but too lazy to continue then i just do quick dump and restore
pg_dump -U xxx -h xxx-service-dms-test-xxx.xxx.us-west-2.rds.amazonaws.com xxx |
pv |
psql -U xxx -h localhost -p 15432 xxx # this the local tunnel to other database
I am seeing a similar issue:
WARNING: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Pg::db handle dbname=xxxxx;host=xxxxx;port=5432 at line 833.
Failed to add sync: DBD::Pg::st execute failed: ERROR: DBD::Pg::db do failed: ERROR: cannot delete from table "bucardo_delta_targets" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE. at line 833. at line 30.
CONTEXT: PL/Perl function "validate_sync" at /usr/local/bin/bucardo line 4670.
My source db has wal_level=logical and my target db has wal_level=replica. Is that correct?
hi, we are investigating to use Bucardo and have stumbled across the same issue. Is there a quick-around solution to fix it?
I started to use rubyrep17:47, 9 декабря 2021 г., Soroush Atarod @.***>: hi, we are investigating to use Bucardo and have stumbled across the same issue. Is there a quick-around solution to fix it?
—You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or unsubscribe.Triage notifications on the go with GitHub Mobile for iOS or Android.
-- Vladimir Krõlov
I moved to just using native postgres logical replication with pglogical. It works great.
Some references:
- https://cloud.google.com/sql/docs/postgres/replication/configure-external-replica
- https://www.alibabacloud.com/help/doc-detail/254763.htm
Hello, i have this issue. can anyone help me?
Failed to add sync: DBD::Pg::st execute failed: ERROR: new row for relation "sync" violates check constraint "sync_name_sane"
DETAIL: Failing row contains (dbsync, herd_test, grp1, t, t, bucardo_latest, , delete, t, null, active, 0, 0, t, f, t, 00:00:00, 00:00:00, f, 0, null, 0, null, 2022-04-13 09:31:56.39336+00). at /usr/bin/bucardo line 4670.
Had the same issue since there still was an active publication (for all tables) on the Database. After deleting the Publication the problem is gone.
