Migrate SQL Server Database To PostgreSQL Database

6 min readOct 26, 2023

Migrating a SQL Server database to a PostgreSQL database is a complex task, and it’s important to plan and execute the migration carefully to ensure data integrity and minimal downtime. Here are the main topics you need to consider and solve when performing this migration:

If you want to migrate only tables, schemas, indexes, and views from SQL Server to PostgreSQL, you can streamline the migration process. Here’s a simplified approach for this specific scenario:

Schema Conversion:

Generate a script to extract the schema (tables, schemas, indexes, and views) from your SQL Server database. You can use tools like SQL Server Management Studio (SSMS) to script the database schema.

Data Migration:

Since you’re only migrating database objects (schema), you won’t need to worry about data migration.

Syntax Adjustments:

Review the generated SQL script to ensure that the SQL Server-specific syntax is compatible with PostgreSQL. You may need to make adjustments to account for differences in data types, constraints, and syntax.

Identity Columns and Sequences:

Modify the schema script to replace SQL Server identity columns with PostgreSQL sequences if needed.

Stored Procedures, Functions, and Triggers (Optional):

If you have any SQL Server-specific stored procedures, functions, or triggers associated with the tables and views, you’ll need to either rewrite them in PostgreSQL syntax or exclude them from the migration.

Testing and Validation:

Test the PostgreSQL schema script on a copy of your PostgreSQL database to ensure that it creates the tables, schemas, indexes, and views without errors.

Execution:

Execute the modified schema script in your PostgreSQL database to create the required tables, schemas, indexes, and views.

Validation:

Verify that the tables, schemas, indexes, and views have been created correctly in PostgreSQL.

Migration — Pgloader Tool

If you prefer a simplified and efficient way to migrate an SQL Server database to PostgreSQL without the need to consider all the manual adjustments and complexities, you can give the tool called pgloader a try. pgloader is a robust data migration tool specifically designed for transferring data between different database systems, including SQL Server and PostgreSQL. It automates much of the migration process, allowing you to focus on the high-level configuration and setup. `pgloader` can handle schema conversion, data migration, and various data type transformations, making it a valuable option for a smooth transition to PostgreSQL. While it’s important to understand the underlying processes for more complex migrations, `pgloader` can significantly simplify the process for those who prefer a more straightforward approach.

Certainly, here’s a mini list of steps that you can include in your Medium blog post for using `pgloader` to migrate a SQL Server database to PostgreSQL:

Install pgloader:

Begin by installing pgloader on your system. It’s a versatile tool that simplifies database migrations, and you can typically install it using package managers like apt, yum, or by downloading it from the official website.

apt-get update -y && apt-get install pgloader -y

Prepare Migration Configuration:

Prepare a configuration file that defines the source (SQL Server) and target (PostgreSQL) databases, as well as the rules for mapping data types and specifying any required transformations. This configuration file, often referred to as a `.load` file, serves as the blueprint for the migration process.

cat sqltopg.conf
load database
from
mssql://sa:Test123.test123@172.17.0.2/sql_source_Db
into postgresql://mig_user:test123@172.17.0.3:5432/dest_db;

Execute pgloader:

With the configuration file in place, execute `pgloader` with the provided configuration to start the migration process. `pgloader` will automatically read the rules and settings from your `.load` file and begin moving data from SQL Server to PostgreSQL.

pgloader sqltopg.conf

You have to check errors,rows and total time to understand if there is a problem in migration process.

These steps provide a concise guide for readers looking to use `pgloader` for their database migration needs. You can then expand on each step in your blog post, providing more detailed instructions and insights to ensure a smooth migration experience for your audience.

While pgloader is a powerful and valuable tool for migrating from SQL Server to PostgreSQL, it’s important to recognize that it may not address all migration scenarios. Each database migration can present unique challenges and specific requirements, and pgloader may not cover every possible use case. Therefore, the key is to understand the limitations of the tool and carefully assess your specific migration needs. It’s highly advisable to consult the official documentation, as it offers insights into the tool’s capabilities and limitations. Additionally, the documentation provides valuable guidance on how to configure and customize migration rules to best suit your database environment. By combining pgloader with a deep understanding of your data, schemas, and the unique characteristics of both SQL Server and PostgreSQL, you can create a tailored migration strategy that leverages the tool effectively while addressing any edge cases or complexities that may arise during the transition. Always remember that while pgloader can streamline much of the migration process, your comprehensive knowledge of your data and databases remains a crucial component of a successful migration.

Validation — SQL Server and PostgreSQL

In order to validate all tables are exits and correct in terms of definitions we can use the following queries in PostgreSQL and SQL Server

-- SQL Server
SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    type_name(c.user_type_id) AS DataType
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name NOT LIKE 'sys%'
ORDER BY TableName, c.column_id;


-- PostgreSQL

SELECT 
    table_name AS TableName,
    column_name AS ColumnName,
    data_type AS DataType
FROM information_schema.columns
WHERE table_name NOT LIKE 'pg_%' AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY TableName, ordinal_position;

After running these queries I got following result from source and destination environments.In both SQL Server and PostgreSQL, the NUMERIC and DECIMAL data types are used to store exact numeric values, and they are functionally equivalent. However, there are some differences in the way they are implemented and some subtle variations in behavior. Another point is that SQL Server and PostgreSQL, both TEXT and VARCHAR are used for storing character string data, and they are often used interchangeably. However, there are some differences in the way they are implemented and how they behave.

  tablename   |  columnname   |         datatype
--------------+---------------+--------------------------
 customers    | customerid    | integer
 customers    | firstname     | text
 customers    | lastname      | text
 customers    | email         | text
 customers    | phone         | text
 orderdetails | orderdetailid | integer
 orderdetails | orderid       | integer
 orderdetails | productid     | integer
 orderdetails | quantity      | integer
 orders       | orderid       | integer
 orders       | customerid    | integer
 orders       | orderdate     | timestamp with time zone
 products     | productid     | integer
 products     | productname   | text
 products     | price         | numeric
 products     | description   | text


|TableName|ColumnName|DataType|
|---|---|---|
|Customers|CustomerID|int|
|Customers|FirstName|varchar|
|Customers|LastName|varchar|
|Customers|Email|varchar|
|Customers|Phone|varchar|
|OrderDetails|OrderDetailID|int|
|OrderDetails|OrderID|int|
|OrderDetails|ProductID|int|
|OrderDetails|Quantity|int|
|Orders|OrderID|int|
|Orders|CustomerID|int|
|Orders|OrderDate|datetime|
|Products|ProductID|int|
|Products|ProductName|varchar|
|Products|Price|decimal|
|Products|Description|varchar|

Migrating a database from SQL Server to PostgreSQL is a complex and challenging endeavor that goes beyond simply moving tables and data. It requires a deep understanding of your database schema model, stored procedures, functions, data types, and query structures. The process demands meticulous planning and a thorough assessment of the existing system to ensure a seamless transition. While tools like pgloader can automate parts of the migration, a successful migration is about more than just copying data; it’s about maintaining performance, data integrity, and overall system maintainability. The differences between SQL Server and PostgreSQL in terms of syntax, features, and data types can pose significant obstacles, making it essential to identify potential issues in advance and develop strategies to address them. A successful migration not only ensures that your data is transferred accurately but also guarantees that your new PostgreSQL database is optimized for performance and provides a platform for sustainable, long-term database management.

References

If you ever have questions or need advice on database-related matters, feel free to reach out to me on LinkedIn, connect on Twitter or Superpeer.

Demir.