5

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.

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');
5

You need to have postgis installed in same schema on your 9.6 as what you are connecting when you use IMPORT FOREIGN SCHEMA.

The reason is internally PostgreSQL IMPORT FOREIGN SCHEMA always schema qualifies column types if they are not in pg_catalog. You could get around the issue I think if you explicitly create the foreign table on your 9.6 side instead of using IMPORT FOREIGN SCHEMA.

So try on your 9.6.

CREATE FOREIGN TABLE data_schema.data_table(
  id bigserial PRIMARY KEY,
  geom geometry(MultiPolygon,4326)
 )   SERVER f_server
 OPTIONS (schema_name 'data_schema', table_name 'data_table');

Related question, is there a reason you didn't install postgis in schema postgis in 9.6 as well? Note that in PostGIS 2.3, I recently committed a change to schema qualify all the calls (I may have missed some) and while PostGIS is not relocatable in PostGIS 2.3, you still have the freedom to install it in any schema you want.

There is a hack to change it if you installed in wrong schema or want to move it to a different schema which I'll describe in an upcoming article once I've tested out the steps.

I should also add the PostgreSQL 9.6 postgres_fdw has a new option where you can list extensions you are using and then it can utilize output from foreign table making it more efficient. This was contributed by King Paul Ramsey of PostGIS fame, so might want to experiment with that as well.

https://www.postgresql.org/docs/9.6/static/postgres-fdw.html

ALTER SERVER f_server OPTIONS (ADD extensions 'postgis');
  • I noticed that postgis was installed in the public schema in CartoDB and wanted to ensure compatibility should the day ever come when it might be possible to hook up a remote postgis instance to CartoDB. In the past I installed using a separate postgis schema, but figured that there are a great many 3rd party tools that won't account for extensions being installed in different schemas, so figured public was the safer location going forward. Would be great if there was a way to specify that the extension location differs between servers. – THX1138 Aug 8 '16 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.