Upgrading PostgreSQL 8.1 to 8.4 on CentOS 5.5

PostgreSQL 8.4 (postgresql84) is now included as a fully supported option in Red Hat Enterprise Linux 5.5/CentOS 5.5.

New features in PostgreSQL 8.4 include: parallel database restore, per-column permissions and new monitoring tools.

A data dump and restore using pg_dump is required for migration from the existing PostgreSQL 8.1 (supplied by the postgres package). Due to this requirement, postgres and postgresql84 contain package level conflicts and only a single version can be installed on a system.

To upgrade PostgreSQL, follow these steps:

1. Create a backups directory:
   

   # mkdir /pgbak
   # chown postgres:postgres /pgbak/
   

2. Dump only global database objects (roles and tablespaces), no databases:
   

   # su - postgres
   # pg_dumpall --globals-only > /pgbak/globals.sql 
   # pg_dump --create --oids --format=c --verbose --file=/pgbak/dbX dbX
   # exit
   

3. Stop the database server:
   

   # /etc/init.d/postgresql stop
   

4. Create full backup of database cluster (is not used in upgrading process):
   

   # mv /var/lib/pgsql/data /pgbak
   

5. Upgrade PostgreSQL 8.1.6 to 8.4.2:
   

   # yum remove postgresql*
   # yum install postgresql84-server
   

6. Initialize new database cluster:
   

   # /etc/init.d/postgresql initdb
   

7. Restore configuration files modifications on /var/lib/pgsql/data/*.conf
8. Start the PostgreSQL service:
   

   # chkconfig --level 35 postgresql on
   # /etc/init.d/postgresql start
   

9. Restore global database objects (roles and tablespaces) and databases:
   

   # su - postgres
   psql -f /pgbak/globals.sql
   pg_restore --create -d postgres /pgbak/dbX
   # exit