currently I’m trying to synchronise data from one Postgres table to another PG table using Debezium source and jdbc sink connectors. I have column with ENUM data type on both source and sink tables. When ENUM type column data transferred from source database to Kafka via Debezium it has been transformed into string and writing on to the topic.
And then the jdbc sink connector cannot convert them back to enum . So are there any ways to migrate enum type from a postgres to another?
I am using JSON schema converter and I also tried with AVRO .But facing similar issue in both connectors .
I’m afraid the limitation here is in the internal data types supported by Kafka Connect. Regardless of how Debezium reads the data from the Postgres table, it needs to create an internal representation of the schema using what Kafka Connect supports, which is dictated by the Schema class. The following data types are supported:
INT8, INT16, INT32, and INT64
FLOAT32 and FLOAT64
BOOLEAN
STRING
BYTES
ARRAY
MAP
STRUCT
Debezium is transforming this into a STRING in the first place, according to its basic data types rules. This makes sense as Kafka Connect does not support enums. Therefore, your best bet would be writing a custom converter to handle this edge case along with the JDBC sink connector.
One possible workaround would be to define a DB function to cast string to enum.
That way even if the sink connector passes the field as a string, your function would cast it back to enum. At least that’s the high level idea. I’m not sure whether in practice that will work in the case of string->enum in Postgres. I know a similar problem (string->jsonb) can be solved that way. See Support for JSON and JSONB in Postgres Sink · Issue #643 · confluentinc/kafka-connect-jdbc · GitHub
