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

Summco is figuring out where to put the data.

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 - 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

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 ~]$