ogr_fdw : foreign data wrapper
I discovered this by reading the Postgres Conference Slides. I am writing this down so I dont forget, as is the case for almost everything I put here.
I was reading the presentation by Regina Obe, co-author of several wonderful books on PostGIS, Postgres, and more.
You can find links to their presentation at Planet Postgis entry for March 25, 2019, or at Postresql Online Journal entry PGCONF 2019 data slides up.
Direct link to the PDF slides look under the section Other Relational Databases, but the rest is interesting as well.
My use case here is Windows PostGIS to Windows SQL Server over an enterprise domain using is an ODBC connection.
First I need to set up a Windows DSN entry for the ODBC connection. Open the ODBC Data Source, 64 Bit in my case, and add the system data source. When the connection attempt is made, it will use this to find the remote server on the domain.
My connection looks like this, with pguser and pguser as database name.
CREATE SERVER svr_sqlserver FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (datasource 'ODBC:pguser/pguser@dnsentryname',format'ODBC');
CREATE SCHEMA IF NOT EXISTS ss;
IMPORT FOREIGN SCHEMA "dbo" FROM SERVER svr_sqlserver INTO ss
I do notice I am not getting any Spatial Data in my Foreign Tables. This is somewhat expected as SQL server does not natively send spatial data accross linked servers. However, the presentation says it should so I might need to revisit this.