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?