Cpanel - Upgrading PostgreSQL

*ANY ACTIONS YOU TAKE, YOU TAKE THEM AT YOUR OWN RESPONSABILITY*

Reason:

PostgreSQL 8.1 offers a huge performance increase pretty much across the board and few other fixes

Platforms:

RHEL 3,4,Centos 3.x,4.x, Fedora Core

Installation:

  • Make sure if you have postgresql intalled
    rpm -qa | grep postgres | tee /root/rpm_pgsql

    The above will list all the installed postgresql rpm packages and store the output to /root/rpm_pgsql. You can use this list to remove all the postgresql related rpm later.

  • Backup all your databases

    su postgres -
    mkdir backups
    pg_dumpall > /var/lib/pgsql/backups/postgresql_backup
    exit

    exit will logout from the postgres user

  • Now stop the postgrsql daemon using the following command

    /sbin/service postgresql stop
  • Backup the whole database folder

    mv /var/lib/pgsql /var/lib/pgsql_old 
  • Remove the old rpms using rpm -e, you can use the previously stored /root/rpm_pgsql to remove the postgresql rpm and its depended, for example the perl-DBD-pg depends on it. So you will be removing the /root/rpm_pgsql list + perl-DBD-gp

    For example, it might differ on your servers

    rpm -e rh-postgresql-server
    rpm -e rh-postgresql
    rpm -e rh-postgresql-devel
    rpm -e rh-postgresql-python
    rpm -e rh-postgresql-libs
    rpm -e perl-DBD-Pg
  • Download the new version of postgreSQL from the postgresql download site into
    /usr/local/src or any directory you prefer.
  • Download the following rpms

    postgresql-8.1.0-2PGDG.i686.rpm
    postgresql-contrib-8.1.0-2PGDG.i686.rpm
    postgresql-devel-8.1.0-2PGDG.i686.rpm
    postgresql-docs-8.1.0-2PGDG.i686.rpm
    postgresql-libs-8.1.0-2PGDG.i686.rpm
    postgresql-python-8.1.0-2PGDG.i686.rpm
    postgresql-server-8.1.0-2PGDG.i686.rpm

    To install

    rpm -Uhv postgresql*.rpm
  • After you have installed the new postgreSQL rpms login to WHM using

    https://ip:2087
    under the SQL service Menu
    Postgres Config
    Install new configuration and change the password

  • To restore the old databases

    psql template1 < /var/lib/pgsql_old/postgresql_backup_file

    The above will restore the databases