Versions Compared

Old Version 29

changes.mady.by.user Paul McCullough

Saved on

compared with

New Version Current

changes.mady.by.user Samuel Valdez

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Section

Table of Contents


Include Page

...

postgresql repository setup

...

postgresql repository setup

Pre-Installation

Before you install, you should make sure you have a clean slate.
First, look for other postgres installs. I found that my centos 5 comes with postgres 8.1 already installed.
I suggest that we remove these older versions before we install the newer ones.
If you really need both versions to work side by side - this wiki page won't help you.

...

If you installed from source you can go the the build directory and type

Code Block
make uninstall

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.

I followed these instructions:

Once you have YUM configured to use only the postgres respositories and not the Centos repos (see above), the following steps provide a pain free installation.

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

Additional Links

Initializing Database

Our Oracle trained DBAs decided to intialize the DB here

...

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

Security - Quick Start

This is my best guess on security and users - feel free to recommend alternatives.

For the time being, I have modified

  • hba.conf
  • postgresql.conf.

so as to allow the postgres user to connect to any database from any of

  • localhost
  • geoserver
  • web server

This needs to be substantially refined for production.
The following section should address this in complete detail.

Server Configuration

...

Performance Tuning

The EAS databases (mad, sfmaps) need to support 3 sorts of activities

...

  • relatively high I/O because it's geospatial
  • light OLTP actvity from single django web application
  • most connections from single django web application (no connection pooling)
  • small number of simultaneous connections (estimate max of 50)
  • heavy write activity during overnight ETL processes
  • heavy read processes from geoserver to produce web maps (sfmaps database), especially during cache seeding

The host VMs for the DB have 8GB different amounts of RAM as shown in the table below.

Here I discuss the settings in the postgresql.conf file.
The default settings are for low resource environments and will result in relatively poor performance.
These seetings settings are my "best guess" based on some reading (see links below).

We'll need to do some monitoring in PROD to tune these values.

parameter

value
DEV

value
QA

value
PROD

links (below)

VM GB RAM

4

6

6

 

shared_buffers

...

1GB

1.5GB

1.5GB

1,2

work_mem

...

16MB

16MB

16MB

2

maintenance_work_mem

...

128MB

128MB

128MB

2

wal_buffers

...

1MB

1MB

1MB

2

checkpoint_segments

...

6

6

6

2

random_page_cost

...

2

2

2

2

seq_page_cost

...

1

1

1

2

effective_cache_size

2GB

3GB

3GB

1

max_connections

...

100

100

100

default

A restart is needed for these configurations to take affect.

Links
1 - http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm
2 - http://workshops.opengeo.org/postgis-intro/tuning.html
3 - http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
4 - http://postgis.refractions.net/docs/ch06.html

Security - Quick Start

This is my best guess on security and users - feel free to recommend alternatives.

For the time being, I have modified

  • hba.conf
  • postgresql.conf.

so as to allow the postgres user to connect to any database from any of

  • localhost
  • geoserver
  • web server

This needs to be substantially refined for production.
The following section should address this in complete detail.

Security - Production

The dataserver should be accessible only from the following:

...

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

Performance Tuning

???
As I understand it, we do not want to use the default configurations.
We'll have to google a bit here I give it our best effort before PG Experts comes in.

Installation Errors

error:

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

...