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?

  • Did you ever find an answer? – Bill Jun 4 '18 at 3:52
  • Is this what create subscription ... with (copy_data=false) is for? – Kurt Aug 24 '18 at 8:28
  • What is your goal with doing this? Just tinkering around? – Daniel Nov 5 '18 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.