Should I install using PostGIS extension

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.

Reasons why you shouldn't

Lets start off with the obvious reasons, why you simply can't use PostGIS extensions:

• You are STILL using PostGIS 1.5 (or god forbid some even lower version) and have not embraced the kewlness of PostGIS 2 series
• You are using PostgreSQL 9.0 or below. Unfortunately extensions is only available for PostgreSQL 9.1+.
• You compiled PostGIS 2+ without raster support. Unfortunately some people ran into problems compiling with raster support and some distributions don't offer PostGIS 2 with raster support. Not being able to use the extension install process without PostGIS raster,I take a lot of blame for. Initially we were going to have raster as a separate extension dependent on the core PostGIS, much like the postgis_topology, and upcoming postgis_tiger_geocoder and pgrouting. I really wanted everybody to have raster and made it required for the PostGIS extension support - figuring it would nudge folks (particularly package maintainers) in the right direction. I figured, if little old me building on a crippled Microsoft windows platform could manage to compile with raster support, then certainly it must be a trivial exercise for a superior Linux/Unix/Mac OS compiled by an experienced Linux hacker to do it. Guess I was wrong there.

Now for the grey areas:

• Somewhat tricky to test in with development versions

  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.

• Not invented here.

  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.

• It reduces your freedom, much like not being able to run around with an armed weapon reduces your freedom. How does it reduce your freedom? Well with many of the PostGIS upgrade scripts, you could run thru them even if they resulted in errors. Extensions require a clean install, meaning nothing can fail. You can't drop functions and other dependent objects willy-nilly that are part of the extension just because you feel like it.

Why should you install with PostGIS extensions?

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.

1. 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\PostgreSQL\9.2\share\contrib\postgis... or 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.

2. 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.

3. You can DROP the postgis extension safely.
   DROP EXTENSION postgis;
   will only drop postgis if you don't have tables or other extensions that rely on it. If you want to drop it and everything associated with it, you can use
   DROP EXTENSION postgis CASCADE;
   with caution. The non-extension PostGIS uninstall script, just drops without asking questions, possibly dropping data with it.
4. You can move PostGIS into another schema later if you are not happy with it in public with single line.
   ALTER EXTENSION postgis SET SCHEMA some_schema;.