PostgreSQL 9.4beta1 and PostGIS 2.2.0 dev on Windows

PostgreSQL 9.4beta1 was released last week and windows binaries for both 32-bit and 64-bit are already available to try it out from http://www.postgresql.org/download/windows. Since this is a beta release, there are no installers yet, just the zip binary archive. To make the pot a little sweeter, we've setup the PostGIS windows build bot (Winnie) to automatically build for 9.4 - PostGIS 2.2.0 development branch and pgRouting 2 branches whenever there is a change in the code. We also have the pointcloud extension in the extras folder. If you are on 9.3, we've got 2.2 binaries for that as well. The PostGIS/pgRouting related stuff you can find at http://postgis.net/windows_downloads in the 9.4 folder.

For the rest of this article we'll discuss a couple of stumbling blocks you may run into.

Much of what we'll describe here is windows specific, but thanks to the beauty of extensions and GUCs, the extension install and GUC setting part for PostGIS is applicable to all operating systems.

Setting up PostgreSQL 9.4beta1 for experimentation

Playing around with PostgreSQL 9.4beta1 is generally fairly easy. Download the zip archive from http://www.postgresql.org/download/windows for 9.4 and write up a batch script to start the database server as we described in Starting PostgreSQL in windows without install.

If you want to use PostGIS raster support, add the following line to your batch script before the initdb line:

@SET GDAL_DATA=%~dp0\gdal-data

If you are interested in learning more about LIDAR support with pointcloud, check out LIDAR in PostgreSQL with PointCloud video and related slides PointCloud slides.

Check things are installed

SELECT postgis_full_version();

Should output something of the form:

POSTGIS="2.2.0dev r12575" GEOS="3.4.2-CAPI-1.8.2 r3924" 
PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" 
LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER

As of PostGIS 2.0.6, 2.1.3 and 2.2.0, raster drivers and outdb are disabled by default, these can be renabled either via environment variables or for PostGIS 2.2.0 the new GUC variables detailed in the What's new section: New in 2.2.

For a generic launch script that works for all versions, I just add the lines to my script before the pg_ctl start:

@SET POSTGIS_ENABLE_OUTDB_RASTERS=1
@SET POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

However you can instead use the new ALTER SYSTEM feature in 9.4, and do this at an SQL Editor prompt:

ALTER SYSTEM SET postgis.gdaL_enabled_drivers TO 'ENABLE_ALL';
ALTER SYSTEM SET postgis.enable_outdb_rasters TO '1';

You can alternatively set these at the database or session level if you want each database/session to have different raster options.

For changes to take effect (if you did at system), you have to stop (enter) and relaunch the batch script.

To confirm you have all drivers, run the below and you should get 10 or more records

SELECT * FROM ST_GDALDrivers();