PostgreSQL Logical Replication Skip LSN of previous iteration

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 continued from the point it stopped in case of a drop of subscription or any other reason...

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 ALL TABLES;
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 get the LSN (by looking the column confired_flush_lsn)

SELECT * FROM pg_replication_slots

We can stop the replication (by dropping the subscription, this will delete also the replication slot)

DROP SUBSCRIPTION sub1

On Publisher, create again same replication slot (because it deleted while deleting the subscription on subscriber)

SELECT pg_create_logical_replication_slot('pub1', 'pgoutput');

On Subscriber, create again same subscription

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

Now if we are going to start again activating the replication for the same tables without cleaning the destination table on subscriber we should make the subscription skip the previous LSNs

ALTER SUBSCRIPTION sub1 SKIP (lsn = '0/1566D10');
ALTER SUBSCRIPTION sub1 ENABLE;

On Publisher, add new data

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

But, I see that new data are not replicated into the subscription table and I always see that the subscription errors still are increasing (by looking the column sync_error_count)

SELECT * FROM pg_stat_subscription_stats;

Any ideas?

PostgreSQL Logical Replication Skip LSN of previous iteration

0 Answers0