I am looking to migrate data from SQL Server to Postgres.
I have been able to get a dump file from my Postgres database and want to restore it to SQL Server 2012.
What are the best practices for doing such a task?
If you cannot simple restore the Postgres dump file to SQL Server is there a better way of transferring data from Postgres to SQL Server?
I would look at using GDAL.
A script similar to the below will connect to PostgreSQL and transfer the support.support table from SQL server, this one is also re-projecting the data into ESPG:27700.
ogr2ogr -overwrite -update -f "PostgreSQL" PG:"host=127.0.0.1 port=5432 dbname=databasename user=postgres password=*******" -f MSSQLSpatial "MSSQL:server=servername;database=databasename;UID=support;PWD=*****" support.support -lco OVERWRITE=yes -lco SCHEMA=public -a_srs EPSG:27700 -progress
This script can be easily switched around to transfer from Postgres to SQL Server:
ogr2ogr -overwrite -update -f MSSQLSpatial "MSSQL:server=servername;database=databasename;UID=username;PWD=********" -f "PostgreSQL" PG:"host=127.0.0.1 port=5432 dbname=databasenaem user=postgres password=*******" public.support -lco OVERWRITE=yes -lco SCHEMA=public -a_srs EPSG:27700 -progress
My favourite guide to installing GDAL is https://sandbox.idre.ucla.edu/sandbox/tutorials/installing-gdal-for-windows if you have not used it before.
There are other ways such as using python that I am not to familiar with but I would like to know more about too.