Re: Adding a '--clean-publisher-objects' option to 'pg_createsubscriber' utility. - Mailing list pgsql-hackers

On Tue, Mar 11, 2025 at 11:32 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Mon, Mar 10, 2025 at 5:00 PM Peter Smith <smithpb2250@gmail.com> wrote:
>>
>> Hi Shubham.
>>
>> Some review comments for patch v16-0001.
>>
>> ======
>> doc/src/sgml/ref/pg_createsubscriber.sgml
>>
>> 1.
>> +     <term><option>-c</option></term>
>> +     <term><option>--drop-all-publications</option></term>
>>
>> Is 'c' the best switch choice letter for this option? It doesn't seem
>> intuitive, but unfortunately, I don't have any better ideas since d/D
>> and p/P are already being used.
>
>
> Agreed.  Better to just not assign a short name to this.
>
> The description for the sgml docs needs to frame up this option's purpose.
>
> How exactly does one go about backing up a publication?  You discuss the topic in the commit message but that
definitelyseems user-facing. 
>
> If we aren't expecting lots of publications maybe name them individually instead of logging "all publications"?
Possiblyone info line each but even just comma separated would work. 
>
> The name of this is shock-inducing.  Admittedly, after pondering things, it is fairly obvious that only the target is
goingto be affected, but there is a source database involved here as well.  It is also unclear on whether it would
happenbefore or after, which is less problematic since it would only impact failure modes anyway - when all is said and
donewith this specified upon restart following the pg_resetwal the server will have no publications, right? 
>
> Maybe: --drop-target-publications-first ?
>
> If we do want a letter either "X" or "Z" probably works for an English-speaking audience probably.  X is how one
denotesremoving something; and Z is a mnemonic for "Zap" which is a synonym for "Drop". "R" for "Remove". 
>
> Can you briefly recap how this is different than the automatic behavior described in the existing Step 6?
> "Drop publications on the target server that were replicated because they were created before the replication start
location.It has no use on the subscriber." 
>
> -R --remove-target-publications; I fine with answering the timing question to the description.
>

Hi David.

This feature is all about removing some objects that were replicated
during the streaming replication before pg_createsubscriber was run,
but that are no longer needed/wanted afterwards.

Although the current thread/patch is just for removing existing
publications from the target server, in the future there might be some
enhancements to remove other kinds of unwanted objects that were
replicated to the target server by streaming replication -- e.g.
remove subscriptions, or remove replication slots, or whatever.

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.

In this case, the option would need to have some generic name like
"--remove-target-existing-object". For now (current patch), it would
be implemented to remove only existing publications. In the future, it
could remove more things. But, there isn't much flexibility: IIUC this
option can only remove either all or none.

~

Choice 2. Different options for removing different things (the current
patch is like this)

"--remove-target-publications" is what this patch is doing.

In future, if we want to remove more things then we would need to add
more options like "--remove-target-subscriptions",
"--remove-target-replication-slots" etc.

~

Choice 3. Implement some option that has an argument saying what to delete

Implement an option that takes some argument saying what objects to remove.

Here, the current patch would be something like:
"--remove-target-objects=publications". In future, this option could
be enhanced to accept other values like
"--remove-target-objects=publications,subscriptions" etc.

~~~

Do you have any thoughts on what kind of option is best here?

======
Kind Regards,
Peter Smith.
Fujitsu Australia