Debezium 2.3.1 PostgreSQL增量快照DateTimeParseException问题求助

问题描述 #

使用Debezium 2.3.1处理PostgreSQL 13.15数据库的增量快照时遇到异常，快照阶段触发DateTimeParseException，错误提示无法解析文本'f'，堆栈信息指向日期时间解析逻辑。

连接器配置 #

{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "connector.displayName": "PostgreSQL",
  "database.user": "docker",
  "database.dbname": "exampledb",
  "transforms": "unwrap",
  "database.server.name": "localhost",
  "heartbeat.interval.ms": "60000",
  "database.port": "5432",
  "plugin.name": "pgoutput",
  "slot.max.retries": "10",
  "schema.include.list": "public",
  "slot.retry.delay.ms": "15000",
  "heartbeat.action.query": "INSERT INTO public.debezium_heartbeat VALUES ('debezium', now())",
  "decimal.handling.mode": "string",
  "database.hostname": "postgres",
  "database.password": "docker",
  "transforms.unwrap.drop.tombstones": "false",
  "signal.data.collection": "public.debezium_signal",
  "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
  "table.include.list": "public.table, public.debezium_signal",
  "max.batche.size": "65000",
  "max.queue.size": "275000",
  "incremental.snapshot.chunk.size": "6500",
  "connector.id": "postgres",
  "topic.prefix": "db-prod"
}

涉及表的DDL #

CREATE TABLE IF NOT EXISTS public.table
(
    id bigint NOT NULL DEFAULT nextval('table_id_seq'::regclass),
    federal_tax_id character varying(255) COLLATE pg_catalog."default",
    external_id character varying(255) COLLATE pg_catalog."default",
    first_name character varying(255) COLLATE pg_catalog."default",
    last_name character varying(255) COLLATE pg_catalog."default",
    email character varying(255) COLLATE pg_catalog."default",
    phone character varying(255) COLLATE pg_catalog."default",
    cellphone character varying(255) COLLATE pg_catalog."default",
    address_street character varying(255) COLLATE pg_catalog."default",
    address_street_number character varying(255) COLLATE pg_catalog."default",
    address_complement character varying(255) COLLATE pg_catalog."default",
    address_city_district character varying(255) COLLATE pg_catalog."default",
    address_post_code character varying(255) COLLATE pg_catalog."default",
    address_city character varying(255) COLLATE pg_catalog."default",
    address_city_code character varying(255) COLLATE pg_catalog."default",
    address_state_code character varying(255) COLLATE pg_catalog."default",
    address_country character varying(255) COLLATE pg_catalog."default",
    address_latitude numeric,
    address_longitude numeric,
    address_geo geometry(Point,4326) GENERATED ALWAYS AS (st_setsrid(st_makepoint((address_longitude)::double precision, (address_latitude)::double precision), 4326)) STORED,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    is_company boolean NOT NULL DEFAULT false,
    state_tax_id character varying(255) COLLATE pg_catalog."default",
    official_name character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT table_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

复现步骤 #

UPDATE public.table
SET updated_at = current_timestamp
WHERE id = 28948709;
-- 增量快照指令
INSERT INTO debezium_signal (id, type, data)
VALUES (
  'sdadsada-sdasdas-9b45-12608f88b2a6',
  'execute-snapshot',
  '{"data-collections": ["public.table"]}'
);

额外错误与疑问 #

启用Debezium日志后，发现额外错误：无法转换bool类型字段is_company的值，且提示生成列address_geo在结果集中不存在，但该列未注册到Schema Registry。请问address_geo未在Schema Registry中是否会引发此问题？

问题分析与解决建议 #

1. DateTimeParseException解析'f'的根源
   错误中的'f'是PostgreSQL布尔类型的简写（代表false），出现日期解析错误是因为Debezium在快照阶段将布尔字段值错误路由到了日期类型的解析逻辑中。这是字段类型匹配异常导致的，和is_company字段的转换错误直接相关：当Debezium无法正确转换布尔字段时，会干扰整个字段解析流程，触发日期解析异常。

2. address_geo生成列的影响
   address_geo未注册到Schema Registry本身不会直接引发日期解析错误，但提示"结果集中不存在"说明Debezium在快照查询时试图读取该列。由于该列是GENERATED ALWAYS AS STORED类型，若Debezium数据库用户无读取权限，或连接器未正确处理生成列，会导致字段读取失败，进而打乱整个解析流程，间接触发其他字段的类型转换异常。

3. 具体修复步骤

   • 排除生成列：在连接器配置中添加column.exclude.list: public.table.address_geo，明确排除这个生成列，避免Debezium尝试读取它；
   • 验证布尔字段处理：Debezium 2.3.1默认应能正确解析PostgreSQL的't'/'f'布尔值，若仍有问题，可检查is_company字段是否存在异常值（非't'/'f'的字符串），或尝试升级Debezium到2.4+版本（修复了部分PostgreSQL类型转换bug）；
   • 排查转换器影响：暂时移除ExtractNewRecordState转换器配置，验证快照是否正常，逐步确认是否是转换器导致的字段类型混乱；
   • 检查数据库权限：确保Debezium使用的docker用户对public.table表的所有字段（包括生成列）有读取权限，若要排除生成列，也可通过权限限制避免读取，但更推荐用column.exclude.list配置。

内容的提问来源于stack exchange，提问作者Henrique Viana