Skip site navigation (1) Skip section navigation (2)

How to upgrade postgres 8.4 -> 9.1 contrib?

From: Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to upgrade postgres 8.4 -> 9.1 contrib?
Date: 2012-11-19 10:56:14
Message-ID: CADbMkNM-Bcz8AZ3XCh8JrumZ4Wp8FbW3GR8uOuddnk-oJTCD-w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hello,

We a little while ago upgraded our databases from 8.4 to 9.1. We
upgraded using pg_upgrade but didn't do anything special for
extensions (other than that the server had the contrib rpm installed).
Everything works just fine but recently we noticed that a lot of the
functions from hstore are no longer available.  Interestingly enough
the type is still there as are the columns of that type and the basic
operators also still work:

proddb=> select hstore 'a => b' -> 'a';
 ?column?
----------
 b
(1 row)

However:

proddb=> select hstore(text 'a', text 'b');
ERROR:  function hstore(text, text) does not exist
LINE 1: select hstore(text 'a', text 'b');
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

More reading of the docs revealed this:

E.7.2.7. Contrib

    All contrib modules are now installed with CREATE EXTENSION rather
than by manually invoking their SQL scripts (Dimitri Fontaine, Tom
Lane)

    To update an existing database containing the 9.0 version of a
contrib module, use CREATE EXTENSION ... FROM unpackaged to wrap the
existing contrib module's objects into an extension. When updating
from a pre-9.0 version, drop the contrib module's objects using its
old uninstall script, then use CREATE EXTENSION.

It is unclear what to do.  Clearly 8.4 < 9.0 but if we were to run the
old uninstall script this would DROP all columns of hstore type with
disastrous consequences for us. I would argue that the above comment
should warn about that!

What is the official guide line?

Thanks,

Bene


Responses

pgsql-general by date

Next:From: Yvon ThoravalDate: 2012-11-19 12:08:21
Subject: remote connection refused
Previous:From: Devrim GÜNDÜZDate: 2012-11-19 10:55:27
Subject: Re: Fuzzystrmatch contrib module on RHEL63

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group