Feedback-ID: i76984098:Fastmail
Message-ID: <bcae9b4b-a85e-4fc7-8e70-5a98c5e5ff28@aklaver.com>
Date: Tue, 15 Apr 2025 17:24:02 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Fwd: Identify system databases
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Igor Korot <ikorot01@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CA+FnnTxab=JyE0DZkfL4Z2MK8RvvTud9MZiNenD45AoV_t2Mxg@mail.gmail.com>
 <CAKFQuwYaDXuuDoEaad5sYW97pRS_FJnmfZCwY3FojnDOdfXc9Q@mail.gmail.com>
 <CA+FnnTzwmMTNymgPuiF4CLfa6YO+HBRx4Qy5_B_KDw1oNpiukw@mail.gmail.com>
 <CA+FnnTyWMXPEVAwNNDnCVDfmTFNHdDfffiF5vLioxyTv8Jmq0A@mail.gmail.com>
 <817abeac-d628-4279-bfef-9e1e3aa1884b@aklaver.com>
 <CAKFQuwbkQTbUwA4c6LFOMUWX5ojQY8DpKPQRh+xoe4VZABPWfw@mail.gmail.com>
 <25b6cd49-f871-47fe-bf3b-5416637736ca@aklaver.com>
 <CAKFQuwYpY7urAHuns26cw=9TKTZJU4JdVBrXWoSH_nHfZ_s3Ww@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKFQuwYpY7urAHuns26cw=9TKTZJU4JdVBrXWoSH_nHfZ_s3Ww@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/bcae9b4b-a85e-4fc7-8e70-5a98c5e5ff28%40aklaver.com>
Precedence: bulk

On 4/15/25 11:35, David G. Johnston wrote:
> On Tue, Apr 15, 2025 at 11:20 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

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

First I have had this discussion before and was not successful and I 
expect the same outcome this time, but I am half Dutch so that is not a 
concern.


> I did.  Here, the full test.
> -- freshly initdb cluster exists and is running
> 
> ❯ cat ~/Downloads/test-drops.bash
> createdb mydb

 From Postgres log:

CREATE DATABASE mydb;

 From here:

https://www.postgresql.org/docs/current/sql-createdatabase.html

"template

     The name of the template from which to create the new database, or 
DEFAULT to use the default template (template1).
"

I know I can use --template=template with createdb, but that still means 
there has to be a database existing in the initial cluster to use as a 
template. You can't get around that fact.


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

See above. It boils down to, there has to be 'seed'(system) databases 
extant in the cluster created by initdb for a user to move forward.

> 
> 
>     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.

Exactly, the cluster needs a database created by the system to function.

> 
> initdb could allow the user to specify the initial database name and 
> only create that single database.  It would work.

 From an earlier post of mine in this thread:

"Further from here:

https://www.postgresql.org/docs/devel/app-initdb.html

"The postgres database is a default database meant for use by users, 
utilities and third party applications"

indicates that postgres is expected to be in a cluster(system).

Lastly the CREATE DATABASE depends on template1 to be there to create at 
least the first user database. Also template0 is a "clean" template 
database that allows for using a new encoding in new database. It is 
also used by pg_dump/restore:

CREATE DATABASE test WITH TEMPLATE = template0 ...
"

It would be possible to do as you say, but then you would need to make 
the system nature of the process explicit by updating pg_database to 
include information on what the default template database, the 'clean' 
template and the 'default connection database' are. As it it stands now 
it is implicit in the naming and the use of those names by the server 
code and outside code.

> 
> By creating the initial three databases the system is more usable due to 
> having established conventions.  They are conventional databases, not 
> system ones.

Then remove them or rename them and see how long it takes the mail 
server to start glowing with posts to --general and --bugs.

The bottom line is the Postgres project has built an infrastructure 
around those names that the community is plugged into.

> 
> 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.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com