Install PostgreSQL 9.1 and PostGIS 1.5.x on Ubuntu 11.10


I’ve seen a few recipes on the web but none worked for me on Ubuntu Oneiric. Here’s what I did.

Install Software

sudo apt-get install pgadmin3 postgresql-9.1-postgis

Set Up to Log In From locahost Using PgAdmin

sudo su postgres
password postgres

Set Up PostGIS

psql -c "create role gisgroup;"
createdb -E UTF8 template_postgis
psql -d template_postgis < /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -d template_postgis < /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
psql -c "alter table geometry_columns owner to gisgroup;" template_postgis
psql -c "alter table spatial_ref_sys owner to gisgroup;" template_postgis

Using PostGIS

psql -c "create user usernamewhatever with password 'itsasecret';"
psql -c "grant gisgroup to usernamewhatever;"
createdb -T template_postgis -O usernamewhatever newdatabasename

Refer to the BostonGIS cheat sheet and the PostGIS book. And you'll need some data, perhaps you might want to download samples from NAVTEQ Network for Developers... ...oh - wait, you can't do that any more. Looks like Nokia now prefers that you source vector map data from somebody else, perhaps OpenStreetMap data from CloudMade. For NAVTEQ data, from now on you will need to access it through the platform.

If you want to do some heavy lifting with your PostgreSQL engine, you may want to do some tune-ups:
Change /etc/postgres/9.1/main/postgres.conf thus:

shared_buffers =  500MB
work_mem = 200MB
bgwriter_delay = 2000ms
wal_buffers = 16MB
checkpoint_segments = 16
effective_cache_size = 1750MB
autovaccum = off

PostgreSQL will almost certainly cough when you (re)start it - look in /var/log/postgres/postgres-9.1-main.log and it will tell you how much memory it's asking for, which is probably more than your Linux OS is currently configured to allow. No worries (assuming you have plenty of free RAM in your system). To fix (permanently, between boots):
Edit /etc/sysctl.d/30-postgresql-shm.conf, uncomment the kernel.shmmax line and change its values to a larger value that matches or exceeds what PostgreSQL is asking for in the logfile error: E.g..

kernel.shmmax = 554434560 # Or whatever PostgreSQL was asking for in that logfile entry.