[[TOC]]
= Installation on PostgreSQL =
Replacing the simple SQLite database with [http://www.postgresql.org PostgreSQL] allows greater scalability & the possibility of [wiki:UserGuidelinesGISData#PostgreSQLmanagement using PostGIS for spatial storage].

== Install PostgreSQL ==
It is assumed that you are running Debian 'squeeze'. 'lenny' works fine for Eden, but the main point is to use PostGIS where squeeze is better...

{{{
apt-get install postgresql-8.4
}}}

Enable automatic DB maintenance:
{{{
vim /etc/postgresql/8.4/main/postgresql.conf
# Enable remote access
listen_addresses = '*'
port = 5432
track_counts = on
autovacuum = on

vim /etc/postgresql/8.4/main/pg_hba.conf
host    all         all         my.ip.add.ress/24     md5

/etc/init.d/postgresql restart

vim /etc/iptables.rules
-A INPUT -p tcp --dport 5432 -j ACCEPT

reboot
}}}
NB Don't do remote access as 'postgres' - to do so means can't simply access psql via 'su postgres'


Adjust postgresql.conf for performance relative to resources available (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)
{{{
vim /etc/sysctl.conf
# Increase Shared Memory available for PostgreSQL
# 512Mb
kernel.shmmax = 279134208
# 1024Mb (may need more)
#kernel.shmmax = 536870912
kernel.shmall = 2097152

sysctl -w kernel.shmmax=58720256
sysctl -w kernel.shmall=2097152

vim /etc/postgresql/8.4/main/postgresql.conf

# This default is probably ok
# adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients
max_connections = 100

# shared_buffers = 24MB
# Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic
# setting too high relative to system resources can be detrimental to the system.
# At the high end this should be no more than 1/4 to 1/3 the available memory (ie that which is not being used by the OS or other processes).
# Values suggested are quite conservative, so you can push them up a bit if there are resources available for it
# but absolutely no more than 1/3 available memory.
# 512Mb system suggest starting at:
shared_buffers = 56MB
# 1024Mb system suggest starting at:
#shared_buffers = 160MB

# effective_cache_size = 128MB
# 512Mb system suggest starting at:
effective_cache_size = 256MB
# 1024Mb system suggest starting at:
#effective_cache_size = 512MB

#work_mem = 1MB
# This is a per sort setting, so setting this too high with a high number of max_connections can be bad.
# If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this
# and drop the max_connections.
# 512Mb system suggest starting at:
work_mem = 2MB
# 1024Mb system suggest starting at:
#work_mem = 4MB

# As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever.
maintenance_work_mem = 16MB

/etc/init.d/postgresql restart
}}}

(Optional) Install the Admin Pack for enhanced intrumentation:
{{{
apt-get install postgresql-contrib-8.4
su postgres
psql -d postgres -f /usr/share/postgresql/8.4/contrib/adminpack.sql
}}}

(Optional) Install [http://phppgadmin.sourceforge.net PHPPgAdmin] if you want a web-based management tool:
{{{
apt-get install phppgadmin
}}}
== Configure PostgreSQL for Sahana Eden ==
{{{
su postgres
createuser -s -P sahana

createdb -O sahana -E UTF8 sahana
}}}

If using [wiki:InstallationGuidelinesGISDataLinux#PostGIS PostGIS] (recommended) then:
{{{
createlang plpgsql -d sahana
psql -d sahana -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d sahana -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
}}}
== Install Python support for PostgreSQL ==
{{{
apt-get install python-psycopg2
}}}

== Configure Sahana Eden to use PostgreSQL ==
{{{
vim models/0000_config.py
deployment_settings.database.db_type = "postgres"
deployment_settings.database.port = "" # use default
deployment_settings.database.database = "sahana"
deployment_settings.database.username = "sahana"
deployment_settings.database.password = "mypassword"
deployment_settings.database.pool_size = 30
}}}

'Connection pools' are used to reuse connections. How many pools should we use?
{{{
whatever you choose it always starts with one and grows with the number of concurrent requests up to the value of pools (the max number of concurrent pools).
So pools should be the max number of concurrent requests you expect. That is the max value of:
<number of requests/second> / <average time per request in seconds> 
}}}

== Add Geometry column to gis_location ==
If using PostGIS, then once the tables have been created:
{{{
su postgres
psql
\d sahana
SELECT AddGeometryColumn( 'public', 'gis_location', 'the_geom', 4326, 'POINT', 2 );
exit
vim models/0000_config.py
deployment_settings.gis.spatialdb = True
}}}
== Backups ==
{{{
su postgres
pg_dump -c sahana > /tmp/sahana.sql
pg_dump -Fc gis > /tmp/gis.dmp
}}}

== Maintenance ==
Connect to Database (or use pgAdmin III GUI's PSQL Console on plugins menu):
{{{
su postgres
psql
}}}
or
{{{
psql -d my_spatial_db -U username
}}}

List Databases:
{{{
\l
}}}

Connect to a Database:
{{{
\c sahana
}}}

List Tables:
{{{
\d
}}}

Describe Table structure:
{{{
\d tablename
select field/* from tablename where field = 'value';
}}}

If you need to build a fresh database:
{{{
rm -f /home/web2py/applications/eden/databases/*
su postgres
pkill -f 'postgres: sahana sahana'
dropdb sahana
createdb -O sahana -E UTF8 sahana
}}}

To restore data from backup:
{{{
su postgres
psql -f /tmp/sahana.sql
createdb gis
psql gis -f /usr/share/postgresql-8.4/contrib/postgis.sql
/usr/share/postgresql-8.4-postgis/utils/new_postgis_restore.pl -f /tmp/gis.dmp | psql gis
}}}

== Also see ==
 * [wiki:InstallationGuidelinesGISDataLinux#PostGIS PostGIS Installation]
 * Setting up Web2Py with PostgreSQL on Ubuntu: http://www.web2pyslices.com/main/slices/take_slice/14

----
InstallationGuidelines