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

  • 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

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
  • you were faster :-) 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??
    – ChrisJ
    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??
    – ChrisJ
    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?
    – ChrisJ
    Mar 15 '11 at 12:14

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: 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
    – ChrisJ
    Mar 17 '11 at 0:07

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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