Re: Is creating logical replication slots in template databases useful at all?

On Tue, Jun 18, 2024 at 03:19:41PM +0530, Ashutosh Bapat wrote:
> On Mon, Jun 17, 2024 at 5:50 PM Bharath Rupireddy <
> bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=29d0a77fa6606f9c01ba17311fc452dabd3f793d
>> ,
>> I noticed that get_old_cluster_logical_slot_infos gets called for even
>> template1 and template0 databases. Which means, pg_upgrade executes
>> queries against the template databases to get replication slot
>> information. I then realized that postgres allows one to connect to
>> template1 database (or any other user-defined template databases for
>> that matter), and create logical replication slots in it. If created,
>> all the subsequent database creations will end up adding inactive
>> logical replication slots in the postgres server. This might not be a
>> problem in production servers as I assume the connections to template
>> databases are typically restricted. Despite the connection
>> restrictions, if at all one gets to connect to template databases in
>> any way, it's pretty much possible to load the postgres server with
>> inactive replication slots.
>
> The replication slot names are unique across databases [1] Hence
> replication slots created by connecting to template1 database should not
> get copied over when creating a new database. Is that broken? A logical
> replication slot is associated with a database but a physical replication
> slot is not. The danger you mention above applies only to logical
> replication slots I assume.

get_old_cluster_logical_slot_infos() on even template0 is still
correct, IMO, even if this template database is not something that
should be modified at all, or even have allow_connections enabled. It
seems to me the correct answer here is that users should not create
slots where they are not going to use them.

>> This leads me to think why one would need logical replication slots in
>> template databases at all. Can postgres restrict logical replication
>> slots creation in template databases? If restricted, it may deviate
>> from the fundamental principle of template databases in the sense that
>> everything in the template database must be copied over to the new
>> database created using it. Is it okay to do this? Am I missing
>> something here?
>
> If applications are using template1, they would want to keep the template1
> on primary and replica in sync. Replication slot associated with template1
> would be useful there.

Templates defined in CREATE DATABASE can be any active database as
long as they are in pg_database, so doing logical replication on
template1 to keep it in sync across nodes is fine.

In short, I am not quite seeing the problem here.
--
Michael