Today we are going to install PostgreSQL  and include Postgis module on a cPanel based servers.  PostgreSQL  is a open source object-relational database system. At this moment cPanel do support installation of this package using the cPanel scripts. But  at this moment the installed version of PostgreSQL  is 9.2 if we are using cPanel script. So we can look for how it can upgraded to the latest version.  Another thing we are implementing in the server is installing   Postgis  PostgreSQL   module in the same server.  By integrating  PostGIS  we will get geographical capabilities to a database created in PostgreSQL   and it can used for local based services or applications. for example an application which is used to find out nearby ATM centers.

System Environment

[[email protected] ~]# cat /etc/redhat-release
 CentOS Linux release 7.3.1611 (Core)
 [[email protected] ~]# cat /usr/local/cpanel/version
 11.64.0.24
 [[email protected] ~]#

Install PostgreSQL

/scripts/installpostgres

Below are the rpm packages installed in the server.

Installing : postgresql-libs-9.2.18-1.el7.x86_64 1/4
 Installing : postgresql-9.2.18-1.el7.x86_64 2/4
 Installing : postgresql-server-9.2.18-1.el7.x86_64 3/4
 Installing : postgresql-devel-9.2.18-1.el7.x86_64 4/4

Now log in the WHM panel and go the section  Home »  SQL Services » Configure PostgreSQL

Click buttons like “install config”, “Create users” and set a Password for the default “postgres” system user password. This is the user created automatically during install of PostgreSQL . The password we set needed at the time of login in to the pgsql terminal.

Test the installed pgsql.

# su postgres
 bash-4.2$ psql
 could not change directory to "/root"
 Password:
 psql (9.2.18)
 Type "help" for help.

postgres=#

Upgrade current Pgsql version in to the latest version available. Going to install PostgreSQL 9.6.

# wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
 # rpm -Uvh pgdg-centos96-9.6-3.noarch.rpm
 # rpm -qa | grep postgresql
 # rpm -e postgresql-9.2.18-1.el7.x86_64 postgresql-devel-9.2.18-1.el7.x86_64 
postgresql-libs-9.2.18-1.el7.x86_64 postgresql-server-9.2.18-1.el7.x86_64 --nodeps
 # yum install postgresql96-server postgresql96 postgresql96-libs postgresql96-devel
 # /usr/pgsql-9.6/bin/postgresql96-setup initdb
 # systemctl enable postgresql-9.6
 # systemctl start postgresql-9.6

At this moment we can see the newly installed postgresql running in the default port number 5432

# netstat -nlp | grep post
 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 11409/postmaster
 tcp6 0 0 ::1:5432 :::* LISTEN 11409/postmaster
 unix 2 [ ACC ] STREAM LISTENING 41969 11409/postmaster /tmp/.s.PGSQL.5432
 unix 2 [ ACC ] STREAM LISTENING 41967 11409/postmaster /var/run/postgresql/.s.PGSQL.5432
 [[email protected]]#

Now we need to setup the symlinks for upgraded Postgres to work with cPanel/WHM.

# cd /usr/pgsql-9.6/bin/; for f in *; do echo $f; [ -e /usr/bin/$f ] && mv /usr/bin/$f /usr/bin/$f.old; 
ln -s $(pwd)/$f /usr/bin/$f; done
 # mv data data.old
 # ln -s 9.6/backups; ln -s 9.6/data
 # /usr/local/cpanel/bin/updatephppgadmin --force
 # /scripts/upcp --force

Now log in the WHM panel and go the section  Home »  SQL Services » Configure PostgreSQL and run “Create users” “install config” and set a Password for “postgres” user.

# su postgres
 bash-4.2$ psql
 could not change directory to "/root": Permission denied
 Password:
 psql (9.6.3)
 Type "help" for help.
 postgres=#

For testing from cPanel. Log in the cPanel of a website and click the “PostgreSQL Databases” icon and create a test database. After that click the “phpPgAdmin” icon a new tab will open and it will show the newly created pgsql database, table and an option to run query. Its like the phpmyadmin used for MySQL.

Now install PostGIS.

yum install postgresql96-contrib
 yum -y install epel-release
 yum list | grep postgis
 yum install postgis23_96 postgis23_96-client

