From SQL Server to PostgreSQL

This article is a summary of the experience obtained by specialists of Intelligent Converters in numerous projects of database migration from SQL Server to PostgreSQL.

Best Practices

Best practice of planning and running the database migration should include the following necessary steps:

1. Assessment. Review the architecture, performance and system requirements of the source DBMS. Estimate the complexity of migration, forecast the timeline and ROI.
2. Database Inventory. This is a logical extension of the previous step dedicated to gathering detailed information about SQL Server database objects that require special attention such specific data types and system functions. 
3. Preventive Troubleshooting. Analyze the key differences between the source and target database management systems, paying attention to the most challenging issues and dependencies.
4. Planning. Find the most effective approaches and solutions for SQL Server to PostgreSQL migration. Explore the best practices, use cases and guidelines to follow.
5. Migration. Run the database migration using the best method in terms of downtime and overhead.
6. Post processing. Some entries of the database logic may require manual efforts to convert from to PostgreSQL format, since it can hardly be fully automated. Those entries are: views, stored functions, procedures and triggers. 
7. Testing. Check stability and efficiency of the resulting database by running tests on capability and performance. Validate migration of every database entry and make all required adjustments when it is necessary.

Main stages of SQL Server to PostgreSQL database migration are explored below. 

Table Structure

Migration of table structures includes safe type mapping, conversion of default values and other necessary properties for every column. SQL Server and PostgreSQL have similar set of built-in data types. However, some types having no direct equivalent in the target DBMS have to be safely mapped from SQL Server to PostgreSQL:

• BINARY(n) is converted into BYTEA
• BIT is converted into BOOLEAN
• CHAR(n), VARCHAR(n), VARCHAR(max), NCHAR(n), NVARCHAR(n) and NVARCHAR(max) are converted into TEXT when n>8000
• DATETIME is converted into TIMESTAMP(3)
• DATETIME2(n) is converted into TIMESTAMP(n)
• DATETIMEOFFSET(n) is converted into TIMESTAMP(n) WITH TIME ZONE
• FLOAT(n) is converted into DOUBLE PRECISION
• IMAGE is converted into BYTEA
• NTEXT is converted into TEXT
• ROWVERSION is converted into BYTEA
• SMALLMONEY is converted into MONEY
• UNIQUEIDENTIFIER is converted into UUID
• VARBINARY(max) is converted into BYTEA

Note that support for spatial data types such as GEOGRAPHY and GEOMETRY in PostgreSQL requires installation of PostGIS extension. 

SQL Server INT and BIGINT columns having IDENTITY attribute are converted into Postgres SERIAL and BIGSERIAL correspondingly. 

Data Migration 

Data migration from SQL Server to PostgreSQL is a quite simple process with a few exceptions. For instance, SQL Server binary data is usually converted to Postgres BYTEA, but data larger than 10MB requires different strategy. The key reason is that BYTEA is always processed as a single data chunk, PostgreSQL does not provide piecewise reading and writing for this type. Obviously, extracting large amounts of data in a single chunk may cause unacceptable RAM overhead.

On the other hand, PostgreSQL provides LARGE OBJECT concept of storing huge binary data offering the streaming data access. LARGE OBJECTS values are serialized in the dedicated system table ‘pg_largeobject’. Capability of piecewise reading allows LARGE OBJECT to breaks size limitations applied to BYTEA.

Another bottleneck of SQL Server to PostgreSQL migration is spatial data. Since internal binary representation of these types in distinguished in the two DBMS, it has to be migrated through special kind of text representation called ‘well-known text’ (WKT). 

Best practices of data migration require thorough assessment of the database particularities and the related project’s demands to choose the most suitable approach that reaches acceptable balance between downtime, overhead and efficiency. 

Main Methods Commonly Used to Migrate Data 

• snapshot migration – reading and writing thorough data in a single transaction
• piecewise snapshot – similar to the previous method except of splitting the source data into multiple chunks and migrating each of them in a separate transaction
• continuous migration – scanning every single modification of the source database via CDR and replicating the updated data only

It makes sense to automate SQL Server to Postgres data migration and simplify the overall process with just a few mouse clicks through the dedicated software tools. 

Migration of Database Logic

Here we discuss primary challenges that are applied to SQL Server to PostgreSQL migration of the stored functions, procedures and triggers.

Unlike SQL Server, PostgreSQL has strict limitations to type casting operation, for instance it does not allow implicit types conversion. It requires direct type casting for table columns in SQL-statements, variables in function calls, operators or expressions. Therefore do not forget to use related operator ‘::type’ wherever it is necessary.

Triggers. SQL Server allows the entire trigger’s body is defined inside the CREATE TRIGGER statement, while PostgreSQL requires trigger to call a function that contains all the source code. That is what needs to be fixed while migrating triggers from SQL Server to PostgreSQL.

Built-in Functions. Important part of database logic migration is converting of SQL Server built-in functions of missing in the destination database management system. Safe conversion for the most common functions is given below.

1. CHARINDEX($substr, $str, $start_pos) is converted into POSITION($substr IN SUBSTRING($str, $start_pos) + $start_pos – 1
2. DATEADD($interval, $n_units, $date) is converted into $date + $n_units  * interval ‘1 $interval ’, where $interval can be ‘second’ or ‘minute’ or ‘hour’ or ‘day’ or ‘month’ or ‘year’
3. DATEDIFF($interval, $date1, $date2) is converted into DATE_PART($interval, $date2 – $date1), where $interval can be ‘second’ or ‘minute’ or ‘hour’ or ‘day’ or ‘month’ or ‘year’
4. IIF($condition,$expr1,$expr2) is converted into CASE WHEN $condition THEN $expr1 ELSE $expr2 END

You Must Read: How Can Beginners Find Effective Spanish Tutoring Classes Online

Conclusion

SQL Server to PostgreSQL database migration is a challenging task demanding lot of time and efforts. Every migration project is unique and has its own bottlenecks, while this whitepaper explores just a few of possible issues. Specialists of Intelligent Converter are ready to help with database migration of any complexity level.