On the problematic machines, do you see a hstore.so file in the pgsql/lib directory?
Also, do you see hstore sql files in the pgsql/share/extension directory?
On Wed, Sep 21, 2016 at 6:09 AM, Uko, Tomáš <uko@xxxxxxxxx> wrote:
Hi everybody, We have a problem with postgres extensions, we have several servers with several instances on each of them (together 42). Each of those instances are same (except table names - aplication partitioning). But when we are preparing to migrate to newer version of Postgres, we discovered this.On all servers, there are tables with hstore columns, therefore there is hstore extension in use. In order to upgrade from 9.3 to 9.5 we need to run "ALTER EXTENSION hstore UPDATE;" on each database. But on some instances, it says, there is no extension hstore:XY=# alter extension hstore update;ERROR: extension "hstore" does not existOn "correct" instance \dx shows (after alter):XY=# \dxList of installed extensionsName | Version | Schema | Description-------------+---------+------------+----------------------- --------------------------- adminpack | 1.0 | pg_catalog | administrative functions for PostgreSQLhll | 1.0 | public | type for storing hyperloglog datahstore | 1.2 | public | data type for storing sets of (key, value) pairspgstattuple | 1.0 | public | show tuple-level statisticsplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language(5 rows)On "weird" one \dx shows:XY=# \dxList of installed extensionsName | Version | Schema | Description-------------+---------+------------+----------------------- ------------------ adminpack | 1.0 | pg_catalog | administrative functions for PostgreSQLhll | 1.0 | public | type for storing hyperloglog datapgstattuple | 1.0 | public | show tuple-level statisticsplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language(4 rows)We are running:Name : postgresql93-serverArch : x86_64Version : 9.3.13Release : 1PGDG.rhel6On CentOS 6 2.6.32-431.20.3.el6.x86_64Each instance has a replica on different machine (via WAL shipping as well as streaming replication) and problem is on both of them (master and slave)Weird is, when we try to add extension again (via CREATE EXTENSION), it ends up succesfully, but \dx won't show itAnother thing, when we try to add extesion with insert to pg_extension it gets OID far greater than any other:XY=# select *,pg_extension.oid from pg_extension;extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition | oid-------------+----------+--------------+----------------+--- ---------+-----------+-------- ------+----------- plpgsql | 10 | 11 | f | 1.0 | | | 12617adminpack | 10 | 11 | f | 1.0 | | | 16471hll | 10 | 2200 | f | 1.0 | | | 16472pgstattuple | 10 | 2200 | t | 1.0 | | | 16473hstore | 10 | 2200 | t | 1.2 | | | 366992783Any ideas what to do to fix?ThanksTomas