How to Migrate SQL Server to PostgreSQL in 2 Ways?

As one of the most popular databases, SQL Server is famous for its easy installation and configuration, secure features like encryption, excellent data recovery, and other conveniences.

However, some limitations of SQL Server intimidate current users. SQL Server has growing restrictive licensing and maintenance costs of ownership as the database size or the number of clients increases. And its maximum size is 10 GB and 1MB buffer cache. It only runs on Windows.

Users of SQL Server may be attracted to the completely free and open-source database PostgreSQL. The database is backed by a global community and available on Windows, Mac, Linux, FreeBSD, and Solaris. It also benefits from open-source add-ons.

In this post, I’ll start by introducing 2 free tools to migrate SQL Server to PostgreSQL, then talk about how to do the migration between the two databases, and then indicate a complete database protection backup solution for multi-database management.

Database Migrations Tools:

1.    Pgloader

Pgloader is a data loader program that migrates MS SQL Server to PostgreSQL. The tool discovers schema (primary key, foreign keys constraints, and indexes build) automatically, and provides casting rules to convert SQL Server data type to that of PostgreSQL.

2.    Sqlserver2pgsql

Sqlserver2pgsql is another open-source migration tool written in Perl, which converts the schema of SQL Server to PostgreSQL and translates all SQL Server data to PostgreSQL with a produced Pentaho Data Integrator (Kettle). It doesn’t migrate stored procedures though. This tool is better to migrate a database offline. Install it from Github.

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

Way 1: Migrate SQL Server to PostgreSQL with SSIS

1.     Install PostgreSQL ODBC Driver for Windows in terms of x86/x64.

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

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

4.     Set the connecting string to the database. Add a data source name that matches the value set for Driver= {your-data-source-name} 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;

5.     Choose the source tables. Free to edit the default mapping of SQL Server.

Way 2: Migrate SQL Server to PostgreSQL with Sqlserver2pgsql

Prerequisites:

• Connection details to SQL Server including IP, port, username, password, names of the database, and instance (if not default).

• Access to an empty target PostgreSQL database.

Build a Docker image

A Docker is an excellent tool for developing a database migration procedure that is cloud agnostic and can be applied to most IT architecture.

Build the image on the OpenJDK 8 that builds and runs Java 1.8 apps.

FROM adoptopenjdk/openjdk8

Define some environment variables for both databases

ENV SRC_HOST=

ENV SRC_PORT=

ENV SRC_USER=

ENV SRC_PWD=

ENV SRC_DB=

Configure the working directory

ENV MIGRATIONDIR=/opt/data_migration

RUN mkdir -p $MIGRATIONDIR

Install some cli tools

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

apt-get install wget unzip postgresql-client -y

Install Kettle to run the 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

Install jTDS to connect SQL Server in the 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

Configure .sh script to run the Kettle job

COPY ./scripts /scripts

RUN chmod +x /scripts/*.sh

WORKDIR $MIGRATIONDIR

Configure migration script

First, bash to run

#!/bin/bash

Set it to fail if its commands fail

set -e

Convert 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

The Kettles job files will be stored at $MIGRATIONDIR/kettlejobs/migration.kjb.

Run before.sql script that enclose the Postgres SQL commands used to generate the table structure

Using SSL certs for PostgreSQL authentication (notice the sslmode, sslrootcert, sslcert, and sslkey parameters required for that)

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 username/password for PostgreSQL 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

Run the Kettle job

echo !!! Running Kettle job &&

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

Run after.sql script

Using SSL certs for PostgreSQL authentication (notice the sslmode, sslrootcert, sslcert, and sslkey parameters required for that)

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 username/password for PostgreSQL authentication

echo !!! Executing after.sql &&

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

Create a Docker container image

docker build -t sqlserver2psql .

Export schema with SQL Server Management Studio

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

2.     Choose the tables to export, and click Next.

3.     In the Set Scripting Options pane, choose Save as a script file> Single script file to save a schema.sql file under <project root dir> / conf (if necessary, create one). Check the Unicode text box. Click Next.

4.     On the review page, click Next. And click Finish after the process.

5.     Get the file on the server where sqlserver2pgsql runs.

Download certs for PostgreSQL authentication using SSL certs

Download the certs and copy them to <project root dir>/conf.

Cert files should be with names: server-ca.pem, client-cert.pem and client-key.pem.

Migrate 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

Multi-database management tool for SQL Server and PostgreSQL

Whether you are running under SQL Server, PostgreSQL, or a multi-database environment, you can trust Vinchin Backup & Recovery for their automatic backup, full recovery, anti-malware protection, and centralized management.

Vinchin Backup & Recovery provides multiple backup types (full, incremental, differential, achieve log, and transaction log backup that changes with databases) under self-defined repeated schedules for 6 databases including Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB, 12 virtualizations, physical servers, and NAS over one web console.

You can save your storage and cost by duplicating and compressing database data and keeping those data per restore point/day for automatic data management. You can also keep an offsite backup copy in a remote location for security.

Besides that, recover databases simply to the old or a new database in 4 steps.

Care for a test? Download the 60-day free trial of the Enterprise version with anti-ransomware features now.

Conclusion

SQL Server and PostgreSQL are both powerful and popular databases among global users, and some may want to convert SQL Server to PostgreSQL for unrestrictive version and cost saving (See how to migrate PostgreSQL to SQL Server).

Here in this article, 2 ways are demonstrated for database migration between the two using SSIS and open-source tool sqlserver2pqsql.

Although, database backup is always necessary before and after the migration to save another copy in case of failure or disaster.