Details for planned template0/template1 change

Here's what I'm planning to do to make the world safe for backup/restore
of user-added template1 data:

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.)

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
a database that isn't marked "dbistemplate" in pg_database, you must
be superuser or owner of the source database. In any case, you can't
clone a database that has any active connections other than your own
(implementation restriction to ensure we don't copy inconsistent
data from transactions-in-progress).

3. initdb will create two identical databases named template0 and
template1. Both will be marked dbistemplate, but only template1
will be marked dballowconn.

4. CREATE DATABASE will always create new databases with dbistemplate =
false and dballowconn = true. Also, the lastsysoid will be copied
from the source database.

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.

Discussion:

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.

template0 will be a real database, just not one you can connect to
(unless you are so foolhardy as to flip its dballowconn bit ;-)).
This is to prevent people from changing it, accidentally or otherwise.
What we really want is a read-only database, but implementing a
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.

lastsysoid will probably always be the same for all databases in an
installation, since they'll all inherit the value from template0 or
template1. However, there is the possibility of changing it to exclude
some items from backup, so I'm continuing to treat it as a per-database
value.

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.

Comments?

regards, tom lane