Recently I needed the function ST_Split for various and sundry reasons, only to discover that I couldn’t use it in my database. The reason was that ST_Split is a new function introduced in Postgis 2.0, and the database was still in 1.5.3. Obviously, time for an upgrade.
I began following the upgrade process, which I’ll summarize below.
First, the installation of the new PostGIS:
$ tar xvfz postgis-2.0.0SVN.tar.gz cd postgis-2.0.0SVN ./configure --with-topology make sudo make install
Next, following the same instructions, I made a test database which was supposed to be in PostGIS 2.0:
$ createdb test createlang plpgsql test psql -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql test
But at this point, it would fail with the following error:
psql:/usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql:73: ERROR: type "spheroid" already exists
Turns out that I still had bits and pieces of the old PostGIS 1.5 getting automatically applied via template. In such cases, PostGIS 2.0 will not be able to create a database with new functions, because the old ones are already there and can’t be overwritten. Therefore, I had to get rid of the old PostGIS in my template. The simplest way to do this in my case was to restore the original template:
psql# UPDATE pg_database SET datistemplate = false WHERE datname = 'template1'; ALTER DATABASE template1 RENAME TO template1old; CREATE DATABASE template1 WITH TEMPLATE template0; UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';
Then, after dropping and recreating the test database, do the following from the terminal:
$ cd /usr/share/postgresql/9.1/contrib/postgis-2.0/ psql -d test -f postgis.sql psql -d test -f spatial_ref_sys.sql psql -d test -f rtpostgis.sql psql -d test -f topology.sql
To see if it went well, do this:
psql# SELECT PostGIS_full_version();
Which should give you something like this:
POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9dev, released 2011/01/18" LIBXML="2.7.8" USE_STATS