Setting-up postgresql with postgis on a grhat box

Time and again, I have to setup PostgreSQL database with PostGIS extension, and it's aways been a painful experience. So I have decided to write down detailed instructions for future reference.

Install PostgreSQL with PostGIS with PROJ4 projection library, GEOS geometry engine and python interface on a grhat box

As this requires custom compilation of PostgreSQL, I decided to install everything from source rather than available rpms from our apt repository.

Downloaded:

postgresql-8.1.4.tar.gz ( http://www.PostgreSQL.org )
postgis-1.1.3.tar.gz ( http://postgis.refractions.net )
geos-2.2.3.tar.bz2 ( http://geos.refractions.net )
proj-4.4.9.zip ( http://www.remotesensing.org/proj )

1. PostgreSQL install

tar xvfz postgresql-8.1.4.tar.gz
cd postgresql-8.1.4

PostGIS manual warned that for GEOS support I might need to explicitly link PostgreSQL against the standard c++ library and use the following configure syntax

LDFLAGS=-lstdc++ ./configure

gmake
sudo gmake install

2. Once PostgreSQL is successfully installed, need to add the postgres user and postgres group

Need to specify a home directory for the postgres user. I prefer using the the data directory as home directory.

Add to /etc/passwd
postgres:x:26:26::/usr/local/pgsql/data:/bin/bash

Add to /etc/group
postgres:x:26:

3. Create postgres data directory and initialize database

sudo mkdir /usr/local/pgsql/data
sudo chown postgres:postgres /usr/local/pgsql/data
sudo chmod 700 /usr/local/pgsql/data


Now initialize the database

First login as a postgres user
sudo su - postgres
( supply your LDAP password )

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

4. Configure PostgreSQL

Install postgres service
sudo cp contrib/start-scripts/linux /etc/rc.d/init.d/postgres
sudo chmod 755 /etc/rc.d/init.d/postgres
sudo chkconfig --add postgres

you can start the PostgreSQL server by typing
sudo /etc/init.d/postgres start

PostgreSQL server should also start automatically on reboot from now on.

It will be useful to add the path /usr/local/pgsql/bin to your PATH variable. I put this in .bashrc file in my postgres home directory ( /usr/local/pgsql/data )
PATH=$PATH:/usr/local/pgsql/bin

Now install GEOS and PROJ libraries and install PostGIS

5. PROJ installation

unzip proj-4.4.9.zip
cd proj-4.4.9
./configure
gmake
sudo gmake install

6. GEOS installation
bunzip geos-2.2.3.tar.bz2
tar xvf geos-2.2.3.tar
cd geos-2.2.3
./configure
gmake
sudo gmake install

I had problems with PostGIS not able to locate the GEOS library correctly. So I had to manually edit ld.so.conf file put the path in there
sudo vi /etc/ld.so.conf and add /usr/local/lib
sudo ldconfig

7. Install PostGIS
tar xvfz postgis-1.1.3.tar.gz
cd postgis-1.1.3

./configure

PostGIS configure should produce a nice summary, make sure that GEOS and PROJ paths are listed there.

gmake
sudo gmake install

8. Python interface
I use python to access PostgreSQL database. I found that it's easiest to install the rpm from the apt repository.
sudo apt-get install postgresql-python


9. This is all that is required for installation. Following steps are for transferring my existing databases, enabling PostGIS support for these databases and loading PostGIS function and object definitions.

Supposing a database called "dgvbr" exists on another server. To copy this database to this new server

On the existing server:

sudo su - postgres
pg_dump dgvbr > /tmp/dgvbr.out

transfer this database to another server using scp
scp /tmp/dgvbr.out username@server:/tmp/

On the new server:

sudo su - postgres
createdb dgvbr

Load PL/pgSQL language extension
createlang plpgsql dgvbr

Load PostGIS object and function definitions in the database
psql -d dgvbr -f lwpostgis.sql (this file lwpostgis.sql can be found in the source directory i.e. postgis-1.1.3 )

Restore the database
psql -d dgvbr -f dgvbr.out

and voila! all set to for some serious PostgreSQL fun.