Below are packages and their dependencies installed in the server.

postgis23_96 x86_64 2.3.2-2.rhel7 pgdg96 4.0 M
 postgis23_96-client x86_64 2.3.2-2.rhel7 pgdg96 116 k
 Installing for dependencies:
 CGAL x86_64 4.7-1.rhel7 pgdg96 254 k
 CharLS x86_64 1.0-5.el7 epel 63 k
 SFCGAL x86_64 1.2.2-1.rhel7 pgdg96 14 k
 SFCGAL-libs x86_64 1.2.2-1.rhel7 pgdg96 1.7 M
 armadillo x86_64 4.320.0-1.el7 epel 21 k
 arpack x86_64 3.1.3-2.el7 epel 101 k
 atlas x86_64 3.10.1-10.el7 base 4.5 M
 blas x86_64 3.4.2-5.el7 base 399 k
 boost-date-time x86_64 1.53.0-26.el7 base 52 k
 cfitsio x86_64 3.370-1.el7 epel 526 k
 freexl x86_64 1.0.0f-1.el7 epel 31 k
 gdal-libs x86_64 1.11.4-10.rhel7 pgdg96 4.4 M
 geos x86_64 3.5.0-1.rhel7 pgdg96 540 k
 hdf5 x86_64 1.8.12-8.el7 epel 1.6 M
 lapack x86_64 3.4.2-5.el7 base 5.4 M
 libdap x86_64 3.13.1-2.el7 epel 423 k
 libgeotiff x86_64 1.4.0-1.rhel7 pgdg96 708 k
 libgta x86_64 1.0.4-1.el7 epel 32 k
 mesa-libGLU x86_64 9.0.0-4.el7 base 196 k
 netcdf x86_64 4.3.3.1-5.el7 epel 693 k
 ogdi x86_64 3.2.0-0.19.beta2.el7 epel 248 k
 openjpeg-libs x86_64 1.5.1-16.el7_3 updates 86 k
 openjpeg2 x86_64 2.1.0-7.el7 epel 105 k
 poppler x86_64 0.26.5-16.el7 base 783 k
 proj x86_64 4.8.0-4.el7 epel 181 k
 unixODBC x86_64 2.3.1-11.el7 base 413 k
 xerces-c x86_64 3.1.1-8.el7_2 base 878 k

Installing the ogrfdw binaries.  ogr_fdw the spatial vector foreign data wrapper.

yum install ogr_fdw96

Installing pgrouting binaries. pgRouting is a PostgreSQL extension that extends PostGIS for building Location Based Services (LBS) specifically for trip navigation and resource dispatch.

yum install pgrouting_96

Now we are going to enable and test the PostGIS extension in a newly created test database.

[[email protected]~]# su postgres
 bash-4.2$ psql
 could not change directory to "/root": Permission denied
 Password:
 psql (9.6.3)
 Type "help" for help.

postgres=# CREATE DATABASE gistest;
 CREATE DATABASE
 postgres=# \connect gistest;
 You are now connected to database "gistest" as user "postgres".
 gistest=# CREATE EXTENSION postgis;
 CREATE EXTENSION
 gistest=# CREATE EXTENSION postgis_topology;
 CREATE EXTENSION
 gistest=# CREATE EXTENSION ogr_fdw;
 CREATE EXTENSION
 gistest=# SELECT postgis_full_version();
 postgis_full_version

--------------------------------------------------------------------------------------------------------------------------
 --------------------------------------------------
 POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.4, 
released 2016/01/
 25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER
 (1 row)

gistest=# CREATE EXTENSION pgrouting;
 CREATE EXTENSION
 gistest=# SELECT * FROM pgr_version();
 version | tag | hash | branch | boost
 ---------+--------+-----------+--------+--------
 2.3.2 | v2.3.2 | 1f2af3c52 | master | 1.53.0
 (1 row)

gistest=#

This completed our install and testing on pgsql,postgis setup on a cPanel based server. The articles we recommend are given below.

http://www.postgresonline.com/journal/archives/
362-An-almost-idiots-guide-to-install-PostgreSQL-9.5,-PostGIS-2.2-and-pgRouting-2.1.0-with-Yum.html
http://postgis.net/install/