Migrating from SQL Server to PostgreSQL in two ways

As one of the most popular databases, SQL Server is renowned for its ease of installation and configuration, security features including encryption, excellent data recovery capabilities, and many convenient tools.

However, due to a number of limitations, SQL Server is gradually losing its users. SQL Server has a fairly restrictive license and maintenance costs that increase as the size of the database or the number of clients increases. Its maximum size is 10 GB, and the buffer cache is 1 MB. It only works on Windows.

PostgreSQL, a completely free and open-source database, can entice SQL Server users. This database boasts international support and is available on Windows, Mac, Linux, FreeBSD and Solaris. In addition, there are many open source add-ons for it.

I'll start this article by introducing two free tools for migrating from SQL Server to PostgreSQL, then show you step-by-step how to migrate between the two databases, and end with a full-fledged secure backup solution to manage multiple databases at once.

Database Migration Tools:

1. Pgloader

Pgloader is a data loader program that performs migration from MS SQL Server to PostgreSQL. The tool automatically discovers the schema (primary key, foreign key constraints, and index structure) and provides casting rules to convert a SQL Server data type to a PostgreSQL data type.

2. Sqlserver2pgsql

Sqlserver2pgsql is another open source migration tool written in Perl that converts SQL Server schema to PostgreSQL and transfers all SQL Server data to PostgreSQL using the resulting Pentaho Data Integrator (Kettle). However, it does not wrap stored procedures. This tool is best used for offline database migration. You can find it at Github.

It should work on Linux, Windows and any Unix system.

Method 1: Migrate from SQL Server to PostgreSQL using SSIS

  1. Install PostgreSQL ODBC Driver for Windows x86/x64.

  2. In SQL Server Management Studio, right-click on the source database and select Tasks> Export Data.

  3. Select SQL Server Native Clients as a data source and .Net Framework Data Provider for ODBC as a destination driver.

  4. Set the database connection string. Add a data source name that matches the value specified for Driver= {name-of-your-data-source} for the PostgreSQL driver.

For 32-bit system

Driver={PostgreSQL UNICODE};Server=;Port=;Database=yourDBname;Uid=postgres;Pwd=admin;

For 64-bit system

Driver={PostgreSQL UNICODE (x64) };Server=;Port=;Database=yourDBname;Uid=postgres;Pwd=admin;

  1. Select source tables. You can also edit the default SQL Server mapping.

Method 2: Migrate from SQL Server to PostgreSQL using Sqlserver2pgsql

What do we need:

  • SQL Server connection information, including IP, port, username, password, database and instance names (if not set by default).

  • Access to an empty PostgreSQL target database.

Creating a Docker image

Docker is a great tool for creating a database migration routine that is cloud independent and can be applied to most IT architectures.

We create an image on OpenJDK 8, which allows you to create and run Java 1.8 applications.

FROM adoptopenjdk/openjdk8

We define a number of environment variables for both databases

ENV SRC_HOST=

ENV SRC_PORT=

ENV SRC_USER=

ENV SRC_PWD=

ENV SRC_DB=

Setting up the working directory

ENV MIGRATIONDIR=/opt/data_migration

RUN mkdir -p $MIGRATIONDIR

We install several tools to work with the client

RUN apt-get update; apt-get install perl netcat -y; \

apt-get install wget unzip postgresql-client -y

Install Kettle to start migration

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

Installing jTDS to connect SQL Server in a Kettle 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

Setting up a .sh script to launch Kettle jobs

COPY ./scripts /scripts

RUN chmod +x /scripts/*.sh

WORKDIR $MIGRATIONDIR

Setting up the migration script

First let's run bash

#!/bin/bash

Let's configure it to fail if its commands are not followed.

set -e

Converting a SQL Server Schema to PostgreSQL Scripts

echo !!! Creating Kettle job && \

./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 $DST_HOST -pp $DST_PORT -pu $DST_USER -pw $DST_PWD -pd $DST_DB

Kettlejob files will be stored in $MIGRATIONDIR/kettlejobs/migration.kjb.

We run the before.sql script containing the Postgres SQL commands used to create the table structure

Using PostgreSQL SSL certificates for authentication (note the parameters sslmode, sslrootcert, sslcert And sslkeynecessary for this)

echo !!! Executing before.sql && \

# restricting access to key file as per psql requirements:

chmod 0600 conf/client-key.pem && \

PGPASSWORD=$DST_PWD psql -h $DST_HOST -p $DST_PORT -U $DST_USER -d $DST_DB -v sslmode=verify-ca -v sslrootcert=conf/server-ca.pem -v sslcert=conf/client-cert.pem -v sslkey=conf/client-key.pem -f before.sql

Using PostgreSQL login/password for authentication

echo !!! Executing before.sql && \

# restricting access to key file as per psql requirements:

chmod 0600 conf/client-key.pem && \

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

Executing the Kettle job

echo !!! Running Kettle job && \

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

Run the after.sql script

Using PostgreSQL SSL certificates for authentication (note the parameters sslmode, sslrootcert, sslcert And sslkeynecessary for this)

echo !!! Executing after.sql && \

PGPASSWORD=$DST_PWD psql -h $DST_HOST -p $DST_PORT -U $DST_USER -d $DST_DB -v sslmode=verify-ca -v sslrootcert=conf/server-ca.pem -v sslcert=conf/client-cert.pem -v sslkey=conf/client-key.pem -f after.sql

Using PostgreSQL login/password for authentication

echo !!! Executing after.sql && \

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

Creating a Docker container image 

docker build -t sqlserver2psql .

Export a schema using SQL Server Management Studio

  1. In SQL Server Management Studio, right-click on the source database. Then select Tasks>Generate Scripts.

  2. Select the tables to export and click the button Next.

  3. On the panel Set Scripting Options select Save as a script file> Single script file, to save the schema.sql file in /conf (create such a file if necessary). Check the box Unicode text. Press Next.

  4. On the overview page, click the button Next. Once the process is complete, click Finish.

  5. Place the file on the server running sqlserver2pgsql.

Uploading certificates for PostgreSQL authentication using SSL

Download the certificates and copy them to /conf.

The certificate files must be named: server-ca.pem, client-cert.pem and client-key.pem.

Migrating from SQL Server to PostgreSQL

docker run --name sqlserver2psql --rm -e SRC_HOST=<SQL Server host> \

  -e SRC_USER=<SQL Server username> -e SRC_PWD="<SQL Server password>" \

  -e SRC_DB=<SQL Server db name> -e DST_HOST=<PostgreSQL host> \

  -e DST_PORT=5432 -e DST_USER=<PostgreSQL username> \

  -e DST_PWD=<PostgreSQL password> -e DST_DB="<PostgreSQL db name>"

  --mount type=bind,source="$(pwd)"/conf,target=/opt/data_migration/conf \

  sqlserver2psql /scripts/migrate.sh

Conclusion

SQL Server and PostgreSQL are powerful and popular databases among users around the world, and some users may want to convert their SQL Server database to PostgreSQL for greater freedom and cost savings (see how migrate from PostgreSQL to SQL Server).

This article demonstrated two ways to migrate databases using SSIS and the open source tool sqlserver2pqsql.

Also, don't forget to back up your database before and after the migration, so you have a copy in case of failure or disaster.

In conclusion, we invite everyone to attend an open lesson on March 26 at 20:00, where we will review automated migration tools from MS SQL Server to PostgreSQL. You can sign up link.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *