Re: RPMS for 7.3 beta. - Mailing list pgsql-hackers

On Tuesday 17 September 2002 10:27 pm, Christopher Kings-Lynne wrote:
> Lamar Owen wrote:
> > Sorry, it's just a sore spot for me, this whole
> > upgrade issue.

> IS there any solution to Postgres's upgrade problems?  I mean, ever?  With
> the complex catalog design, etc - how is it every possible for us to do a
> plug-n-play major version upgrade (assuming datafile format doesn't change
> anymore)

While I should know better, I'm going to reply.....:-)

The system catalog has poor system-user separation.  Better separation might 
help the issue.  Putting all the stuff that belongs to system into the 
'system' catalog and then putting the user's customizations into a 'user' 
catalog, with a set of SQL scripts to upgrade the user portion if columns or 
other metadata changed in the user portion.  This statement is vastly 
simplified.  Then you can blow out the system portion and reinit it without 
disturbing the user data and metadata.  The problem I believe would be 
enforcing a strict enough demarcation to make that possible.  Then there's 
the nontrivial issue of where the point of demarcation lies.  But I should 
let someone better versed in the system catalog structure answer that.

<heresy>
I'd give up a few extensibility features for solid upgrading.  If I didn't 
have so much invested in PostgreSQL I might take a hard look at MySQL 4, 
since data migration has heretofore been one of their few real strengths.  
But I've got three years of RPM maintenance and five years of infrastructure 
built on PostgreSQL, so migrating to something else isn't a real palatable 
option at this point.
</heresy>

> How does pg_upgrade work?

If I am not mistaken pg_upgrade attempts to do just exactly what I described 
above, moving data tables and associated metadata out of the way, initdb, and 
move the data back, rebuiding the system catalog linkages into the user 
metadata as it goes. And it works in a state where there is mixed metadata.  
At least that's what I remember without looking at the source code to it -- 
the code is in contrib/pg_upgrade and is a shell script.  For laughs I have 
the source code in another window now, and it is rather involved, issuing a 
number of queries to gather the information to relink the user metadata back 
in.

It then vacuums so that losing the transaction log file (!!) isn't fatal to 
the upgrade.

It then stops postmaster and moves things out of the way, then an initdb is 
performed.  The schema is restored; the transaction statuses are restored, 
and data is moved back in, into the proper places.  Moving back into the 
proper places is nontrivial, and the existing code makes no attempt to 
rollback partial upgrades.  That failing could be fixed, however.

Then:
# Now that we have moved the WAL/transaction log files, vacuum again to
# mark install rows with fixed transaction ids to prevent problems on xid
# wraparound.

Like I said, it's involved.  I'm not sure it works for a 7.2.2-> 7.3 upgrade.  

If the on-disk binary format has changed, tough cookie.  It won't help us, 
since it doesn't make any effort to convert data -- it's just moving it 
around and recreating the metadata linkages necessary.

Now if a binary data converter could be paired with what pg_upgrade is 
currently doing, it might fly.  But scattered in the code is the discouraging 
comment:
# Check for version compatibility.
# This code will need to be updated/reviewed for each new PostgreSQL release.

Keeping abreast of the changing formats and the other 'gotchas' is just about 
going to be a full-time job, since changes are made to the system catalogs, 
syntax, semantics, and data format with little regard as to how it will 
impact data migration.  IOW, migration/upgrading shouldn't be an afterthought 
if it's going to work right.

I wish (in a somewhat wistful, yet futile manner) that each change was 
accompanied by data migration strategies for that change, but I'm not holding 
my breath, since the core developers have more important things to do.  (Not 
being sarcastic -- just observing a fact).

Oh well.  Chris, you got me wound up again... :-(  I wish I had the time and 
funding to go after it, but I have a full-time job already as a broadcast 
engineer, and while we use PostgreSQL in a mission critical role here, I 
can't justify diverting other monies for this purpose.  Money is tight enough 
already.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11