PostgreSQL 9.4beta1 and PostGIS 2.2.0 dev on Windows   

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