From SQL Server to PostgreSQL

Schema migration is the first step in the process of exporting a SQL Server database to PostgreSQL server. In SQL Management Studio the following steps must be done to export the schema:

• Right-click on the database, select “Tasks” and “Generate Scripts” menu items
• Select “Select specific database objects” and check only the application schema tables
• “Types of data to script” parameter of advanced options must be set to “Schema only”
• Review and save the database tables schema file, as for example tables.sql script
• Clone sqlserver2pgsql, a migration tool written in Perl, from GitHub to your database server
• Execute the following commands to convert the MS SQL schema into PostgreSQL format:

perl sqlserver2pgsql.pl -f tables.sql -b tables-before.sql -a tables-after.sql -u tables-unsure.sql

After these steps converted schemas will be written in the script tables-before.sql, and the statements to create all constraints will be written in the script tables-after.sql. Pay attention at the script tables-unsure.sql since it contains tables that were not converted by the tool automatically for some reason and make all necessary changes.

If you want to change any schema names in Postgres, rename them in the SQL file using a sed command:

$ sed -i ‘s/sql_server_schema/public/g’ *.sql.

Load the script tables-before.sql into your PostgreSQL database to create tables:

psql -U user_name -p 5432 -h localhost -d database_name -f tables-before.sql

Data migration through data dump and restore can be cumbersome, with manual processes and potential data mismatches. It is advisable to use existing stable systems instead of investing more time into it. For example, Pentaho provides a range of dependable data-centric products, one of which is Pentaho Data Integration (PDI) – an ETL tool that facilitates hassle-free migration of data between different databases without manual intervention. For the purpose at hand, the community edition of PDI suffices, as it can establish connections to both source and destination databases and migrate data from SQL server to Postgres database by executing a PDI job.

To get started, download the Pentaho Data Integration Community Edition and extract the tarball in your local environment. Run spoon.sh to launch the GUI application in your local environment, following these steps:

• Create a new job by navigating to File → New → Job.
• Create a source database connection by clicking on View in the left sidebar, right-clicking on ‘Database Connections’, selecting ‘New’, and providing SQL Server connection details
• Create a destination database connection by clicking on View in the left sidebar, right-clicking on ‘Database Connections’, selecting ‘New’, and providing Postgres connection details
• Select the ‘Copy Tables Wizard’ from the Tools → Wizard menu.
• Choose the source and destination databases.
• Select the list of tables to be migrated and the path for job and transformation files.
• The transformations will be created to copy data from the source to the destination database.

Final step of this data migration method is to execute PDI job using Pentaho kitchen tool on the database server. Execute the tables-after.sql schema-​migration script to apply constraints once the data migration is done.

If this approach seems too complicated or taking too much efforts, it is reasonable to consider commercial solutions for SQL Server to PostgreSQL database migration that can do all job with just a few button clicks. One of such solutions is SQL Server to PostgreSQL converter offered by Intelligent Converters vendor.