Should I install using PostGIS extension? is an FAQ that comes up quite a bit in PostGIS circles and unfortunately askers get mixed answers. In How to upgrade your database to PostGIS 2.0 we covered WHY you should use extensions. In this article I'll use my Lincoln-Douglas debate skills to argue why you shouldn't. Keep in mind that I put a great deal of effort in fitting PostGIS extensions into the existing PostGIS build structure and I eat my own dogfood, so I might be a little biased and a poor defender of the counter argument.
Lets start off with the obvious reasons, why you simply can't use PostGIS extensions:
Now for the grey areas:
The biggest issue for development was that you couldn't upgrade your extension if you didn't have a versioned release and we never have versioned releases until we release. I think I took care of this issue by introducing, what I call the next AKA the yo-yo option:
ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext"; -- this you do the next time you upgrade -- ALTER EXTENSION postgis UPDATE TO "2.1.0SVN"; --if you need to upgrade again -- ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";
This is the approach I use for upgrading my production systems running 2.1.0SVN and allowed me to discover a very important bug, which I still need to backport to 2.0. It's a good check for me to make sure we didn't introduce a feature in code base that is not upgradeable using extensions.
The main issue that remains is our regression model doesn't support testing extensions, because our
make check, doesn't actually install PostGIS in
PostgreSQL install folders, but instead installs in a staging area and changes the scripts to point to this staging area. Since extension script files currently have to be in local PostgreSQL
extension folder, this means we can't test before installing.
I'm hoping we can come up with some work-around like defining a temporary extension something like postgis_check that gets deleted and dropped after make check is done similar to what we do with the regress database we create during make check. This in thinking about shouldn't be too difficult to do except for the issue the extension would have to be copied to final install folder and we need to know that before make install. Another option, which would be more powerful is have true older versions of PostGIS ready to be upgraded using our buildbots and have them try to do true upgrades as a second battery of tests.
Some people just don't like things if they don't fully understand how it works, did not invent it, or do not feel they have full control of it. I'm not naming names, but you know who you are. They consider extensions VooDoo magic because it hides the fact there is a script doing something.
I'm going to sum it up, before I elaborate: It is the sanest, easiest way to go, especially for production use.. Now I'm going to bore you by repeating myself. You can stop here if you are already sold on using extensions. The main reason I poured so much effort into packaging the PostGIS extension is because it would solve a couple of my big pain points for both myself and many others.
Platform independent way of enabling PostGIS in your database. No matter which OS you are on, to enable PostGIS in a database you do:
CREATE EXTENSION postgis;
Before you had to find the files to install, which was particularly painful on windows because it was buried either in
C:\Program Files (x86)\PostgreSQL\9.2\share\contrib\postgis... or some other god awful place.
And if you were on Linux/Unix -- good luck figuring out where your distro put these files. I always had to resort to doing a pg_settings query to figure this out.
Then you had to remember which files to install --
postgis.sql, rtpostgis.sql, spatial_ref_sys.sql. Right there I estimated we lost 80% of the audience that would otherwise use PostGIS.
Then there are those smart folks who think -- hey I can install two versions of PostGIS in the SAME database or better yet I can accidentally install 2 different versions of PostGIS in the SAME database or the same version in different schemas. COOL. When you accidentally stumble into this mess, you often don't find out until everyone is screaming at you that the queries have grinded to a halt.
The postgis extension, regardless of which schema you installed it, won't let you get into this drunken stupor. It will recognize you already have a version of PostGIS installed and won't let you move on without uninstall or just upgrading.
I know from experience and after lossing a couple of hours of my life, I vowed this will never happen to ME again. Precautions need to be put in to save Regina and others from this nightmare.
DROP EXTENSION postgis;
DROP EXTENSION postgis CASCADE;
ALTER EXTENSION postgis SET SCHEMA some_schema;.