Upgrading from 8.4 to 9.0 on Fedora / Red Hat / CentOS using RPMs

As I blogged before, 9.0 RPMs can now be installed in parallel, even with 8.4 RPMs (as of now, 9.0-server RPM has a bug that prevents it being installed via yum when 8.4 package is installed, but it is already fixed in SVN and I'll push packages soon You can use rpm -i --force in order to get rid of that issue).

This article assumes that you installed 8.4 already, running on port 5432, and it runs under the standard directory /var/lib/pgsql/data. If not, please adjust the info below to suit your needs.

Install 9.0 as described in here. You will also need to install -contrib and -devel packages for pg_upgrade, pg_config binaries and related files.

Now, let's initdb 9.0 cluster:

service postgresql-9.0 initdb

Edit postgresql.conf+init script to change port number, and change port numbers from 5432 and 5433 (yes, you need to change edit both files wen PostgreSQL is installed using RPMs).

vim ~postgres/9.0/data/postgresql.conf ; vim /etc/init.d/postgresql-9.0

You may want to apply some more changes to 9.0's postgresql.conf, and optimize it more for data loading.

Now you can stop 8.4 postmaster. In order to pg_upgrade to work, all postmasters needs to be shut down.

service postgresql stop

Now, we can start upgrade process. The command is simple.

/usr/pgsql-9.0/bin/pg_upgrade -b /usr/bin/ -B /usr/pgsql-9.0/bin/ -d /var/lib/pgsql/data/ -D /var/lib/pgsql/9.0/data - -l /tmp/pg_upgrade.log

Grab a beer, and watch logs now. If you want debugging, please add -g parameter to pg_upgrade. When upgrade is done, you will see such a message:

Upgrade complete
----------------
| Optimizer statistics is not transferred by pg_upgrade
| so consider running:
| vacuumdb --all --analyze-only
| on the newly-upgraded cluster.

| Running this script will delete the old cluster's data files:
| /var/lib/pgsql/delete_old_cluster.sh

Please start 9.0 cluster and test. Also, you may want to take a look at /tmp/pg_upgrade.log.