Re: Problem with extension

Tomáš Uko <uko@xxxxxxxxx> · Thu, 22 Sep 2016 13:10:09 +0200



No, that doesnâ??t work as well
Â 
XY=# drop extension hstore;
ERROR:Â  extension "hstore" does not exist
Â 
Â 
Â 
From: Aislan Luiz Wendling [mailto:aislanluiz@xxxxxxxxxxx] 
Sent: Thursday, September 22, 2016 1:06 PM
To: TomĂĄĹĄ Uko <uko@xxxxxxxxx>
Subject: Re: [ADMIN] Problem with extension
Â 
If it is possible, you can try to drop that extension, copy the contrib from a machine that is working and try to create again. It appears to be a library problem with no treatment.
Â 
From: pgsql-admin-owner@xxxxxxxxxxxxxx <pgsql-admin-owner@xxxxxxxxxxxxxx> on behalf of TomĂĄĹĄ Uko <uko@xxxxxxxxx>
Sent: Thursday, September 22, 2016 7:46 AM
To: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re:  Problem with extension 
Â 
Another thing, 
Â 
Recreating extension again via â??CREATE EXTENSIONâ?? doesnâ??t work
XY=# CREATE EXTENSION hstore SCHEMA public;
ERROR:Â  type "hstore" already exists
XY=# CREATE EXTENSION hstore;
ERROR:Â  type "hstore" already exists
Â 
Any ideas?
Â 
Â 
Â 
From: TomĂĄĹĄ Uko [mailto:uko@xxxxxxxxx] 
Sent: Wednesday, September 21, 2016 4:58 PM
To: Payal Singh <payal@xxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: RE:  Problem with extension
Â 
Hi,
Â 
Sure, otherwise tables with hstore column wonâ??t be created/usable (IMHO). Extension is part of contrib package (if I remember correctly)
Â 
[root@XX ~]# ls -al /usr/pgsql-9.3/lib/hstore.soÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
-rwxr-xr-x 1 root root 56336 May 11 16:59 /usr/pgsql-9.3/lib/hstore.soÂ Â  
Â 
[root@XX ~]# ls -al /usr/pgsql-9.3/share/extension/hstore*
-rw-r--r-- 1 root rootÂ Â  279 May 11 16:57 /usr/pgsql-9.3/share/extension/hstore--1.0--1.1.sqlÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
-rw-r--r-- 1 root rootÂ  1201 May 11 16:57 /usr/pgsql-9.3/share/extension/hstore--1.1--1.2.sqlÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
-rw-r--r-- 1 root root 12646 May 11 16:57 /usr/pgsql-9.3/share/extension/hstore--1.2.sql Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
-rw-r--r-- 1 root rootÂ Â  158 May 11 16:57 /usr/pgsql-9.3/share/extension/hstore.controlÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
-rw-r--r-- 1 root rootÂ  5509 May 11 16:57 /usr/pgsql-9.3/share/extension/hstore--unpackaged--1.0.sqlÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
Â 
T.
Â 
From: Payal Singh [mailto:payal@xxxxxxxxxx] 
Sent: Wednesday, September 21, 2016 4:34 PM
To: Uko, TomĂĄĹĄ <uko@xxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re:  Problem with extension
Â 
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?Â 

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone:Â 240.646.0770Â x 253
Â 
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 exist
Â 
On "correct" instance \dx shows (after alter):
XY=# \dx
Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â List of installed extensions
Â  Â  Name Â  Â  | Version | Â  Schema Â  | Â  Â  Â  Â  Â  Â  Â  Â  Â  Description
-------------+---------+------------+--------------------------------------------------
Â adminpack Â  | 1.0 Â  Â  | pg_catalog | administrative functions for PostgreSQL
Â hll Â  Â  Â  Â  | 1.0 Â  Â  | public Â  Â  | type for storing hyperloglog data
Â hstore Â  Â  Â | 1.2 Â  Â  | public Â  Â  | data type for storing sets of (key, value) pairs
Â pgstattuple | 1.0 Â  Â  | public Â  Â  | show tuple-level statistics
Â plpgsql Â  Â  | 1.0 Â  Â  | pg_catalog | PL/pgSQL procedural language
(5 rows)
Â 
On "weird" one \dx shows:
XY=# \dx
Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â List of installed extensions
Â  Â  Name Â  Â  | Version | Â  Schema Â  | Â  Â  Â  Â  Â  Â  Â  Description
-------------+---------+------------+-----------------------------------------
Â adminpack Â  | 1.0 Â  Â  | pg_catalog | administrative functions for PostgreSQL
Â hll Â  Â  Â  Â  | 1.0 Â  Â  | public Â  Â  | type for storing hyperloglog data
Â pgstattuple | 1.0 Â  Â  | public Â  Â  | show tuple-level statistics
Â plpgsql Â  Â  | 1.0 Â  Â  | pg_catalog | PL/pgSQL procedural language
(4 rows)
Â 
Â 
We are running:
Name Â  Â  Â  Â : postgresql93-server
Arch Â  Â  Â  Â : x86_64
Version Â  Â  : 9.3.13
Release Â  Â  : 1PGDG.rhel6
Â 
On Â CentOS 6 2.6.32-431.20.3.el6.x86_64
Each 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 it
Â 
Another 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 Â  Â  Â  Â | Â  Â  Â  Â  Â  | Â  Â  Â  Â  Â  Â  Â | Â  Â  12617
Â adminpack Â  | Â  Â  Â  10 | Â  Â  Â  Â  Â  11 | f Â  Â  Â  Â  Â  Â  Â | 1.0 Â  Â  Â  Â | Â  Â  Â  Â  Â  | Â  Â  Â  Â  Â  Â  Â | Â  Â  16471
Â hll Â  Â  Â  Â  | Â  Â  Â  10 | Â  Â  Â  Â  2200 | f Â  Â  Â  Â  Â  Â  Â | 1.0 Â  Â  Â  Â | Â  Â  Â  Â  Â  | Â  Â  Â  Â  Â  Â  Â | Â  Â  16472
Â pgstattuple | Â  Â  Â  10 | Â  Â  Â  Â  2200 | t Â  Â  Â  Â  Â  Â  Â | 1.0 Â  Â  Â  Â | Â  Â  Â  Â  Â  | Â  Â  Â  Â  Â  Â  Â | Â  Â  16473
Â hstore Â  Â  Â | Â  Â  Â  10 | Â  Â  Â  Â  2200 | t Â  Â  Â  Â  Â  Â  Â | 1.2 Â  Â  Â  Â | Â  Â  Â  Â  Â  | Â  Â  Â  Â  Â  Â  Â | 366992783
Â 
Â Any ideas what to do to fix?
Â 
Â Thanks
Â Tomas
Â