Upgrading PostgreSQL 8.2 on Cpanel Server

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

Reason :

PostgreSQL 8.2 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 5,6
Make sure if you have postgresql installed.

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-gpFor 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.
  • After you have download the postgreSQL rpms from PostgreSQL download site.Download the following rpms 
    postgresql-8.2.4-1PGDG.i686.rpm
postgresql-contrib-8.2.4-1PGDG.i686.rpm
postgresql-devel-8.2.4-1PGDG.i686.rpm
postgresql-libs-8.2.4-1PGDG.i686.rpm
postgresql-plpython-8.2.4-1PGDG.i686.rpm
postgresql-server-8.2.4-1PGDG.i686.rpm

    To install

    rpm -Uhv postgresql*.rpm
  • After you have installed the new postgreSQL rpms login to WHM usinghttps://ip:2087under 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

    if you see any mistake please feel free to comment using comments