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

On 2025/03/14 15:25:00 </o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=3da34e28d9d240d4abadbbb549f9ff21-houzj.fnst> wrote:
On Wed, Mar 12, 2025 at 12:13 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Tuesday, March 11, 2025, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com <mailto:amit(dot)kapila16(at)gmail(dot)com> > wrote:
> > On Tue, Mar 11, 2025 at 8:21 PM David G. Johnston
> > <david(dot)g(dot)johnston(at)gmail(dot)com <mailto: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 <mailto: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.
> > >
> >
> > >
> > 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.
>
> I’m against a UX that drops database objects selected by algorithm without
> informing the user what those objects are and giving them a chance to abort.
> The output file method makes that very simple to implement. I think an
> interactive command to accomplish that requirement would put commit more at
> risk. And a constant increase of options for each object type seems annoying
> given the alternative of simply doing them all as we get around to them and
> letting the file and the user remove the ones they wish to keep.
>
> I’m honestly question how much value this provides - no improvement here seems
> like a viable choice. Let them issue the drop commands they desire manually.
> This doesn’t feel like something one should/would automate.

For logical replication, there is a frequent need to automatically delete all
objects (including publications) on replicas that are no longer needed. This
requirement comes from a common use case in logical replication, which is to
replicate only a subset of database objects. Personally, I am uncertain about
the use case for retaining only some of the publications while dropping others.

Besides, I am unsure if the rationale behind pg_upgrade's output script is
applicable here. AFAIK, The scripts output by pg_upgrade, such as
reindex_hash.sql and update_extensions.sql are to address major version
differences. The script delete_old_cluster.sh is generated instead of directly
removing the instance to provide an option to revert if any issues arise during
the upgrade.

I think the option proposed in this thread is not to handle the
version mismatch between the publisher and the subscriber. The primary goal of
pg_createsubscriber is simply to create a subscriber. Even if problems occur
after dropping the publication, creating another subscriber using the existing
publisher is straightforward and we can't get back to standby whether we remove
publications or not. This concern is minimized since we do not delete
publications by default (only when --drop-xx option is specified). Overall,
I favor automatically deleting publications rather than offering a script for
manual execution.

Best Regards,
Hou zj