Upgrading rant.

It's that time of year again, when I remind everyone just how difficult life
in the trenches with PostgreSQL can be, when the life in the trenches
involves upgrades. If you don't want to read about it, then please hit
DELETE in you e-mail (or nntp) client.

I'll not get too vehement this time (well, I'll try not to), but I am
continuously bombarded with requests for help in upgrading. These requests
really bother me, particularly since I believe PostgreSQL is the finest Open
Source database, period.

I have attempted to help in this by building some older PostgreSQL versions on
more modern Red Hat distributions; alas and alack, some relatively recent
versions of PostgreSQL will simply not build on more recent Red Hat.

Case in point: I tried to help out a fellow (Tony) who upgraded from Red Hat
7.1 (I believe) to Red Hat 8.0. Red Hat 8.0 includes PostgreSQL 7.2.2, and
Red Hat 7.1 has PostgreSQL 7.1.something. Good thing he didn't go back to
Red Hat 7.0 (PG 7.0....).

So I figured I'd roll a 7.1.3 RPMset for him to install onto Red Hat 8. It
was very bad. It simply would not build -- I guess it's the gcc 3 stuff. He
can't downgrade! (Really! Red Hat 8 upgrades more than just PostgreSQL --
the upgrade wiped out libraries that PostgreSQL 7.1 on the previous Red Hat
needed to function. The RPM installation of PostgreSQL 7.1 from the previous
Red Hat would NOT reinstall.). So this man is up the creek, without a
paddle, in a chicken-wire canoe WRT his 7.1 data. This is unacceptable.

THIS DOESN'T HAPPEN WITH MySQL. I'm more than a little perturbed that, as
MySQL adds features, it doesn't make you upgrade your database each release:
it simply doesn't allow the features your database doesn't support. You can
then migrate each table as you need the new features.

While he really should have read our documentation and been a little more
careful, we shouldn't be so anal about upgradability, either. I know it's
been hashed to death, but the problem isn't going away anytime soon. I'm
afraid that this is going to become a key feature, and one we are missing,
but our primary Open Source competition isn't missing.

And I _know_ some are just going to fingerpoint and blame Red Hat. Any such
replies I will rather quickly redirect to /dev/null, as it isn't Red Hat's
fault we can't do a sane upgrade. Others are going to handwave and say
'we're so advanced we can't upgrade', and still others are going to say
'Oracle can't do it; why whould we?' These replies also will meet the
bottomless bit bucket -- I'm not interested in arguing whether we should
allow good upgrading or not, so don't bother trying to convince me upgrades
aren't important, or 'dump/initdb/restore IS an upgrade!' I am interested in
sane discussion of how to make it happen.

Red Hat at least has a data dumper, but even at that it isn't an easy task to
upgrade. (source.redhat.com/rhdb)

I believe, as I have said before, that the biggest problem preventing easy
upgrades is our tight coupling of system catalog data with user data, in the
same tablespace. If the system catalog data were less tightly coupled, then
it might be an easier job. I also know, from the last time this was
discussed, that drawing the line between 'system' and 'user' data is very
difficult due to our highly extensible nature.

I thought the last time through would be the _last_ time through -- but I also
thought I'd be able to build older PostgreSQL packages for newer dists, which
would prevent much of this problem. (OS upgrade hosed your PostgreSQL? Here's
packages for your old PostgreSQL built for your shiny new OS!)

In my opinion, upgrading shouldn't be something a user should have to even
think about. It should just happen. Kindof like 'space reuse should just
happen' too.... Postmaster should have a fallback mode when it starts up in
PGDATA where PGVERSION is < postmaster version. This would take care of
ninety percent or more of upgrades -- the user can dump and restore later if
need be, or a migration tool can be written, or... this is where I'd like to
see more discussion and less of a back burner approach. And I'd love to see
someone who has the time to do so (not me) grab this bull by the horns and
make it happen.

(Yes, I realize the use of certain of our extensibility features will be
impossible to upgrade cleanly, but that's what you get when you allow
embedded C code in the backend.) I'm talking about the majority of cases
where a user simply has some relational data (no custom functions, types, or
operators) that is critical to their business that they need to move over
QUICKLY. (dump/restore is anything but quick). And some are going to do this
upgrade on their production server, regardless of how many times we tell
people not to do so. Not every entity who uses PostgreSQL has on staff a
professional DBA and an extra server to do the migration with.

MySQL is even touting the ability to quickly upgrade, at this point (January
2003 Linux Magazine article on MySQL 4). I'm sorry, but that just gets under
my skin.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11