public inbox for [email protected]help / color / mirror / Atom feed
Fwd: Identify system databases 4+ messages / 3 participants [nested] [flat]
* Fwd: Identify system databases @ 2025-04-15 16:21 Igor Korot <[email protected]> 2025-04-15 16:30 ` Re: Fwd: Identify system databases Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Igor Korot @ 2025-04-15 16:21 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> ---------- Forwarded message --------- From: Igor Korot <[email protected]> Date: Tue, Apr 15, 2025 at 11:15 AM Subject: Re: Identify system databases To: David G. Johnston <[email protected]> Hi, David, On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston < [email protected]> wrote: > On Tuesday, April 15, 2025, Igor Korot <[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. 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. > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fwd: Identify system databases 2025-04-15 16:21 Fwd: Identify system databases Igor Korot <[email protected]> @ 2025-04-15 16:30 ` Adrian Klaver <[email protected]> 2025-04-15 16:48 ` Re: Fwd: Identify system databases David G. Johnston <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Adrian Klaver @ 2025-04-15 16:30 UTC (permalink / raw) To: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> 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. 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 [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fwd: Identify system databases 2025-04-15 16:21 Fwd: Identify system databases Igor Korot <[email protected]> 2025-04-15 16:30 ` Re: Fwd: Identify system databases Adrian Klaver <[email protected]> @ 2025-04-15 16:48 ` David G. Johnston <[email protected]> 2025-04-15 18:20 ` Re: Fwd: Identify system databases Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: David G. Johnston @ 2025-04-15 16:48 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fwd: Identify system databases 2025-04-15 16:21 Fwd: Identify system databases Igor Korot <[email protected]> 2025-04-15 16:30 ` Re: Fwd: Identify system databases Adrian Klaver <[email protected]> 2025-04-15 16:48 ` Re: Fwd: Identify system databases David G. Johnston <[email protected]> @ 2025-04-15 18:20 ` Adrian Klaver <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2025-04-15 18:20 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 4/15/25 09:48, David G. Johnston wrote: > On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver <[email protected] > <mailto:[email protected]>> wrote: > > > 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. 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? > David J. > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-04-15 18:20 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-15 16:21 Fwd: Identify system databases Igor Korot <[email protected]> 2025-04-15 16:30 ` Adrian Klaver <[email protected]> 2025-04-15 16:48 ` David G. Johnston <[email protected]> 2025-04-15 18:20 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox