Unable to update postgis after postgres pg_upgrade going from < 9.5 to pg > 9.4
|Reported by:||gregburek||Owned by:||robe|
|Keywords:||pg_upgrade extension update lifecycle||Cc:||gregburek|
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.
- 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
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:
- 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.
- Generate postgis update scripts that use
CREATE IF NOT EXISTfor 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.
- 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+
- Provide a script which creates, if not present, version dependent items but preserves the postgres extension and allows for later extension updates.
- 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.
Change History (6)
comment:1 by , 7 years ago
|Component:||postgis → build/upgrade/install|
|Priority:||medium → blocker|
comment:3 by , 7 years ago
|Summary:||Unable to update postgis after postgres pg_upgrade → Unable to update postgis after postgres pg_upgrade going from < 9.5 to pg > 9.4|