stack_240718.sql

drop database remote;
drop database localdb;

CREATE DATABASE remote
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    TEMPLATE template0;

CREATE DATABASE localdb
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    TEMPLATE template0;

\c remote

CREATE EXTENSION postgis;
CREATE EXTENSION postgres_fdw;

CREATE SCHEMA remote;

CREATE TABLE remote.locations
(
    id bigserial NOT NULL UNIQUE,
    name character varying,
    lonlat geometry NOT NULL,
    PRIMARY KEY (id)
) 
WITH (
    OIDS = FALSE
);
CREATE INDEX remote_locations_lonlat_idx
    ON remote.locations USING spgist
    (lonlat);

\c localdb

CREATE EXTENSION postgis;
CREATE EXTENSION postgres_fdw;

CREATE SCHEMA localdb;

CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'remote', port '5432', extensions 'postgis');
CREATE USER MAPPING FOR postgres SERVER remote OPTIONS (user 'postgres');

IMPORT FOREIGN SCHEMA remote FROM SERVER remote INTO localdb OPTIONS (import_not_null 'true');

SELECT * from localdb.locations;

--ERROR:  cache lookup failed for type 0
--CONTEXT:  remote SQL command: SELECT id, name, lonlat FROM remote.locations
--SQL state: XX000