<pbj@cmicdo.com> writes:
>>> On 12/23/2015 04:17 PM, Paul Jones wrote:
>>> This isn't the best characterization...the "postgres" data is not a "system" database but rather a convenient
defaultuser database. Maybe I'm being overly picky here but seeing "system" in this context does have a connotation
thatwe really don't want to impart onto the "postgres" database.
> This was one of the big lessons I learned from this. All this time I was
> under the mistaken impression that it was special.
FWIW, there really aren't any special databases in a Postgres
installation. As Paul already explained, "postgres" exists mainly to
provide a convenient default landing point for utility applications.
The only thing magic about it is the name --- you can drop it, and
create a new database with that same name, and be none the worse for
wear. Or do without, if you don't mind specifying a different landing
point.
The other two created-by-default databases, template0 and template1,
are similarly not really hardwired in. template1 is hardwired to
the extent that CREATE DATABASE operates by copying whatever database
is named "template1" (in the absence of a different TEMPLATE option).
That's about it. There is a convention that template0 should be an
absolutely unmodified virgin copy of the state created by initdb,
but it's only a convention not something that the core database code
either enforces or depends on.
Now, if you were to try to drop either of those databases, you'd get
a Bronx cheer:
regression=# drop database template1;
ERROR: cannot drop a template database
regression=# drop database template0;
ERROR: cannot drop a template database
but that's only driven by the fact that pg_database.datistemplate is set
for them (and that flag does little more than allow nonprivileged users
to select them as sources for CREATE DATABASE). If you were up against
the wall in trying to reconstitute a broken installation, you could
manually unset the datistemplate flag for either, drop it, and clone it
from the other one.
In short, there's less magic here than meets the eye.
regards, tom lane