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

On Mon, Jun 17, 2024 at 5:50 PM Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.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]https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS -- Best Wishes, Ashutosh Bapat 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
https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS
--
Best Wishes,
Ashutosh Bapat

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.rupireddyforpostgres@gmail.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