Client Portal

Quick PostGIS and DbLink install on cPanel server with PostgreSQL 9.1

PostGIS

If you try 'yum install postgis91 postgis91-utils geos proj' yum will complain about inability to install DBD::Pg module already provided by cPanel. So we get packages manually:

wget http://yum.postgresql.org/9.1/redhat/rhel-5-x86_64/postgis91-utils-1.5.5-1.rhel5.x86_64.rpm
wget http://yum.postgresql.org/9.1/redhat/rhel-5-x86_64/postgis91-1.5.5-1.rhel5.x86_64.rpm
rpm --nodeps -Uvh postgis91-1.5.5-1.rhel5.x86_64.rpm postgis91-utils-1.5.5-1.rhel5.x86_64.rpm
yum install geos proj
createdb pgisdb
psql -d pgisdb -f /usr/pgsql-9.1/share/contrib/postgis-1.5/postgis.sql
psql -d pgisdb -f /usr/pgsql-9.1/share/contrib/postgis-1.5/spatial_ref_sys.sql

This way you have imported PostGIS functions and types into pgisdb and can use the database as template when creating another PostGIS aware databases for example:

create database webapp_db3 template pgisdb; 

To enable PostGIS for already created database 'gps' owned by 'gps':

root=# drop database gps;
DROP DATABASE
root=# \q
# createdb -O gps -T pgisdb gps
# psql gps

To enable PostGIS for already created database 'gps' owned by 'gps':

root=# drop database gps;
DROP DATABASE
root=# \q
# createdb -O elixir -T pgisdb gps
# psql gps
gps=# \d
 List of relations
 Schema | Name | Type | Owner 
--------+-------------------+-------+-------
 public | geography_columns | view | root
 public | geometry_columns | table | root
 public | spatial_ref_sys | table | root
(3 rows)
gps=# ALTER TABLE geometry_columns OWNER TO gps;
ALTER TABLE
gps=# ALTER TABLE spatial_ref_sys OWNER TO gps;
ALTER TABLE
gps=# ALTER TABLE geography_columns OWNER TO gps;
ALTER TABLE
gps=# \d
 List of relations
Schema | Name | Type | Owner 
--------+-------------------+-------+------------
 public | geography_columns | view | gps
 public | geometry_columns | table | gps
 public | spatial_ref_sys | table | gps

DbLink

Make sure the contrib package is installed, e.g. from your shell:

yum install postgres*contrib 

Then create the extension from psql shell connect to the database you wnat to ebale it for and run

gps=# CREATE EXTENSION dblink;

To list exensions run:

gps=# \dx
 List of installed extensions
 Name | Version | Schema | Description 
---------+---------+------------+--------------------------------------------------------------
 dblink | 1.0 | public | connect to other PostgreSQL databases from within a database
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language