Import Foreign Schema Fails with “schema postgis does not exist”

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Import Foreign Schema Fails with “schema postgis does not exist”

Andrew Joseph

I am connecting to a Postgres 9.4 foreign server instance where Postgis 2.1 was installed using CREATE EXTENSION postgis WITH SCHEMA postgis from a Postgres 9.6 Beta where Postgis 2.3dev was installed with CREATE EXTENSION postgis in the public schema (see gis.stackexchange question: http://gis.stackexchange.com/questions/206098/import-foreign-schema-fails-with-schema-postgis-does-not-exist).

When running:

IMPORT FOREIGN SCHEMA data_schema FROM SERVER f_server INTO data_schema;
I receive the following error:

[2016-08-07 11:23:36] [3F000] ERROR: schema "postgis" does not exist
  Where: importing foreign table "data_table"
[2016-08-07 11:23:36] [25P02]
WHERE data_table is defined as:

CREATE TABLE data_schema.data_table(
  id bigserial PRIMARY KEY,
  geom geometry(MultiPolygon,4326)
);
CREATE INDEX ON data_schema.data_table USING GIST (geom);

I am aware that some postgis functions have schema qualification issues, as demonstrated by this postgis ticket; however that doesn't seem to be the issue I am encountering since I merely have a geometry column and a GIST index.

Tried importing the postgis schema from the foreign server as well, but that errors out with:

IMPORT FOREIGN SCHEMA postgis FROM SERVER f_server INTO postgis
[2016-08-07 11:43:08] [42704] ERROR: type "postgis.geometry" does not exist
  Where: importing foreign table "raster_columns"
Foreign Server Definition:

CREATE SERVER f_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5433', dbname 'f_db');

CREATE USER MAPPING FOR postgres
SERVER f_server
OPTIONS (user 'postgres', password 'password');