Thread: 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
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
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
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
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 |/
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
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
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 |/
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
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 |/
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? I still think that pg_dump needs to use the lastoid in template0 - did you fail to implement this because you disagree, or because you think it should use the current db lastsysoid? ---------------------------------------------------------------- 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 |/
Philip Warner <pjw@rhyme.com.au> writes:
> I still think that pg_dump needs to use the lastoid in template0 - did you
> fail to implement this because you disagree, or because you think it should
> use the current db lastsysoid?
I think it should use the current DB's lastsysoid, which is how I
left the code.
Given the present backend coding, all the DBs in an installation will
have the same lastsysoid as template0 anyway, barring manual
intervention. The only reason they'd be different is if the dbadmin
deliberately changed one in order to prevent pg_dump from dumping part
of that database. Now offhand I don't see a good *reason* for someone
to do that, but I don't see any reason to make it impossible to do it,
either.
regards, tom lane
At 23:20 14/11/00 -0500, Tom Lane wrote: >Philip Warner <pjw@rhyme.com.au> writes: >> I still think that pg_dump needs to use the lastoid in template0 - did you >> fail to implement this because you disagree, or because you think it should >> use the current db lastsysoid? > >I think it should use the current DB's lastsysoid, which is how I >left the code. > >Given the present backend coding, all the DBs in an installation will >have the same lastsysoid as template0 anyway, barring manual >intervention. Not the way the current 'CREATE DATABASE' code works - remember the changes to set the OID at create time? AFAICT, that's still there. If we want dump files to be portable, then we can not do anything other than backup & restore relative to template0. If we *do* backup relative to the template database used to create the DB, then we should be recording the template name in the backups as well, but this makes things even less portable. You never addressed the scenario I described in an earlier mail: - create function template1.foo - create db mydb from template1 - drop mydb.foo - create a new mydb.foo Both pg_dump & pg_dumpall will now dump the foo function, which means that they both have to be restored against template0, not 1. If the function were never dropped, then they would both need to be restored against template1. I really don't think this is ideal. There are only two solutions I can think of: - Set datlastsysoid to the same value as in template1 for all databases - Always dump (perhaps unless asked otherwise) using datlastsysoid of template1. Personally I would prefer the second. >The only reason they'd be different is if the dbadmin >deliberately changed one in order to prevent pg_dump from dumping part >of that database. Now offhand I don't see a good *reason* for someone >to do that, but I don't see any reason to make it impossible to do it, >either. The second solution above does allow this, but I am not even sure the option should be there. It is likely to cause more confusion & trouble than it's worth. It will probably never function the way people expect (especially in the absence of ALTER FUNCTION), and pg_dump/restore already provides a cleaner method to remove specific items at restore time. Going for what looks like a solution that will never work properly, when we already have a potential better solution in mind (the 'I am a system object' flag), seems ill-advised. ---------------------------------------------------------------- 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 |/
Philip Warner <pjw@rhyme.com.au> writes:
>> Given the present backend coding, all the DBs in an installation will
>> have the same lastsysoid as template0 anyway, barring manual
>> intervention.
> Not the way the current 'CREATE DATABASE' code works - remember the changes
> to set the OID at create time? AFAICT, that's still there.
Look again. lastsysoid is now inherited from the database being cloned.
AFAICT all your subsequent comments are based on that
misunderstanding...
regards, tom lane
At 23:48 14/11/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>>> Given the present backend coding, all the DBs in an installation will
>>> have the same lastsysoid as template0 anyway, barring manual
>>> intervention.
>
>> Not the way the current 'CREATE DATABASE' code works - remember the changes
>> to set the OID at create time? AFAICT, that's still there.
>
>Look again. lastsysoid is now inherited from the database being cloned.
It looks to me like that was the intent of the code; but there is still:
tuple = heap_formtuple(pg_database_dsc, new_record, new_record_nulls);
tuple->t_data->t_oid = dboid; /* override heap_insert's OID
selection */
heap_insert(pg_database_rel, tuple);
which I think stuffs up everything, since dboid was allocated earlier in
the same call. But I agree, the code looks like it tries to do what you said.
I could have missed something, though.
>AFAICT all your subsequent comments are based on that
>misunderstanding...
Pretty much. Sorry. Is there a smiley for embarrasment?
----------------------------------------------------------------
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 |/
At 15:59 15/11/00 +1100, Philip Warner wrote: > >It looks to me like that was the intent of the code; but there is still: > ... > >Pretty much. Sorry. Is there a smiley for embarrasment? > I really need that smiley now. Just saw my mistake. ---------------------------------------------------------------- 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 |/