Replicating postgres database using Debezium

[Ankita Sawant]

Hi Team,

I would like to know how we can handle FK constraints while replicating postgres database using Debezium.
For example, I have two identical databases running in different PostgreSQL servers. When I do inserts in two different tables with FK constraints: Product and Merchant, first I must insert Merchant then Product, how will it be executed by the Debezium connector for PostgreSQL? Will it execute first the parent inserts and then the child?

Debezium doesn't guarantee the order, because it has two different tables and the events will be sent to different Kafka topics, but if it was executed in different orders by the postgres connector then it will result in a Foreign Key constraint Exception.
How we can handle this kind of situation?

Note: We are using Debezium connector for PostgreSQL as Source connector and kafkaJS consumer for consuming change events.

[Chris Cranford]

Hi,

There are several ways.

The most common and obvious would be to route changes for the "product" and "merchant" table to the same topic in Kafka. This topic would also be constrained to a single partition to maintain complete order. On the sink, you would have a single sink that is responsible for reading from this one topic, and because the topic has only 1 partition, the sink will be constrained to only 1 task.  The downside here is you loose concurrency potential to gain the ability to maintain order.

Another alternative might be to use some intermediate post processing step, whether this is in Kafka Streams or using a set of intermediate staging tables in the target database to calculate whether the FK constraint is satisfied before applying the change for the table that has the FK relationship.  This allows you to maintain high throughput and concurrency in Kafka but involves this extra step of "eventual FK constraint resolution".

Chris

--
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 on the web visit https://groups.google.com/d/msgid/debezium/28db3f2f-8ac3-42cf-9f23-e63146758f85n%40googlegroups.com.

[rkerne...@gmail.com]

Some other simple options would be to temporary disable integrity / constraint checks. That's what you do when adding a replica to a cluster for example.

Some options are shown here:

https://www.dbi-services.com/blog/enabling-disabling-and-validating-foreign-key-constraints-in-postgresql/

or there

https://stackoverflow.com/questions/38112379/disable-postgresql-foreign-key-checks-for-migrations

Some require higher db privileges though but that might be easier to achieve than building "golden records", adding expensive pre-processing tasks and building custom sinks to respect transactions borders or processing golden records.

I hope that helps.

Best,

René