remotedb.sql

-- create required extensions if they don't already exist
create extension if not exists postgis;
create extension if not exists postgres_fdw;

--drop remote server if exists
drop server if exists sde_host;

--create postgres_fdw server
create server sde_host foreign data wrapper postgres_fdw options (host 'slibby10.esri.com', dbname 'db_pro_create', port '5432');

--drop user mapping to avoid conflicts
drop user mapping if exists for postgres server sde_host;

--create user mapping
create user mapping for postgres server sde_host options (user 'postgres', password 'postgres');

-- create a new schema to hold our foreign tables (I prefer this, not entirely necessary)
create schema if not exists sde_connector;

-- import the schema from SDE database
import foreign schema connector from server sde_host into sde_connector;

--create view in your schema that generates a PostGIS geometry from the WKT text
create or replace view sde_connector.remote_locations_view_pg_geom as select *, public.st_geomfromtext(geom_wkt) as shape from sde_connector.remote_locations_view;

-- create materialized view to make sure all the data is loaded to your new db rather than dynamically querying
create materialized view sde_connector_remote_locations_view_pg_geom_m as select * from sde_connector.remote_locations_view_pg_geom;

select geom_wkt as geom_wkt_from_sde, st_geometrytype(shape) as geom_type_in_postgis, shape as postgis_geometry, st_astext(shape) as geom_wkt_from_postgis from  sde_connector_remote_locations_view_pg_geom_m;

sde.sql

-- create new schema to hold our views
create schema if not exists connector;

-- Create a view of my point FC (sde.locations) that extracts Esri Geometry to WKT
create or replace view connector.locations_wkt as 
	select objectid, sde.ST_AsText(shape)::varchar as geom_wkt from sde.locations;

-- Done in SDE!

run sde.sql-like commands on your SDE database

run remotedb.sql commands on your postgis database.