Subject: Fwd: Identify system databases

From: Igor Korot <[email-masked]>
Date: Tue, 15 Apr 2025 11:21:15 -0500

---------- Forwarded message ---------
From: Igor Korot <[email-masked]>
Date: Tue, Apr 15, 2025 at 11:15 AM
Subject: Re: Identify system databases
To: David G. Johnston <[email-masked]>

Hi, David,

On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston <
[email-masked]> wrote:

> On Tuesday, April 15, 2025, Igor Korot <[email-masked]> wrote:
>
>> Hi, ALL,
>> Is there a field in the pg_databases table which indicates that
>> particular DB is a system one?
>>
>
> What is a system database?
>

I consider system database a database that is created by default when the
server is run for the first time.

Kind of a predefined set of databases…

As an example - there is a database called Postgres. It is created when the
server runs for the first time, whether I as a user like it or not.

Thank you.

> David J.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 15 Apr 2025 09:30:56 -0700

On 4/15/25 09:21, Igor Korot wrote:

> Hi, David,
> 
> On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     On Tuesday, April 15, 2025, Igor Korot <[email-masked]
>     <mailto:[email-masked]>> wrote:
> 
>         Hi, ALL,
> I consider system database a database that is created by default when 
> the server is run for the first time.

Agreed.

The fact that initdb creates the template0, template1 and postgres 
databases and you can't change that makes them system not user databases.

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

> As an example - there is a database called Postgres. It is created when 
> the server runs for the first time, whether I as a user like it or not.
> 
> Thank you.
> 
> 
>     David J.
> 

-- 
Adrian Klaver
[email-masked]

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 15 Apr 2025 09:48:49 -0700

> 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 in pg_database
identifying system databases - there are none.

The system will continue to operate if you do:

initdb
createdb newdb
psql -c 'alter database template0 is_template false;'
dropdb template0
psql -c 'alter database template1 is_template false;'
dropdb template1
dropdb --maintenance-db newdb postgres

An operational definition worth considering, though, is that any database
owned by the bootstrap superuser is a system database.  After all, the
system owner created/owns them?  If you want non-system databases for your
application, assign their ownership to a non-system role.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 15 Apr 2025 11:20:08 -0700

On 4/15/25 09:48, David G. Johnston wrote:
> On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 

> 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 in 
> pg_database identifying system databases - there are none.

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

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

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