Thanks for the reply Tom. template1 is definitely empty and does not contain
any hstore objects. I did a little debugging and placed the below SQL before
and after the hstore creation in the file produced by the pg_dump and
determined that these operator objects only become present immediately after
the creation of the hstore extension, and not before. Then, much later down the
pg_dump file, it attempts to create the operator family for these 4 items
producing the errors. I did a pg_dump of the same database on a 9.1 instance
and it does not produce the operator creation objects SQL outside of the
extension. This seems to be something that happened as part of the pg_upgrade.
Any idea why these have showed up outside the extension? Is there anything I
can do to fix this? Thanks for all of your help.
Mike
SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid and opf.opfname like '%hstore%'
ORDER BY index_method, opfamily_name, opfamily_operator;
-----Original Message-----
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Wednesday, April 06, 2016 7:01 PM
To: Feld, Michael (IMS) <fe...@imsweb.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator
"Feld, Michael (IMS)" <fe...@imsweb.com> writes:
> Thanks for the assist Tom. That worked for us. Noticing a different
> issue following the pg_upgrade. If we take a pg_dump of a database on
> this upgraded instance with the hstore extension and try to pg_restore
> it back up to the same instance we get the following errors
Those are the *only* errors you get? That seems rather odd. I could believe
something like this happening if, say, you had an "unpackaged"
(that is, pre-extensions) version of hstore lying about. But then you'd
probably get conflicts on all the hstore-related objects, not only the
opclasses.
In any case, by far the most likely explanation is that you're trying to
restore into a non-empty database, probably because you've put stuff into
template1 and are cloning the new database from there.
regards, tom lane
________________________________
Information in this e-mail may be confidential. It is intended only for the
addressee(s) identified above. If you are not the addressee(s), or an employee
or agent of the addressee(s), please note that any dissemination, distribution,
or copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender of the error.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
