[[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 ==
 * [wiki:InstallationGuidelinesGISDataLinux#PostGIS]

Enable automatic DB maintenance:
{{{
vim /etc/postgresql/8.4/main/postgresql.conf
port = 5432
track_counts = on
autovacuum = on

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

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

It is possible to 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
}}}

 * [wiki:UserGuidelinesGISData#PostgreSQLmanagement]

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

== 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]
 * [wiki:UserGuidelinesGISData#PostgreSQLmanagement]
 * Setting up Web2Py with PostgreSQL on Ubuntu: http://www.web2pyslices.com/main/slices/take_slice/14

----
InstallationGuidelines