PostGis - Troubleshooting on Ubuntu 11.10
Step 1 (install the stuff)
$ sudo apt-get install postgresql-9.1 postgresql-server-dev-9.1 postgresql-9.1-postgis $ sudo apt-get install binutils gdal-bin python-psycopg2 python-setuptools
Step 2 (create templates)
create a POSTGIS template (from which your databases will be derived)
$ POSTGIS_SQL_PATH=`pg_config --sharedir`/contrib/postgis-1.5
# Creating the template spatial database.
$ sudo -u postgres createdb -E UTF8 template_postgis
******** You may get an error here ***********
could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
This is a typical error if you had PostgreSQL 8.4 and 9.1 on Ubuntu at the same time or if you have upgraded from Ubuntu 10 -> 11.
The problem is that the postgresql port gets changed upon upgrading or with different versions of posgresql. So edit the configuration with your favorite editor:
$ sudo emacs -nw /etc/postgresql/9.1/main/postgresql.conf
change port from 5433 to 5432, and restart the database:
$ sudo service postgresql restart
Add language support for PLPGSQL
$ sudo -u postgres createlang -d template_postgis plpgsql
Allows non-superusers the ability to create from this template
$ sudo -u postgres psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
Load the PostGIS SQL routines
$ sudo -u postgres psql -d template_postgis -f $POSTGIS_SQL_PATH/postgis.sql $sudo -u postgres psql -d template_postgis -f $POSTGIS_SQL_PATH/spatial_ref_sys.sql
(note that the dollar sign above after -f is to signify the variable $POSTGIS_SQL_PATH, it should all be a single line from "$sudo -u .... to ... .sql") ******** You may get an error here *********** the Error is that /postgis.sql cannot be found. So I tried:
$ sudo updatedb $ locate "postgis.sql" # which gave me the answer: /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql /usr/share/postgresql/9.1/contrib/postgis-1.5/uninstall_postgis.sql
so I changed those two commands to commands to,
$ sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql $ sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
Some user rights for users to be able to alter spatial tables
$ sudo -u postgres psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;" $ sudo -u postgres psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;" $ sudo -u postgres psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
So now we are ready to create our own local database. Let's call it test_db with user test_admin
sudo -u postgres createuser --createdb test_admin shall the new role be a superuser? Y sudo -u postgres createdb -T template_postgis -O test_admin test_db sudo -u postgres psql test_db -c "ALTER USER test_admin with password 'random-obscure-password'";
I mostly followed the steps from ChicagoDjango
together with countless hours of debugging for those two simple errors! :-/ ...
So I hope it's of use to you all.
