How to Convert SQL Server to Postgres

Based on the expertise of Intelligent Converters specialists gained from a variety of migration projects, this whitepaper reveals best practices, key bottlenecks, and some tips and tricks for migrating databases from SQL Server to Postgres.

Database migration projects typically include these general stages:

1. Evaluation of every particular object in the source database, including data types, attributes, and built-in functions.
2. Considering the differences between the two DBMSs, determine the most effective and appropriate way to move all of that metadata from SQL Server to PostgreSQL. Use the source database as a reference.
3. Examine the optimal data migration strategy in light of the SQL Server system’s capacity to withstand overhead and outages. Use the most efficient method measured in terms of downtime to migrate the data.
4. Convert all SQL Server database logic entries into PostgreSQL format, including stored procedures, functions, triggers, and views.
5. Perform functional and performance tests to verify the resulting database’s stability and effectiveness. Check that every conversion was completed correctly, and make any required performance adjustments.

The most crucial steps of the database migration are preciously described below.

Table Definitions

Type mapping, default value conversion, and other relevant characteristics for every table column being migrated are all included in the SQL Server to Postgres table definition migration. Most of the basic data types are similar in these two DBMS, for instance: BIGINT, DATE, DECIMAL, INT, MONEY, NUMERIC, REAL, SMALLINT, and TEXT. Nonetheless, the following distinct types also need to be securely converted from SQL Server to Postgres:

Additionally, the spatial types GEOGRAPHY and GEOMETRY offered by SQL Server require the installation of the unique PostGIS extension in order to function in PostgreSQL.

The IDENTITY attribute for integer columns in SQL Server may be changed to a Postgres SERIAL for INT or BIGSERIAL for BIGINT when the seed and increment parts are equal to 1. In other cases, IDENTITY attribute must be kept in Postgres table that is supported in versions starting from 10. For example, SQL Server table declared as:

CREATE TABLE Logs(

     Id INT NOT NULL IDENTITY(2,4) PRIMARY KEY,

     Msg VARCHAR(200)

);

must be converted according to Postgres syntax as follows:

CREATE TABLE Logs(

     Id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY

(START WITH 2 INCREMENT BY 4) PRIMARY KEY,

     Msg VARCHAR(200)

);

Migration of Data

With a few exceptions, converting data from SQL Server to Postgres is generally a fairly simple process. Most SQL Server binary data have been moved to Postgres BYTEA, but large data (greater than 10MB) calls for a different strategy. This is because Postgres does not support piecewise reading for BYTEA data, which can only be extracted as a single fragment. As a result, reading large amounts of BYTEA data could result in a large RAM overhead.

Thankfully, PostgreSQL offers an alternate method for storing large binary data, called the LARGE OBJECT, which allows for stream-style data access. The values of LARGE OBJECTS are kept in a unique internal table called ‘pg_largeobject’ that can store up to 4 billion records. Besides, LARGE OBJECT allows piecewise reading that is a workaround for BYTEA limitations.

Another thing to consider when migrating data is spatial data. It needs to be moved using well-known text (WKT), a unique text representation.

Data Migration Techniques

When migrating large databases, one of the most crucial questions is how to avoid unacceptably high overhead or system outages. Generally speaking, there are three recognized methods for data migration:

• The simplest approach is snapshot migration, which necessitates that all data be moved in a single transaction. It goes without saying that this strategy might result in necessary database downtime while reading data in order to guard against data loss or corruption.
• Piecewise Snapshot Migration divides the data into separate parts and uses parallel threads or processes to migrate those parts at the same time. When compared to the snapshot method, the downtime is greatly decreased by doing this. For the majority of migration projects, Intelligent Converter specialists employ piecewise snapshot migration.
• Continuous data migration based on monitoring small changes and only replicating updated data is known as “Changed Data Replication” (CDR). Because it handles the smallest amount of data per transaction, this technique enables the source system’s downtime to be nearly zero.

Database migration best practices demand a thorough assessment of the project’s needs in order to select the best approach that strikes a balance between allowable downtime, overhead, and efficiency. It makes sense to use specialized software to convert SQL Server to Postgres automating and streamlining the migration process. This product offers safe type mapping, allows for deep process customization in accordance with customer needs, and implements the piecewise snapshot method of data migration (customize migration rules, edit the target table definition, filter data for migration, etc.)

T-SQL Code Migration

This section covers SQL Server to Postgres migration of the stored procedures, functions, and triggers.

Types casting. Postgres has essentially more strict type casting compared to SQL Server. It requires explicit casting to the proper data type in function calls, operators or SQL-statements. Do not forget to include type casting operators ‘::type’ wherever it is required.

Triggers. When converting triggers from SQL Server to Postgres, there is a problem that needs to be fixed. In PostgreSQL, the trigger must call a function that contains all of the trigger’s logic, whereas in SQL Server, the trigger’s source code is contained within the CREATE TRIGGER statement. For instance:

CREATE OR REPLACE FUNCTION employees_on_update_func()

  RETURNS TRIGGER

  LANGUAGE PLPGSQL

  AS

$$

BEGIN

     IF (NEW.first_name <> OLD.first_name) OR (NEW.last_name <> OLD.last_name) OR (NEW.email <> OLD.email)

THEN

            INSERT INTO changes_log(id,changed_on)

            VALUES(OLD.id,now());

     END IF;

     RETURN NEW;

END;

$$

CREATE TRIGGER employees_on_update

  BEFORE UPDATE ON employees FOR EACH ROW

  EXECUTE PROCEDURE employees_on_update_func();

Built-in Functions. Important part migrating T-SQL code to Postgres is replacement of all built-in functions of SQL Server missing in the target DBMS. The table below contains safe equivalents of the most common functions:

Conclusion

For large databases, converting SQL Server to Postgres can be a challenging process that takes a lot of time and work. Each of its five logical phases has subtleties of its own. Every project has its own set of difficulties, and this article only covers a few of the database migration issues. Intelligent Converter experts are always available to assist you with any database migration task.