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
connectionsto that database will be accepted. (An even better solution would be to make a "dbisreadonly" attribute,
butimplementing 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
becloned. To clone a database that isn't marked "dbistemplate" in pg_database, you must be superuser or owner of the
sourcedatabase. In any case, you can't clone a database that has any active connections other than your own
(implementationrestriction 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
onlytemplate1 will be marked dballowconn.
4. CREATE DATABASE will always create new databases with dbistemplate = false and dballowconn = true. Also, the
lastsysoidwill be copied from the source database.
5. pg_dump should ignore objects with OID <= lastsysoid of the target database. pg_dumpall should ignore databases
notmarked dballowconn, and should save/restore the setting of dbistemplate. All databases created by a pg_dumpall
scriptwill 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
