Upgrading PostgreSQL on Cpanel Server

Posted November 19th, 2005 by xerophyte
in

Upgrading PostgreSQL on Cpanel Server

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

if you need commercial support please order linux Network Care Cpanel One hour Service

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

  • 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.
  • After you have download the postgreSQL rpms from PostgreSQL download site.
    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

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

NA

On May 23rd, 2006 NA says:

This method did not work at all!

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

That directory /var/lib/pgsql/backups/ was completely empty when I went to check during restore, it should NOT have been empty.

I had to restore a clients database from cpanel backup and then it showed jibberish japanese characters.

Complete Install, I started to have this problem with the Apache

On April 22nd, 2006 SysopBR says:

Complete Install, thanks very much , for all the supplied tips, I only recommend to bring up to date the current version of PostgSQL 8.1.3, the tip so that others do not commit errors.

root@alpha[~]# rpm -qa | grep postgres | tee /root/rpm_pgsql
postgresql-server-8.1.3-1PGDG
postgresql-jdbc-8.1.3-1PGDG
postgresql-test-8.1.3-1PGDG
postgresql-8.1.3-1PGDG
postgresql-contrib-8.1.3-1PGDG
postgresql-docs-8.1.3-1PGDG
postgresql-pl-8.1.3-1PGDG
postgresql-tcl-8.1.3-1PGDG
postgresql-libs-8.1.3-1PGDG
postgresql-devel-8.1.3-1PGDG
postgresql-python-8.1.3-1PGDG

After some hours of my installation to have been fact, I started to have this problem with the Apache, below as my colleague had what she can be wrong, helps me please.

/scripts/restartsrv_httpd
Syntax error on line 62 of /usr/local/apache/conf/httpd.conf:
Cannot load /usr/local/apache/libexec/libphp4.so into server: libpq.so.3: cannot open shared object file: No such file or directory
/etc/rc.d/init.d/httpd start: httpd could not be started

Kinds regards,

Sysop Brazil

Apache integration

On February 11th, 2006 jmiridis says:

Hi xerophyte,

I followed your instructions and got Postgres 8.1.1 running.
Now I have the problem that the integration with Apache is broken because it can't find the correct shared library:

Cannot load /usr/local/apache/libexec/libphp4.so into server: libpq.so.3: cannot open shared object file: No such file or directory

Have you come across this problem? Any idea of how to make Apache/PHP work with Postgres again?