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.
-1
.