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

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.

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?

Thoughts?

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

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