Instructions for Upgrading PostreSQL

It is occassionally required to upgrade PostgreSQL (the database management system) separately from a Linux install.  For the record, here are the procedures used to upgrade from PostgreSQL 7.4 (the one incorporated in the Fedora Core 2 release) to PostgreSQL 8.1.2 (the most recent version at the time of this document.

  1. First, make a backup of all of your databases with the command
pg_dumpall -U postgres > database_dump

You should also make a copy of the output file somewhere safe (e.g. on a CD) in case anything subsequently screws up.
  1. Log in as root and download the PostgreSQL version from www.postgresql.org or one of its mirror sites.  You'll be provided with many options, take the most comprehensive.  This is the largest version that ends in a .tar.gz suffix.  It can be downloaded anywhere, but since the downloaded version is unnecessary after installation I generally download it to the /tmp directory.
  2. Unzip it with the command "tar -zxf postgresql-8.1.2.tar.gz (changing the version appropriately).
  3. Stop any currently running versions with the command "service postgresql stop".
  4. Change directory into the postgres source directory (e.g. postgres-8.1.2 in this case) and issue the following commands:
./configure --prefix=/usr; make; make install

Note that the "--prefix=/usr" argument is required for correct configuration.
  1. Get rid of the old database directories with the command "rm -rf ~postgres/data"
  2. Restart the database with the command "service postgresql start".  You should be notified that the data directory is being initialized.
  3. Assuming you autostart PostgeSQL at systemn boot you'll want to update the PGVERSION reference in /etc/init.d/postgresql to the new version (8.1.2 in this example). 
  4. Log out as root and log back in as the user that you originally dumped the database (in step 1).  Reload the old data with the command
psql -U postgres -d postgres -f database_dump > /dev/null

You'll get some errors that are OK for this database update.  This is because the database commands have been updated.  Here's a typical output from an installation that worked:

psql -d postgres -f database_dumpall_1feb06 > /dev/null
psql:database_dumpall_1feb06:11: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:database_dumpall_1feb06:13: NOTICE:  SYSID can no longer be specified
psql:database_dumpall_1feb06:14: NOTICE:  SYSID can no longer be specified
psql:database_dumpall_1feb06:21: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:database_dumpall_1feb06:81: NOTICE:  using pg_pltemplate information instead of CREATE LANGUAGE parameters
psql:database_dumpall_1feb06:146356: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "grouplist_pkey" for table "grouplist"
psql:database_dumpall_1feb06:146365: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "siop_pts_pkey" for table "siop_pts"
psql:database_dumpall_1feb06:146374: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "targetlist_pkey" for table "targetlist"
psql:database_dumpall_1feb06:146383: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "objectdefn_pkey1" for table "objectdefn"
psql:database_dumpall_1feb06:146392: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "medcrossref_pkey" for table "medcrossref"
psql:database_dumpall_1feb06:146401: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "catcrossref_pkey" for table "catcrossref"
psql:database_dumpall_1feb06:146410: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "platformchar_pkey" for table "platformchar"
psql:database_dumpall_1feb06:146419: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pga_graphs_pkey" for table "pga_graphs"
psql:database_dumpall_1feb06:146428: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pga_diagrams_pkey" for table "pga_diagrams"
psql:database_dumpall_1feb06:146437: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pga_images_pkey" for table "pga_images"

FYI, the number after the input file name is the line number in this file associated with the message.  The warnings associated with lines 81 through 146437 will repeat for every loaded database (obviously with different line numbers).  The errors associated with lines 11, 13, 14, and 21 are errors associated with database upgrades.  Review has proven that these errors are unimportant when a new database system is being installed, as is the case here.  So ignore these errors for upgrading from version 7.4 to 8.1.  But it's important to verify this is the case in future upgrades.

Finally, you'll want to configure the database for ODBC connections.  To do this edit ~postgres/data/pg_hba.conf and set up the security section as required.  Under version 8.1.2 the following lines permitted ODBC connections from the local machine and any other machines on my two local LANS (192.168.0.x and 207.225.107.8 through 207.225.107.15):

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
local   all     all             ident   sameuser
host    all         all         127.0.0.1/32           ident sameuser
host    all         all         127.0.0.1  255.255.255.255   trust
host    all         all         207.225.107.8  255.255.255.248   trust

I think some of these lines are unnecessary, but have not had the time to minimize the commands yet.

Remember to restart the database after changing this file by issueing the following command as root:

service postgresql restart

10.  While not always required, it's often advisable to update the ODBC bridge software as well.  Unfortunately, this is no longer part of the core postgresql package, so you'll have to download, build and link it separately.  To do so:
psqlodbcw.so
psqlodbcw.la
cd /usr/lib
rm psqlodbc.so psqlodbc.la
ln -s psqlodbcw.so  psqlodbc.so
ln -s psqlodbcw.la psqlodbc.la

That should do it.