Unable to update postgis after postgres pg_upgrade going from < 9.5 to pg > 9.4

[gregburek]

Hello,

I work for Heroku Postgres, where we provide Postgres as a service for our customers. A fair number of these customers use the postgis extension, which we install and support. Many of the dbs are quite long lived and have grown to +100GB. We support using pg_upgrade for these databases to move to the latest stable major version of postgres with minimal downtime, as a dump and restore could take hours.

I currently am working with a few customers, who are running postgis 2.3.0 on postgres 9.5 and are attempting to update to postgis 2.3.1 in preparation for upgrading to postgres 9.6. They are encountering this error:

[7-1] LOG:  statement: ALTER EXTENSION "postgis" UPDATE;
[8-1] ERROR:  operator does not exist: gidx public.&& geography at character 14
[8-2] HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
[8-3] QUERY:
[8-4]       SELECT $2 OPERATOR(public.&&) $1;

It appears that the gidx public.&& geography operator was introduced in ​https://github.com/postgis/postgis/commit/f49d42880f2aad1e23daaf5930fb66ec359a11a2 and is gated behind a #if POSTGIS_PGSQL_VERSION > 94 check.

In the case of these customers, it appears that they are seeing a problem where a postgis update, followed by a postgres pg_upgrade results in a postgis which is no longer able to weather a minor version update and is missing functionality that the postgis and postgres versions would otherwise support.

To reproduce:

• Create a 9.4 postgres cluster and install postgis 2.1
• Update postgis to 2.3.0
• Execute a pg_upgrade to postgres 9.6
• Run ALTER EXTENSION "postgis" UPDATE TO "2.3.1";

As the update to 2.3 was done on a postgres version which did not allow for BRIN, the update path of 2.3.0 -> 2.3.1 attempts to modify operators that do not exist, as the current major version of postgres suggests that they should.

As a note, no 2.1 postgis debian packages are maintained for postgres 9.6, so the initial update was needed to allow pg_upgrade to succeed. It is unfortunate that an update to latest available for 9.4 set the stage for this bug.

There are a few paths forward here:

1. Require and document a dump and restore for all postgis clusters that have experienced a pg_upgrade and may be missing postgis items that are postgres version dependent.
2. Generate postgis update scripts that use CREATE IF NOT EXIST for items that are otherwise gated behind postgres version checks, so that a given postgis update may repair itself, no matter the postgres version history of the cluster.
3. Provide a matrix of maximum postgis update paths that preclude pg_upgrades. ie starting at postgis 2.0 on postgres 9.2, you can only update to postgis 2.2 before encountering features that require 9.5+
4. Provide a script which creates, if not present, version dependent items but preserves the postgres extension and allows for later extension updates.
5. Provide older extension packages for latest postgres major versions, so that postgis updates may be deferred until after the postgres upgrade.

More ideas are welcome.

Thanks, Greg

[robe]

Greg,

Thanks for the report. We are planning on another release in about 2 weeks (2.3.2). I'll see if we can work something out (using your suggestions) that will allow pg_upgrade to gracefully upgrade from pre-9.5 2.3 or lower to post-9.5 2.3+ and include that as part of our 2.3.2.

[robe]

In 15290:

Fix for pg_upgrade of pg < 9.5 + 2.3+, pg_upgrade and doing a postgis micro update to latest stable.
References #3683 for PostGIS 2.4 (trunk)

[robe]

In 15291:

Fix for pg_upgrade of pg < 9.5 + 2.3+, pg_upgrade and doing a postgis micro update to latest stable.
closes #3683 for PostGIS 2.3

[robe]

Greg,

When you get a chance, can you test this out.

I tested by

Installing PostGIS 2.3.2dev on PostgreSQL 9.4 server (this ends up not including the new brin operators).

CREATE EXTENSION postgis VERSION "2.3.2dev";

Then used pg_upgrade to migrate this to PostgreSQL 9.6.1

Once up, I ran:

ALTER EXTENSION postgis UPDATE TO "2.3.2devnext";

I verified after running the update step that I now have the brin operators, classes, and families.

Since I'm not using any new constructs, I expect it to work for PostgreSQL 9.2+