Re: Have pg_basebackup write "dbname" in "primary_conninfo"?

On Thu, Mar 14, 2024 at 5:57 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> This fact makes me think that the slotsync worker might be able to
> accept the primary_conninfo value even if there is no dbname in the
> value. That is, if there is no dbname in the primary_conninfo, it uses
> the username in accordance with the specs of the connection string.
> Currently, the slotsync worker connects to the local database first
> and then establishes the connection to the primary server. But if we
> can reverse the two steps, it can get the dbname that has actually
> been used to establish the remote connection and use it for the local
> connection too. That way, the primary_conninfo generated by
> pg_basebackup could work even without the patch. For example, if the
> OS user executing pg_basebackup is 'postgres', the slotsync worker
> would connect to the postgres database. Given the 'postgres' database
> is created by default and 'postgres' OS user is used in common, I
> guess it could cover many cases in practice actually.
>

I think this is worth investigating but I suspect that in most cases
users will end up using a replication connection without specifying
the user name and we may not be able to give a meaningful error
message when slotsync worker won't be able to connect. The same will
be true even when the dbname same as the username would be used.

> Having said that, even with (or without) the above change, we might
> want to change the pg_basebackup so that it writes the dbname to the
> primary_conninfo if -R option is specified. Since the database where
> the slotsync worker connects cannot be dropped while the slotsync
> worker is running, the user might want to change the database to
> connect, and it would be useful if they can do that using
> pg_basebackup instead of modifying the configuration file manually.
>
> While the current approach makes sense to me, I'm a bit concerned that
> we might end up having the pg_basebackup search the actual database
> name (e.g. 'dbname=template1') from the .pgpass file instead of
> 'dbname=replication'. As far as I tested on my environment, suppose
> that I execute:
>
> pg_basebackup -D tmp -d "dbname=testdb" -R
>
> The pg_basebackup established a replication connection but looked for
> the password of the 'testdb' database. This could be another
> inconvenience for the existing users who want to use the slot
> synchronization.
>

This is true because it is internally using logical replication
connection (as we will set set replication=database). I feel the
mentioned behavior is an expected one with or without slotsync worker
usage. Anyway, this is an optional feature, so users can still choose
to ignore specifying dbname in the connection string. The point is
that commit cca97ce6a6 allowed using dbname in the connection string
in pg_basebackup and we are just extending to write that dbname along
with other things in connection_info.

> A random idea I came up with is, we add a new option to the
> pg_basebackup to overwrite the full or some portion of the connection
> string that is eventually written in the primary_conninfo in
> postgresql.auto.conf. For example, the command:
>
> pg_basebackup -D tmp -d "host=1.1.1.1 port=5555" -R
> --primary-coninfo-ext "host=2.2.2.2 dbname=postgres"
>
> will produce the connection string that is based on -d option value
> but is overwritten by --primary-conninfo-ext option value, which will
> be like:
>
> host=2.2.2.2 dbname=postgres port=5555
>
> This option might help not only for users who want to use the slotsync
> worker but also for users who want to take a basebackup from a standby
> but have the new standby connect to the primary.
>

Agreed, this could be another way though it would be good to get some
inputs from users or otherwise about the preferred way to specify
dbname. One can also imagine using the Alter System for this purpose.

> But it's still just an idea and I might be missing something. And
> given we're getting closer to the feature freeze, it would be a PG18
> item.
>

+1. At this stage, it is important to discuss whether we should allow
pg_baseback to write dbname (either a specified one or a default one)
along with other parameters in primary_conninfo?

--
With Regards,
Amit Kapila.