Logical Replication Structure Change History Sync

Question

Lately I am using PostgreSQL for replicating some data from multiple databases to a single database. For testing the Logical Replication I have created a simple example transferring data from one database to another only for a single table. The idea is to check how replication can be sync additional columns for data which are already replicated...

On publisher and subscriber databases, create the table:

CREATE TABLE IF NOT EXISTS public."Data"
(
    tenant_id integer NOT NULL,
    id integer NOT NULL,
    description character varying COLLATE pg_catalog."default",
    CONSTRAINT "Data_pkey" PRIMARY KEY (tenant_id, id)
)

On publisher, create the publication:

CREATE PUBLICATION pub1 FOR TABLE public."Data" (tenant_id, id);
SELECT pg_create_logical_replication_slot('pub1', 'pgoutput');

On publisher, insert the data:

INSERT INTO public."Data"(
    tenant_id, description)
    VALUES (1, 1, 'TEST1');

On Subscriber, create the subscription:

CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost port=5432 user=postgres password=xxxxxx dbname=publisher1' PUBLICATION pub1 with (enabled = false, create_slot = false, slot_name = NONE);
ALTER SUBSCRIPTION sub1 SET (slot_name = pub1);
ALTER SUBSCRIPTION sub1 ENABLE;

On Subscriber, check the data:

SELECT * FROM public."Data"

Now that the subscriber has this replicated row, we can check that description column is not replicated as we configured on the publication. In case we need to add the column on the publication

ALTER PUBLICATION pub1 SET TABLE public."Data" (tenant_id, id, description);

On Publisher, add new data

INSERT INTO public."Data"(
    tenant_id, description)
    VALUES (1, 2, 'TEST2');

But, I see that new data are replicated into the subscription table and the new row has the description but the old row from the previous iteration does not have the value for the description

Any ideas?

Logical Replication Structure Change History Sync

0 Answers0