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.
