[CentOS] Postgresql 8

[Lamar Owen]

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.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu