David Ghedini

Linux, Java, Oracle, and PostgreSQL


David Ghedini

Sunday Aug 21, 2011

Install PostgreSQL 9 on cPanel


**** AS OF WHM 11.36.0 (build 11) cPanel is no longer supporting custom PostgreSQL installation as previously ****
**** You will need to perform additional configurations not covered in this post for PostgreSQL to perform with cPanel. ****
**** cPanel has also added a new 'feature' that will over-write your pg_hba.conf file with their own settings. ****
**** Unless you wish to spend a lot of time adjusting to cPanel's changes, I would recommend just giving cPanel a miss and using a less intrusive control panel such as Webmin or Virtualmin ****

This post will cover installing PostgreSQL 9 on cPanel.

The OS is CentOS 5.5 and cPanel/WHM version is WHM 11.30.2 (build 1), but below should work for other releases as well.

We will start with the general installation and configuration and then see how to compile support for PHP, Perl, PDO, Python, and others.

Instructions are the same for PostgreSQL 9.0.x and 9.1.x. Just use the appropriate files names for the version you are installing.

It is important to note that cPanel supports ONLY the release that is available via the CentOS distribution (or Fedora or whatever supported distribution you are using).

By installing or upgrading to PostgreSQL 9.x, you will not be supported by cPanel.

The order of the steps below may seem idiosyncratic, but this order is what I have found to work.

As of PostgreSQL 9.x, the directory structures of PostgreSQL has changed.

In brief, installing PostgreSQL 9.x on cPanel simply requires creating the needed symlinks so that cPanel can find the files where it is looking for them (i.e. in the >=7.3 locations).


Step 1: Install the PostgreSQL Repo



You can download the PostgreSQL 9.0 repo here: http://yum.pgrpms.org/reporpms/repoview/pgdg-centos.html

If you want to install 9.1., simply download the PostgreSQL 9.1 repo. Again, the installation is identical for 9.0 and 9.1

Download the pgdg-centos-9.0-2.noarch (or pgdg-centos-9.1-3.noarch for 9.1) and upload to your server, or simply use wget.

root@s1 [~]# wget http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
--2011-08-18 15:09:07--  http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
Resolving yum.pgrpms.org... 77.79.103.58
Connecting to yum.pgrpms.org|77.79.103.58|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4623 (4.5K) [application/x-rpm]
Saving to: `pgdg-centos-9.0-2.noarch.rpm'

100%[======================================>] 4,623       --.-K/s   in 0.007s

2011-08-18 15:09:08 (682 KB/s) - `pgdg-centos-9.0-2.noarch.rpm' saved [4623/4623]


Install the repo.

root@s1 [~]# rpm -i pgdg-centos-9.0-2.noarch.rpm


We now need to update the CentOS-Base.repo file located at /etc/yum.repos.d

[root@sv2 ~]# cd /etc/yum.repos.d
[root@server1 yum.repos.d]# vi CentOS-Base.repo


add 'exclude=postgresql*' to the 'base' and 'updates' sections as shown below:

# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the 
# remarked out baseurl= line instead.
#
#

