<div dir="ltr"><br><div class="gmail_quote"><div dir="ltr">
<div><div>Hello all,<br></div></div>
<div>
<p>I would like to ask for your help in understanding a postgres issue that I could not find out till now.
<br>So I have a postgres dump file of 159G created by pg_dump that I want
to restore to my database. It is running well till it throws me the
following error:</p>
<pre><code>$ psql -a -U postgres -d mydb < /opt/postgres_restore_files/db_mydb_2020-06-11_18-00.dmp
.....
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR: type "geometry" does not exist
LINE 1: SELECT geog::geometry
^
QUERY: SELECT geog::geometry
CONTEXT: PL/pgSQL function public._togeom(public.geography) line 3 at RETURN
STATEMENT: CREATE INDEX my_index ON mydb.geo_zone USING gist (public._togeom(geom));
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
<br>
I already looked and tried the comment on similar issue in the following links but it does not help:<br><a href="https://stackoverflow.com/questions/6850500/postgis-installation-type-geometry-does-not-exist" target="_blank">https://stackoverflow.com/questions/6850500/postgis-installation-type-geometry-does-not-exist</a><br>
<br> Below are the definitions:
CREATE FUNCTION public._togeom(geog public.geography) RETURNS public.geometry
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
RETURN geog::geometry;
END;
$$;
ALTER FUNCTION public._togeom(geog public.geography) OWNER TO postgres;
CREATE TABLE mydb.geo_zone (
...
geom public.geography,
...
);
ALTER TABLE mydb.geo_zone OWNER TO my_user;
....
CREATE INDEX my_index ON mydb.geo_zone USING gist (public._togeom(geom));
</code></pre>
<p>I can see type geometry exists in public schema after creating
extension postgis in mydb, not sure why it throws that error during the restore
procedure. Also I tried afterward to run the CREATE INDEX command again
manually and it works without any error and I can see the index
"my_index" created. But of course i don't want this manual fix, just
try to understand what causes the issue.</p>
<p>Any idea from you is highly appreciated!
Thanks.</p>
</div>
<div><img src="cid:ii_kbmr9ehu0" alt="image.png" width="542" height="246"></div><div><br><br></div><div>Best regards,</div><div>Lan Pham</div>
</div>
</div></div>