postgis-boshrelease

Contents

• Summary
• Supported Postgres Versions
• Deploying
• Considerations before deploying
• Considerations after a successful deployment

Summary

This is a BOSH release for PostGIS, which builds on top of the postgres release.

It provides a set of modules and extensions that are loaded into postgres at runtime, and thereby make various spatial functions available to postgres users.

Specifically, it adds the following postgres extensions:

• postgis
• postgis_topology
• postgis_sfcgal
• fuzzystrmatch
• address_standardizer
• address_standardizer_data_us
• postgis_tiger_geocoder

Supported Postgres Versions

This release supports the following postgres versions:

• postgres 9.6
• postgres 10.7
• postgres 11.1
• postgres 11.2

It will pick the version of postgres that was packaged by the postgres-release that you deploy alongside this release. Only one postgres version can be active at a time.

It is currently tested against the following postgres-releases:

• v31 - provides postgres 9.6
• v36 - provides postgres 11.2

It provides the following dependencies:

• boost 1.67.0
• CGAL 4.13
• gdal 2.4.0
• geos 3.7.1
• gmp 6.1.2
• json-c 0.13.1
• libxml2 2.9.9
• mpfr 4.0.2
• proj 5.2.0
  • datumgrid 1.8
  • sqlite 3.27.2
• pcre 8.42
• SFCGAL 1.3.6

Deploying

This release has the same pre-requisites as the postgres release. You will need a working bosh director and you must have a way to upload releases to it (either development releases or final releases). You should generate a deployment manifest as per the postgres release's documentation.

The following instructions assume you have a valid deployment manifest that will deploy an instance of the postgres release. You may have generated this using the generate-deployment-manifest script provided by the postgres-release.

1. Add the postgis-release to your deployment manifest

   In the releases block, add a reference to the postgis-release, as follows:

   releases:
   - name: postgis
     version: latest
   

   (be sure to leave the postgres release reference intact).

2. Add the postgis add-on to your manifest

   The postgis job should be included as an addon. Place the following at the end of your deployment manifest:

   addons:
   - name: postgis
     jobs:
     - name: postgis
       release: postgis
   

3. Deploy:

   bosh -d DEPLOYMENT_NAME deploy OUTPUT_MANIFEST_PATH
   

Considerations before deploying

Make sure to back up any existing postgres data before deploying. This release will not alter any of your existing data but does alter the behaviour of postgres itself.

Considerations after a successful deployment

A number of new database tables and extensions are available after a successful deployment. To access those you should execute the following queries as a db user with sufficient permissions (eg postgres or vcap).

In a future version of this bosh release these steps could be included as deployment configuration options. For the time being these are left as tasks for the operator.

Creating new extensions

The postgres extensions provided by this release need to be CREATEd in order for them to be available to database users. Depending on which extensions you want to enable, some subset of the following queries will need to be executed by a db user with sufficient permissions (e.g. postgres or vcap):

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE EXTENSION IF NOT EXISTS address_standardizer;
CREATE EXTENSION IF NOT EXISTS address_standardizer_data_us;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;

Allowing read access to new spatial tables

You may encounter an error like this:

spatial_ref_system relation does not exist

This may be because the db user does not have access to the spatial_ref_sys table that postgis provides. You can grant access as follows (note that this also grants access to several other tables that your users probably need).

Allowing all users in your database to read the spatial tables:

GRANT SELECT ON TABLE public.geometry_columns TO PUBLIC;
GRANT SELECT ON TABLE public.spatial_ref_sys TO PUBLIC;
GRANT SELECT ON TABLE public.raster_columns TO PUBLIC;
GRANT SELECT ON TABLE public.raster_overviews TO PUBLIC;

You may prefer a more restricted model whereby you only grant access to particular postgres users, or sets of users in specific schemas. In that case something like this might be preferred:

-- using a particular db schema...
GRANT SELECT ON TABLE geometry_columns TO <your_user>;
GRANT SELECT ON TABLE spatial_ref_sys TO <your_user>;
GRANT SELECT ON TABLE raster_columns TO <your_user>;
GRANT SELECT ON TABLE raster_overviews TO <your_user>;