[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5
exclude=postgresql*

#released updates 
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5
exclude=postgresql*


Now, let's check everything is ready to install using yum list postgres*.

root@s1 [~]# yum list postgres*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * addons: mirror.symnds.com
 * base: mirror.cs.vt.edu
 * extras: mirror.ash.fastserv.com
 * updates: ftp.usf.edu
http://mirror.symnds.com/CentOS/5.6/addons/i386/repodata/repomd.xml: [Errno 4] IOError: 
Trying other mirror.
addons                                                   |  951 B     00:00
base                                                     | 1.1 kB     00:00
extras                                                   | 2.1 kB     00:00
pgdg90                                                   | 2.8 kB     00:00
pgdg90/primary_db                                        |  54 kB     00:00
updates                                                  | 1.9 kB     00:00
Excluding Packages in global exclude list
Finished
Excluding Packages from CentOS-5 - Base
Finished
Excluding Packages from CentOS-5 - Updates
Finished
Installed Packages
postgresql-libs.i386                     8.1.22-1.el5_5.1              installed
Available Packages
postgresql-jdbc90.i386                   9.0.801-1PGDG.rhel5           pgdg90
postgresql-jdbc90-debuginfo.i386         9.0.801-1PGDG.rhel5           pgdg90
postgresql90.i386                        9.0.4-1PGDG.rhel5             pgdg90
postgresql90-contrib.i386                9.0.4-1PGDG.rhel5             pgdg90
postgresql90-debuginfo.i386              9.0.4-1PGDG.rhel5             pgdg90
postgresql90-devel.i386                  9.0.4-1PGDG.rhel5             pgdg90
postgresql90-docs.i386                   9.0.4-1PGDG.rhel5             pgdg90
postgresql90-jdbc.i386                   9.0.801-1PGDG.rhel5           pgdg90
postgresql90-jdbc-debuginfo.i386         9.0.801-1PGDG.rhel5           pgdg90
postgresql90-libs.i386                   9.0.4-1PGDG.rhel5             pgdg90
postgresql90-odbc.i386                   09.00.0200-1PGDG.rhel5        pgdg90
postgresql90-odbc-debuginfo.i386         09.00.0200-1PGDG.rhel5        pgdg90
postgresql90-plperl.i386                 9.0.4-1PGDG.rhel5             pgdg90
postgresql90-plpython.i386               9.0.4-1PGDG.rhel5             pgdg90
postgresql90-pltcl.i386                  9.0.4-1PGDG.rhel5             pgdg90
postgresql90-python.i386                 4.0-2PGDG.rhel5               pgdg90
postgresql90-python-debuginfo.i386       4.0-2PGDG.rhel5               pgdg90
postgresql90-server.i386                 9.0.4-1PGDG.rhel5             pgdg90
postgresql90-tcl.i386                    1.8.0-2.rhel5                 pgdg90
postgresql90-tcl-debuginfo.i386          1.8.0-2.rhel5                 pgdg90
postgresql90-test.i386                   9.0.4-1PGDG.rhel5             pgdg90
postgresql_autodoc.noarch                1.40-1.rhel5                  pgdg90
root@s1 [~]#


Ok, looks good. We can now install the PostgreSql software


Step 2: Install PostgreSQL 9 Using YUM:



root@s1 [~]# yum install postgresql90 postgresql90-devel postgresql90-server postgresql90-libs postgresql90-contrib



Step 3: Create the Required Symlinks for Data Directory:



The directory structure for PostgreSQL has changed with release 9.

So, we need to create some symlinks from the new location to the old locations (where cPanel is expecting to find the files).

root@s1 [~]# ln -s /var/lib/pgsql/9.0/data /var/lib/pgsql
root@s1 [~]# ln -s /var/lib/pgsql/9.0/backups /var/lib/pgsql


The above will create the required symlinks as shown below.




Step 4: Inialize PostgreSQL:



Now, initialize Postgres but do NOT start it.
root@s1 [~]# service postgresql-9.0 initdb
Initializing database:                                     [  OK  ]
root@s1 [~]#



Step 5: Configuring PostgreSQL using WHM:



My explanation of this part of the process is going to be a bit verbose. So, let's first give a quick summary of the steps we will take in this section.

1. In WHM, go to Main >> SQL Services >> Postgres Config. Click the "Install config" button under Postgres Config Install

2. In /var/lib/pgsql/9.0/data open pg_hba.conf file and change 'md5' to 'trust' (this is temporary, we will set it back to md5)

3. In WHM, go to: Main >> Restart Services >> SQL Server (PgSQL). Restart PostgreSQL

4. In WHM, go to Main >> SQL Services >> Postgres Config. Enter and confirm a password for the user postgres, and click the "Change Password" button

5. In WHM, Got to Main >> SQL Services >> Postgres Config. In the Create Postgres Users section, click the Create Users button.


6. In WHM, go to: Main >> Restart Services >> SQL Server (PgSQL). Restart PostgreSQL



If you like, you can simply follow steps 1 to 6 above and then move on to next section (Step 6: Check cPanel and PhpPgAdmin Functionality).

However, if you take a few extra minutes to go through the full explanations below (5a to 5e) it may help to avoid problems and provide a better understanding.

Now, for the long version:



Since we initialized PostgreSQL step 4 above, you should now find that the /data directory at /var/lib/pgsql/9.0/data is now populated with the required files as shown below



Before proceeding, let's take a look at the default configuration in our pg_hba.conf file located in /var/lib/pgsql/9.0/data

It will look like this:

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     ident
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident


To get PostgreSQL and cPanel communicating correctly and securely using md5, follow steps 5a to 5fbelow in the order shown.

5a. Set the Postgres Configuration File in WHM

1. In WHM, go to Main >> SQL Services >> Postgres Config

2. Click the "Install config" button under Postgres Config Install

5b. Edit the pg_hba.conf file

1. Go to the /var/lib/pgsql/9.0/data directory

2. Open the pg_hba.conf file in text editor or vi

The pg_hba.conf file will look like this:

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
# IPv4 local connections:
# IPv6 local connections:

local all all        md5   
host all all  127.0.0.1   255.255.255.255   md5


This now has md5 listed as our authentication method but at this point it will NOT work correctly.

In order to proceed, we need to TEMPORARILY change 'md5' to 'trust':

So, change this:

local all all        md5   
host all all  127.0.0.1   255.255.255.255   md5


to this:

local all all        trust   
host all all  127.0.0.1   255.255.255.255   trust


Save the pg_hba.conf file

5c. Restart PostgreSQL via WHM

1. In WHM, go to: Main >> Restart Services >> SQL Server (PgSQL)

2. Restart PgSQL.

When completed you will get a screen with something like this:

Waiting for postgresql to restart...............finished.

postmaster (/usr/pgsql-9.0/bin/postmaster -p 5432 -D /var/lib/pgsql/9.0/data) running as postgres with PID 14040

postgresql started ok


5d. Change the Postrges Password.

1. In WHM, go to Main >> SQL Services >> Postgres Config

2. In the Postgres Password section at bottom, enter and confirm a password for the user postgres, and click the "Change Password" button.

If you have done everything successfully, the output on the screen should be:

ALTER ROLE Password successfully changed


Now, note that if we go back to our pg_hba.conf file in /var/lib/pgsql/9.0/data, we will see that the authentication is no longer 'trust' but has now been returned to md5.

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL listen
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
# IPv4 local connections:
# IPv6 local connections:

local all all        md5   
host all all  127.0.0.1   255.255.255.255   md5 


5e. Add Existing Users.

If you have existing users on the server, you can now add them

1. In WHM, Got to Main >> SQL Services >> Postgres Config

2. In the Create Postgres Users section, click the Create Users button.

5f. Restart the PostgreSQL server via WHM

Finally, In WHM, go to: Main >> Restart Services >> SQL Server (PgSQL). Restart PostgreSQL


Step 6: Check cPanel and PhpPgAdmin Functionality:



Now that we have completed the initial configuration, we should log into a cPanel account for a user and test functionality.

IMPORTANT: When testing functionality, do NOT open the users cPanel via WHM. If you do this, PhpPgAdmin will appear not to work. Rather, open a new browser and log into an cPanel normally as the end user.

1. Create a database using the cPanel GUI



2. Add a user to the database you created using the cPanel GUI







3. Check PhpPgAdmin functionality.



Use PhpPgAdmin to create a table and insert some data.

Finally, using cPanel and PhpPgAdmin, I have created the following:

Database: david_testing
User: david_myuser
Password: mypassword
Table: testtable
Table Columns: myid and myname

You can create whatever you like but I'll use the database, user, and table above in all subsequent examples for PHP, PDO, etc...

Since I will be using this database, table, and user to check my PHP other functionality below, before proceeding I'll just check what our data should look like using the command line (you can use PhpPgAdmin as well).

Open a shell as the end user:

login as: david
david@s1s password:
Last login: Sun Aug 21 14:10:38 2011 from ....
david@davespgtest.com [~]# psql david_testing
Password:
psql (9.0.4)
Type "help" for help.

david_testing=>
david_testing=> select * from testtable;
 myid |                       myname
------+----------------------------------------------------
    1 | David
    2 | Elouise
(2 rows)

david_testing=>




Step 7: Creating the pg_config Symlink:



When we first installed PostgreSQL 9 above, the installation automatically created a number of useful symlinks under /etc/alternatives as shown below.



These are links into the CentOS /usr/bin directory from the new /usr/pgsql-9.0 directory where PostgreSQL /bin, /include, /share, /lib, and others are now installed as of PostgreSQL 9.

However, the file that we need to compile support for PHP, Perl, PDO, etc.. does not have a symlink created automatically.

The file we need for this is the pg_config file located under /usr/pgsql-9.0/bin.

PHP (EasyApache), Perl, PDO, Python, etc.. all use the pg_config file to locate required PostgreSQL files. More to the point, they expect to find the pg_config file under /usr/bin

So, in order to rectify this, we will create the required symlink:

root@s1 [~]# ln -s /usr/pgsql-9.0/bin/pg_config /usr/bin


Now, we are able to compile support for PHP, PDO, Perl, and the rest.



Step 8: Compile PHP Support:



You can now compile PHP support for PostgreSQL in WHM via EasyApache (Apache Update) as you normally would.

In WHM, go to Main >> Software and then to EasyApache (Apache Update)

Select the exhaustive list for the build and be sure to tick PGsql:





If you want to compile PDO support, you should enable PDO here as well. The specific PgSQL PDO support will be done below

Once EasyApahce completes, create a phpinfo() file in your domain and verify that PHP support now shows up.

Finally, create a test page, pgphp-test.php, like below:

Be sure to substitute the db, username, and password with your own.


  
   PG PHP Test
  
  
  \n";
    $row = pg_fetch_array($result, $ri);
    echo " 
  ";
   }
   pg_close($link);
  ?>
  
