Re: Fwd: Identify system databases

On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

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.