postgresql installation

Installation

We want to install postgresql 8.3.7 for CentOS release 5.5, 64 bit (x86_64).
todo - add Summco directions here
Links

• http://wiki.postgresql.org/wiki/YUM_Installation
• http://www.postgresql.org/docs/8.3/static/runtime-config-file-locations.html
• http://www.postgresonline.com/journal/archives/45-An-Almost-Idiots-Guide-to-PostgreSQL-YUM.html

Initializing Database

I will advocate using the defaults /usr/local/pgsql/data or /var/lib/pgsql/data as described here:

• http://www.postgresql.org/docs/8.3/static/creating-cluster.html

If we diverge from default settings I want it to be for a good reason.
A good reason would be that we are using a SAN.
Since we are using the local file system, I think using a default location is fine.
As I understand it, this is easy to change later:

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

Thoughts?

Security

This is my best guess - feel free to recommend alternatives.
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