[postgis-tickets] [PostGIS] #3683: Unable to update postgis after postgres pg_upgrade

[PostGIS]

#3683: Unable to update postgis after postgres pg_upgrade
-------------------------------------------------+-------------------------
 Reporter:  gregburek                            |      Owner:  pramsey
     Type:  defect                               |     Status:  new
 Priority:  medium                               |  Milestone:  PostGIS
                                                 |  PostgreSQL
Component:  postgis                              |    Version:  2.3.x
 Keywords:  pg_upgrade extension update          |
  lifecycle                                      |
-------------------------------------------------+-------------------------
 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

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3683>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.