<div dir="ltr">Alternatively you can play with sed to patch the dump and get another schema name:<div><br></div><div><span style="background-color:rgb(255,255,255);color:rgb(0,0,0);font-family:monospace">pg_dump ... |sed s/postgis./public./g | psql </span></div><span style="font-family:monospace"><br></span></div><br><div class="gmail_quote"><div dir="ltr">чт, 9 нояб. 2017 г. в 4:25, Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Okay I just tested installing postgis in public schema. Then creating a whole bunch of foreign tables with ogr_fdw and confirmed the geometries showed as public.geometry and confirmed I could query with ST_SetSRID and ST_Transform.<br>
<br>
Then I moved postgis to postgis schema, making sure to add postgis to the database search_path first.<br>
Opened a new database connection as was able to open up my ogr tables fine.<br>
<br>
Now if I remove postgis schema from database search path and reconnect, then my database crashes if I try to query an ogr_fdw table.<br>
<br>
-- so basic steps<br>
<br>
CREATE EXTENSION postgis;<br>
<br>
CREATE SERVER svr_shp FOREIGN DATA WRAPPER ogr_fdw<br>
OPTIONS (datasource 'C:/fdw_data/massgis/shps',<br>
format 'ESRI Shapefile'<br>
);<br>
CREATE SCHEMA shps;<br>
<br>
IMPORT FOREIGN SCHEMA ogr_all<br>
FROM SERVER svr_shp INTO shps;<br>
<br>
-- then disconnect from database and reopen.<br>
-- this works fine<br>
SELECT ST_Transform(geom, 4269) from shps.towns_poly limit 10;<br>
<br>
CREATE SCHEMA postgis;<br>
<br>
ALTER DATABASE test_db<br>
SET search_path = public, postgis;<br>
<br>
ALTER EXTENSION postgis<br>
SET SCHEMA postgis;<br>
<br>
ALTER EXTENSION postgis<br>
UPDATE TO "2.4.1next";<br>
<br>
ALTER EXTENSION postgis<br>
UPDATE TO "2.4.1";<br>
<br>
-- disconnect and make new connection<br>
-- if you are using pgAdmin, make sure to refresh as pgAdmin will show a cached structure from before.<br>
-- inspected table shps.towns_poly using pgAdmin and confirmed geometry column now reads postgis.geometry<br>
-- ran query<br>
<br>
-- then disconnect from database and reopen.<br>
-- this works fine<br>
SELECT ST_Transform(geom, 4269) from shps.towns_poly limit 10;<br>
<br>
<br>
-- now if I remove postgis from search_path by either removing it from my database search_path and reconnecting.<br>
-- or doing<br>
<br>
set search_path=public;<br>
<br>
-- returns true -- all nulls - aka bad<br>
SELECT postgis.ST_Transform(geom, 4269) is null from shps.towns_poly limit 10;<br>
<br>
-- this one is a bit weird in that it still requires me to disconnect and reconnect before trying, as there still seems to be a shared read hook on table if I try to reuse the same connection. Without disconnecting I sometimes get invalid transform (all nulls back) and a crash in one case.<br>
<br>
set search_path=public,postgis;<br>
<br>
-- returns false -- no nulls aka good<br>
SELECT postgis.ST_Transform(geom, 4269) is null from shps.towns_poly limit 10;<br>
<br>
Anyway I am able to repeat the above to switch postgis back to public and all seems fine as far as I can tell.<br>
<br>
<br>
<br>
<br>
<br>
-----Original Message-----<br>
From: Regina Obe [mailto:<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>]<br>
Sent: Wednesday, November 08, 2017 7:52 PM<br>
To: 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
Subject: RE: [postgis-users] Importing relations from database where postgis extension was created with a different schema<br>
<br>
You can move it back I've done that before.<br>
<br>
Though I'm puzzled how this broke ogr_fdw. I do recall some code in ogr_fdw that looks for where postgis is installed.<br>
<br>
It looks for it in search_path<br>
<br>
<br>
<a href="https://github.com/pramsey/pgsql-ogr-fdw/blob/07238c75d8bee08acb14619c413495a6965405d6/ogr_fdw.c#L187" rel="noreferrer" target="_blank">https://github.com/pramsey/pgsql-ogr-fdw/blob/07238c75d8bee08acb14619c413495a6965405d6/ogr_fdw.c#L187</a><br>
<br>
Are you sure you have the schema that postgis resides in in your database search_path?<br>
<br>
<br>
The error might be a temporary one caused by open connections during the move. The issue should resolve if you kill all connections or restart the database service.<br>
<br>
I'll try to replicate the issue on my end. When did you get the proj error below? Was it during querying a foreign table or a physical one?<br>
<br>
<br>
<br>
<br>
Thanks,<br>
Regina<br>
<br>
<br>
<br>
<br>
-----Original Message-----<br>
From: postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of Andrew Joseph<br>
Sent: Wednesday, November 08, 2017 2:12 PM<br>
To: <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
Subject: Re: [postgis-users] Importing relations from database where postgis extension was created with a different schema<br>
<br>
I just learned that the hard way: all my ogr_fdw foreign tables broke after the relocation. I attempted moving the extension back to the public schema<br>
using:<br>
<br>
UPDATE pg_extension<br>
SET extrelocatable = TRUE<br>
WHERE extname = 'postgis';<br>
<br>
ALTER EXTENSION postgis<br>
SET SCHEMA public;<br>
<br>
ALTER EXTENSION postgis UPDATE TO "2.4.0devnext";<br>
<br>
ALTER EXTENSION postgis UPDATE TO "2.4.0dev";<br>
<br>
UPDATE pg_extension<br>
SET extrelocatable = FALSE<br>
WHERE extname = 'postgis';<br>
<br>
However, it appears this doesn't actually move the relations back to the proper schema, and also seems to have corrupted proj4 in a way that persists across databases:<br>
<br>
[2017-11-08 13:04:00] [XX000] ERROR: AddToPROJ4SRSCache: could not parse<br>
proj4 string '+proj=lcc +lat_1=31.88333333333333 +lat_2=30.11666666666667<br>
+lat_0=29.66666666666667 +lon_0=-100.3333333333333<br>
++x_0=699999.9998983998<br>
+y_0=3000000 +datum=NAD83 +units=us-ft +no_defs ' unknown elliptical<br>
parameter name<br>
<br>
Is it safe to assume moving the schema using the methods you outlined is a one-time only operation (i.e you can't move it back after importing the relevant data)?<br>
<br>
<br>
<br>
<br>
--<br>
Sent from: <a href="http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html" rel="noreferrer" target="_blank">http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>