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