Re: Enhance 'pg_createsubscriber' to retrieve databases automatically when no database is provided.

On Tue, Jan 28, 2025 at 3:58 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Fri, Jan 24, 2025 at 7:28 PM Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> >
> > On Fri, Jan 24, 2025 at 8:14 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > >
> > > On Thu, Jan 23, 2025 at 10:33 PM Ashutosh Bapat
> > > <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > > >
> > > > On Wed, Jan 22, 2025 at 7:29 PM Shubham Khanna
> > > > <khannashubham1197(at)gmail(dot)com> wrote:
> > > > >
> > > > > Hi all,
> > > > >
> > > > > I am writing to propose an enhancement to the pg_createsubscriber
> > > > > utility that enables it to automatically fetch all non-template
> > > > > databases from the publisher when no specific databases are specified
> > > > > by the user. This was an open item from [1] that was planned for
> > > > > future implementation. The attached patch has the changes for the
> > > > > same.
> > > >
> > > > I think the feature will be useful, but UI might cause some unwanted
> > > > results. If a user forgets to specify -d option, the utility will
> > > > create subscriptions to all the databases, some of which may or may
> > > > not have the publications. I think it's better to provide an option to
> > > > specify all databases explicitly (e.g. --all-databases).
> > > >
> > >
> > >
> > > +1 better to be safe.
> > >
> > > Instead of a new switch, how about changing the --database switch to
> > > accept a pattern (like pg_dump --schema does [1])
> > >
> > > Then "all databases" would be specified something like --database = *
> > >
> >
> > WFM but that will be more work than what's in the patch.
> >
>
> OK, what if, instead of full pattern matching it could recognise just
> one special dbname value of '*' (meaning "all")
>
> So, "all databases" could still be specified as --database = *
>
> The implementation would be almost no more work than the current
> patch, while at the same time leaving it open to be extended as a
> pattern if needed in the future. Or, is it too hacky?

I don't remember any precedence here. pg_dump has pg_dumpall which
dumps all the databases, so they chose to create a separate binary. If
we go that route, I think we will be able to produce a more flexible
utility, like replication slot names per database, or per database
subscription settings etc. Maybe we should consider that option.

If we want to stick to --database= supporting a pattern looks better
than just a single special pattern *.

--
Best Wishes,
Ashutosh Bapat