Re: viewing connectioninfo used by subscriber on the publication server when inactive

On Thu, May 14, 2020 at 8:33 AM Wim Bertels <wim(dot)bertels(at)ucll(dot)be> wrote:

> >
> >
> > The replication slot connection could use any role that has been
> > configured to allow replication. So there is no way to know which
> > role is actually using a slot without it actually being connected.
> > You can look and see which roles have replication privileges by
> > checking the output of \du in psql, but there is nothing tying any
> > roles to any specific slots.
>
> i assume that a replication slot can only be used by 1 subscriber,
> and subscribers can migrate; just to be clear the conneinfo is only
> kept on the subscriber and used on the publisher, but not stored on the
> publisher (unless active)
>
> so if a replication slot is inactive, you can't contact for example the
> user that (implicitely) created the replication slot, to see if it is
> still needed for example.
>
> background: i'm trying out an atypical? setup with many students
> subscribing to a single publication server, thus with subscribers that
> are not always online. As the number of slots might quickly grow:)
>
> >
> --
> mvg,
> Wim Bertels
>
> --
> Swerve me? The path to my fixed purpose is laid with iron rails,
> whereon my soul is grooved to run. Over unsounded gorges, through
> the rifled hearts of mountains, under torrents' beds, unerringly I rush!
> -- Captain Ahab, "Moby Dick"
>
>

While running CREATE SUBSCRIPTION on the subscriber side does conveniently
make a slot for you back on the primary, it's also possible to manually
create logical replication slots directly on the primary. So a replication
slot could potentially be made directly on the publication side and have no
awareness of what role may eventually connect.

If you're going to have multiple subscribers connecting to a single
publication, you're going to have to have different slots for each of those
subscribers anyway. Otherwise one subscriber could cause another one to
miss data. Honestly, the logical replication system really isn't meant to
have subscribers be offline for any extended period of time, especially if
the publisher is continuing to write. I would question the design of this
that is allowing the subscribers to be offline. You may want to rethink
this design a bit.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com