Re: pg_upgrade error regarding hstore operator - Mailing list pgsql-general

Thanks for the assist Tom. That worked for us. Noticing a different issue following the pg_upgrade. If we take a
pg_dumpof a database on this upgraded instance with the hstore extension and try to pg_restore it back up to the same
instancewe get the following errors (ignore the likeness to your name, apparently when this cluster was created years
agothey chose to set it up in your honor): 

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3879; 2753 745119 OPERATOR FAMILY btree_        hstore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family "btree_hstore_ops" for access method
"btree"already exists 
    Command was: CREATE OPERATOR FAMILY btree_hstore_ops USING btree;

pg_restore: [archiver (db)] Error from TOC entry 3880; 2753 745127 OPERATOR FAMILY gin_hs        tore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family "gin_hstore_ops" for access method "gin"
alreadyexists 
    Command was: CREATE OPERATOR FAMILY gin_hstore_ops USING gin;

pg_restore: [archiver (db)] Error from TOC entry 3881; 2753 745137 OPERATOR FAMILY gist_h        store_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family "gist_hstore_ops" for access method "gist"
alreadyexists 
    Command was: CREATE OPERATOR FAMILY gist_hstore_ops USING gist;

pg_restore: [archiver (db)] Error from TOC entry 3882; 2753 745151 OPERATOR FAMILY hash_h        store_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR:  operator family "hash_hstore_ops" for access method "hash"
alreadyexists 
    Command was: CREATE OPERATOR FAMILY hash_hstore_ops USING hash;

We do not have this issue for any new databases created following the pg_upgrade. I noticed that new databases have the
hstore1.3 version while the originals still have the 1.1 version. I updated the extension on one of these to see if
thatwould resolve the issue and it did not. Thanks for any help you can offer. 

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, March 08, 2016 6:22 PM
To: Feld, Michael (IMS) <FeldM@imsweb.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <FeldM@imsweb.com> writes:
> I am attempting to upgrade my organization's database cluster from
> 9.1.19 to 9.5.1 using the pg_upgrade utility.

That's kind of a big jump :-( ... you missed the versions where => was deprecated as an operator name.

> I tried dropping the operator before doing the upgrade but it's dependent on the existence of the hstore extension.
Ideas?

The clean solution would be to copy share/extension/hstore--1.0--1.1.sql
from the 9.5 installation into the 9.1 installation and then do

ALTER EXTENSION hstore UPDATE TO '1.1';

Under the hood that's just doing

ALTER EXTENSION hstore DROP OPERATOR => (text, text); DROP OPERATOR => (text, text);

but if you did that manually, you'd have a problem when you want to update hstore to current versions later.  If you do
whatI suggest, the extension will properly look like it's 1.1 after pg_upgrade'ing. 

regards, tom lane

________________________________

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are
notthe addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or
copyingof this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error.