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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
