1. Install prerequisites

% cd /usr/ports/textproc/libxml2
% make
% make install

2. Install python

% cd /usr/ports/lang/python26
% make config #opens up blue terminal which allows to choose options
% make
% make install

3. Install

% cd /usr/ports/databases/postgresql90-server
% make
% make install clean

4. Initialize

% vim /etc/rc.conf
postgresql_enable=YES
postgresql_data=/var/db/pgsql
% mkdir /var/db/pgsql
% chown -Rf pgsql:pgsql /var/db/pgsql
% /usr/local/etc/rc.d/postgresql initdb -E utf8

5. Configuration
Allow incoming connections and set the default timezone to ‘UTC’

% vim /var/db/pgsql/postgresql.conf
listen_addresses = '*'     # what IP address(es) to listen on;
 
timezone = 'UTC' #not necessary
 
% vim /var/db/pgsql/pg_hba.conf
# your network
host    all             all             192.168.10.0/24          trust

6. Start/Stop

% /usr/local/etc/rc.d/postgresql start
% telnet 127.0.0.1 5432

7. Create user. Login as root then switch to pgsql user

% su - pgsql
[root@rupert ~]# su - pgsql
$ psql -d postgres
psql (9.0.6)
Type "help" for help.
 
postgres=# CREATE ROLE rupert WITH LOGIN PASSWORD '**********' SUPERUSER INHERIT CREATEDB CREATEROLE;
CREATE ROLE
postgres=#

8. Install plpython

% /usr/ports/databases/postgresql-plpython
% make
% make install clean

To test if plpython is working properly, we create a testdb and loadup plpythonu and call a plpython function.

CREATE OR REPLACE FUNCTION pyver() RETURNS text AS
$$
import sys
RETURN sys.version
$$ LANGUAGE 'plpythonu';
 
SELECT pyver();
 
[root@rupert ~]# createdb -U rupert testdb
[root@rupert ~]# psql -d testdb -U rupert
psql (9.0.6)
Type "help" for help.
 
testdb=# CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler;
NOTICE:  using pg_pltemplate information instead of CREATE LANGUAGE parameters
CREATE LANGUAGE
testdb=# create or replace function pyver() returns text as
testdb-# $$
testdb$# import sys
testdb$# return sys.version
testdb$# $$ language 'plpythonu';
CREATE FUNCTION
testdb=# select pyver();
                   pyver                    
--------------------------------------------
 2.6.7 (r267:88850, Feb  6 2012, 13:10:39) +
 [GCC 4.2.1 20070831 patched [FreeBSD]]
(1 row)
 
testdb=#

9. Install postgis

% cd /usr/ports/databases/postgis
% make
% make install

Note that ports should install proj and geos.

10. Create template_postgis

% cd /usr/local/share/postgis
% su - pgsql
$ createdb -E utf8 template_postgis
$ psql -d template_postgis -f postgis.sql
$ psql -d template_postgis -f spatial_ref_sys.sql

11. Install adminpack module

This will eliminate “servers instrument error” when pgAdmin loads up postgres (default) db

% cd /usr/ports/databases/postgresql90-contrib
% make
% make install
% cd /usr/local/share/postgresql/contrib
% su - pgsql
$ psql -U pgsql -d postgres -f adminpack.sql