pg_upgrade from 9.3.5 to 9.6 with PostGIS (Windows)

Question

Versions

• current:

  • PostgreSQL: 9.3.5 64-bit
  • PostGIS: 2.2.3
  • notes: 2.2.3 is the latest available on StackBuilder for 9.3.5;

• upgrade

  • PostgreSQL: 9.6 64-bit
  • PostGIS: 2.3.1
  • notes: 2.3.1 is the only version available on StackBuilder for 9.6, although in principle I could downgrade to an archived 2.3.0 from OSGEO's installer archives, but that's as close as I can get to matching versions (which it turns out would not resolve anything anyhow).

DB size (not counting tables under 1MB in size): ~230GB. Not trivial, but not 'big data' either. Large enough that pg_dump would be a monumental PITA.

The Problem

If I enable the PostGIS extension on the destination (9.6) and run pg_upgrade with the appropriate source and destination bin and data directories, I get

New Cluster database "postgres" is not empty
Failure, exiting

This is because the PostGIS extension creates a table (spatial_ref_sys) when installed; the table can't be dropped without dropping the extension.

If I disable PostGIS in the destination (9.6), pg_upgrade fails because the destination lacks the appropriate loadable libraries (hint: libraries to do with PostGIS).

pg_upgrade offers the dandy suggestion that I remove the functions using the libraries from the old installation, which sounds like a recipe for disaster to me.

UPDATE: just tried deleting one of the PostGIS functions (ST_Locate_Between_Measures() - I've never used it and am unlikely to). Can't be done without dropping PostGIS, which can't be done because all the geometry columns in the data have GIST indexes.

I had tried to make the PostGIS versions conformable across the two PostgreSQL versions, but that can't happen, apparently - at least, not on Windows.

My guess

My guess is that it's monumentally unlikely that the entire PostGIS-enabled world can't update their PostgreSQL versions in a reasonably straightforward (i.e., non-pg_dump) way.

PostGIS folks tend to care a lot about how long stuff takes to do (try doing spatial comparisons on 3 million polygons without a GIST index to see what a long query looks like), so it stands to reason that someone will have worked out how to upgrade rapidly.

Reach Goal

In the best of all worlds I would pg_upgrade --link, because once I migrate to the new version, the old cluster is dead to me.

CODA/inb4

Why would anybody run a Windows machine to do anything that requires reliability?, I hear you ask.

Legacy systems ("It was like that when I got here, officer") plus inertia (both institutional and personal)

I'm going interstate this weekend, so maybe the DB will just have to pg_dump itself across while I'm gone.

Answer 1

• PostGIS 2.2 isn't built for PostgreSQL 9.6 on Windows
• PostGIS 2.3 isn't built for PostgreSQL 9.3 on Windows.

You have two options

1. Do a hard upgrade.
2. Build for 9.6 PostGIS 2.2, then do a soft upgrade

I would do a hard upgrade. Seriously 230 GB is not much to bring in. You'll live. (I think).

My guess is that it's monumentally unlikely that the entire PostGIS-enabled world can't update their PostgreSQL versions in a reasonably straightforward (i.e., non-pg_dump) way.

They do have a method for doing a quick and easy upgrade, but it requires the server to have both versions of PostGIS. Then you can run,

ALTER EXTENSION postgis UPDATE TO "2.3.2devnext";
ALTER EXTENSION postgis_topology UPDATE TO "2.3.2devnext";

That method isn't readily available to you because I can't find anyone who has compiled PostGIS 2.3 for MS Windows PostgreSQL 9.3, but strictly speaking it's possible.

If you were using Ubuntu/Debian this wouldn't be a problem as a precompiled version of PostGIS is already in the repository for all versions of PostgreSQL also in the repository.