2

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.

  • 230 GB will actually load fairly quick, especially with pg_restore -1. – Evan Carroll Jan 17 '17 at 23:08
1

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.

  • 1
    I did some BOTE calcs on how long it would take to do a full pg_dump [credentials, port etc] old_cluster | psql [credentials, port etc] new_cluster; my shoddy calcs resulted in a number that's slightly less than half a day, so I set it running before I went out at 9a.m.; it's now 11:50a.m. and psql is populating tables from the 201503 schema (the schemae are annual from 2010 and quarterly from 201412, so it's done 5-and-bit out of 13 schemae). I must have divided by zero somewhere. I'll survive (like when I took an arrow to the knee). – GT. Jan 18 '17 at 0:57
  • Godspeed. That's all you can do sometimes. PostGIS isn't core. The upgrade support isn't up to par with core either. Being an extension, it's just not practical to have it built for every version of PostgreSQL to alleviate this problem. That said, if you were using Debian/Ubuntu this would be easily solved, and not an issue. apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/… It's in the official repository. – Evan Carroll Jan 18 '17 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.