Upgrading a database to PostGIS 2

Posted by & filed under QGIS.

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

2 Responses to “Upgrading a database to PostGIS 2”

  1. wwwald

    Is there a recommended way to upgrade an *existing* database using PostGIS 1.* to PostGIS 2?

    Ever since I tried to do such an upgrade, I try to keep PostGIS functions & tables isolated in the ‘public’ schema and put all the rest in separate schemas. This way, it’s less hard to remove all ‘old’ PostGIS functions/tables (since they’re not mixed up with the rest) and install a newer version on the same database.

    But that still seems like it’s harder than it should be… hence the question.
    Any suggestions appreciated!

    • Rudi Thiede

      Hi,

      “Is there a recommended way to upgrade an *existing* database using PostGIS 1.* to PostGIS 2?”

      I’m not sure I understand the question, because that’s exactly what I demonstrated above. In the link to the upgrade process, they will tell you the rest (see http://postgis.refractions.net/documentation/manual-svn/postgis_installation.html#upgrading).

      Basically, the test database I created in the example above is where you will be restoring your old PostGIS 1.* database dump into.

      I didn’t write out how to do that part of it, because PostGIS 2 has not been officially released yet and so is probably subject to change. Please see PostGIS’s own documentation for how to do a Hard Upgrade.

Leave a Reply

You must be logged in to post a comment.