postgresql installation

Installation

While we developed EAS under postgresql 8.3.7, installing 8.3.7 was giving us trouble. 8.3.11 was available via YUM and once we used YUM the install went smoothly.Using YUM is the way to go.

Once you have YUM configured to use only the postgres respositories and not the Centos repos, follow these steps for a pain free installation:

yum install postgresql
yum install postgresql-devel
yum install postgresql-server
yum install postgis
yum install postgresql-contrib

Links

• http://wiki.postgresql.org/wiki/YUM_Installation
• http://www.postgresonline.com/journal/archives/45-An-Almost-Idiots-Guide-to-PostgreSQL-YUM.html

Initializing Database

Our Oracle trained DBAs decided to intialize the DB here

/ur01/pgsql/data

Then we want to create 2 databases.

createdb mad --owner postgres
createdb sfmaps --owner postgres

At this point, you can use pg_restore.
For more on that read
http://eas.googlecode.com/svn/trunk/releases/1_0_prod/README

Links

• http://www.postgresql.org/docs/8.3/static/creating-cluster.html
• http://www.postgresql.org/docs/8.3/static/runtime-config-file-locations.html
• http://archives.postgresql.org/pgsql-admin/2007-05/msg00104.php

Security

This is my best guess on security and users - feel free to recommend alternatives.
To get things simply to work, I modified hba.conf and postgresql.conf.
I am allowing all the postgres user to connect from localhost and from geoserver and from the web server.

The dataserver should be accessible only from the following:

• web server
• geo server
• localhost

We will use postgres's hba.conf to control this access.

Remember that each environment (DEV, QA, PROD) will have it's own trio of severs:

• web server
• geoserver
• dataserver
  See This diagram for an example.

Get the IPs for the hba.conf from Henry or Paul.
We want to be as strict as is reasonable - not as strict as possible.

Database Accounts
I know we'll want at least 2 users

• postgres
• eas_user

Let's discuss if there is any value in having additional users such as:

• geoserver user
• etl_user

Postgres User
The postgres user will be

• used mostly by DBAs
• be used by developers in DEV and QA
• used in emergency cases by developers in PROD

EAS User
The eas_user is for users of the EAS application...

• needs to be able to read and write to all tables the mad and sfmaps databases
• should we use a group or role here?
• to make things simple, we'll use eas_user for access from geoserver

For any new users, we'll need to set the correct access privileges.
Until now, I have been using the postgres user everywhere (sorry!).

Installation Errors

error:

  configure: error: no acceptable C compiler found in $PATH

solution:

yum install gcc-c++

error: 

configure: error: readline library not found

solution:

yum install readline-devel.x86_64

error: 

configure: error: zlib library not found

solution:

yum install zlib.x86_64 zlib-devel.x86_64

During pg_restore you may see the following:

pg_restore --host localhost --port 5432 --username postgres --dbname mad ./mad-1_0-beta-2.backup
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 734; 1255 116672554 FUNCTION st_simplifypreservetopology(geometry, double precision) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  could not find function "topologypreservesimplify" in file "/usr/lib64/pgsql/liblwgeom.so"
    Command was: CREATE FUNCTION st_simplifypreservetopology(geometry, double precision) RETURNS geometry
    AS '$libdir/liblwgeom', 'topolo...
pg_restore: [archiver (db)] could not execute query: ERROR:  function public.st_simplifypreservetopology(geometry, double precision) does not exist
    Command was: ALTER FUNCTION public.st_simplifypreservetopology(geometry, double precision) OWNER TO postgres;
WARNING: errors ignored on restore: 2
[postgres@CentOSDB17882 ~]$

solution
Don't worry about it.
This is because we upgraded postgres and postgis.
Moreover, we do not use these functions.