Hi Raúl Marín Rodríguez,
Thank you for your answer in this topic. You are right, I actually checked
the content of the backup file and saw that pg_dump set the search_path to
empty, that explains why it was working when I tried to run the query
manually but not during the restoration.
SELECT pg_catalog. set_config('search_path', '', false);
After discovering that, I changed the function from "geog::geometry" to
"geog::public.geometry" and it works.
Best regards
On Thu, Jul 2, 2020 at 7:04 PM <rmrodrig...@carto.com> wrote:
> I don't remember the exact details, but restoring functions require
> extension types to be fully qualified.
>
> Can you try changing the definition of the _togeom function from
> "geog::geometry" to "geog::public.geometry"?
>
> On Thu, Jul 2, 2020 at 6:36 PM pham lan <phamla...@gmail.com> wrote:
>
>>
>> Hello all,
>>
>> I would like to ask for your help in understanding a postgres issue that
>> I could not find out till now.
>> 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:
>>
>> $ 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
>>
>> I already looked and tried the comment on similar issue in the following
>> links but it does not help:
>> https://stackoverflow.com/questions/6850500/postgis-installation-type-geometry-does-not-exist
>>
>>
>> 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));
>>
>> 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.
>>
>> Any idea from you is highly appreciated! Thanks.
>> [image: image.png]
>>
>>
>> Best regards,
>> Lan Pham
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> --
> Raúl Marín Rodríguez
> carto.com
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
