Re: [I] [Bug] PostgreSQL-CDC] pg实时同步不支持GEOMETRY 类型 [seatunnel]

[via GitHub]

Tenvie commented on issue #10155:
URL: https://github.com/apache/seatunnel/issues/10155#issuecomment-3615905565

   > > > <img alt="Image" width="944" height="311" 
src="https://private-user-images.githubusercontent.com/200727089/522373980-7af894a9-c39a-4013-b642-ba916d7f8b5f.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NjQ4OTkwMjIsIm5iZiI6MTc2NDg5ODcyMiwicGF0aCI6Ii8yMDA3MjcwODkvNTIyMzczOTgwLTdhZjg5NGE5LWMzOWEtNDAxMy1iNjQyLWJhOTE2ZDdmOGI1Zi5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjUxMjA1JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI1MTIwNVQwMTM4NDJaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT0wMmNmMDZiYTdmODI4ZjFiM2Y4MWZhNDkzZGRiNDE5OTBjZDEyNzUzZjkxMGRjMjVkOWNiYzkwMmViYWE5OWYzJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.XXvoeVR8PDCBH9lAk2J29GFRYQPuuDpOoI-rdxwO3nY";>
   > > > Hello, based on the current check, it seems that the issue is that the 
sink side does not support the GEOMETRY type. It should be resolved simply by 
changing the data type on the sink side to string. As can be seen from the 
figure above, the data type mapping is GEOMETRY --> string. It is recommended 
to set the corresponding type on the sink side to string and have a try.
   > > 
   > > 
   > > After changing to string type, the synchronized data is incorrect, and 
non-CDC PostgreSQL synchronization of the GEOMETRY type works normally.
   > 
   > If possible, could you also please provide detailed information? For 
example, after modifying "sink" to "string" (or similar), what the content 
should be and what the actual content is—could you provide detailed 
desensitized data and table creation statements? Does "non-CDC PostgreSQL" 
refer to the offline pgsql2pgsql? Is this normal? Could you provide the 
desensitized pipeline?
   config：
   {
     "env" : {
       "job.mode" : "STREAMING",
       "parallelism" : 1,
       "checkpoint.interval" : 10000
     },
     "source" : [ {
       "result_table_name" : "10f5ae39c4f60772f92bc9245c8fd010",
       "plugin_name" : "Postgres-CDC",
       "base-url" : "jdbc:postgresql://ip:port/test",
       "username" : "postgres",
       "password" : "123456",
       "database-names" : [ "test" ],
       "table-names" : [ "test.public.com_info_ue_data" ],
       "startup.mode" : "initial",
       "stop.mode" : "never",
       "slot.name" : "95232ec01d144d6488f5e4314c88680a",
       "server-time-zone" : "Asia/Shanghai"
     } ],
     "sink" : [ {
       "schemaSaveMode" : "CREATE_SCHEMA_WHEN_NOT_EXIST",
       "dataSaveMode" : "APPEND_DATA",
       "primary_keys" : null,
       "update_pass_key" : false,
       "index_sql" : null,
       "source_table_name" : "10f5ae39c4f60772f92bc9245c8fd010",
       "plugin_name" : "Jdbc",
       "url" : "jdbc:postgresql://ip:port/test_ben",
       "driver" : "org.postgresql.Driver",
       "user" : "postgres",
       "password" : "123456",
       "database" : "test_ben",
       "table" : "public.ods_com_info_ue_data",
       "generate_sink_sql" : true
     } ]
   }
   table-sql:
   CREATE TABLE "public"."com_info_ue_data" (
     "geom" "public"."geometry",
     "shape_leng" float8,
     "社区名" text COLLATE "pg_catalog"."default",
     "sgeoc_guid" int4 NOT NULL,
     CONSTRAINT "com_info_ue_data_pkey" PRIMARY KEY ("sgeoc_guid")
   )
   ;
   INSERT INTO "public"."com_info_ue_data"("geom", "shape_leng", "社区名", 
"sgeoc_guid") VALUES 
('0105000020C511000001000000010200000005000000F897DDB3E2032041C00E9CDB6B934E41C8C3420DFE02204174F90FD9E6934E41986E12A30F04204158A8351508944E41C042AD49F4042041A4BDC1178D934E41F897DDB3E2032041C00E9CDB6B934E41',
 '846.673843212', '1社区', 0);
   
   pgsql2pgsql config:
   {
     "env" : {
       "parallelism" : 4,
       "job.mode" : "BATCH",
       "job.name" : "0f4fde35eb2bbfc5957e16e43328859c"
     },
     "source" : [ {
       "result_table_name" : "5f44b986a4ddf77baace3cd140ddafdc",
       "plugin_name" : "Jdbc",
       "url" : "jdbc:postgresql://ip:port/test",
       "driver" : "org.postgresql.Driver",
       "user" : "postgres",
       "password" : "123456",
       "table_path" : "public.com_info_ue_data",
       "query" : "select \"geom\" as \"geom\",\"shape_leng\" as 
\"shape_leng\",\"社区名\" as \"社区名\",\"sgeoc_guid\" as \"sgeoc_guid\" from 
public.com_info_ue_data"
     } ],
     "sink" : [ {
       "source_table_name" : "5f44b986a4ddf77baace3cd140ddafdc",
       "plugin_name" : "Jdbc",
       "url" : "jdbc:postgresql://ip:port/test_ben",
       "driver" : "org.postgresql.Driver",
       "user" : "postgres",
       "password" : "123456",
       "database" : "test_ben",
       "table" : "public.ods_com_info_ue_data",
       "generate_sink_sql" : true,
       "schema_save_mode" : "RECREATE_SCHEMA",
       "data_save_mode" : "DROP_DATA",
       "update_pass_key" : true,
       "primary_keys" : null,
       "custom_sql" : null,
       "index_sql" : null
     } ]
   }
   
   In the same table, a pgsql2pgsql job can successfully synchronize the values 
of geometry-type columns to the target table. However, in a CDC PostgreSQL job, 
the geometry-type column values are not synchronized and appear as empty in the 
target table, accompanied by the aforementioned error.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@seatunnel.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org