Re: [patch] pg_upgrade script for 8.3->8.4

On Thu, 4 Dec 2008, Zdenek Kotala wrote:

> 1) Keep relfileid of toast file same. It is important because toast pointer 
> contains relfileid. Currently script creates fake files with same number to 
> protect postgresql to create new relation with this refileid. It works but by 
> my opinion it is not much robust. I suggest to use following syntax:
>
> create table foo (id int) with (relfileid=16544, reltoastid=11655, 
> reltoastidx=16543)

But once there's a more core integrated in-place upgrade, as envisioned 
for 8.4->8,5, this syntax wouldn't been useful for anything anymore, 
right?  I understand your distaste for the hack, but it seems a bit 
extreme to start fiddling with CREATE TABLE and pg_dump* just to implement 
a work-around for a temporary problem.  I think your magic is strong 
enough for this, as long as the upgrade script does some sanity checks and 
proceeds cautiously I think we can live with it.

> Another problem with tablespace location is that CREATE TABLESPACE 
> checks if directory is empty and it fails when it contains any 
> file/directory...Suggested sugar syntax is:
> CREATE DATABASE foobar WITH ID=17012;
> CREATE TABLESPACE bar LOCATION '/dev/null/' ID=15543 IGNORECONTENT;

The logic of the above continues here; the "id=xxx" construct seems like 
it will be useless clutter in the future, and if the script is capable of 
sorting it out with a bit of hacking then go with that.  The way I see 
things, the sequence of events will go like this:

-Create new cluster
-Restore schema
-Fiddle with IDs, table spaces, etc.
-Then, only if there were no errors or problems with the above, are the 
old files moved over.

As long as all the hacking and safety checks happen before any of the 
original files are touched at all, it's kind of ugly but it should work 
well enough for the target audience:  relatively saavy DBAs who just can't 
take the time for a dump/reload because their database is too large.  The 
kind of admins who doesn't know how to test and stage a major version 
upgrade with appropriate backups are the group that are still running 
PG7.4.[1]

I'm not sure if the same logic applies to the "IGNORECONTENT" suggestion 
for tablespaces though.  I know I've been vaguely annoyed by that 
limitation before, typically because it would have been easier to directly 
use a new mount point as a tablespace except that there's a "lost+found" 
directory in there.  End up adding another directory level for no good 
reason.  This seems like a relatively small caliber foot-gun to provide; 
will have to take a look at the code to see if I still feel that way 
afterwards, if nobody jumps up to protest the whole concept first that is.

[1] I hereby propose Greg's Law of DBAs:  the larger and more critical a 
database is, the more likely it is to attract a clueful DBA to take care 
of it.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD