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

On Mon, Jun 17, 2024 at 5:50 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> Hi,
>
> While looking at the commit
>
> 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.

>
> 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.

[1]
https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS
--
Best Wishes,
Ashutosh Bapat