Re: Adding a '--clean-publisher-objects' option to 'pg_createsubscriber' utility.

On Tue, Mar 11, 2025 at 8:21 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Tue, Mar 11, 2025 at 12:20 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>>
>>
>> Unfortunately, we are spinning in circles a bit trying to come up with
>> a good way to represent the option needed for this, while at the same
>> time trying to be future-proof. I see 3 choices...
>>
>> ======
>>
>> Choice 1. Generic option
>>
>> Implement a single boolean option to remove everything.
>>
>>
>> Do you have any thoughts on what kind of option is best here?
>>
>
> Option 1 by far.
>

Among the current discussed options, I would prefer
"--remove-all-publications", or "--drop-all-publications" without any
shorthand notation. We don't need to add target as we already have
options like "--socketdir", or "--config-file" that apply to the
target server and are explained in the text. It is better to follow
the same to keep the consistency.

Also, the new subscriber can act as a publisher, so it won't be a good
idea to remove the existing copied publications by default. Similarly,
users may want to retain copied subscriptions if they want the new
target server to keep receiving the changes from previous publishers.
I feel giving individual options and then a remove-all-objects that
pre-exist on the target server would be a good direction to improve
this tool.

>
Though personally I'd rather do something like what pg_upgrade does
and output a script with drop commands that can be executed via psql
instead of having pg_createsubscriber execute said commands. I'd call
it "pruning the target".
>

Yes, this is a good idea, and IIRC, we discussed in brief on this in
the original thread where we developed this tool but left it as a
future improvement. We should definitely go in this direction in the
future, but let's improve this tool incrementally, otherwise, there is
a risk of making no improvements.

--
With Regards,
Amit Kapila.