Re: RPMS for 7.3 beta.

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