Hi,
On Wed, Nov 09, 2022 at 09:16:40PM -0800, Adrian Klaver wrote:
> On 11/9/22 20:57, Ian Lawrence Barwick wrote:
> >
> > template0 and template1 are the mandatory system databases which
> > cannot be dropped.
>
> Actually that is not strictly true:
>
> https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
>
> "
> Note
>
> template1 and template0 do not have any special status beyond the fact that
> the name template1 is the default source database name for CREATE DATABASE.
> For example, one could drop template1 and recreate it from template0 without
> any ill effects. This course of action might be advisable if one has
> carelessly added a bunch of junk in template1. (To delete template1, it must
> have pg_database.datistemplate = false.)
>
> The postgres database is also created when a database cluster is
> initialized. This database is meant as a default database for users and
> applications to connect to. It is simply a copy of template1 and can be
> dropped and recreated if necessary.
> "
And one important thing about template0 is that postgres will assume that it
only contains identifiers with plain ASCII characters, so that the on-disk data
is compatible with any encoding (at least any supported server encoding, stuff
like EBCDIC clearly wouldn't play well with that assumption), and can therefore
be used to create a new database with a different encoding from template1 (or
another template database). Breaking that assumption is usually a very bad
idea.