Upgrading PostgreSQL on Cpanel Server

Guide available for CentOS / Fedora / RHEL

• 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-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-***-2PGDG.i686.rpm
  postgresql-contrib-***-2PGDG.i686.rpm
  postgresql-devel-***-2PGDG.i686.rpm
  postgresql-docs-***-2PGDG.i686.rpm
  postgresql-libs-***-2PGDG.i686.rpm
  postgresql-python-***-2PGDG.i686.rpm
  postgresql-server-***-2PGDG.i686.rpm

  To install

  rpm -Uhv postgresql*.rpm

• After you have installed the new postgreSQL rpms login to WHM usinghttps://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

• Notes: This is a manual installation, on the current cPanel 11, /scripts/installpostgres will definitely do the job, the guide is pretty old, but still, this should be very easy to do.

• VERY CAREFUL, in order to have apache/php working with postgres, you have to recompile, either from WHM, either from shell, with postgres support.

Original guide here