Thread: How to upgrade postgres 8.4 -> 9.1 contrib?

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

Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> What is the official guide line?

You could try (1) run the 9.0 version of the hstore install script
and then (2) do the CREATE EXTENSION FROM UNPACKAGED bit.  I'd
strongly recommend testing this procedure in a scratch copy of your
installation first, though.

In a quick look through the diffs between the 8.4 and 9.0 hstore
install scripts, it looks like this should mostly work, except that
9.0 has some new operators added to the GIST and GIN index opclasses.
If you care about whether those are indexable, you would need to
drop those opclasses (and any dependent indexes) before running
the 9.0 install script, and then recreate the indexes after.

            regards, tom lane

On Mon, Nov 19, 2012 at 3:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Benedikt Grundmann <bgrundmann@janestreet.com> writes:
>> What is the official guide line?
>
> You could try (1) run the 9.0 version of the hstore install script
> and then (2) do the CREATE EXTENSION FROM UNPACKAGED bit.  I'd
> strongly recommend testing this procedure in a scratch copy of your
> installation first, though.
>
Thanks Tom.  Due to <complicated-not-relevant-reasons> it might take a
while for us to do so but if we do try this out I'll let the list know
if it worked.

Cheers,

Bene