This whitepaper exposes tips and tricks, the most important bottlenecks and best practices of SQL Server to Postgres database migration based on experience of Intelligent Converters specialists collected on wide range of migration projects.
Usually, database migration projects consist of these generic phases:
- Assessment of all specific objects such as data types, attributes, built-in functions in the source database.
- Find the most efficient and suitable option to migrate all those metadata from SQL Server to PostgreSQL with respect to differences between the two DBMS. Run it against the source database.
- Explore the best method of data migration in terms of SQL Server system’s tolerance to the downtime and overhead. Migrate the data using that approach.
- Translate all entries of SQL Server database logic such as stored procedures, functions, triggers and views into the PostgreSQL format.
- Check stability and efficiency of the resulting database by running performance and functional tests. Verify that all conversions have been made properly, adjust the performance when it is necessary.
Below the most important steps are explained in details.
Table Definitions
SQL Server to Postgres migration of table definitions includes types mapping, conversion of default values and other related attributes for each table column being migrated. These database management systems have similar set of data types. For example, types BIGINT, DATE, DECIMAL, INT, MONEY, NUMERIC, REAL, SMALLINT, TEXT are common for both DBMS. However, there are also distinguished types that must be safely mapped from SQL Server to Postgres as follows:
| SQL Server | Postgres |
| BINARY(n) | BYTEA |
| BIT | BOOLEAN, BOOL |
| CHAR(n) where n>8000 | TEXT |
| DATETIME | TIMESTAMP(3) |
| DATETIME2(n) | TIMESTAMP(n) |
| DATETIMEOFFSET(n) | TIMESTAMP(n) WITH TIME ZONE, TIMESTAMPTZ |
| FLOAT(n) | DOUBLE PRECISION, FLOAT8 |
| IMAGE | BYTEA |
| NCHAR(n) where n>8000 | TEXT |
| NTEXT | TEXT |
| NVARCHAR(max) | TEXT |
| ROWVERSION | BYTEA |
| SMALLMONEY | MONEY |
| UNIQUEIDENTIFIER | CHAR(16), UUID |
| VARBINARY(max) | BYTEA |
| VARCHAR(max) | TEXT |
Spatial types GEOGRAPHY and GEOMETRY requires installation of the special extension PostGIS when migrating from SQL Server to PostgreSQL.
IDENTITY attribute of SQL Server integer columns is another point of attention. If it has seed and increment parts equal to 1, the correct conversion to Postgres is SERIAL for INT or BIGSERIAL for BIGINT. In PostgreSQL versions starting from 10 IDENTITY can be converted with respect to syntax of the DBMS. For example, SQL Server table declared as:
CREATE TABLE Logs(
Id INT NOT NULL IDENTITY(2,4) PRIMARY KEY,
Msg VARCHAR(200)
);
must be migrated to Postgres 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)
);
Data Migration
SQL Server to Postgres data migration is a straight forward process that can be implemented according to extract-transform-load approach. One bottleneck that requires attention is migration of binary data. Binary data in commonly migrated from SQL Server to Postgres BYTEA. However, huge data with size exceeding 10MB must be transferred using another technique since BYTEA can be extracted as a single fragment only and reading by fragments is not allowed for this data type. Therefore, migrating huge volume of data through BYTEA may cause critical RAM overhead.
PostgreSQL offers alternative approach to storing binary data that supports stream-style access to the data. This approach is known as the LARGE OBJECT and it is implemented by storing data in the service table called ‘pg_largeobject’ with capacity up to 4 billion rows and maximum size of each object 4TB. Due to support of piecewise reading LARGE OBJECT approach can resolve BYTEA limitations.
Another bottleneck of SQL Server to Postgres data migration is spatial data. SQL Server spatial types geography and geometry must be migrated through the special text form called “well-known text” (WKT). Assume there is SQL Server table defined as follows:
CREATE TABLE spatialdata(Id INT NOT NULL, data1 geometry, data2 geography)
Text representation of geometry and geography data can be extracted via built-in function STAsText:
SELECT Id, data1.STAsText(), data2.STAsText() FROM spatialdata
The resulting WKT-representation can be inserted in the target PostgreSQL table as follows:
INSERT INTO spatialdata VALUES (1,’POLYGON ((5 5, 10 5, 10 10, 5 5))’, ‘LINESTRING (-122.36 47.656, -122.343 47.656)’);
Methods of SQL Server to PostgreSQL Data Migration
The high priority goal of large database migration is to prevent unacceptable DBMS downtime or overhead. There are three common techniques of the data migration having their own cons and pros:
- Snapshot is the easiest to implement approach provided that all data is migrated in a single transaction. This may cause essential downtime of the source database during the reading stage to avoid data loss or corruption.
- Piecewise Snapshot method requires the data is split in fragments and those chunks are migrated simultaneously from parallel threads or processes. The downtime of this approach is significantly less than the previous option.
- Changed Data Replication (CDR) approach requires continuous replication of SQL Server data to PostgreSQL database provided by tracking changes and migrating the updated data only. CDR allows to achieve near-to-zero downtime of the source database since it migrates the minimal volume of data per transaction.
In order to guarantee smooth and safe SQL Server to Postgres database migration, it is necessary to implement the most suitable method that balances downtime and efficiency. For this purpose, people responsible for the migration project should careful assess all the requirements and particularities of the source and target systems. Many professionals use special software to simplify and automate the database migration. One of these tools is SQL Server to Postgres converter produced by Intelligent Converters software company. Key features of the product:
- migrates schemas, table definitions, data, sequences, indexes, constraints and views with all related properties
- provides safe types mapping (including spatial, lob, xml)
- implements piecewise snapshot method of data migration
- allows to filter data for migration
- allows to customize migration rules and edit the target table definition
- supports all modern versions of on-premise and cloud SQL Server and PostgreSQL
Conclusion
Migration from SQL Server to Postgres is a complicated procedure consisting of five generic stages, each of them has its own nuances. This whitepaper explores only a few major issues of database migration, while every project is unique and has its own challenges. Specialists of Intelligent Converters are always ready to help you with any database migration project.
