Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 7 down vote favorite
1

Somehow my postGIS template got overwritten by one with data from one of the databases I previously created.

I've tried everything I read on goggle but could not delete or replace the template with the correct one... I am hoping that someone here would know the best way to fix this problem without having to do a complete a database software.

Thanks Chris

share|improve this question
3  
BTW, don't forget to periodically make the database dumps. – Pablo Mar 11 '11 at 14:37
AND test that the dump works/can be restored or you might have a bad surprised at the moment you need the backup. – wildpeaks Mar 12 '11 at 20:15

3 Answers

up vote 8 down vote

You can create a new template by creating a new database and executing postgis.sql and spatial_ref_sys.sql. You might have to search for them on your system.

On Ubuntu it can be done like this (assuming postgres 8.4 and postgis 1.4):

sudo su postgres
createdb postgis_template
createlang plpgsql postgis_template
psql -d postgis_template -f /usr/share/postgresql/8.4/contrib/postgis.sql
psql -d postgis_template -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql
share|improve this answer
you were faster :-) – Nicklas Avén Mar 11 '11 at 7:22
Well I finally had sometime to try you suggestion... I'm getting the following suggestion when I try to run step 4.... ERROR: could not access file "$libdir/postgis-1.4": No such file or directory Any ideas?? – Chris Mar 15 '11 at 3:00
Digging a bit I found that when I ran pg_config --libdir.... It says that $libdir should be /usr/lib Not sure what that means... Can someone help figure out my next step?? – Chris Mar 15 '11 at 4:56
Maybe this thread helps: mail-archive.com/postgis-users@postgis.refractions.net/… – underdark♦ Mar 15 '11 at 7:30
Based on the thread you gave me I found 2 copies: /opt/PostgresPlus/8.4SS/lib/postgis-1.4.so and /usr/lib/postgresql/8.4/lib/postgis-1.4.so The my next question is how do I get $libdir to point to one of them? – Chris Mar 15 '11 at 12:14
up vote 4 down vote

that is not a big problem. the template just helps you to get the postgis functions installed automatic when you use it to create a new database.

you can install the functions manually. and if you do that in an empty database you can use that as template.

to install the functions and projection definitions just run the file postgis.sql in pgadmin or psql and then you run spatial_ref_sys.sql (I think that is the name or something similar) search for postgis.sql and the other file should be in the same directory. I think the path to th files is: windows c:\progrm files\postgresql\9.0\share\contrib\postgis1-5\

linux /usr/share/postgresql/9.0/contrib/postgis1-5/

/Nicklas

share|improve this answer
Nicklas: I did as you suggested, run the script via pgadmin. But as you would expect from all my efforts attempting the first suggestion, I am getting the same error... So now I'm stuck with what seems to be my only option.... to delete postgres and install from scratch.... I'm hoping that someone can help figure out how to give $libdir the correct path to the file – Chris Mar 17 '11 at 0:07
up vote 0 down vote accepted

After trying just about everything I could find on the web, I backed up my data files and went ahead with deleting the software and installed the latest version of Enterprisedb's postgresplus (enterprisedb.com).

So far it looks like I have the postGIS files and all my databases!!

Thanks everyone for the suggestions and support

Regards
Chris

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.