public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Igor Korot <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Fwd: Identify system databases
Date: Tue, 15 Apr 2025 09:48:49 -0700
Message-ID: <CAKFQuwbkQTbUwA4c6LFOMUWX5ojQY8DpKPQRh+xoe4VZABPWfw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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>
<[email protected]>
On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver <[email protected]>
wrote:
> On 4/15/25 09:21, Igor Korot wrote:
> >
> >
>
> > Hi, David,
> >
> > On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston
> > <[email protected] <mailto:[email protected]>> wrote:
> >
> > On Tuesday, April 15, 2025, Igor Korot <[email protected]
> > <mailto:[email protected]>> 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.
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fwd: Identify system databases
In-Reply-To: <CAKFQuwbkQTbUwA4c6LFOMUWX5ojQY8DpKPQRh+xoe4VZABPWfw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox