Database migration from SQL Server to PostgreSQL

Microsoft SQL Server and PostgreSQL are both very popular RDBMS having rich set of administration tools and programming APIs.Important disadvantage of MS SQL is restrictive licensing policy along with high total cost of ownership.At the same time PostgreSQL comes with liberal license like BSD or MIT and ever better it is an open-source software. This is the key reason explaining why many organizations migrate their databases from SQL Server to PostgreSQL or compatible could solutions.

Database migration from SQL Server to PostgreSQL is usually implemented using design pattern known as extract-transform-load that includes the following steps:

  • Definitions of SQL Server tables are extracted as data definition language(DDL) statements
  • These DDL queries must be transformed to comply with the destination format and then load into PostgreSQL database
  • SQL Server data is exported as comma separate(CSV) files
  • Extracted data is converted to comply with the destination format (especially dates and binary data) and then imported into PostgreSQL
  • Database logic entries such as views, stored procedures, functions and triggers are exported from SQL Server database in form of CREATE-queries and the source code
  • The resulting queries and source are converted according to the target SQL dialect and imported into PostgreSQL database

Obviously, the procedure of migrating database from SQL Server to PostgreSQL requires many efforts when doing it manually. Moreover,it is implied high risk of data loss or corruption due to possible human errors, that is why many database experts recommend using dedicated software for automating the database migration.

SQL Server to PostgreSQL converter developed by Intelligent Converters is one of such database migration tools. It has extremely high performance due to direct reading and writing data without any middleware libraries or components. This migration tool works all versions of SQL Server and PostgreSQL including popular forks and cloud solutions such as Azure. Command line version of the tool may help automate and schedule database migration.The converter not only migrates MS SQL data into new database but also can merge or synchronize it with existing PostgreSQL database.

For purpose of filtering data to migrate, the tool provides capability of migrating result of SELECT queries as if it would be a regular table. This advanced method may be used to select individual columns and records, merge multiple SQL Server tables into single one, preprocess the data before converting it into PostgreSQL format.

Sometimes, migration projects may require customization of PostgreSQL tables structure. For this purpose, SQL Server to PostgreSQL converter supports custom column mapping.Using this feature users can modify column name, type and other attributes as well as exclude it from migration.

For those situations when PostgreSQL server or cloud instance declines remote connections, SQL Server to PostgreSQL migration tool supports export of the source data into a local script file in form of SQL statements to create tables, indexes, constraints and import the data. The resulting script file can be imported into PostgreSQL database using standard client tools.

Having all features specified above, SQL Server to PostgreSQL converter appears to be easy to use solution to automate database migration with just a few clicks. At the same time, it is powerful enough to migrate large and complicated corporate scale databases in the most intelligent way. The converter comes with free demo version that migrates not more than 50 rows per table and does not migrate foreign keys and views. Demo version allows users to understand if the migration tool fits their needs.


Back To Top