PostgreSQL Foreign Data Wrapper (MSSQL)

Question

I'm trying to do some slow migration from a MS SQL Server 2008 R2 to a PostgreSQL 9.5 + PostGIS.

I created a FDW in PostgreSQL to access my MSSQL data and I imported most of my tables, but a few didn't make It.

Here is an exemple:

CREATE FOREIGN TABLE ms_table_name(
col1 varchar (64), col2 date, geometry geometry
) SERVER mssql_db_server OPTIONS (query 'SELECT col1, col2, cast(geometry.STAsText() as varchar(max)) "geometry" FROM ms_table_name', 
row_estimate_method 'showplan_all');
 CREATE TABLE pg_table_name AS (select * from ms_table_name);
 ALTER TABLE pg_table_name ADD oid serial PRIMARY KEY;
 CREATE INDEX pg_table_name_gix ON pg_table_name USING GIST (GEOMETRY);

I can't use binary because it returns two invalid characters, that's why I need to convert it to text.

As I said, it works for most geometries but for long/big geometries that have a large number of coordinates, it gets truncated.

ERRO: parse error - invalid geometry SQL state: XX000 Hint: "...65032851836 8268588.7680116147, 6129" <-- parse error at position 64513 within geometry

This geometry has 138280 chars, I confirmed all chars are being sent from MSSQL but PosgreSQL only reads 64512. I tried varchar, text, but still I get this 64512 char limit!

How do I work around it?

• John Powell: If you can dump from SQL Server to txt and then load that using LOAD DATA ... etc and finally convert your text column to geom using ST_GeomFromText, you will likely succeed. Given that you have to convert from text to geom anyway, you aren't adding any extra steps this way.
• A. Jesus: Unfortunately I don't have access and the DBA doesn't look like is going to give me access any time soon... I'm able to send the table from MSSQL to PGSQL using QGIS (simple drag and drop(!)) but I would like to have something more dynamic and autonomous

Answer 1

Maybe use an FDW with explicit support for spatial? OGR FDW does what you need, and there is a windows build.

WRT the specifics of your question, the maximum value of 64512 is suspiciously close to the maximum size of a short, 65536. Perhaps you're hitting a max string length limit somewhere in the stack.

• A. Jesus: I did tried OGR_FDW but somewhat It is worse than TDS_FDW. CREATE SERVER ogr_mssql_server FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource 'ODBC:user/password@mssql_server',format 'ODBC'); ALTER SERVER ogr_mssql_server OWNER TO postgres; and the table: CREATE FOREIGN TABLE ogr_ms_table (id int,geom geometry) SERVER ogr_mssql_server OPTIONS ( layer 'dbo.ms_table' ) I can access the non-spatial data, but no spatial data is sent. I tried using bytea and ST_GeomFromWKB(geometry) but the coordinates get all messed up