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 restrictions, 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 complete, secure backup solution for managing 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 on Github .
It should work on Linux, Windows and any Unix system.
Method 1: Migrate from SQL Server to PostgreSQL using SSIS
- Install PostgreSQL ODBC Driver for Windows x86/x64.
- In SQL Server Management Studio, right-click on the source database and select Tasks → Export Data .
- Select SQL Server Native Clients as the data source and .Net Framework Data Provider for ODBC as the destination driver.
- 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;
- 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 sslmode, sslrootcert, sslcertand parameters sslkeyrequired 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 sslmode, sslrootcert, sslcertand parameters sslkeyrequired 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
- In SQL Server Management Studio, right-click on the source database. Then select Tasks → Generate Scripts .
- Select the tables to export and click Next .
- In the Set Scripting Options panel, select Save as a script file → Single script file to save the schema.sql file to <project root directory>/conf (create one if necessary). Check the Unicode text box . Click on Next .
- On the overview page, click Next . Once the process is complete, click Finish.
- Place the file on the server running sqlserver2pgsql.
Uploading certificates for PostgreSQL authentication using SSL
Download the certificates and copy them to <project root>/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.
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.