Version 11 (modified by 14 years ago) ( diff ) | ,
---|
Table of Contents
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
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:
man pg_restore
Also see
- InstallationGuidelinesGISDataLinux
- UserGuidelinesGISData
- Setting up Web2Py with PostgreSQL on Ubuntu: http://www.web2pyslices.com/main/slices/take_slice/14
Note:
See TracWiki
for help on using the wiki.