[postgis-devel] ERROR: operator class "brin_geography_inclusion_ops" does not exist for access method "brin"

[Justin Pryzby]

On Sat, Oct 09, 2021 at 10:45:43AM -0400, Regina Obe wrote:
> You know by chance if these ever went thru pg_upgrade.

Yea, I pg-upgrade our instances each year.

This instance was probably pg_upgraded initially from v8.3 to ~9.2, and then
every year since then.  I had no issues with the previous postgis versions.

However, I recently added a nagios check for:
| SELECT * FROM pg_available_extensions WHERE default_version!=installed_version;
and a check for "need upgrade" in SELECT postgis_full_version();

It seems very possible that postgis was not upgraded between versions as you
intended.  My usual procedure is to install the newest postgis for both the old
and new postgres version, and then run postgis_upgrade.sql, and then run
pg_upgrade.  But until recently I never ran ALTER EXTENSION postgis UPGRADE,
nor postgis_extensions_upgrade() - unless that's enforced, it probably can't be
assumed that someone did that.

The issue might be only because of my use of pre-release packages in 2019, and
2021.  If it's useful, I could look at other affected instances to see if they
have that in common.

$ sudo grep -H 'Installed: postgis' /var/log/yum.log*
/var/log/yum.log:Oct 08 20:09:05 Installed: postgis31_14-3.1.4-1.rhel7.x86_64
/var/log/yum.log:Oct 08 20:09:05 Installed: postgis31_14-client-3.1.4-1.rhel7.x86_64
/var/log/yum.log:Oct 08 20:57:47 Installed: postgis32_14-3.2.0-alpha1_1.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 17:59:22 Installed: postgis30_11-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 17:59:22 Installed: postgis30_11-utils-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 17:59:22 Installed: postgis30_11-client-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 17:59:25 Installed: postgis30_11-docs-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 18:09:55 Installed: postgis24_11-2.4.8-5.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 18:18:40 Installed: postgis30_11-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 18:18:41 Installed: postgis30_11-client-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 18:42:51 Installed: postgis30_12-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20190927:Sep 26 18:42:51 Installed: postgis30_12-client-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20200101:Sep 27 12:27:32 Installed: postgis24_11-2.4.8-5.rhel7.x86_64
/var/log/yum.log-20200101:Sep 27 12:30:06 Installed: postgis30_12-3.0.0alpha4-6.rhel7.x86_64
/var/log/yum.log-20210101:Sep 19 13:21:56 Installed: postgis31_13-3.1.0-alpha1_1.rhel7.x86_64
/var/log/yum.log-20210101:Sep 19 13:22:02 Installed: postgis31_13-client-3.1.0-alpha1_1.rhel7.x86_64
/var/log/yum.log-20210101:Sep 30 20:27:21 Installed: postgis31_13-debuginfo-3.1.0-alpha2_2.rhel7.x86_64

Before this, we ran on a centos6 VM, for which the logs look like:
$ sudo grep -H 'Installed: postgis' /mnt/tmp/var/log/yum.log*
/mnt/tmp/var/log/yum.log:Sep 26 16:58:53 Installed: postgis24_11-2.4.5-1.rhel6.1.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 09 00:31:52 Installed: postgis23_96-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 09 00:31:52 Installed: postgis23_96-client-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 09 00:31:52 Installed: postgis23_96-utils-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 09 00:31:56 Installed: postgis23_96-docs-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 09 15:51:52 Installed: postgis22_95-2.2.5-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 10 00:22:14 Installed: postgis23_96-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 10 00:23:11 Installed: postgis23_96-client-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 10 00:54:58 Installed: postgis23_95-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Sep 11 00:44:10 Installed: postgis23_96-2.3.3-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Oct 08 19:27:32 Installed: postgis24_10-2.4.0-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Oct 08 19:54:27 Installed: postgis24_96-2.4.0-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Oct 08 19:55:58 Installed: postgis24_96-client-2.4.0-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20180101:Oct 08 19:56:33 Installed: postgis24_10-client-2.4.0-1.rhel6.x86_64
/mnt/tmp/var/log/yum.log-20190101:Oct 18 13:51:52 Installed: postgis24_11-2.4.5-1.rhel6.1.x86_64
/mnt/tmp/var/log/yum.log-20190101:Oct 18 13:51:54 Installed: postgis24_11-client-2.4.5-1.rhel6.1.x86_64