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.rupireddyforpostg...@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
signature.asc
Description: PGP signature
