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

• Make sure when you launch for first time you have the initdb line unremarked so it builds the cluster.

  Here is where you may run into problems.

• If you don't have Visual C++ 2013 runtime installed on your pc, you may get an error:

  "The program can't start because MSVCR120.dll is missing from your computer. Try reinstalling the program to fix this problem."

  If you get that error, download the Visual C++ Redistributable Packages for Visual Studio 2013: http://www.microsoft.com/en-us/download/details.aspx?id=40784. The vcredist_x86.exe you will need if you are running the 32-bit version of PostgreSQL 9.4 and the vcredist_x64.exe is generally the one you want if you are running the 64-bit PostgreSQL 9.4beta1.

  After you install the respective setup, you should be able to launch your batch script without error.

• Next shut down out of your batch script by click enter to stop this assumes you are using our sample script.
• Download the respective PostGIS and pgRouting binaries from: http://postgis.net/windows_downloads and copy into your PostgreSQL folder.
• Use the pgAdmin in bin folder of your new 9.4 setup, connect to the server
• Create a new database

• Connect to the database and running the following commands at pgAdmin SQL editor or psql shell. This part is the same regardless what OS you are on and if you want the full shebang.

  CREATE EXTENSION postgis;
  CREATE EXTENSION fuzzystrmatch; --needed for tiger geocoder
  CREATE EXTENSION address_standardizer; --commonly used with tiger geocoder
  CREATE EXTENSION postgis_tiger_geocoder; 
  CREATE EXTENSION postgis_topology;

  If you also unzipped in the pgRouting binaries:

  CREATE EXTENSION pgrouting;

  More instructions here: http://pgrouting.org

  This PostGIS family of extensions is very fertile and more babies expected later.

• If you want PointCloud - still experimental, then make sure to copy the pointcloud extension from the extras folder (PostgreSQL specific folder) http://postgis.net/windows_downloads and then run:

  CREATE EXTENSION pointcloud;
  CREATE EXTENSION pointcloud_postgis;

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();