keyboard_backspaceBack to main blog page

businessmarket

Ever since the first crypto currency, Bitcoin, was launched - numerous digital currencies have been launched to date.

Migrate from SQL Server to PostgreSQL

Anna Johnson Anna Johnson , 3/24/2018
This content has not been rated yet.

When comparing Microsoft SQL Server and PostgreSQL it is important to understand that both RDBMS are very popular and have huge collection of administration and development tools. The main advantage of PostgreSQL towards MS SQL is less restrictive licensing policy and open source. And this is the reason why many companies migrate from SQL Server to PostgreSQL.

Migration from MS SQL to PostgreSQL can be organized in these steps:

-          Extract table definitions from SQL Server database with corresponding indexes and constraints in form of CREATE-statements

-          Translate these statements into PostgreSQL format and load into the database

-          Export SQL Server data into comma separated values (csv) files

-          Make the data complied with PostgreSQL format and import it into the database

-          Extract MS SQL views, stored procedures and triggers in form of SQL statements and source code

-          Translate these statements and source code into PostgreSQL format and load it into the database

Even this brief algorithm indicates that database migration from SQL Server to PostgreSQL is quite complicated procedure requiring a lot of resources. Also there is high risk of data loss or corruption due to the human factor when migrating manually. It is reasonable to use special software in order to simplify and automate migration process especially for large and complicated databases.

One of the tools designed to migrate databases from SQL Server to PostgreSQL is offered by Intelligent Converters, software provider specializing in field of database conversion, migration and synchronization since 2001. The program converts table definitions, data, indexes, constraints and views with extremely high performance due to efficient approach to reading and writing data. It supports all versions of MS SQL and PostgreSQL including cloud variations such as Azure SQL and Heroku. SQL Server to PostgreSQL converter supports command line in order to automate, to script and to schedule database migration. The program allows not only converts MS SQL data into new database but also merge or synchronize it with existing PostgreSQL database.

If there is need to convert partial data only, the tool can filter data via SELECT queries for this purpose. This feature allows to select particular columns, apply restrictions to the data and even transform it before migrating to PostgreSQL.

Some migration projects require to change column type, name or other attributes in the resulting database. SQL Server to PostgreSQL converter provides custom column mapping feature for these purposes. It is implemented as a dialog to specify new name, type, default value and NULL-attribute for any column and to exclude certain columns from migration.

For situations when there is no direct connection to PostgreSQL server, the program can export the SQL Server database into SQL script. This approach provides exporting source database into a local script file with SQL-statements to create tables, indexes, constraints and the data. Later the person responsible for database migration can import this script file into PostgreSQL database via standard tools like psql or pgAdmin.