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