#StackBounty: #replication #postgresql-10 Postgres 10 Logical Replication without truncating existing table

Bounty: 100

I have been fooling around with Postgres 10 logical replication, trying to learn about it and potentially apply it to a couple of projects. I have observed something that I am wondering about, but I am not sure if I know enough terminology to properly search for the answer. Consider the following:

I have two machines running postgres, they are able to access each other and I have logical replication going from machine 1 (publisher) to machine 2 (subscriber). I wish to flip that relationship however – I want to make 2 the publisher, and 1 the subscriber.

I drop the subscription and the publication on their respective machines and create them on the opposite machines, so now 2 has a publication, and 1 has a subscription. Everything seems just dandy.

The problem is that when I insert something on 2, it does not get published to 1. The subscription/publication are properly configured and seem to be receiving messages from each other.

I played around and discovered that if I truncate the table being replicated from 2 to 1, the replication resumes precisely as expected. This seems like a problem. My database that I’m doing this on is very small, 8 entries, so truncating the table and reloading it is not an issue. But is it possible to resume replication without this?

Assuming I can guarantee that the two tables on two machines have the exact same data when I initialize the publication/replication, is it possible for it to function properly without having to truncate the table on the subscriber node?

Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.