Open-source SQL Server to Postgres Converters

Database migration between advanced DBMS is non-trivial task that requires a lot of time and efforts. This article explores different tools for database migration from SQL Server or Azure SQL to PostgreSQL.

First open-source solution is pgloader, a well-known tool that migrates schemas, data, indexes, and foreign keys from SQL Server into PostgreSQL.

Features:

loads data from various sources like MS SQL, SQLite, MySQL, CSV
pgloader is distributed as open-source software under free license
pgloader is a cross-platform software as it is a set of LISP scripts
docker image allows to run database migration in container
SQL Server views are migrated as regular tables
pgloader uses the FreeTDS driver to access SQL Server databases and expects the data is sent in UTF-8 encoding

Simplest example of running SQL Server to Postgres migration via pgloader is illustrated by the following command line:

$ pgloader mssql://mssql_user@mssql_host/mssql_dbname pgsql://pgs_user@pgs_host/pgs_dbname

where mssql_user, mssql_host, mssql_dbname, pgs_user, pgs_host, pgs_dbname must be replaced by actual SQL Server and PostgreSQL username, host and database name.

Sqlserver2pgsql

Next open-source solution to automate database migration from Microsoft SQL Server to Postgres is Sqlserver2pgsql script written in Perl.

Features:

converts SQL Server schemas into PostgreSQL schemas
generates Pentaho Data Integrator (Kettle) job to migrate SQL Server data to PostgreSQL
generates incremental version of the job specified above to migrate changes of the database since the last run

Although Sqlserver2pgsql is a cross-platform tool, it is recommended to run it inside a Docker container to simplify distribution of the required environment. So, here are the key statements for building proper container for SQL Server to Postgres migration using Sqlserver2pgsql:

Build image based on OpenJDK 8 to run Java apps
```
FROM adoptopenjdk/openjdk8
```

Define necessary environmental variables for connection to SQL Server and Postgres databases (SRC_HOST, SRC_POSRT, SRC_USER, SRC_PWD, SRC_DB, DEST_HOST, DEST_PORT, DEST_USER, DEST_PWD, DEST_DB)

Set up the directory for migration

ENV MIGRATIONDIR=/opt/mss2pgs_migration
RUN mkdir -p $MIGRATIONDIR

Install necessary client tools

RUN apt-get update; apt-get install perl netcat -y; \
apt-get install wget unzip postgresql-client -y

Install Kettle used to launch data migration job

RUN wget --progress=dot:giga https://sourceforge.net/projects/pentaho/files/latest/download?source=files -O /tmp/kettle.zip; \
unzip /tmp/kettle.zip -d /tmp/kettle; \
mv /tmp/kettle/data-integration $MIGRATIONDIR; \
chmod -R +x $MIGRATIONDIR/data-integration/*.sh

Install jTDS JDBC Driver to access SQL Server from the Kettle's job

RUN wget https://sourceforge.net/projects/jtds/files/latest/download?source=files -O /tmp/jtds.zip; \
unzip /tmp/jtds.zip -d /tmp/jtds; \
cp /tmp/jtds/jtds-*.jar $MIGRATIONDIR/data-integration/lib/; \
rm -Rf /tmp/jtds;rm -f /tmp/jtds.zip

Set up shell script to run the Kettle job

COPY ./scripts /scripts
RUN chmod +x /scripts/*.sh
WORKDIR $MIGRATIONDIR

Create SQL Server to Postgres schema migration scripts
```
./sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k kettlejobs -stringtype_unspecified -f conf/$SCHEMA_FILE \  
-sh $SRC_HOST -sp $SRC_PORT -su $SRC_USER -sw $SRC_PWD -sd $SRC_DB \
-ph $DEST_HOST -pp $DEST_PORT -pu $DEST_USER -pw $DEST_PWD -pd $DEST_DB
```
This statement generates script before.sql that creates all schemas (it must be run first), after.sql that creates indexes and constraints (it must be run after the data migration completes) and Kettles job file at $MIGRATIONDIR/kettlejobs/migration.kjb.

Run before.sql to create the table structure. On this step you can edit before.sql script to customize type mapping with respect to rules of SQL Server to Postgres safe type conversion.
```
PGPASSWORD=$DEST_PWD psql -h $DEST_HOST -p $DEST_PORT -U $DEST_USER -d $DEST_DB -f before.sql
```

Run the generated Kettle job to migrate the data

data-integration/kitchen.sh -file=kettlejobs/migration.kjb -level=rowlevel

Run after.sql script to create indexes and constraints

PGPASSWORD=$DST_PWD psql -h $DST_HOST -p $DST_PORT -U $DST_USER -d $DST_DB -f after.sql

Build the image of Docker container for SQL Server to Postgres database migration
```
docker build -t sqlserver2psql .
```

Now you can run the migration through the following command line statement:

docker run --name sqlserver2psql --rm -e SRC_HOST= \
  -e SRC_USER= -e SRC_PWD="" \
  -e SRC_DB= -e DEST_HOST= \
  -e DEST_PORT=5432 -e DSET_USER= \
  -e DEST_PWD= -e DEST_DB=""
  --mount type=bind,source="$(pwd)"/conf,target=/opt/data_migration/conf \
  sqlserver2psql /scripts/migrate.sh

Both of these tools do not migrate views (as CREATE VIEW statements), stored procedures, triggers and have no advanced capabilities on customization of metadata and migration rules. Besides, pgloader and Sqlserver2pgsql are only available in form of console tools that may be not comfortable for users not so familiar with command line.

For those who look for easy way of running SQL Server to Postgres migration or need to deeply customize the process, the most reasonable option is dedicated commercial software with intuitive graphical interface. One of those tools is SQL Server to PostgreSQL converter.