Feedback-ID: i76984098:Fastmail
Message-ID: <817abeac-d628-4279-bfef-9e1e3aa1884b@aklaver.com>
Date: Tue, 15 Apr 2025 09:30:56 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Fwd: Identify system databases
To: 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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CA+FnnTyWMXPEVAwNNDnCVDfmTFNHdDfffiF5vLioxyTv8Jmq0A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/817abeac-d628-4279-bfef-9e1e3aa1884b%40aklaver.com>
Precedence: bulk

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

> Hi, David,
> 
> On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Tuesday, April 15, 2025, Igor Korot <ikorot01@gmail.com
>     <mailto:ikorot01@gmail.com>> 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.

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

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com