Re: Resurrecting pg_upgrade

Tom Lane wrote:

>Dave Smith <dave.smith@candata.com> writes:
>  
>
>>Why not go the other way. 
>>1) Dump the schemas.
>>2) Initdb with the new schemas in a tmp PGDATA
>>3) backup the schemas in the current PGDATA
>>4) move the new schemas from the new db into the current one.
>>    
>>
>
>This seems like approximately the same thing except you lose the
>property of not having modified the old DB if you fail partway through.
>What's the advantage exactly?
>
>  
>
I do not think that approach buys you much.  More than just the schemas
change from each major release.  The binary (on-disk) format of the
relations can change as well, hence the need for the upgrade program.  A
schema with corrupt data is worthless.

**

Warning the user to backup the PGDATA directory, should be sufficient,
IMHO.  Perhaps even echo a URL to the postgresql.org site for specific
backup and upgrade procedures and recommendations.  With a full copy of
the PGDATA directory an admin can copy the data back reinstall the old
version of postgresql and do a postmortem while the old version is still
operational without having to keep the service unavailable.  If someone
is absolutely certain the upgrade will work without an errors then they
can holster their loaded gun with the safety off.

If there is an error the data can be copied back, old postmaster
started, and possibly correct the problem (maybe a reindex operation or
the like).  Then repeat the upgrade procedure.

This approach seems much more simple and flexible as the admin could
backup the database to tape or some other medium, possibly multiple
volumes, and then do the upgrade in place.

**

If the pg_upgrade program were to read/copy old data and output a new
data doubling the storage requirements, then you have a quick way to
restart the upgrade procedure on failure without having to load the old
data again.  It seems to me that an error in the upgrade program would
likely happen again at the same point on a repeat attempt, so I don't
think there are any significant advantages to the upgrade program doing
the copy/backup operation.   Exceptionally large databases would have to
find additional storage for the copy operation.  If the copy and upgrade
approach were to be followed, it would be advantageous to the admin to
be able to specify where the copy of the existing PGDATA would go or the
newly generated files could go before they could be moved back to the
PGDATA directory.

>>This means that doing an update you would only have to have space for
>>the system catalogs  not the whole database. 
>>    
>>
>
>That's true either way.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>  
>