Re: Problem with extension
От | Tomáš Uko |
---|---|
Тема | Re: Problem with extension |
Дата | |
Msg-id | 05172a3859cb6805b368084dfd015dbc@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Problem with extension (Payal Singh <payal@omniti.com>) |
Ответы |
Re: Problem with extension
(Tomáš Uko <uko@avast.com>)
|
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления:
Следующее
От: David DeMannДата:
Сообщение: Streaming basebackup initialization does not capture pg_xlog transfer on replica.