Specify schema in called functions for pg_dump / pg_restore

[zstatmanweil]

Let me know if you have another recommendation for backing up and restoring the database, but I attempted to use pg_dump and pg_restore and I get this error:

CONTEXT:  SQL function "stac_geom" during inlining
....
pg_restore: error: COPY failed for table "items_p2021w37": ERROR:  function st_geomfromgeojson(text) does not exist
LINE 5:                 ST_GeomFromGeoJSON(value->>'geometry')
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  
SELECT
    CASE
            WHEN value->>'geometry' IS NOT NULL THEN
                ST_GeomFromGeoJSON(value->>'geometry')
            WHEN value->>'bbox' IS NOT NULL THEN
                ST_MakeEnvelope(
                    (value->'bbox'->>0)::float,
                    (value->'bbox'->>1)::float,
                    (value->'bbox'->>2)::float,
                    (value->'bbox'->>3)::float,
                    4326
                )
            ELSE NULL
        END as geometry
;

This is happening because pg_dump explicitly sets the search_path to empty so custom functions (e.g., from the extension or pgstac's function) without specific schemas set aren't found. I was able to fix this specific error but I adding "public" in front of the ST_GeomFromGeoJSON function but there were many more errors like this.

If functions called in the pgstac functions had explicit schemas set, I think it would solve the issue and pg_dump and pg_restore could be used for backups.

[bitner]

Hmm, I'm not entirely sure what the best course of action for this is. It is not uncommon for people to use the PostgreSQL extension options to install PostGIS to schemas other than public, so I'm not sure it would be good to hard code that to public (at least I know one client of ours where that would definitely break things). I wonder if you set the environment variable PGOPTIONS='-c search_path=public' prior to running the pg_restore if that would work?

[zstatmanweil]

That makes sense. Thanks for the response and brainstorming. I just gave it a shot, and I get the same error as before. I think since pg_dump explicitly sets the search_path to empty right up top with SELECT pg_catalog.set_config('search_path', '', false); that may override the env variable. It does it for some security reason.

I also don't have another idea! This is more a "nice to have". The workaround I have used is creating a database with pypgstac.migrate, exporting item data as ndjson and then bulk loading it back in.