Migrating from Sql Server to PostgreSQL

 

Looking for Sql Server alternatives with comparable features and performance (but less costly), our preference went to PostgreSQL, mostly for its merge capabilities.
PostgreSQL is free, open-source and can be hosted on Linux or Windows. For Windows, the official site offers two download options from which we chose EnterpriseDB. The installation passed without problems.

Below is our experience with the EDB Migration Toolkit . You can read the documentation online or in PDF. We used it to move some large SQL Server tables to PostgreSQL.

The migration software was installed thru StackBuilder, a tool included in the PostgreSql installation, see 4.3 in the documentation above.

First thing to do was to customize the configuration file C:\Program Files (x86)\edb\mtk\etc\toolkit.properties. Our settings were:

    SRC_DB_URL=jdbc:jtds:sqlserver://127.0.0.1:1433/ssDBname
    SRC_DB_USER=user1
    SRC_DB_PASSWORD=xxxx

    TARGET_DB_URL=jdbc:postgresql://localhost:5432/postgres
    TARGET_DB_USER=postgres
    TARGET_DB_PASSWORD=zzzz

Then, to start the migration, we ran the following two commands in a Command Prompt window:

cd "C:\Program Files (x86)\edb\mtk\bin"  
runMTK.bat -sourcedbtype sqlserver -targetdbtype postgres SSschema

And got the following error:

MTK-11009: Error Connecting Database "SQL Server"
java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Driver

The Sql Server driver was missing. So we got it following these two links https://www.enterprisedb.com/advanced-downloads https://sourceforge.net/projects/jtds/files/latest/download?source=files
Then unzipped file jtds-1.3.1-dist.zip in folder C:\Program Files (x86)\edb\mtk\lib

Trying again got us another error:

Connecting with target Postgres database server...
Exception in thread "main" java.lang.NoClassDefFoundError: org/postgresql/Driver
com.edb.dbhandler.postgresql.PGConnection.< init>(PGConnection.java:32

Turned out the PostgreSql driver was missing too! Naively we thought that at least this one was included...
Searched and found it here. Link "PostgreSQL JDBC 4.2 Driver" downloaded file postgresql-42.1.1.jar. We moved it to folder C:\Program Files (x86)\edb\mtk\lib, the same where the Sql Server driver was.

A little too late we noticed the page instruction "If you are using Java 8 or newer then you should use the JDBC 4.2 version."
Well, we had already the latest 4.2 driver, but did we have the latest Java environment ? Decided to go ahead and update Java to the current version.

After that runMTK did not even start, coming back with error "Unable to find JRE in path."
Apparently runMTK.bat was referencing another config file C:\Program Files (x86)\edb\mtk\etc\sysconfig\edbmtk-50.config which had this line:

JAVA_EXECUTABLE_PATH="C:\Program Files (x86)\Java\jre1.8.0_111\bin\java.exe"

We updated it to the path to the newly installed JRE:

JAVA_EXECUTABLE_PATH="C:\Program Files (x86)\Java\jre1.8.0_131\bin\java.exe"

 

Finally everything fell into place and the batch migration proceeded as expected.

    >runMTK.bat -sourcedbtype sqlserver -targetdbtype postgres SSschema
    Connecting with source SQL Server database server...
    Connected to Microsoft SQL Server, version '10.50.4042'
    Connecting with target Postgres database server...
    Connected to PostgreSQL, version '9.6.3'
    Importing sql server schema SSschema...
    Creating Schema...SSschema
    Creating Tables...
    ... Table Data Load Summary: Total Time(s): 786 Total Rows: 19078096 Total Size(MB): 1155 ...
    Schema SSschema imported successfully.
    Migration process completed successfully.

Phew, another rollercoaster!

 

Additional Resources