Thread: Problem with extension

Problem with extension

From
Uko, Tomáš
Date:
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

Re: Problem with extension

From
Payal Singh
Date:
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@avast.com> 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

Re: Problem with extension

From
Tomáš Uko
Date:

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@omniti.com]
Sent: Wednesday, September 21, 2016 4:34 PM
To: Uko, Tomáš <uko@avast.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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@avast.com> 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

 

Re: Problem with extension

From
Tomáš Uko
Date:

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@avast.com]
Sent: Wednesday, September 21, 2016 4:58 PM
To: Payal Singh <payal@omniti.com>
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] 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@omniti.com]
Sent: Wednesday, September 21, 2016 4:34 PM
To: Uko, Tomáš <uko@avast.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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@avast.com> 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

 

Re: Problem with extension

From
Tomáš Uko
Date:

No, that doesn’t work as well

 

XY=# drop extension hstore;

ERROR:  extension "hstore" does not exist

 

 

 

From: Aislan Luiz Wendling [mailto:aislanluiz@hotmail.com]
Sent: Thursday, September 22, 2016 1:06 PM
To: Tomáš Uko <uko@avast.com>
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@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of Tomáš Uko <uko@avast.com>
Sent: Thursday, September 22, 2016 7:46 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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@avast.com]
Sent: Wednesday, September 21, 2016 4:58 PM
To: Payal Singh <payal@omniti.com>
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] 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@omniti.com]
Sent: Wednesday, September 21, 2016 4:34 PM
To: Uko, Tomáš <uko@avast.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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@avast.com> 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

 

Re: Problem with extension

From
Tom Lane
Date:
=?UTF-8?B?VG9tw6HFoSBVa28=?= <uko@avast.com> writes:
> 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

Perhaps what you've got there is an hstore installation from before
extensions existed?  Try "create extension hstore from unpackaged"
to update 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;

When you don't know what you are doing, trying to fix it with manual
surgery on the system catalogs is a good way to make things worse.

            regards, tom lane


Re: Problem with extension

From
Tomáš Uko
Date:
Hi Tom,

It worked!
XY=# create extension hstore from unpackaged;
CREATE EXTENSION
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)

Thanks for help.

T.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, September 22, 2016 1:37 PM
To: Tomáš Uko <uko@avast.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Problem with extension

=?UTF-8?B?VG9tw6HFoSBVa28=?= <uko@avast.com> writes:
> 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

Perhaps what you've got there is an hstore installation from before
extensions existed?  Try "create extension hstore from unpackaged"
to update 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;

When you don't know what you are doing, trying to fix it with manual surgery
on the system catalogs is a good way to make things worse.

            regards, tom lane