Assistance Required: Handling DR Drill Failover and Duplicate Records in ClickHouse

[PraveenBalaji001]

Currently, we are using the Altinity Sink Connector Lite to replicate data from PostgreSQL to ClickHouse.

During Disaster Recovery (DR) drills, the data center may switch from Primary to Secondary, making the Secondary PostgreSQL instance the active one. In such cases, all incoming data is directed to the Secondary database, which was previously in standby mode.

We are looking for best practices or recommended approaches to handle this switchover scenario seamlessly with minimal replication impact. Specifically:

How should we handle replication during a DR drill or failover to ensure data consistency and continuity?

We are noticing a high number of duplicate records in the target ClickHouse database—is there a recommended way to de-duplicate or prevent this issue when using the Altinity sink connector?

Appreciate your guidance or suggestions to address these challenges.

[subkanthi]

Currently, we are using the Altinity Sink Connector Lite to replicate data from PostgreSQL to ClickHouse.

During Disaster Recovery (DR) drills, the data center may switch from Primary to Secondary, making the Secondary PostgreSQL instance the active one. In such cases, all incoming data is directed to the Secondary database, which was previously in standby mode.

We are looking for best practices or recommended approaches to handle this switchover scenario seamlessly with minimal replication impact. Specifically:

How should we handle replication during a DR drill or failover to ensure data consistency and continuity?

We are noticing a high number of duplicate records in the target ClickHouse database—is there a recommended way to de-duplicate or prevent this issue when using the Altinity sink connector?

Appreciate your guidance or suggestions to address these challenges.

When connecting to a Postgres primary (i.e. not a read replica) and the version of Postgres is 17 or later, Debezium will create a replication slot enabled for failover. This means Debezium can continue to read changes from a replica promoted to primary in case of a failure, without missing any events. This requires the state of the slot to be synchronized from primary to replica server; refer to the Postgres documentation for the details.
https://debezium.io/documentation/reference/stable/connectors/postgresql.html

For duplicates please make sure the order by key is selected correctly
https://altinity.com/blog/clickhouse-replacingmergetree-explained-the-good-the-bad-and-the-ugly