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.