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

This is a better description that I could make. If you look at the
script it is very well commented so you should be able to see it works. 
Also, read the manual page first. 

In summary, doing any kind of data changes is quite involved (smaller
tuple header for 7.3) and because it has to be redone for every release,
it is quite a pain.  Also, considering commercial databases don't do
much better, I fell pretty OK about it.  However, we do make releases
more frequently than commercial folks, so the pain is more consistent.

MySQL hasn't changed their base table format in perhaps 10 years, so
yea, that is a real win for them.  Of course, they don't shoot out
features as fast as we do so that helps.  You could pretend you are
using MySQL and just not upgrade for 5 years.  ;-)

---------------------------------------------------------------------------

Lamar Owen wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073