wiki:InstallationGuidelines/PostgreSQL

Version 10 (modified by Fran Boon, 14 years ago) ( diff )

--

Installation on PostgreSQL

Replacing the simple SQLite database with PostgreSQL allows greater scalability & the possibility of using PostGIS for spatial storage.

Install PostgreSQL

Enable automatic DB maintenance:

vim /etc/postgresql/8.3/main/postgresql.conf
track_counts = on
autovacuum = on

Configure PostgreSQL for Sahana Eden

su postgres
createuser -s -P sahana

createdb -O sahana -E UTF8 sahana

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

man pg_dump

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

List Tables:

\d

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

man pg_restore

Also see


InstallationGuidelines

Note: See TracWiki for help on using the wiki.