ID Name
", $row["myid"], " ", $row["myname"], "



Note: if you get an error, log into either PhpPgAdmin, or the command line and grant select, and other privs you want, to the user you created on the table you are testing.


Step 9 (Optional): Compile PDO Support:



To compile PDO support, from a terminal, as Root, issue:

root@s1 [~]# pecl install pdo_pgsql


When installation successfully completes, restart Apache and check your phpinfo() file to see if PDO for PgSQL has been added.

You should now find pdo_pgsql listed. Again, you can view my phpinfo() file here: http://pg1.davidghedini.com/pgphp-test.php

Finally, create a test page, pgpdo-test.php, like below:

Be sure to substitute the db, username, and password with your own.

prepare("select * from testing order by myid");      
    $query->execute();                                                          
    while($row = $query->fetch())                                             
    {
       var_dump($row);                                                         
    }
    ?>



Step 10 (Optional): Install DBD::Pg:



In WHM, go to Main >> Software >> Install a Perl Module

In the search box, enter DBD::Pg

You should now see the screen below:




Click the Install button for DBD::Pg

The procedure should terminate successfully with:

Installing /usr/local/lib/perl5/site_perl/5.8.8/i686-linux/DBD/testme.tmp.pl
Installing /usr/local/lib/perl5/site_perl/5.8.8/i686-linux/Bundle/DBD/Pg.pm
Installing /usr/local/share/man/man3/DBD::Pg.3
Installing /usr/local/share/man/man3/Bundle::DBD::Pg.3
Appending installation info to /usr/local/lib/perl5/5.8.8/i686-linux/perllocal.pod
OK
Successfully installed DBD-Pg-2.18.1
Expiring 4 work directories.
1 distribution installed


