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.

[hyojin lee]

Hi Chris,

Thank you for the detailed recommendations!

After reviewing the options, I decided to implement a simplified architecture using a Flag column (msk_flag) and Triggers to avoid the complexity of developing a custom SMT. This approach effectively prevents infinite loops while achieving bidirectional synchronization.

Here is a summary of the implemented data flow:

1. Forward Flow (Source -> Target):

   • Any change in the Source DB is published to MSK Topic ①.

   • When the Sink Connector applies this change to the Target DB, it explicitly sets the msk_flag to FALSE. This signifies the end of the replication cycle for that event.

2. Reverse Flow (Target -> Source):

   • If a user or service on the Target side needs to push an update back, they must set msk_flag to TRUE.

   • A DB Trigger on the Target table detects the TRUE status and records the change into a separate log table (_dml_msk).

   • This log is published to MSK Topic ②, which then flows into a queue table (_dml_queue) on the Source side. A final trigger on the Source DB applies the data back to the main table.

3. Loop Prevention Logic:

   • The infinite loop is avoided because the Sink Connector always forces msk_flag to FALSE during its write operation. Since the reverse flow trigger only activates when the flag is TRUE, the "echo" back to the source is never triggered by replicated events.

This allows us to handle partial column ownership (A~F for Source, G for Sink) without the risk of circular updates.

Thanks again for your support! It helped me refine the final design.

Best regards, Hyojin

2026년 1월 6일 화요일 PM 6시 39분 36초 UTC+9에 Chris Cranford님이 작성: