partition tables in Postgres

[tellienne]

Hello!
We have a table with partitions in Postgres. When we transfer data to clickhouse, the connector transfers partitions as separate real tables. Is it possible to combine partition tables into one master table in clickhouse during migration?

[aadant]

@tellienne which Postgres version is it ?

[aadant]

See https://groups.google.com/g/debezium/c/NroAVr2saxU

[tellienne]

@tellienne which Postgres version is it ?

v.14

[tellienne]

See https://groups.google.com/g/debezium/c/NroAVr2saxU

thanks for the link, but it looks like we have a different problem.
In Postgres we create a table using a query

CREATE TABLE public.table_master (
guid varchar(100) NOT NULL,
id uuid NOT NULL,
store_id varchar(100) NOT NULL,
day_updated_at timestamptz NOT NULL,
base_unit varchar(50) NULL,
vat numeric(16, 4) NULL,
price_with(16, 4) NOT NULL,
price_without(16, 4) NOT NULL,
remains numeric(16, 4) NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT pk_product_source PRIMARY KEY (guid, id, store_id, day_updated_at)
)
PARTITION BY RANGE (day_updated_at);

After this we have one master table with many table sections that look like
table_2023_03
table_2023_04
table_2023_05
table_2023_06
table_2023_07
table_2023_08 etc

When the connector runs, it treats table sections as separate tables and creates them with a query:

CREATE TABLE db_name.table_2023_03
(

`guid` String,

`id` UUID,

`store_id` String,

`day_updated_at` DateTime64(6),

`base_unit` Nullable(String),

`vat` Nullable(Decimal(16, 4)),

`price_with` Decimal(16, 4),

`price_without` Decimal(16, 4),

`remains` Decimal(16, 4),

`updated_at` DateTime64(6),

`_sign` Int8,

`_version` UInt64

)
ENGINE = ReplacingMergeTree(_version)
PRIMARY KEY (guid,
id,
store_id,
day_updated_at)
ORDER BY (guid,
id,
store_id,
day_updated_at)
SETTINGS index_granularity = 8192;

As a result, in clickhouse, instead of one master table with data, we see many tables, each of which is equal to a partition in Postgres.

[tellienne]

hi!
is there any news about this?
We still have partition tables from Postgres created as separate physical tables in clickhouse. But we would like to copy all the partition tables into one table in the clickhouse.
We found suitable settings in the Debezium documentation https://debezium.io/documentation/reference/2.7/transformations/topic-routing.html.
Is there something similar for a connector?

[tellienne]

Hello!
We solved the problem using publish_via_partition_root
https://www.postgresql.org/docs/current/sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-WITH-PUBLISH-VIA-PARTITION-ROOT
But to be able to do a reroute as described in the Debezium documentation would be great :)