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:

Now for the grey areas:

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:


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