<div>Thanks for reply!<br />It makes sense now why that happened and what to do in case of emergency</div><div> </div><div>09.02.2021, 10:01, "Kyotaro Horiguchi" <horikyota(dot)ntt(at)gmail(dot)com>:</div><blockquote><p>At Mon, 08 Feb 2021 22:42:21 +0700, Игорь Выскорко <<a href="mailto:vyskorko(dot)igor(at)yandex(dot)ru" rel="noopener noreferrer">vyskorko(dot)igor(at)yandex(dot)ru</a>> wrote in</p><blockquote> Hi, community!<br /> Unfortunately can't find answer in docs and google. Hope only for you)<br /> [local]:5433 <a href="mailto:postgres(at)postgres" rel="noopener noreferrer">postgres(at)postgres</a>=# drop publication pub ;<br /> DROP PUBLICATION<br /> Time: 3,793 ms<br /> <br /> [local]:5433 <a href="mailto:postgres(at)postgres" rel="noopener noreferrer">postgres(at)postgres</a>=# insert into tbl(d) values ('test2');<br /> INSERT 0 1<br /> Time: 9,002 ms<br /> <br /> [local]:5433 <a href="mailto:postgres(at)postgres" rel="noopener noreferrer">postgres(at)postgres</a>=# create publication pub for table tbl;<br /> CREATE PUBLICATION<br /> Time: 6,646 ms<br /> <br /> result: nothing changed, same errors appears again and again. I couldn't find<br /> how to restore replication without drop&create subscription again.</blockquote><p><br />If you recreated the publication before the insert, replication would<br />continue.<br /> </p><blockquote> Questions here:<br /> 1. what is going under the hood here - why walsender thinks that "publication<br /> "pub" does not exist" when it actually exists?</blockquote><p><br />The answer is "because the publication did not exist at the time of<br />the INSERT". Thus the insert cannot be replicated using the new<br />publication.<br /><br />It is because logical replication tries to find publications using the<br />same snapshot with the WAL record to be sent. Although it is the<br />designed behavior, I'm not sure that is true also for pg_publication.<br /> </p><blockquote> 2. what is the right way to restore replication in my example?</blockquote><p><br />The most conservative way is just to drop the subscription then delete<br />all rows from the subscriber table then recreate the<br />subscription. This allows the newly created publication to work.<br /><br />Also you can drop the subscription, then manually fix the subscriber<br />table to sync with the publisher table, then create a new subscription<br />using WITH (copy_data = false);<br /><br />regards.<br /> </p>--<br />Kyotaro Horiguchi<br />NTT Open Source Software Center</blockquote>