Re: Details for planned template0/template1 change

Tom Lane wrote:
> 1. pg_database will have two new boolean columns, dbistemplate and
>    dballowconn --- if dballowconn is false then no new connections to that
>    database will be accepted.  (An even better solution would be to make
>    a "dbisreadonly" attribute, but implementing such a thing would be a
>    bigger change than I have time for now.)

I like the dballowconn, but an eventual dbreadonly might prove useful
for static data sources.
> 2. CREATE DATABASE will accept a new option "TEMPLATE = name", where the
>    name is the name of an existing database to be cloned.  To clone

Reasonable implementation.
> 5. pg_dump should ignore objects with OID <= lastsysoid of the target
>    database.  pg_dumpall should ignore databases not marked dballowconn,
>    and should save/restore the setting of dbistemplate.  All databases
>    created by a pg_dumpall script will be created WITH TEMPLATE template0.

Finally, a good backup of user data in template1.  I've documented the
fact that template1 data wasn't dumpable in the RPM README, as of last
year -- I'll be rather happy to remove that paragraph. :-)  Since
upgrading involves dump/restore, and the dump of template1 user data
didn't work, template1 data was unupgradeable.
> dbistemplate is intended to avoid hard-wiring any notion about which
> database(s) can be the template for CREATE DATABASE.  I started out
> intending to allow templates named "templateSOMETHING", but a flag
> column seems a better idea.

The less that is hardwired, the better.  That is, after all, part of the
Postgres Paradigm.
> restriction like that looks like too much work for 7.1.  I think that
> a dballowconn flag might have other uses anyway, such as temporarily
> disallowing new connections to a database you are doing major work in.

This could potentially be a very useful feature -- I know of more than
one instance I could have used such (instead of stopping postmaster,
then restarting on another port in order to do major work of various
sorts).
> With this scheme, template1 is actually not special except for being
> the default CREATE DATABASE template and the default connection target
> for various scripts like createdb.  You could drop it and recreate it
> from template0, if you were so inclined --- this could be a recovery
> method if template1 got messed up.

That is a great side-effect.  Now, if there were a way to initdb just
template0, leaving everything else in place, then rebuilding template1
-- of course, a similar ability is there now, but the two-stage initdb
this implies could make pg_upgrade work smoother, in cases where the
system catalogs are the only change from one major version to the next.

All in all, sounds like you;ve done your homework again, Tom.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11