On Mon, Jun 17, 2024 at 5:50 PM Bharath Rupireddy < bharath.rupireddyforpostg...@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] 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
