YUM 2: Almost Idiot's Guide to upgrade from PostgreSQL 8.3.1 to 8.3.3

UPDATE we have new instructions for PostgreSQL 9.0 -- refer to Installing PostgreSQL 9.0 via Yum

In our April Issue An Almost Idiot's Guide to PostgreSQL YUM we covered using the new PostgreSQL Yum repository to install the PostgreSQL 8.3.1 release on Fedora, RedHat Enterprise, and CentOS. We also received numerous useful feedback from others on issues they ran into and how they overcame them. The blog comments are definitely worth a read.

Now that 8.3.3 has come out, many of you should be considering upgrading if you haven't already since there are a couple of bug fixes as outlined in http://www.postgresql.org/docs/8.3/static/release-8-3-2.html, http://www.postgresql.org/docs/8.3/static/release-8-3-3.html, and for those running 8.3.0 you will need to reindex your tables after as noted in http://www.postgresql.org/docs/8.3/static/release-8-3-1.html. If you are running version 8.3.1 and above then this is a fairly painless upgrade that just requires you to backup your data as a precautionary measure, but doesn't require a dump reload.

In addition to the full dump or instead of the full dump if you are short on space or patience, you may want to selectively dump out important databases with a

pg_dump -i -h localhost -p 5432 -U postgres - F c -b -v -f "\somepath\somedb.backup" somedb
pg_dump -i -h localhost -p 5432 -U postgres - F c -b -v -f "\somepath\postgres.backup" postgres

With a compressed backup of the database vs. the pg_dumpall (well its good to have a pg_dumpall version too), you can restore portions of a database from the backup file. A pg_dumpall backup doesn't alot you that convenience, but does alot you the advantage of being able to restore your whole PgServer.

NOTE: If you made custom changes to your postgresql startup service script /etc/rc.d/init.d/postgresql make sure to back it up. The upgrade wipes it out with the prepackaged version.
cp /etc/rc.d/init.d/postgresql /etc/rc.d/init.d/postgresql.080705

Now to upgrade your YUM install from 8.3.1 to 8.3.3 do the following:

yum list postgresql
this should return output something of the form

rhel-i386-server-5        100% |=========================| 1.4 kB    00:00
pgdg83                    100% |=========================| 1.9 kB    00:00
rhel-i386-server-vt-5     100% |=========================| 1.4 kB    00:00
rhn-tools-rhel-i386-serve 100% |=========================| 1.2 kB    00:00
Reading repository metadata in from local files
primary.xml.gz            100% |=========================| 1.5 MB    00:01
################################################## 4206/4206
primary.xml.gz            100% |=========================|  25 kB    00:00
################################################## 90/90
primary.xml.gz            100% |=========================|  24 kB    00:00
################################################## 131/131
Installed Packages
postgresql.i686                          8.3.1-1PGDG.rhel5      installed
Available Packages
postgresql.i386                          8.3.3-1PGDG.rhel5      pgdg83

yum update postgresql
It should then download the updates and say somethong to the effect Total download size: 8.3 M Is this ok [y/N]:
y
Note the below may fail if you have made custom changes to your postgresql service script such as the location of the database cluster. Make sure to restore your original and then run the below. service postgresql restart
If you had installed pgagent as well before also do a service pgagent restart

As a final step - connect to your postgresql server via PgAdminIII or psql or whatever tool of choice you prefer and run the following to verify you are running the latest version:

SELECT version();