Re: logical replication snapshots

On 07/26/2018 04:48 PM, Dimitri Maziuk wrote:
> On 07/26/2018 05:34 PM, Adrian Klaver wrote:
>> On 07/26/2018 03:01 PM, Dimitri Maziuk wrote:
>
>>> Let me ask a different question: if I drop and re-create a published
>>> table on the publisher without doing anything to the publication and
>>> subscription, what happens?
>>
>> Take a look at:
>>
>> https://www.postgresql.org/docs/10/static/sql-alterpublication.html
>
> Thanks but what I ask (and what I suspect happened here while I wasn't
> looking) is
>
> - create schema foo with tables bar and baz and some rows in 'em
> - create publication foopub with foo.bar and foo.baz
> - create subscription to foopub on the other node, initial sync and all
> - drop schema foo on the publisher
> - create schema foo with tables bar and baz and some rows in 'em
>
> The publication foopub is at this point fubar I take it? And needs to be
> re-created on the publisher and reconnected on the subscriber? Complete
> with with inital resync?

Not sure. Personally I would try:

1) ALTER PUBLICATION DROP TABLE foo|bar;

2) ALTER PUBLICATION ADD TABLE foo|bar;

3) ALTER SUBSCRIPTION sub_name REFRESH PUBLICATION

If you get to 3) it will re-sync the data unless you tell it otherwise.

The above is probably dependent on the size of the publication. If you
did a publication for ALL it would make more sense to do the above then
if you did a publication for just foo or bar.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com