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. However, MS SQL has quite restrictive licensing policy and high total cost of ownership while PostgreSQL is open source released under Open Source Initiative Approved License.
Shall we consider this economic aspect more precisely. License prices of SQL Server are ranged from $3K for the Standard Edition to more than $10K for the Enterprise edition (two cores). A free version of this database management system is only available to students and developers for internal using. The use of PostgreSQL is free even for any commercial purpose.
That reason forces many organizations migrate their databases from SQL Server to PostgreSQL. However, before beginning this complicated procedure it is reasonable to compare two DBMS to find all possible bottlenecks of the migration.
Scalability
In terms of scalability SQL Server provides enhancements to the on-disk storage for memory-optimized tables. It supports running multiple concurrent threads to make memory-optimized tables more persistent, multi-threaded approach to recovery and merge operations. PostgreSQL implements scalability though indexing and partitioning options to improve data operations and query performance on table QL Server supports computed columns with a number of limitations: it cannot be used as as.
Table scalability can be greatly improved since partitions and Indexes can be placed in separate tablespaces on different disk file systems. Horizontal table partitioning is not supported in PostgreSQL.
Computed Columns
Although SQL Server supports computed columns, this feature has some limitations. It cannot be used as a part of DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. Also, computed columns cannot be used in INSERT or UPDATE statements in MS SQL. Since version 12 PostgreSQL also supports computed columns that must be declared by the term ‘generated columns’. Generated columns cannot be part of a partition key; they can only reference the current row and cannot use subqueries. Generated values cannot be included INSERT or UPDATE, however DEFAULT constraint is accepted.
Spatial Data
SQL Server provides ‘geography’ data type for storing spatial data. PostgreSQL does not have native support for spatial data. The 3rd party open-source PostGIS resource can be used to support for spatial objects.
Identity Columns
SQL Server uses identity column property for generating unique key values for rows. Identity declaration includes initial value for the first row (seed) and amount to increase value over the previous row (increment). PostgreSQL provides a special data type SERIAL that allows to assign a unique value to an identity column automatically. Unique constraint or primary key over SERIAL column must be specified just like for other data types.
Views
SQL Server provides user-defined and system-defined views to restrict access to the data. Views can be automatically updated using triggers. Materialized views are called ‘Indexed Views’ in SQL Serves. Unlike materialized views in other RDBMS, indexed views are synched to the underlying data and updated automatically.
PostgreSQL supports generic and updatable views. Views created with simple queries (without recurrency, subqueries, etc.) can be updated; complex views can be updated using rules. The DBMS also supports materialized views. To update data in materialized views the statement ‘REFRESH MATERIALIZED VIEW’ can be used.
Migration Tools
Differences between SQL Server and PostgreSQL listed above prove that migration between the two database management systems requires many efforts when doing it manually. Human factor leads to high risk of data loss or corruption caused by migration errors. This issue can be resolved using special database conversion software.
One of such conversion tools is SQL Server to PostgreSQL developed by Intelligent Converters, company specializing in database migration and synchronization between all popular database management systems like MS SQL, PostgreSQL, Oracle, MySQL and others. The converter has extremely high performance due to direct importing and exporting data without any middleware libraries or components. It supports all modern versions of SQL Server and PostgreSQL including cloud solutions.
Command line support is a powerful option to automate the database migration.
If Postgres server does not allow remote connection, the program can convert SQL Server database into local script file containing SQL statements to create tables, indexes, constraints and to insert rows. Then the person responsible for database migration can load that file into PostgreSQL database via standard tools like psql or pgAdmin.
