On Monday 17 October 2005 09:25, James B. Byrne wrote:

Does anyone here know of a repository that has PostgreSQL v.8 for CentOS-4? If so, where is it?

No repository, but the canonical location for downloading PostgreSQL is from ftp.postgresql.org's mirrors. Let's see: ftp://ftp.us.postgresql.org/pub/mirrors/postgresql/binary/v8.0.4/linux/rpms/redhat/rhel-es-4/ has what you want, I believe. No, it's not yummified. Due to the upgrade issues referenced below, this is, IMO, a Good Thing.

Are there caveats that I should know about?

Yes, several.

PostgreSQL major version upgrades require a specific procedure to be followed, that, if you don't follow it exactly, might render your database unusable until you downgrade (it will never destroy data, but you just won't be able to get to it until to reinstall the older version). Now, if you don't have any data stored on the existing version, you can upgrade to your heart's content (but you might have to rebuild some things from source RPM, like php's PostgreSQL support, due to a different client library version).

The upgrade procedure is fully documented in the main PostgreSQL documentation, but, in a nutshell: Dump your old database while the old version is running (see the man page for pg_dumpall); Save that dumpfile somewhere the RPM's don't touch (that is, move it out of /var/lib/pgsql) Stop postmaster (service postgresql stop is standard; some Red Hat versions have used service rhdb or service rhpostgresql) Blow away the contents of /var/lib/pgsql/data, saving a copy of postgresql.conf and pg_hba.conf first. Upgrade the postgresql packages. Start the postmaster with 'service postgresql start' as root. This also initializes the database tree under /var/lib/pgsql; you don't have to separately initdb. However, if I remember correctly, there are some odd SELinux issues; hmm... see https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=152591 https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=143208 and, for a high-level view: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=149237 (also read https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=159503 for some really interesting discussion and input from Tom Lane. Tom is one of the core PostgreSQL hackers, to put it into perspective).

In a nutshell, setenforce 0 then start postmaster, then stop postmaster, then setenforce 1 again.

Now, back to the upgrade: Once the database is initialized and postmaster is running, restore your dump from the earlier step (see the pg_restore manpage for options).

For most databases this should work; there are exceptions, particularly if you've developed custom server-side functions in C (they have to be recompiled and re-created with the new version).

As mentioned previously, you are putting in a new version of libpq.so; this breaks many things if you aren't prepared for it. ANYTHING linked to the older libpq.so will fail dependencies, and will need to be rebuilt from source RPM and installed. You will then have to do this everytime a security update is issued for that package. This impacts things like php's pgsql support and perl's DBI postgresql support, for starters. I think, but am not sure, that there is a workaround in the 8.0.x RPM's; since I passed the baton of maintainership to Devrim, I'm not as up on it as I used to be when I did all the packaging.