I've played a little with postgresql, but still I can't say that it's in my comfort zone..

After digging around for a geoDjango project, it was clear that I had to with postgres because of the shortcomings of MySQL in term of GIS support.

But when tried to configure a geospatial database following Django's documentation .. nothing worked. I had these kind of errors:

h3@bomb:/www/project.com/trunk/project$ ./create_template_postgis-debian.sh ... psql:/usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql:15137: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK psql:/usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql:15139: ERROR: relation "spatial_ref_sys" does not exist ERROR: relation "geometry_columns" does not exist ERROR: relation "geography_columns" does not exist

Weird.. and then I couldn't rerun the command because the template was already created:

h3@bomb:/www/project.com/trunk/project$ ./create_template_postgis-debian.sh createdb: database creation failed: ERROR: database "template_postgis" already exists ERROR: relation "geography_columns" does not exist

That's how I learned how fun it was to delete a template:

update pg_database set datistemplate = false where datname = 'template_postgis';

Then only you can do a dropdb template_postgis ..

Great .. now what ? Again stackoverflow saves the day..

Sure enough, I was running PostgreSQL 9+ for which there is no postgis package ready yet .. this explained why I was having a hard time making postgis work with it. facepalm.

I just did the obvious: removed postgres 9.0

sudo apt-ger remove postgresql-9.0 postgresql-9.1 postgresql-client-9.0 postgresql-client-9.1 postgis

Then I installed 8.4:

sudo apt-get install postgresql-8.4 postgresql-client-8.4 postgis postgresql-8.4-postgis

Retried Django's documentation steps, worked like a charm.

Let's this serve me as a lesson to not use unstable distros on a workstation..

Update:

When trying to sync my db I got this error:

psycopg2.OperationalError: 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"?

I solved it bey changing the listen port to 5432.

At this point .. whatever.