Version 40 (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
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=536870912 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
Logging-in as user postgres (Ubuntu Users)
sudo su postgres
(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 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 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
NOTE: you must install PostGIS first
Ubuntu users (Lucid/PostgreSQL 8.4/Post-GIS 1.4) - the paths are slightly different
psql -d sahana -f /usr/share/postgresql/8.4/contrib/postgis.sql psql -d sahana -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql
Install Python support for PostgreSQL
apt-get install python-psycopg2
Configure Sahana Eden to use PostgreSQL
vim models/000_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 # Ensure all POINTs have WKT entries UPDATE public.gis_location SET wkt = 'POINT (' || lon || ' ' || lat || ')' WHERE gis_feature_type = 1; # Add geometry Column SELECT AddGeometryColumn( 'public', 'gis_location', 'the_geom', 4326, 'GEOMETRY', 2 ); # Populate existing entries UPDATE public.gis_location SET the_geom = ST_SetSRID(ST_GeomFromText(wkt), 4326); exit
(if using Windows can use the Query Editor that comes with pgAdmin III)
Ensure that future entries auto-populate properly:
su postgres psql \d sahana CREATE OR REPLACE FUNCTION s3_update_geometry() RETURNS "trigger" AS $BODY$ DECLARE BEGIN if (NEW.wkt != '') then NEW.the_geom = SetSRID(GeomFromText(NEW.wkt), 4326); end if; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION s3_update_geometry() OWNER TO sahana; CREATE TRIGGER s3_locations_update BEFORE INSERT ON gis_location FOR EACH ROW EXECUTE PROCEDURE s3_update_geometry(); exit
Tell Sahana that the DB is spatially-enabled so that routines can make use of this:
vim models/000_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
- PostGIS Installation
- Setting up Web2Py with PostgreSQL on Ubuntu: http://www.web2pyslices.com/main/slices/take_slice/14