How to Use PostGIS Extensions to upgrade to non-released PostGIS versions

I love PostgreSQL extensions model except for one small little problem. When you want to work with not yet released versions of PostGIS, upgrading the extensions is not as simple as:

The problem is there is no migration path to go from: 2.1.0SVN to 2.1.0SVN and PostGIS is not anal enough to maintain micro-micro version scripts.

If you try something like:

When you are already at 2.1.0SVN, you'll get a polite message: NOTICE: version "2.1.0SVN" of extension "postgis" is already installed.

Specifying such a migration is in fact illegal probably because it creates all sorts of cyclical redundancy issues.

However there is an easy get out of jail free card for this issue. The free card hinges on two fundamental axioms:

• PostgreSQL extensions don't care about version numbers per se, but only about migration paths and the migration path are all convention based on the file names of the extension files.
• PostGIS minor upgrade scripts can be run a trillion times and not cause any damage. They will merely skip over or redo what's already been done.

Get out of Jail Free Card

So here is the trick - hack, free card however you want to call it, but before I tell you this, I must mention This will not work going from PostGIS 2.0.something to PostGIS 2.1.something, but will work going from PostGIS 2.0.someting to unreleased PostGIS 2.0.something. This is a bug in PostGIS 2.1.0 more than anything because we haven't revised even the standard upgrade scripts to recognize structural additions added in 2.1. That issue we hope to have fixed shortly so that people can actually upgrade with the generated upgrade scripts.

Let us say you are on extension version PostGIS 2.1.0SVN of some PostGIS 2.1.0SVN, and you just compiled and built and now have a version PostGIS 2.1.0SVN r10154 which is inconveniently enough called PostGIS 2.1.0SVN. How do you go from 2.1.0SVN to 2.1.0SVN. Simple you walk forward and then you walk back. At least that's the little lie you tell extensions to appease it. How do you do that?

• Make a copy of the currently badly named postgis--2.0.0--2.1.0SVN.sql and name it postgis--2.1.0SVN.sql--2.1.0SVNnext.sql
• Make another copy of the same file and call it postgis--2.1.0SVNnext.sql--2.1.0SVN.sql

Copy these new files into your PostgreSQL share/extension folder.

Now you can get away with doing this:

ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";
ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";

The second step is really there just so you can easily upgrade in the future and you can safely delete these interim files.