Re: speed up a logical replica setup

On Mon, Mar 11, 2024 at 9:42 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Sat, 9 Mar 2024 at 00:56, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> >
> > >> 5) slot / publication / subscription name
> > >>
> > >> I find it somewhat annoying it's not possible to specify names for
> > >> objects created by the tool - replication slots, publication and
> > >> subscriptions. If this is meant to be a replica running for a while,
> > >> after a while I'll have no idea what pg_createsubscriber_569853 or
> > >> pg_createsubscriber_459548_2348239 was meant for.
> > >>
> > >> This is particularly annoying because renaming these objects later is
> > >> either not supported at all (e.g. for replication slots), or may be
> > >> quite difficult (e.g. publications).
> > >>
> > >> I do realize there are challenges with custom names (say, if there are
> > >> multiple databases to replicate), but can't we support some simple
> > >> formatting with basic placeholders? So we could specify
> > >>
> > >> --slot-name "myslot_%d_%p"
> > >>
> > >> or something like that?
> > >
> > > Not sure we can do in the first version, but looks nice. One concern is that I
> > > cannot find applications which accepts escape strings like log_line_prefix.
> > > (It may just because we do not have use-case.) Do you know examples?
> > >
> >
> > I can't think of a tool already doing that, but I think that's simply
> > because it was not needed. Why should we be concerned about this?
> >
>
> +1 to handle this.
> Currently, a) Publication name = pg_createsubscriber_%u, where %u is
> database oid, b) Replication slot name = pg_createsubscriber_%u_%d,
> Where %u is database oid and %d is the pid and c) Subscription name =
> pg_createsubscriber_%u_%d, Where %u is database oid and %d is the pid
> How about we have a non mandatory option like
> --prefix_object_name=mysetup1, which will create a) Publication name =
> mysetup1_pg_createsubscriber_%u, and b) Replication slot name =
> mysetup1_pg_createsubscriber_%u (here pid is also removed) c)
> Subscription name = mysetup1_pg_createsubscriber_%u (here pid is also
> removed).
>
> In the default case where the user does not specify
> --prefix_object_name the object names will be created without any
> prefix names.
>

Tomas's idea is better in terms of useability. So, we should instead
have three switches --slot-name, --publication-name, and
--subscriber-name with some provision of appending dbid's and some
unique identifier for standby. The unique identifier can help in
creating multiple subscribers from different standbys.

--
With Regards,
Amit Kapila.