Strategy for Bidirectional Replication with Split Column Ownership (PG & Oracle)

[hyojin lee]

Hi Debezium community,

I have a question regarding a bidirectional replication scenario between PostgreSQL and Oracle (covering PG<->Oracle, PG<->PG, and Oracle<->PG).

I am trying to implement a topology where column ownership is split between the source and the sink side to avoid full conflict resolution, but I am concerned about infinite loops.

Here is the specific scenario:

1. Table Structure: Both Source and Sink tables have columns ColA through ColG.

2. Source to Sink Flow: The source application updates ColA ~ ColF. These changes are replicated to the Sink.

3. Sink Side Logic: A specific service on the sink side only updates ColG.

4. Sink to Source Flow: The update on ColG at the Sink needs to be replicated back to the Source's ColG.

My Questions:

1. Is this bidirectional pattern (split column ownership) achievable with Debezium source/sink connectors?

2. Loop Prevention: Since the update of ColG on the Sink will generate a change event, how can I prevent the "echo" or infinite loop where this event travels back to the Source, and potentially triggers another update for ColA~ColF?

3. Are there any specific configurations (e.g., using SMTs like Filter, or checking transaction metadata) recommended for this partial update scenario?

Thanks in advance for your help!

[Chris Cranford]

Hi -

Yes the ownership can be achieved but it requires specialized configurations for it to work. 

    1. Both incoming/outgoing events from both sides need to include the primary key columns.
    2. Source To Sink flow, the producer would use `column.exclude.list` to exclude non-primary key column ColG (Source owns all but ColG).
    3. Source To Sink flow, the producer would set `skip.messages.without.change` to `true`.
    4. Source To Sink flow, the consumer would set `schema.evolution` as `none`.
    5. Sink To Source flow, the producer would use `column.exclude.list` to exclude non-primary key columns that are not ColG (Sink owns ColG).
    6. Sink To Source flow, the producer would set `skip.messages.without.change` to `true`.
    7. Sink To Source flow, the consumer would set `schema.evolution` as `none`.

The column.exclude.list omits the columns from that specific producer's payload that it doesn't own. For the source that is ColG, for the Sink that would be columns A-F. The `skip.messages.without.change` makes sure that there are minimal feedback loop issues, but it won't solve it 100%, see below.

The loop issue can be solved natively by Debezium but only if you have a well defined data flow where inserts and deletes originate from the source only. If that's the case, then on the sink side's producer, you can set `skipped.operations` with a value of `cdt` to skip emitting creates, deletes, and truncate events. This will only allow updates to flow back to the source from the sink side of the topology and the bidirectional loop concerns should be dealt with.

However, if rows can originate from either side of the topology, I'm afraid you will need a custom SMT to handle filtering the events based on some value that triggers dropping events that originate from one side, written to the target, and that write triggers and send back to the origin side of the flow.

Hope that helps.
-cc

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/9414efa8-1c43-4f87-b7e6-492f0a09cfc6n%40googlegroups.com.