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

Mon, 13 Nov 2000 11:48:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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

Mon, 13 Nov 2000 12:02:17 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: Details for planned template0/template1 change
Lamar Owen <lamar.owen@wgcr.org> writes:
> 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.

I'm missing something --- I don't see how this affects pg_upgrade one
way or the other, except of course that it should be prepared to cope
with user data in template1 (not sure if it does or not now).

pg_upgrade won't be usable for the 7.1 transition anyway, because of WAL
changes (page header format is changing).  I dunno whether it will be
usable at all under WAL --- Vadim will have to comment on that.

			regards, tom lane

Mon, 13 Nov 2000 12:22:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Details for planned template0/template1 change 
At 11:48 13/11/00 -0500, Tom Lane wrote:
>
>5. pg_dump should ignore objects with OID <= lastsysoid of the target
>   database.

I think it should ignore objects with OID <= lastsysoid of template0; then
when it does a restore, it should use 'WITH TEMPLATE template0'. At least
this should be an option.

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

eg. while a restore script is running...

BTW: are there any nice ways to:

- set the flag
- kick current users off without corrupting memory

(ie. shutdown a single database).


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

Sounds fine; but we need to use lastsysoid of template0 in pg_dump. Consider:

- add function foo() to template1, OID=100000
- create db1 (inherits foo())
- update function foo() in template1, and also update it in db1. New OID >
100000
- dump db1 - will dump foo().
- restore db1 using template1->crash

However, dumping based oi lastsysoid of template0, then restoring based on
template0 works...


Otherwise sounds good!


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Tue, 14 Nov 2000 04:28:04 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Details for planned template0/template1 change
Philip Warner <pjw@rhyme.com.au> writes:
>> 5. pg_dump should ignore objects with OID <= lastsysoid of the target
>> database.

> I think it should ignore objects with OID <= lastsysoid of template0; then
> when it does a restore, it should use 'WITH TEMPLATE template0'.

Restores will be with template template0 anyway, but it seems to me that
dump should look to lastsysoid of the database being dumped (sorry if my
term "target database" confused you).  I'm not sure whether there's any
value in altering lastsysoid of individual databases to suppress dumping
of some items therein, but it can't hurt to retain the flexibility.

> BTW: are there any nice ways to:
> (ie. shutdown a single database).

Not at the moment.

> Sounds fine; but we need to use lastsysoid of template0 in pg_dump. Consider:

> - add function foo() to template1, OID=100000
> - create db1 (inherits foo())
> - update function foo() in template1, and also update it in db1. New OID >
> 100000
> - dump db1 - will dump foo().
> - restore db1 using template1->crash

No, because you aren't ever going to restore db1 using template1 as
template.  That's specifically what template0 is for.

			regards, tom lane

Mon, 13 Nov 2000 12:38:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Details for planned template0/template1 change 
Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > That is a great side-effect.  Now, if there were a way to initdb just
> > template0, leaving everything else in place, then rebuilding template1
 
> I'm missing something --- I don't see how this affects pg_upgrade one
> way or the other, except of course that it should be prepared to cope
> with user data in template1 (not sure if it does or not now).

Maybe I spoke too soon....
 
> pg_upgrade won't be usable for the 7.1 transition anyway, because of WAL
> changes (page header format is changing).  I dunno whether it will be
> usable at all under WAL --- Vadim will have to comment on that.

Of course, the upgrade from 7.0 to 7.1 involves a physical on disk
format change (implying pg_upgrade's uselessness in doing its job
there).  You know, our version numbers aren't at all consistent WRT disk
format.  ISTM that 6.5 should have been 7.0 due to its format change,
and 7.1 should be 8.0.

Changing from 6.5 to 7.0 is less of a format change than 6.4 to 6.5 or
7.0 to 7.1.

But, what's in a version number.... :-) They don't _have_ to be
consistent, really.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Mon, 13 Nov 2000 12:40:54 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: Details for planned template0/template1 change
At 12:38 13/11/00 -0500, Tom Lane wrote:
>>
>> I think it should ignore objects with OID <= lastsysoid of template0; then
>> when it does a restore, it should use 'WITH TEMPLATE template0'.
>
>Restores will be with template template0 anyway, but it seems to me that
>dump should look to lastsysoid of the database being dumped

I may have been a little unclear in my message (it was late), or I may have
misundertsood your response (it's now a little early), but I don't think
this will work; dump & restore must use the same baseline, and I think that
has to be template0.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Tue, 14 Nov 2000 10:28:01 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Details for planned template0/template1 change 
I've committed the template0/template1 changes we were discussing
earlier.  Plain pg_dump and pg_dumpall are changed to behave properly,
but I didn't touch pg_backup or pg_restore; can you deal with those?

			regards, tom lane

Tue, 14 Nov 2000 13:39:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Details for planned template0/template1 change 
At 13:39 14/11/00 -0500, Tom Lane wrote:
>I've committed the template0/template1 changes we were discussing
>earlier.  Plain pg_dump and pg_dumpall are changed to behave properly,
>but I didn't touch pg_backup or pg_restore; can you deal with those?

There's no such think as pg_backup, but pg_restore should work if pg_dump
is working. I'll have a look...


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 15 Nov 2000 09:34:50 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Details for planned template0/template1 change 