Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u4l8q-006vgw-S8 for pgsql-general@arkaria.postgresql.org; Tue, 15 Apr 2025 18:36:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u4l8o-00F4om-BV for pgsql-general@arkaria.postgresql.org; Tue, 15 Apr 2025 18:36:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u4l8n-00F4od-UY for pgsql-general@lists.postgresql.org; Tue, 15 Apr 2025 18:36:14 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u4l8l-000G3p-1f for pgsql-general@lists.postgresql.org; Tue, 15 Apr 2025 18:36:14 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-2cc82edcf49so1661612fac.1 for ; Tue, 15 Apr 2025 11:36:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744742170; x=1745346970; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=3zZ6MYdRARXCd33ojTGi2JLIuH1wjChJG9FSIAHq6Hg=; b=fdA27i2Cy00r+VD9LAVF+yAOGUZ2TUadUiMncJt6mjlZ1GJToyi3kP8k3eXkPepBsD 9eZeYfZdlNVyDwppm5YZN10y5UC/Q8HbuJgIFJMW1sp5Ldo0BR/0XGkkr27toY7PqEE4 DwDoiAaaTqbvpLPplu7gkg3eg8gbxRYf37WSYjrhO/odGJ3AHOAknzdLkz8bMBe9czMB kwh6MpQo7e+Z1qnHWzaDrtoU9wsjttLLPyWGXvRpJp1p4WgmujX6uMPBNa4eeZDjnelH BFiuoHTJqgnqCBKs1qFhhL236+VU/igzbYEf9V61LbaQWUQmJCNs2ogRsAR5NGwjWcFK RD5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744742170; x=1745346970; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=3zZ6MYdRARXCd33ojTGi2JLIuH1wjChJG9FSIAHq6Hg=; b=ikEnjxOLD3drfGI1Jj+Rz0mEclG4tgNo79UiR0uFCkRaonP2r6kC8ST9gm+GZQNKmx kwFOPaWPaGPPBNc0R+GQNhE7C/TGrsETHDkF9taUxwIm5uAVpTnvwZxxGC5Tfl7HtM2B /2NXdD2XjlgNi0Epk9By3+yD++FIosbIG1MiFL3OS9l5+NWTU3tsk8iHFDVqpuwmOLIr CgoQTQNx/acMG5TzZoB6vkMCw2Rbl/pcdjZ1Y7R1NIr7q7nyRY88cbqoBxt6ilmeyDVE yvLjLTsmJz3TFen7hPYXoRjhgeppYDdJlLx6eftHhHNOjot2/JlmF2tUq4VkixtmEryc pvWw== X-Forwarded-Encrypted: i=1; AJvYcCX8RFvlyv687r567XEyzn2ge+/vDDI6wo7ERCiSQzNczCOdMZGiMLk82bKBF4KZj6uyyfxfHDAzX9SLtXzB@lists.postgresql.org X-Gm-Message-State: AOJu0YzUCs8jhvkIQA2+f3uSVsxs7PcuGF55uiuRzZ4rPhzWGu5+LnI0 EafPJNaZp/Vu0ksRTsaPXpo6ETlVaHnRO4q0D27P0pUEFsaWuKdJGaWEajgl1ybAHp2nZ2zTL14 CvBCxhuC3DRnfBWQrABUBPj7Rd/e2qxLO X-Gm-Gg: ASbGncsWkgPcSvgtGtT7w9SPWssUM2b+MihjrYooHeIPv55IDYGU58Lx9XOdTcaE+TY BoW8YBKrWRot95BHX1zET6w6kIout8awdSmomHvw7wJW8UliY7l8FlBZOroGav/3r5tW4/98U61 J3Wtf9aboVvi9vmz/SUcHExEC0wKpiV94= X-Google-Smtp-Source: AGHT+IFGB82bfuGi2CBY+jTilH5DtbG0wDe2JEiTDTPCwjpREc5J4F1y09wo0i7UmketZhJ//ZfOAa67d+1vecyZsXs= X-Received: by 2002:a05:6871:54e:b0:2cc:39cb:da38 with SMTP id 586e51a60fabf-2d4c3973dd1mr191609fac.13.1744742170228; Tue, 15 Apr 2025 11:36:10 -0700 (PDT) MIME-Version: 1.0 References: <817abeac-d628-4279-bfef-9e1e3aa1884b@aklaver.com> <25b6cd49-f871-47fe-bf3b-5416637736ca@aklaver.com> In-Reply-To: <25b6cd49-f871-47fe-bf3b-5416637736ca@aklaver.com> From: "David G. Johnston" Date: Tue, 15 Apr 2025 11:35:34 -0700 X-Gm-Features: ATxdqUH40ApCNcU09W2Umv3SSonap4OG2ZwqNkW1pcQzaOkODBXJmg67fxdqI5k Message-ID: Subject: Re: Fwd: Identify system databases To: Adrian Klaver Cc: Igor Korot , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004801a60632d571f4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004801a60632d571f4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Apr 15, 2025 at 11:20=E2=80=AFAM Adrian Klaver wrote: > On 4/15/25 09:48, David G. Johnston wrote: > > On Tue, Apr 15, 2025 at 9:31=E2=80=AFAM Adrian Klaver > > wrote: > > > > > > > Agreed. > > > > The fact that initdb creates the template0, template1 and postgres > > databases and you can't change that makes them system not user > > databases. > > > > > > Based on that definition there is a boundary in the system where OIDs > > are considered bootstrap/system OIDs versus user OIDs. Key off of > > that. Though since the names never change, and there are always/only > > three, it seems pointless to use the OID aspect of initdb as a basis. > > > > My definition of a "system database" would be a database that, if it > > didn't exist, would cause the system to break. i.e., is a database > > whose presence is integral to the operations of the system. None of > > these qualify under that definition. Which is why there is no column i= n > > pg_database identifying system databases - there are none. > > 1) Try connecting(not using single user mode) to a cluster without them. > I did. Here, the full test. -- freshly initdb cluster exists and is running =E2=9D=AF cat ~/Downloads/test-drops.bash createdb mydb psql -c 'alter database template0 is_template false;' mydb dropdb template0 psql -c 'alter database template1 is_template false;' mydb dropdb template1 dropdb --maintenance-db mydb postgres psql -c '\l' mydb =E2=9D=AF bash ~/Downloads/test-drops.bash ALTER DATABASE ALTER DATABASE List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges ------+--------+----------+-----------------+------------+------------+----= ----+-----------+------------------- mydb | davidj | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | (1 row) > 2) Trying creating a database or databases to replace them without them > existing in the first place. > Why? > If what you say is true why does initdb lack an option to not create > them on creating a cluster? > Well, it has to create one database because it is not possible to connect to the cluster without naming a specific database. initdb could allow the user to specify the initial database name and only create that single database. It would work. By creating the initial three databases the system is more usable due to having established conventions. They are conventional databases, not system ones. Plus, it leaves the cluster in a state that is generally much more user-friendly. And, as shown, people who dislike the convention can rearrange things into whatever form they desire. David J. --0000000000004801a60632d571f4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 15, 2025 at 11:20=E2=80=AFAM Adrian Klaver <= ;adrian.klaver@aklaver.com= > wrote:
On 4/15/25 09:48, Dav= id G. Johnston wrote:
> On Tue, Apr 15, 2025 at 9:31=E2=80=AFAM Adrian Klaver <adrian.klaver@aklaver.co= m
> <mailto:adrian.klaver@aklaver.com>> wrote:
>

