[Bucardo-general] No automatic kick/ping after onetimesync
Markus Wollny
markus.wollny at gmail.com
Fri Jun 26 16:38:53 UTC 2015
Hi,
I'm currently stuck with a bucardo replication that is working when
replicating tables from database A to B, but not the other way round. Syncs
that go from B to A are doing the onetimecopy and also work when being
manually kicked, but the NOTIFY issued by the row level trigger on changes
to the source doesn't actually trigger a kick.
Here are the details and what I've tried to far:
I'm using Bucardo 4.99.5 from Debian Wheezy. I don't wish to roll my own
and worry about manual upgrades, so moving to 5.x is not on the table at
the moment.
Bucardo is running on the same machine as the PostgreSQL 9.2 cluster.
Replication is between databases on this cluster only, so no network issues
here. Bucardo is already running fine with several sycs from one of our
databases named CBOX to another database named MAGAZINE.
Now I tried to set up two syncs that would go in the opposite direction,
i.e. from the source db magazine to the target cbox. This doesn't work as
expected, although I am at a complete loss as to why, as I've kept strictly
to the very same procedures for setting up the original syncs and there are
no overlaps between herds/tables that are being synced in either direction.
What's more mysterious: The original sync (onytimecopy/fullsync) is working
fine, but the deltas aren't being replicated automatically, I actually need
to use the bucardo commandline tool to kick the sync - if I do that, the
deltas are being pushed to the target. I can tell the the trigger is
issueing the NOTIFY for the kick alright, but there seems to be noone
listening.
Here's what I did so far:
Table, herd, dbgroup and sync are created from scratch here, so no updating
anything that already exists:
bucardo add table public.entity_default_article db=magazine
herd=various_mag_to_cbox
bucardo add dbgroup various_mag_to_cbox_sync magazine:source cbox:target
bucardo add sync various_mag_to_cbox_sync herd=various_mag_to_cbox
dbs=various_mag_to_cbox_sync onetimecopy=1
Then I restarted bucardo, onetimecopy was working fine, the target table
contains the same data as the source.
When I add a row to the source, wait a while, the target doesn't show it.
When I delete a row or update anything, the same applies. When I do
bucardo kick various_mag_to_cbox_sync
all the changes immediately show up on the target.
bucardo validate sync various_mag_to_cbox_sync
Validating sync various_mag_to_cbox_sync...DONE!
# bucardo status sync various_mag_to_cbox_sync
======================================================================
Last good : Jun 26, 2015 17:56:56 (time to run: 1s)
Rows deleted/inserted : 1 / 1
Sync name : various_mag_to_cbox_sync
Current state : Good
Source herd/database : various_mag_to_cbox / magazine
Tables in sync : 1
Status : active
Check time : none
Overdue time : 00:00:00
Expired time : 00:00:00
Stayalive/Kidsalive : yes / yes
Rebuild index : No
Ping : yes
Onetimecopy : No
Post-copy analyze : Yes
Last error: :
======================================================================
DB magazine:
INSERT INTO entity_default_article (entity_id, article_id, site_id) VALUES
(258315, 1113736, 445);
INSERT 0 1
DB CBOX:
SELECT * FROM entity_default_article WHERE entity_id=258315 AND site_id=445;
entity_id | article_id | site_id
-----------+------------+---------
(0 rows)
# bucardo status sync various_mag_to_cbox_sync
======================================================================
Last good : Jun 26, 2015 17:56:56 (time to run: 1s)
Rows deleted/inserted : 1 / 1
Sync name : various_mag_to_cbox_sync
Current state : Good
Source herd/database : various_mag_to_cbox / magazine
Tables in sync : 1
Status : active
Check time : none
Overdue time : 00:00:00
Expired time : 00:00:00
Stayalive/Kidsalive : yes / yes
Rebuild index : No
Ping : yes
Onetimecopy : No
Post-copy analyze : Yes
Last error: :
======================================================================
# bucardo kick various_mag_to_cbox_sync
Kicked sync various_mag_to_cbox_sync
# bucardo status sync various_mag_to_cbox_sync
======================================================================
Last good : Jun 26, 2015 18:19:38 (time to run: 1s)
Rows deleted/inserted : 0 / 1
Sync name : various_mag_to_cbox_sync
Current state : Good
Source herd/database : various_mag_to_cbox / magazine
Tables in sync : 1
Status : active
Check time : none
Overdue time : 00:00:00
Expired time : 00:00:00
Stayalive/Kidsalive : yes / yes
Rebuild index : No
Ping : yes
Onetimecopy : No
Post-copy analyze : Yes
Last error: :
======================================================================
DB cbox:
SELECT * FROM entity_default_article WHERE entity_id=258315 AND site_id=445;
entity_id | article_id | site_id
-----------+------------+---------
258315 | 1113736 | 445
(1 row)
When I look at the bucardo-log, there's something obviously strange going
on - the database magazine is both source and target:
(3486) [Fri Jun 26 17:56:22 2015] MCP Activating sync
"various_mag_to_cbox_sync"
(3486) [Fri Jun 26 17:56:22 2015] MCP Running validate_sync on
"various_mag_to_cbox_sync"
(3486) [Fri Jun 26 17:56:22 2015] MCP Inspecting source table
"public.entity_default_article" on database "magazine"
(3486) [Fri Jun 26 17:56:22 2015] MCP Inspecting target table
"public.entity_default_article" on database "magazine"
bucardo list all shows that the configuration is quite clear:
Database group: various_mag_to_cbox_sync Members: cbox:target
magazine:source
Database: cbox Status: active Conn: psql -p -U bucardo -d cbox -h
127.0.0.1
Database: magazine Status: active Conn: psql -p -U bucardo -d magazine
-h 127.0.0.1
Herd: various_mag_to_cbox DB: magazine Members:
public.entity_default_article
Used in syncs: various_mag_to_cbox_sync
Sync: various_mag_to_cbox_sync Herd: various_mag_to_cbox [Active]
DB group various_mag_to_cbox_sync: cbox (target) magazine (source)
Table: public.entity_default_article DB: magazine PK:
entity_id|article_id|site_id (int4|int4|int4) Syncs:
various_mag_to_cbox_sync
The same thing happens for another sync with other tables but the same
direction, i.e. from magazine to cbox, whereas everything else just works
fine. The initial fullsync is no problem, the target table is initially
populated alright. Manual kicks are working, too, but the NOTIFYs issued by
the triggers don't seem to get any attention.
Does anybody have any idea what I could try to get bucardo working in both
directions? Thank you!
Kind regards
Markus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20150626/06b6ee14/attachment.html>
More information about the Bucardo-general
mailing list