Re: pg_restore: [archiver (db)] could not execute query: ERROR: operator does not exist: public.hstore = public.hstore

Cherio <cherio(at)gmail(dot)com> writes:
> I am exporting DB from 10.6 and importing into 10.7 and an UPDATE rule
> fails to restore:
> pg_restore: [archiver (db)] could not execute query: ERROR: operator does
> not exist: public.hstore = public.hstore

> From the log I can see that hstore was extension successfully created

It's probably not in the search_path that pg_restore was using.

This is one of the hard-to-fix consequences of the decision to band-aid
over CVE-2018-1058 by having pg_restore run with a minimal search_path.
pg_dump can't forestall the problem by schema-qualifying the operator
name, because there is no explicit operator name in IS DISTINCT FROM.

I complained at the time that there needed to be a way to relax the
restriction, but I lost the argument.

AFAIK the only workaround that exists at the moment is to hand-edit
the dump script to change the forced search_path setting to include
whereever you put hstore (and any other extensions you have similar
issues with).

There's a previous discussion here:

https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com

but it seems like nobody's done any work on it since then.

regards, tom lane