Sample Header Ad - 728x90

Postgresql Logical Replication from citus distributed table to another citus distributed table

0 votes
0 answers
77 views
I followed this guide (https://docs.citusdata.com/en/stable/develop/integrations.html) to implement logical replication of the "items" table between two postgres db. On DB1 the "items" table is distributed using the citus extension (1 coordinator + 2 worker nodes), on DB2 the citus extension is installed but the "items" table is not distributed (single node). In detail: On DB1 I created the items_pub publication:
CREATE PUBLICATION items_pub FOR TABLE items;
and then I created the replication slots:
SELECT * FROM run_command_on_all_nodes(
   $$ SELECT pg_create_logical_replication_slot('cdc_slot', 'pgoutput', false) $$
);
On DB2 I created the subscriptions:
create subscription sub
   connection 'host=10.100.100.37 user=user dbname=DB1 password=password port=5432'
   publication items_pub
   WITH (copy_data=true,create_slot=false,slot_name='cdc_slot');

create subscription sub_1
   connection 'host=10.100.100.38 user=user dbname=DB1 password=password port=5432'
   publication items_pub
   WITH (copy_data=false,create_slot=false,slot_name='cdc_slot');

create subscription sub_2
   connection 'host=10.100.100.39 user=user dbname=DB1 password=password port=5432'
   publication items_pub
   WITH (copy_data=false,create_slot=false,slot_name='cdc_slot');
Everything works perfectly and every event of INSERT, DELETE, UPDATE on the items table of DB1 is propagated correctly to the items table of DB2. The problem arises when I transform the "items" table on DB2 into a distributed table via Citus. (again with 1 coordinator + 2 worker nodes). At this point any event coming from DB1 is not received by DB2. I have tried several solutions without success. I would like to understand if what I am trying to implement is possible or not and if possibly there are other solutions.
Asked by Babylon Big Data (1 rep)
Jul 10, 2024, 12:07 PM
Last activity: Jul 10, 2024, 12:16 PM