You can test your DBD::Pg functionality with something like below.

Be sure to substitute the db, username, and password with your own.

Save the code as test.pl in the user home directory.

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect ("dbi:Pg:dbname=david_testing", "david_myuser", "mypassword");

$sth  = $dbh->prepare("SELECT * FROM testtable");
$sth->execute();

while ( ($myid,$myname) = $sth->fetchrow_array() ) 
{
    	print "$myid\t\t  $myname \n";
}

$sth->finish();

$dbh->disconnect();


Now, open a terminal as and log in as the user to test the perl file, test.pl, that we created above:
david@davespgtest.com [~]# perl test.pl
1                 David
2                 Elouise
david@davespgtest.com [~]#



Step 11 (Optional): Install PSYCOPG Support (Python):



First, download the source file,psycopg2-2.4.2.tar.gz, from: http://www.initd.org/psycopg/download/

Or, just use wget:

root@s1 [~]# wget http://initd.org/psycopg/tarballs/PSYCOPG-2-4/psycopg2-2.4.2.tar.gz
--2011-08-21 16:11:23--  http://initd.org/psycopg/tarballs/PSYCOPG-2-4/psycopg2-2.4.2.tar.gz
Resolving initd.org... 94.23.246.98
Connecting to initd.org|94.23.246.98|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 666719 (651K) [application/x-gzip]
Saving to: `psycopg2-2.4.2.tar.gz'

100%[======================================>] 666,719      451K/s   in 1.4s

2011-08-21 16:11:25 (451 KB/s) - `psycopg2-2.4.2.tar.gz' saved [666719/666719]

root@s1 [~]#


Unpack the file:

root@s1 [~]# tar -xzf psycopg2-2.4.2.tar.gz


Change to the psycopg2-2.4.2 directory:

