id summary reporter owner description type status priority milestone component version resolution keywords cc 3683 Unable to update postgis after postgres pg_upgrade going from < 9.5 to pg > 9.4 gregburek robe "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: {{{#!sql [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" defect closed blocker PostGIS 2.3.2 build 2.3.x fixed pg_upgrade extension update lifecycle gregburek