>
>=C2=A0 =C2=A0 =C2=A0Agreed.
>
>=C2=A0 =C2=A0 =C2=A0The fact that initdb creates the template0, templat= e1 and postgres
>=C2=A0 =C2=A0 =C2=A0databases and you can't change that makes them = system not user
>=C2=A0 =C2=A0 =C2=A0databases.
>
>
> Based on that definition there is a boundary in the system where OIDs =
> are considered bootstrap/system OIDs versus user OIDs.=C2=A0 Key off o= f
> that.=C2=A0 Though since the names never change, and there are always/= only
> three, it seems pointless to use the OID aspect of initdb as a basis.<= br> >
> My definition of a "system database" would be a database tha= t, if it
> didn't exist, would cause the system to break.=C2=A0 i.e., is a da= tabase
> whose presence is integral to the operations of the system.=C2=A0 None= of
> these qualify under that definition.=C2=A0 Which is why there is no co= lumn in
> pg_database identifying system databases - there are none.

1) Try connecting(not using single user mode) to a cluster without them.

I did.=C2=A0 Here, the full test.
-= - freshly initdb cluster exists and is running

=E2=9D= =AF cat ~/Downloads/test-drops.bash
createdb mydb
psql -c 'alter= database template0 is_template false;' mydb
dropdb template0
psq= l -c 'alter database template1 is_template false;' mydb
dropdb t= emplate1
dropdb --maintenance-db mydb postgres
psql -c '\l' m= ydb

=E2=9D=AF bash ~/Downloads/test-drops.bash
ALTER DATABASEALTER DATABASE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0List of databases
=C2=A0Name | Own= er =C2=A0| Encoding | Locale Provider | =C2=A0Collate =C2=A0 | =C2=A0 Ctype= =C2=A0 =C2=A0| Locale | ICU Rules | Access privileges
------+--------+= ----------+-----------------+------------+------------+--------+-----------= +-------------------
=C2=A0mydb | davidj | UTF8 =C2=A0 =C2=A0 | libc =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| en_US.utf8 | en_US.utf8 | =C2=A0 = =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
(1 row)
<= br>


2) Trying creating a database or databases to replace them without them existing in the first place.

Why?


If what you say is true why does initdb lack an option to not create
them on creating a cluster?

Well, it has = to create one database because it is not possible to connect to the cluster= without naming a specific database.

initdb could allo= w the user to specify the initial database name and only create that single= database.=C2=A0 It would work.

By creating the initia= l three databases the system is more usable due to having established conve= ntions.=C2=A0 They are conventional databases, not system ones.
Plus, it leaves the cluster in a state that is generally much mor= e user-friendly.=C2=A0 And, as shown, people who dislike the convention can= rearrange things into whatever form they desire.

Davi= d J.

--0000000000004801a60632d571f4--