root@s1 [~]# cd psycopg2-2.4.2
root@s1 [~/psycopg2-2.4.2]#
Now, as Root, Install using 'python setup.py install'

root@s1 [~]# cd psycopg2-2.4.2
root@s1 [~/psycopg2-2.4.2]# python setup.py install
Test it out as end user:

root@s1 [~/psycopg2-2.4.2]# su - david
david@davespgtest.com [~]# python
Python 2.4.3 (#1, May  5 2011, 16:39:09)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-50)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> conn = psycopg2.connect("dbname=david_testing user=david_myuser password=mypassword")
>>> cur = conn.cursor()
>>> cur.execute("SELECT * FROM testtable;")
>>> cur.fetchone()
(1, 'David                                             ')
>>> cur.close()
>>> conn.close()
>>>



Upgrading From a Previous PostgreSQL 8.x or PostgreSQL 9.x Release:



Above, in step 1, we installed postgresql90-contrib.

The postgresql90-contrib contains pg_upgrade.

You can use pg_upgrade to easily upgrade from 8.x (or 9.x) to 9.x

1. Install PostgreSQL 9.x using the PostgreSQL repo and yum following steps 1 to 4 above.

2. Initialize the new cluster (e.g. postgresql-9.1 initidb).

3. Run pg_upgrade with appropriate values. Below, I am upgrading from 9.0 to 9.1

root@s1 [~]#  su - postgres
-bash-3.2$ /usr/pgsql-9.1/bin/pg_upgrade -b /usr/pgsql-9.0/bin/ -B /usr/pgsql-9.1/bin/ -d /var/lib/pgsql/9.0/data/ -D /var/lib/pgsql/9.1/data - -l /tmp/pg_upgrade.log
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system oid user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok
Checking for prepared transactions                          ok
Checking for presence of required libraries                 ok

| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from /var/lib/pgsql/9.0/data/global/pg_control.old.

Performing Upgrade
------------------
Adding ".old" suffix to old global/pg_control               ok
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting new commit clogs                                   ok
Copying old commit clogs to new server                      ok
Setting next transaction id for new cluster                 ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Creating databases in the new cluster                       ok
Adding support functions to new cluster                     ok
Restoring database schema to new cluster                    ok
Removing support functions from new cluster                 ok
Restoring user relation files
                                                            ok
Setting next oid for new cluster                            ok
Creating script to delete old cluster                       ok

Upgrade complete
----------------
| Optimizer statistics are not transferred by pg_upgrade
| so consider running:
|       vacuumdb --all --analyze-only
| on the newly-upgraded cluster.

| Running this script will delete the old cluster's data files:
|       /var/lib/pgsql/delete_old_cluster.sh
-bash-3.2$


Once upgrade has completed, you can continue with the remaining configurations for WHM, etc... above.


Bookmark and Share



Comments:

Good morning, very good and very detailed tutorial thanks for the input. I commented that I did all the steps and install postgres on my server iweb So far so good, but when I try to run the phpPgAdmin link that came out (./3rdparty/phpPgAdmin/index.php) did not exist. I hope you can help me indicating that it may be happening or what I overlooked, Greetings and thanks Antel.

Posted by Ricardo O. on September 22, 2011 at 12:11 PM CDT #

Hi Ricardo -

It sounds like phpPgAdmin did not get installed.

Check in /usr/local/cpanel/base/3rdparty

If it is not there:

cd /usr/local/cpanel/base/3rdparty
wget http://downloads.sourceforge.net/phppgadmin/phpPgAdmin-5.0.2.zip?download
unzip phpPgAdmin-5.0.2.zip
mv phpPgAdmin-5.0.2 phpPgAdmin

Locate config.inc.php under phpPgAdmin/conf and, at minimum, be sure to change

$conf['owned_only'] = false;

to

$conf['owned_only'] = true;

HTH, David Ghedini

Posted by David Ghedini on September 22, 2011 at 06:54 PM CDT #

Thanks a lot, my problem you solved.

Posted by Ricardo O. on September 22, 2011 at 10:25 PM CDT #

You're very welcome. Glad it helped.

Posted by David Ghedini on September 23, 2011 at 12:06 AM CDT #

Problem: root@server [/etc/yum.repos.d]# service postgresql-9.1 initdb Initializing database: [FAILED] I had no errors on previous steps otherwise.

Posted by Chad on October 20, 2011 at 08:48 PM CDT #

Hi Chad -

Is this an upgrade or a new install?

grep postgres to see if it is already running.

Also check the default port 5432

Check the logs under /var/log and postgres logs for any errors.

David

Posted by David Ghedini on October 21, 2011 at 07:11 AM CDT #

This is a new install. By the way, how do we do an upgrade on a cpanel shared hosting environment form 8x to 9x without losing databases? Thanks for your help.

Posted by Chad on October 21, 2011 at 07:19 AM CDT #

This is a new install. Also, if I do an upgrade on a production environment how do I back up live PG databases safely? Thanks.

Posted by Chad on October 22, 2011 at 01:31 PM CDT #

Hi Chad -

Did you do/check any of the items I suggested above: grep, sys logs, pg logs, 5432....?

I can't be of much help without knowing what error(s) you are getting

David

Posted by David Ghedini on October 22, 2011 at 03:18 PM CDT #

I managed to resolve symbolic links error with the help of cpanel forum
http://forums.cpanel.net/f5/symbolic-links-problem-257392.html
Problem now I cannot init server.
I checked error log and:
creating conversions ... FATAL: could not load library "/usr/pgsql-9.0/lib/utf8_and_johab.so": /usr/pgsql-9.0/lib/utf8_and_johab.so: failed to map segment from shared object: Cannot allocate memory
STATEMENT: CREATE OR REPLACE FUNCTION johab_to_utf8 (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/utf8_and_johab', 'johab_to_utf8' LANGUAGE C STRICT;

Posted by Ciprian Mihalache on February 09, 2012 at 05:16 AM CST #

Hi Ciprian -

Which version did you install from the repo?

Which packages did you install

Please show me output from:

rpm -qa|grep postgresql

Thanks, David

Posted by David Ghedini on February 09, 2012 at 08:12 AM CST #

Hi David,
I've updated my PostgreSQL but I still have a problem with my libs.
I've currently PostgreSQL 9.1.3 but in my phpinfo it's still 8.1.23.
Can you help me to fix it? How can I change it?

Posted by Yannick on April 23, 2012 at 01:32 PM CDT #

Hi Yannik -

Did you run EasyApache update?

Thanks,
David

Posted by David Ghedini on April 27, 2012 at 05:00 AM CDT #

ln -s /usr/pgsql-9.0/bin/pg_config /usr/bin That command just saved me a ton of headaches! Thank you so much!

Posted by DanOMatic on April 29, 2012 at 12:23 AM CDT #

Thanks a bunch. This article saved me a bunch more time and headache. I had a good idea this was the problem, but I didn't know all the specifics I needed to make it work.
Luckily for me, you did!

Posted by Kris Reeves on June 28, 2012 at 05:40 AM CDT #

Hi Kris -

Glad you found it useful.

Thanks,
David

Posted by David Ghedini on June 28, 2012 at 06:09 AM CDT #

Thanks for the wonderful guide.
I have some question about something I'm not sure if it's normal behavior or not:
Today, one of our customers open a ticket asking how to create 'tablespace' using phpPgAdmin. I check to try to create it, and it's true. No permission.
But if you click in Select Owner from TABLESPACE in phpPgAdmin, ALL ACCOUNTS will be listed. In my vision this is a big security role problem.
Hoping for some guidance/help/answers. Thank you !

Posted by Alex on July 11, 2012 at 04:46 PM CDT #

Hey David, great how-to! Thank you.
I suggest adding in phpPhAdmin upgrade as part of the how-to, the version with cPanel is out of date.
If someone access it as a superuser they will run into some problems.
This worked flawlessly with Postgresql 9.2 and phpPgAdmin 5.0.4.
http://yum.pgrpms.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm

Posted by Adam Sentner on March 16, 2013 at 03:47 PM CDT #

Thank you :)

Posted by Luis Lara on October 30, 2014 at 08:58 PM CDT #

I know this is an old thread but I'm looking for a solution may be some of you guys can help. I'm not a developer btw so please try to use a simple language. :P

If you click in Select Owner from TABLESPACE in phpPgAdmin, ALL ACCOUNTS will be listed. In my vision this is a big security role problem.

Thanks in Advance.

Posted by Lindsey Anderson on October 22, 2015 at 10:42 AM CDT #

Post a Comment:
  • HTML Syntax: Allowed

Main Menu

Built With

Pages

LinkedIn

Tag Cloud

Hosted by:

Navigation

Visitors

Sponsors

petiole Cloud

Feeds