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