Postgres OnLine Journal: April 2016 / May 2016 / June 2016
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

From the Editors
What's new and upcoming in PostgreSQL
PostgreSQL Q & A
Basics

From the Editors

 

PGConfUS 2016 PostGIS slides and tutorial material



We gave a PostGIS Intro Training and a PostGIS talk at PGConfUS 2016 in Brooklyn, New York and just got back. A number of people asked if we'd make the slides and material available. We have these posted on our presentation page: http://www.postgis.us/presentations and will be putting on the PostgreSQL Wiki as well in due time. There will be a video coming along for the talk, but the training was not recorded.

We also have two more talks coming up in North Carolina in Early May at FOSS4G NA 2016 - one on PostGIS Spatial Tricks which has more of a GIS specialist focus than the top 10 talk we gave, but there will be some overlap. The other talk is a topic a couple of people asked us in training and after our talk, on routing along constrained paths. If you are attending FOSS4G NA 2016, you won't want to miss our talk pgRouting: A Crash Course which is also the topic of our upcoming book.

Just like FOSS4G NA 2015, there is a pgDay track which is PostgreSQL specific material, useful to a spatial crowd, but not PostGIS focused.

I should also add, that there will be a couple of other talks on routing (not all pgRouting) and other PostGIS topics like PostGIS topology. Couple that I'm looking forward to seeing


What's new and upcoming in PostgreSQL

 

PLV8 binaries for PostgreSQL 9.6beta1 windows both 32-bit and 64-bit



To celebrate recent release of PostgreSQL 9.6beta1, we've started to experiment with our favorite extensions. For starters, PLV8 (aka PL/JavaScript) binaries listed below and upcoming PostGIS 2.3.0 and ogr_fdw detailed here

Listed below PL/V8 binaries built for PostgreSQL 9.6beta1 for windows 64-bit and 32-bit. These I tested with EDB PostgreSQL 9.6 windows installs and seem to work fine and also passed all regression tests. These were built with PLV8 v1.4.8.

PL/V8 PostgreSQL 9.6 binaries

We hope windows users find these useful.


PostgreSQL Q & A

 

PostGIS 2.2 Windows users hold off on installing latest PostgreSQL patch release



Someone reported recently on PostGIS mailing list, that they were unable to install PostGIS 2.2.1 bundle or PostGIS 2.2.2 binaries on a clean PostgreSQL 9.5.2 install. Someone also complained about PostgreSQL 9.3 (though not clear the version) if that is a separate issue or the same. I have tested on PostgreSQL 9.5.2 Windows 64-bit and confirmed the issue. The issue does not affect PostgreSQL 9.5.1 and older. I haven't confirmed its an issue with the 32-bit installs, but I suspect so too. This issue will affect OGR_FDW users and people who used our compiled WWW_FDW.

We've built new binaries for PostGIS 2.2.2 that have CURL compatible with the OpenSSL distributed with lastest PostgreSQL patch release for (PostgreSQL 9.3-9.5). These downloadable from experimental section - http://postgis.net/windows_downloads/ If you still have issues after using these, post to postgis mailing list. We'll be making new installers in a couple of weeks.

What's the issue?

In PostGIS 2.2, we started packaging CURL support with the GDAL driver. GDAL services both PostGIS raster and OGR_FDW extensions packaged with PostGIS 2.2 and enabling curl allowed for a couple more popular raster and vector drivers to be exposed. Since our clients needed SSL support and we needed SSL support with Curl, we compiled CURL with SSL support. However EDB already ships OpenSSL, so we had compiled against a version of OpenSSL that is compatible with what they shipped with PostgreSQL and do not ship it with our binaries. In PostgreSQL 9.5.2 (and I suspect other versions released in last round of releases), the OpenSSL libraries libeasy32.dll and ssleay32.dll got upgraded to 1.0.2g (9.5.1 shipped with 1.0.2f which in theory should be compatible with 1.0.2g since they look like micro differences), but for whatever reason aren't. Might have to do with something else. Anyrate the error you get when you try to do.

CREATE EXTENSION postgis;
is: ERROR: could not load library "../9.5/lib/rtpostgis-2.2.dll":The specified procedure could not be found. Since the www_fdw.dll and ogr_fdw.dll were also compiled with Curl support, I suspect you'll run into the same issue.

How do I fix it now?

If you already installed the latest PostgreSQL 64-bit patch release and are running into this issue, we do have a hot fix which seems to work but haven't throughly tested. We have recompiled the libcurl-4 with the same version of OpenSSL 1.0.2g that ships with latest EDB binaries and that seems to do the trick. To fix your install, replace the libcurl-4.dll packaged with PostGIS 2.2, with the version in http://winnie.postgis.net/download/windows/pg95/buildbot/libcurl7.48withsslx64.zip. On quick tests, this seems to still work okay with PostgrSQL 9.5.1 as well.

We are working on a fix now, that works with PostgreSQL 9.5.2 and is also backward compatible with PostgreSQL 9.5.1 (and other). We will create a new PostGIS 2.2.2 binary package with (latest PostgreSQL patch release). The binaries will be available in the experimental section of http://postgis.net/windows_downloads.

We will not have the installers available for another couple of weeks or month because we want to package pgRouting 2.2.0 with that (which is right now in RC1) and also installer packaging takes a lot more time to prepare and test. Given we do this in our free-time, paying work almost always takes priority over installer packaging.

To those who have asked, if you would like to contribute to our packaging efforts (and other PostGIS activties), I have set up a patreon account.


Basics

 

First Look at pgAdmin 4



When David Page announced pgAdmin 4, I was really excited to try it out. I was impressed I could compile it so easily on windows. I had a few bumps, but not too bad.

One of the reasons I'm excited about it is that it's built on Python and a web framework, and there is a large Python and web developer following in the GIS community, so I suspect someone will step up to the plate to add a mapviewer plugin to this so I can have a seamless PostGIS experience.

The interface is also very slick and pretty and I love the sorting and paging capability now in the query window. Check this sampling from our workshop database.

Still room in Introduction to PostGIS Training

We'll be giving a Training on PostGIS in this coming Monday in PGConf.us New York. Still slots available for signup.

If you are looking for something a bit shorter and will be attending the conference, we'll also be giving a PostGIS talk covering some things you can do with PostGIS.